
CREATE TABLE appellations (
	"No" INTEGER, 
	"Appelation" TEXT, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT, 
	PRIMARY KEY ("No"), 
	UNIQUE ("Appelation")
)


CREATE TABLE grapes (
	"ID" INTEGER, 
	"Grape" TEXT, 
	"Color" TEXT, 
	PRIMARY KEY ("ID"), 
	UNIQUE ("Grape")
)


CREATE TABLE wine (
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT, 
	FOREIGN KEY("Grape") REFERENCES grapes ("Grape"), 
	FOREIGN KEY("Appelation") REFERENCES appellations ("Appelation")
)

CREATE TABLE appellations (
	"No" INTEGER, 
	"Appelation" TEXT, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT, 
	PRIMARY KEY ("No"), 
	UNIQUE ("Appelation")
)

/*
2 rows from appellations table:
No	Appelation	County	State	Area	isAVA
1	Alexander Valley	Sonoma	California	North Coast	Yes
2	Amador County	Amador	California	Sierra Foothills	No
*/


CREATE TABLE grapes (
	"ID" INTEGER, 
	"Grape" TEXT, 
	"Color" TEXT, 
	PRIMARY KEY ("ID"), 
	UNIQUE ("Grape")
)

/*
2 rows from grapes table:
ID	Grape	Color
1	Barbera	Red
2	Cabernet Franc	Red
*/


CREATE TABLE wine (
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT, 
	FOREIGN KEY("Grape") REFERENCES grapes ("Grape"), 
	FOREIGN KEY("Appelation") REFERENCES appellations ("Appelation")
)

/*
2 rows from wine table:
No	Grape	Winery	Appelation	State	Name	Year	Price	Score	Cases	Drink
1	Zinfandel	Robert Biale	St. Helena	California	Old Kraft Vineyard	2008	44	93	275	now
2	Zinfandel	Chiarello Family	Napa Valley	California	Giana	2008	35	93	480	now
*/
What is the average price for wines not produced in Sonoma county?
SELECT avg(price) FROM wine WHERE Appelation NOT IN (SELECT T1.Appelation FROM APPELLATIONS AS T1 JOIN WINE AS T2 ON T1.Appelation  =  T2.Appelation WHERE T1.County  =  'Sonoma')
[{'avg(price)': 46.53503184713376}]