CREATE TABLE grapes (
    ID NUMBER PRIMARY KEY,
    Grape TEXT,
    Color TEXT
);
CREATE TABLE appellations (
    No NUMBER PRIMARY KEY,
    Appelation TEXT,
    County TEXT,
    State TEXT,
    Area TEXT,
    isAVA TEXT
);
CREATE TABLE wine (
    No NUMBER,
    Grape TEXT,
    Winery TEXT,
    Appelation TEXT,
    State TEXT,
    Name TEXT,
    Year NUMBER,
    Price NUMBER,
    Score NUMBER,
    Cases NUMBER,
    Drink TEXT,
    FOREIGN KEY ("Appelation") REFERENCES appellations("Appelation"),
    FOREIGN KEY ("Grape") REFERENCES grapes("Grape")
);

Question: What is the area of the appelation that produces the highest number of wines before the year of 2010?
SQL: SELECT T1.Area FROM APPELLATIONS AS T1 JOIN WINE AS T2 ON T1.Appelation  =  T2.Appelation GROUP BY T2.Appelation HAVING T2.year  <  2010 ORDER BY count(*) DESC LIMIT 1

Question: What is the area for the appelation which produced the most wines prior to 2010?
SQL: SELECT T1.Area FROM APPELLATIONS AS T1 JOIN WINE AS T2 ON T1.Appelation  =  T2.Appelation GROUP BY T2.Appelation HAVING T2.year  <  2010 ORDER BY count(*) DESC LIMIT 1

Question: What is the color of the grape whose wine products has the highest average price?
SQL: SELECT T1.Color FROM GRAPES AS T1 JOIN WINE AS T2 ON T1.Grape  =  T2.Grape GROUP BY T2.Grape ORDER BY AVG(Price) DESC LIMIT 1

Question: Give the color of the grape whose wine products have the highest average price?
SQL: SELECT T1.Color FROM GRAPES AS T1 JOIN WINE AS T2 ON T1.Grape  =  T2.Grape GROUP BY T2.Grape ORDER BY AVG(Price) DESC LIMIT 1

Question: Find the distinct names of wines produced before the year of 2000 or after the year of 2010.
SQL: SELECT DISTINCT Name FROM WINE WHERE YEAR  <  2000 OR YEAR  >  2010

Question: Give the distinct names of wines made before 2000 or after 2010.
SQL: SELECT DISTINCT Name FROM WINE WHERE YEAR  <  2000 OR YEAR  >  2010

Question: Find the distinct winery of wines having price between 50 and 100.
SQL: SELECT DISTINCT Winery FROM WINE WHERE Price BETWEEN 50 AND 100

Question: What are the distinct wineries which produce wines costing between 50 and 100?
SQL: SELECT DISTINCT Winery FROM WINE WHERE Price BETWEEN 50 AND 100

Question: What are the average prices and cases of wines produced in the year of 2009 and made of Zinfandel grape?
SQL: SELECT AVG(Price) ,  AVG(Cases) FROM WINE WHERE YEAR  =  2009 AND Grape  =  "Zinfandel"

Question: Give the average price and case of wines made from Zinfandel grapes in the year 2009.
SQL: SELECT AVG(Price) ,  AVG(Cases) FROM WINE WHERE YEAR  =  2009 AND Grape  =  "Zinfandel"

Question: What are the maximum price and score of wines produced by St. Helena appelation?
SQL: SELECT max(Price) ,  max(Score) FROM WINE WHERE Appelation  =  "St. Helena"

Question: Give the maximum price and score for wines produced in the appelation St. Helena.
SQL: SELECT max(Price) ,  max(Score) FROM WINE WHERE Appelation  =  "St. Helena"

Question: What are the maximum price and score of wines in each year?
SQL: SELECT max(Price) ,  max(Score) ,  YEAR FROM WINE GROUP BY YEAR

Question: What are the maximum price and score of wines for each year?
SQL: SELECT max(Price) ,  max(Score) ,  YEAR FROM WINE GROUP BY YEAR

Question: What are the average price and score of wines grouped by appelation?
SQL: SELECT avg(Price) ,  avg(Score) ,  Appelation FROM WINE GROUP BY Appelation

Question: What are the average price and score of wines for each appelation?
SQL: SELECT avg(Price) ,  avg(Score) ,  Appelation FROM WINE GROUP BY Appelation

Question: Find the wineries that have at least four wines.
SQL: SELECT Winery FROM WINE GROUP BY Winery HAVING count(*)  >=  4

Question: Which wineries produce at least four wines?
SQL: SELECT Winery FROM WINE GROUP BY Winery HAVING count(*)  >=  4

Question: Find the country of all appelations who have at most three wines.
SQL: SELECT T1.County FROM APPELLATIONS AS T1 JOIN WINE AS T2 ON T1.Appelation  =  T2.Appelation GROUP BY T2.Appelation HAVING count(*)  <=  3

Question: What are the countries for appelations with at most 3 wines?
SQL: SELECT T1.County FROM APPELLATIONS AS T1 JOIN WINE AS T2 ON T1.Appelation  =  T2.Appelation GROUP BY T2.Appelation HAVING count(*)  <=  3

Question: What are the names of wines whose production year are before the year of all wines by Brander winery?
SQL: SELECT Name FROM WINE WHERE YEAR  <  (SELECT min(YEAR) FROM WINE WHERE Winery  =  "Brander")

Question: What are the names of wines produced before any wine from the Brander winery?
SQL: SELECT Name FROM WINE WHERE YEAR  <  (SELECT min(YEAR) FROM WINE WHERE Winery  =  "Brander")

Question: What are the names of wines that are more expensive then all wines made in the year 2006?
SQL: SELECT Name FROM WINE WHERE Price  >  (SELECT max(Price) FROM WINE WHERE YEAR  =  2006)

Question: Give the names of wines with prices above any wine produced in 2006.
SQL: SELECT Name FROM WINE WHERE Price  >  (SELECT max(Price) FROM WINE WHERE YEAR  =  2006)

Question: Find the top 3 wineries with the greatest number of wines made of white color grapes.
SQL: 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

Question: Which 3 wineries produce the most wines made from white grapes?
SQL: 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

Question: List the grape, winery and year of the wines whose price is bigger than 100 ordered by year.
SQL: SELECT Grape ,  Winery ,  YEAR FROM WINE WHERE Price  >   100 ORDER BY YEAR

Question: What are the grapes, wineries and years for wines with price higher than 100, sorted by year?
SQL: SELECT Grape ,  Winery ,  YEAR FROM WINE WHERE Price  >   100 ORDER BY YEAR

Question: List the grape, appelation and name of wines whose score is higher than 93 ordered by Name.
SQL: SELECT Grape ,  Appelation ,  Name FROM WINE WHERE Score  >  93 ORDER BY Name

Question: What are the grapes, appelations, and wines with scores above 93, sorted by Name?
SQL: SELECT Grape ,  Appelation ,  Name FROM WINE WHERE Score  >  93 ORDER BY Name

Question: Find the appelations that produce wines after the year of 2008 but not in Central Coast area.
SQL: SELECT Appelation FROM WINE WHERE YEAR  >  2008 EXCEPT SELECT Appelation FROM APPELLATIONS WHERE Area  =  "Central Coast"

Question: What are the appelations for wines produced after 2008 but not in the Central Coast area?
SQL: SELECT Appelation FROM WINE WHERE YEAR  >  2008 EXCEPT SELECT Appelation FROM APPELLATIONS WHERE Area  =  "Central Coast"

Question: Find the average price of wines that are not produced from Sonoma county.
SQL: SELECT avg(price) FROM wine WHERE Appelation NOT IN (SELECT T1.Appelation FROM APPELLATIONS AS T1 JOIN WINE AS T2 ON T1.Appelation  =  T2.Appelation WHERE T1.County  =  'Sonoma')

Question: What is the average price for wines not produced in Sonoma county?
SQL: SELECT avg(price) FROM wine WHERE Appelation NOT IN (SELECT T1.Appelation FROM APPELLATIONS AS T1 JOIN WINE AS T2 ON T1.Appelation  =  T2.Appelation WHERE T1.County  =  'Sonoma')

Question: Find the county where produces the most number of wines with score higher than 90.
SQL: 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

Question: What is the county that produces the most wines scoring higher than 90?
SQL: 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