human
Schema:
grapes (ID, Grape, Color)
appellations (No, Appelation, County, State, Area, isAVA)
wine (No, Grape, Winery, Appelation, State, Name, Year, Price, Score, Cases, Drink)
Please answer with SQL.
assistant
Sure, I understand.
human
List the grapes and appelations of all wines.
assistant
SELECT Grape , Appelation FROM WINE
human
Find the appelations that produce wines after the year of 2008 but not in Central Coast area.
assistant
SELECT Appelation FROM WINE WHERE YEAR > 2008 EXCEPT SELECT Appelation FROM APPELLATIONS WHERE Area = 'Central Coast'
human
Find the names of all distinct wines that have appellations in North Coast area.
assistant
SELECT DISTINCT T2.Name FROM APPELLATIONs AS T1 JOIN WINE AS T2 ON T1.Appelation = T2.Appelation WHERE T1.Area = 'North Coast'
human
Give me the average prices of wines that are produced by appelations in Sonoma County.
assistant
SELECT AVG(T2.Price) FROM APPELLATIONS AS T1 JOIN WINE AS T2 ON T1.Appelation = T2.Appelation WHERE T1.County = 'Sonoma'
human
How many appelations are in Napa Country?
assistant
SELECT count(*) FROM APPELLATIONS WHERE County = 'Napa'
human
List the area and county of all appelations.
assistant
SELECT Area , County FROM APPELLATIONS ;