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 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 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 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 average prices of wines for different years?
assistant
SELECT avg(Price) , YEAR FROM WINE GROUP BY YEAR
human
What are the prices of wines produced before the year of 2010?
assistant
SELECT Price FROM WINE WHERE YEAR < 2010
human
What are the grapes, wineries and years for wines with price higher than 100, sorted by year?
assistant
SELECT Grape , Winery , YEAR FROM WINE WHERE Price > 100 ORDER BY YEAR ;