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
Find the names of all wines produced in 2008.
assistant
SELECT Name FROM WINE WHERE YEAR = '2008'
human
List the names and scores of all wines.
assistant
SELECT Name , Score FROM WINE
human
List the names of all distinct wines in alphabetical order.
assistant
SELECT DISTINCT Name FROM WINE ORDER BY Name
human
Find the names of all distinct wines that have appellations in 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'
human
Find the wineries that have at least four wines.
assistant
SELECT Winery FROM WINE GROUP BY Winery HAVING count(*) >= 4
human
Find the country of all appelations who have at most three wines.
assistant
SELECT T1.County FROM APPELLATIONS AS T1 JOIN WINE AS T2 ON T1.Appelation = T2.Appelation GROUP BY T2.Appelation HAVING COUNT( * ) <= 3 ;