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
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')
human
What are the wines that have prices lower than 50 and have appelations in Monterey county?
assistant
SELECT T2.Name FROM APPELLATIONS AS T1 JOIN WINE AS T2 ON T1.Appelation = T2.Appelation WHERE T1.County = 'Monterey' AND T2.price < 50
human
List the names of all distinct wines ordered by price.
assistant
SELECT DISTINCT Name FROM WINE ORDER BY price
human
List the grape, winery and year of the wines whose price is bigger than 100 ordered by year.
assistant
SELECT Grape , Winery , YEAR FROM WINE WHERE Price > 100 ORDER BY YEAR
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 ;