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 is the name of the highest rated wine?
assistant
SELECT Name FROM WINE ORDER BY Score LIMIT 1
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 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 names of wines whose production year are before the year of all wines by Brander winery?
assistant
SELECT Name FROM WINE WHERE YEAR < (SELECT min(YEAR) FROM WINE WHERE Winery = 'Brander')
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 ) ;