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 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
List the names of all distinct wines in alphabetical order.
assistant
SELECT DISTINCT Name FROM WINE ORDER BY Name
human
List the names and scores of all wines.
assistant
SELECT Name , Score FROM WINE
human
What are the names and scores of wines that are made of white color grapes?
assistant
SELECT T2.Name , T2.Score FROM GRAPES AS T1 JOIN WINE AS T2 ON T1.Grape = T2.Grape WHERE T1.Color = 'White'
human
What are the numbers of wines for different grapes?
assistant
SELECT count(*) , Grape FROM WINE GROUP BY Grape
human
What are the grapes and appelations of each wine?
assistant
SELECT Grape , Appelation FROM WINE ;