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 in each year?
assistant
SELECT max(Price) , max(Score) , YEAR FROM WINE GROUP BY YEAR
human
What is the name of the highest rated wine?
assistant
SELECT Name FROM WINE ORDER BY Score LIMIT 1
human
List the names of all distinct wines that have scores higher than 90.
assistant
SELECT Name FROM WINE WHERE score > 90
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
Which winery is the wine that has the highest score from?
assistant
SELECT Winery FROM WINE ORDER BY SCORE LIMIT 1
human
What is the county that produces the most wines scoring higher than 90?
assistant
SELECT T1.County FROM APPELLATIONS AS T1 JOIN WINE AS T2 ON T1.Appelation = T2.Appelation WHERE T2.Score > 90 GROUP BY T1.County ORDER BY COUNT( * ) DESC LIMIT 1 ;