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
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
What is the area of the appelation that produces the highest number of wines before the year of 2010?
assistant
SELECT T1.Area FROM APPELLATIONS AS T1 JOIN WINE AS T2 ON T1.Appelation = T2.Appelation GROUP BY T2.Appelation HAVING T2.year < 2010 ORDER BY count(*) DESC LIMIT 1
human
Find the country of all appelations who have at most three wines.
assistant
SELECT T1.County FROM APPELLATIONS AS T1 JOIN WINE AS T2 ON T1.Appelation = T2.Appelation GROUP BY T2.Appelation HAVING count(*) <= 3
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
How many appelations are in Napa Country?
assistant
SELECT count(*) FROM APPELLATIONS WHERE County = 'Napa'
human
What are the areas and counties for all appelations?
assistant
SELECT Area , County FROM APPELLATIONS ;