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 wineries that have at least four wines.
assistant
SELECT Winery FROM WINE GROUP BY Winery HAVING count(*) >= 4
human
List the names and scores of all wines.
assistant
SELECT Name , Score FROM WINE
human
Which winery is the wine that has the highest score from?
assistant
SELECT Winery FROM WINE ORDER BY SCORE LIMIT 1
human
What are the numbers of wines for different grapes?
assistant
SELECT count(*) , Grape FROM WINE GROUP BY Grape
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
How many wines are produced at Robert Biale winery?
assistant
SELECT COUNT( * ) FROM WINE WHERE Winery = "Robert Biale" ;