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 are the average prices of wines for different years?
assistant
SELECT avg(Price) , YEAR FROM WINE GROUP BY YEAR
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 wines that have prices higher than 50 and made of Red color grapes?
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
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 ;