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
What is the color of the grape whose wine products has the highest average price?
assistant
SELECT T1.Color FROM GRAPES AS T1 JOIN WINE AS T2 ON T1.Grape = T2.Grape GROUP BY T2.Grape ORDER BY AVG(Price) DESC LIMIT 1
human
List the names of all distinct wines that are made of red color grape.
assistant
SELECT DISTINCT T2.Name FROM GRAPES AS T1 JOIN WINE AS T2 ON T1.Grape = T2.Grape WHERE T1.Color = 'Red'
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
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 names of wines made from red grapes and with prices above 50?
assistant
SELECT T2.Name FROM Grapes AS T1 JOIN WINE AS T2 ON T1.Grape = T2.Grape WHERE T1.Color = "Red" AND T2.price > 50 ;