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
What are the maximum price and score of wines produced by St. Helena appelation?
assistant
SELECT max(Price) , max(Score) FROM WINE WHERE Appelation = 'St. Helena'
human
What are the numbers of wines for different grapes?
assistant
SELECT count(*) , Grape FROM WINE GROUP BY Grape
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
List the area and county of all appelations.
assistant
SELECT Area , County FROM APPELLATIONS
human
How many appelations are in Napa Country?
assistant
SELECT COUNT( * ) FROM APPELLATIONS WHERE County = "Napa" ;