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 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 price and score of wines grouped by appelation?
assistant
SELECT avg(Price) , avg(Score) , Appelation FROM WINE GROUP BY Appelation
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
Give the average price and case of wines made from Zinfandel grapes in the year 2009.
assistant
SELECT AVG ( Price ) , AVG ( Cases ) FROM WINE WHERE YEAR = 2009 AND Grape = "Zinfandel" ;