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 numbers of wines for different grapes?
assistant
SELECT count(*) , Grape FROM WINE GROUP BY Grape
human
List the names and scores of all wines.
assistant
SELECT Name , Score FROM WINE
human
What are the names of wines that are more expensive then all wines made in the year 2006?
assistant
SELECT Name FROM WINE WHERE Price > (SELECT max(Price) FROM WINE WHERE YEAR = 2006)
human
Find the wineries that have at least four wines.
assistant
SELECT Winery FROM WINE GROUP BY Winery HAVING count(*) >= 4
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 countries for appelations with at most 3 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 ;