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 whose production year are before the year of all wines by Brander winery?
assistant
SELECT Name FROM WINE WHERE YEAR < (SELECT min(YEAR) FROM WINE WHERE Winery = 'Brander')
human
List the names and scores of all wines.
assistant
SELECT Name , Score FROM WINE
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
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 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 distinct names of wines that have appellations in the 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" ;