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
Find the average price of wines that are not produced from Sonoma county.
assistant
SELECT avg(price) FROM wine WHERE Appelation NOT IN (SELECT T1.Appelation FROM APPELLATIONS AS T1 JOIN WINE AS T2 ON T1.Appelation = T2.Appelation WHERE T1.County = 'Sonoma')
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
Give me the average prices of wines that are produced by appelations in Sonoma County.
assistant
SELECT AVG(T2.Price) FROM APPELLATIONS AS T1 JOIN WINE AS T2 ON T1.Appelation = T2.Appelation WHERE T1.County = 'Sonoma'
human
Give the neames of wines with prices below 50 and with 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 ;