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
Hi, could you please show me the list of all appellations those produce wine after 2008?
assistant
SELECT Appelation FROM WINE WHERE YEAR > 2008;
human
That's a big list, thank you. Then could you please exclude Central Coast area from them?
assistant
SELECT Appelation FROM WINE WHERE YEAR > 2008 EXCEPT SELECT Appelation FROM APPELLATIONS WHERE Area = 'Central Coast';
human
That's great, thank you. Could you please show me the list of states where these appellations are located at?
assistant
SELECT state FROM APPELLATIONS WHERE appelation IN ( SELECT Appelation FROM WINE WHERE YEAR > 2008 EXCEPT SELECT Appelation FROM APPELLATIONS WHERE Area = 'Central Coast' );
human
Oh.. actually somewhat expected that. Thank you, then could you please let me know the average price of all wines?
assistant
SELECT AVG ( price ) FROM wine ;