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 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
Find the names of all distinct wines that have appellations in North Coast area.
assistant
SELECT DISTINCT T2.Name FROM APPELLATIONs AS T1 JOIN WINE AS T2 ON T1.Appelation = T2.Appelation WHERE T1.Area = 'North Coast'
human
List the names of all distinct wines in alphabetical order.
assistant
SELECT DISTINCT Name FROM WINE ORDER BY Name
human
Find the distinct winery of wines having price between 50 and 100.
assistant
SELECT DISTINCT Winery FROM WINE WHERE Price BETWEEN 50 AND 100
human
List the names of all distinct wines ordered by price.
assistant
SELECT DISTINCT Name FROM WINE ORDER BY price
human
Find the distinct names of all wines that have prices higher than some wines from John Anthony winery.
assistant
SELECT DISTINCT Name FROM WINE WHERE Price > ( SELECT MIN( Price ) FROM wine WHERE Winery = "John Anthony" ) ;