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 distinct winery of wines having price between 50 and 100.
assistant
SELECT DISTINCT Winery FROM WINE WHERE Price BETWEEN 50 AND 100
human
Find the top 3 wineries with the greatest number of wines made of white color grapes.
assistant
SELECT T2.Winery FROM GRAPES AS T1 JOIN WINE AS T2 ON T1.GRAPE = T2.GRAPE WHERE T1.Color = 'White' GROUP BY T2.Winery ORDER BY count(*) DESC LIMIT 3
human
Find the county where produces the most number of wines with score higher than 90.
assistant
SELECT T1.County FROM APPELLATIONS AS T1 JOIN WINE AS T2 ON T1.Appelation = T2.Appelation WHERE T2.Score > 90 GROUP BY T1.County ORDER BY count(*) DESC LIMIT 1
human
List the grape, appelation and name of wines whose score is higher than 93 ordered by Name.
assistant
SELECT Grape , Appelation , Name FROM WINE WHERE Score > 93 ORDER BY Name
human
List the names of all distinct wines that have scores higher than 90.
assistant
SELECT Name FROM WINE WHERE score > 90
human
Find the the grape whose white color grapes are used to produce wines with scores higher than 90.
assistant
SELECT DISTINCT T1.Grape FROM GRAPES AS T1 JOIN WINE AS T2 ON T1.Grape = T2.Grape WHERE T1.Color = "White" AND T2.score > 90 ;