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 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
Find the appelations that produce wines after the year of 2008 but not in Central Coast area.
assistant
SELECT Appelation FROM WINE WHERE YEAR > 2008 EXCEPT SELECT Appelation FROM APPELLATIONS WHERE Area = 'Central Coast'
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 average price and score of wines grouped by appelation?
assistant
SELECT avg(Price) , avg(Score) , Appelation FROM WINE GROUP BY Appelation
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
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" ) ;