
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
*/
List the area and county of all appelations.
SELECT Area ,  County FROM APPELLATIONS
[{'Area': 'North Coast', 'County': 'Sonoma'}, {'Area': 'Sierra Foothills', 'County': 'Amador'}, {'Area': 'N/A', 'County': 'N/A'}, {'Area': 'North Coast', 'County': 'Mendocino'}, {'Area': 'Central Coast', 'County': 'San Luis Obispo'}, {'Area': 'North Coast', 'County': 'Napa'}, {'Area': 'North Coast', 'County': 'Sonoma'}, {'Area': 'Sierra Foothills', 'County': 'Calaveras'}, {'Area': 'California', 'County': 'N/A'}, {'Area': 'North Coast', 'County': 'Napa'}, {'Area': 'Central Coast', 'County': 'N/A'}, {'Area': 'North Coast', 'County': 'Sonoma'}, {'Area': 'Central Coast', 'County': 'Monterey'}, {'Area': 'Santa Cruz Mountains', 'County': 'Contra Costa'}, {'Area': 'North Coast', 'County': 'Sonoma'}, {'Area': 'Central Coast', 'County': 'San Luis Obispo'}, {'Area': 'Sierra Foothills', 'County': 'Amador'}, {'Area': 'North Coast', 'County': 'Sonoma'}, {'Area': 'North Coast', 'County': 'Lake'}, {'Area': 'Central Coast', 'County': 'Santa Barbara'}, {'Area': 'North Coast', 'County': 'Napa'}, {'Area': 'North Coast', 'County': 'Sonoma'}, {'Area': 'North Coast', 'County': 'Lake'}, {'Area': 'Santa Cruz Mountains', 'County': 'Alameda'}, {'Area': 'Central Valley', 'County': 'San Joaquin'}, {'Area': 'North Coast', 'County': 'Mendocino'}, {'Area': 'Central Coast', 'County': 'Monterey'}, {'Area': 'Central Coast', 'County': 'San Benito'}, {'Area': 'North Coast', 'County': 'Napa'}, {'Area': 'North Coast', 'County': 'Napa'}, {'Area': 'North Coast', 'County': 'N/A'}, {'Area': 'North Coast', 'County': 'N/A'}, {'Area': 'North Coast', 'County': 'Napa'}, {'Area': 'Central Coast', 'County': 'San Luis Obispo'}, {'Area': 'North Coast', 'County': 'Lake'}, {'Area': 'North Coast', 'County': 'Sonoma'}, {'Area': 'North Coast', 'County': 'Sonoma'}, {'Area': 'North Coast', 'County': 'Napa'}, {'Area': 'Central Coast', 'County': 'San Luis Obispo'}, {'Area': 'Central Coast', 'County': 'Santa Barbara'}, {'Area': 'Santa Cruz Mountains', 'County': 'N/A'}, {'Area': 'Central Coast', 'County': 'Monterey'}, {'Area': 'Central Coast', 'County': 'Santa Barbara'}, {'Area': 'Central Coast', 'County': 'Santa Barbara'}, {'Area': 'Sierra Foothills', 'County': 'Amador'}, {'Area': 'Sierra Foothills', 'County': 'N/A'}, {'Area': 'North Coast', 'County': 'Solano'}, {'Area': 'North Coast', 'County': 'Sonoma'}, {'Area': 'North Coast', 'County': 'Sonoma'}, {'Area': 'North Coast', 'County': 'Sonoma'}, {'Area': 'North Coast', 'County': 'N/A'}, {'Area': 'North Coast', 'County': 'Napa'}, {'Area': 'North Coast', 'County': 'Napa'}, {'Area': 'Central Coast', 'County': 'Santa Barbara'}, {'Area': 'North Coast', 'County': 'Napa'}, {'Area': 'Central Valley', 'County': 'Yolo'}, {'Area': 'North Coast', 'County': 'Napa'}]