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 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
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 prices of wines produced before the year of 2010?
assistant
SELECT Price FROM WINE WHERE YEAR < 2010
human
What are the maximum price and score of wines in each year?
assistant
SELECT max(Price) , max(Score) , YEAR FROM WINE GROUP BY YEAR
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 maximum price of wins from the appelations in Central Coast area and produced before the year of 2005.
assistant
SELECT MAX( T2.Price ) FROM APPELLATIONS AS T1 JOIN WINE AS T2 ON T1.Appelation = T2.Appelation WHERE T1.Area = "Central Coast" AND T2.year < 2005 ;