lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### 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"
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### 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"
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### 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"
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### 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
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### 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
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### 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
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(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
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### Question
Find the wineries that have at least four wines.

### SQL
SELECT Winery FROM WINE GROUP BY Winery HAVING count(*)  >=  4
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### Question
Which wineries produce at least four wines?

### SQL
SELECT Winery FROM WINE GROUP BY Winery HAVING count(*)  >=  4
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### 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
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### 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
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### 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")
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### 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")
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### 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)
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### 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)
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### 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
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### 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
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### 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
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### 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
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### 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
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### 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
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### 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"
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### 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"
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### 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')
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### 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')
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### 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
### Database Schema
CREATE TABLE "grapes" ( 
	"ID" INTEGER PRIMARY KEY, 
	"Grape" TEXT UNIQUE, 
	"Color" TEXT 
)

CREATE TABLE "appellations" ( 
	"No" INTEGER PRIMARY KEY, 
	"Appelation" TEXT UNIQUE, 
	"County" TEXT, 
	"State" TEXT, 
	"Area" TEXT, 
	"isAVA" TEXT
)

CREATE TABLE "wine" ( 
	"No" INTEGER, 
	"Grape" TEXT, 
	"Winery" TEXT, 
	"Appelation" TEXT, 
	"State" TEXT, 
	"Name" TEXT, 
	"Year" INTEGER, 
	"Price" INTEGER, 
	"Score" INTEGER, 
	"Cases" INTEGER, 
	"Drink" TEXT,
	FOREIGN KEY (Grape) REFERENCES grapes(Grape),
	FOREIGN KEY (Appelation) REFERENCES appellations(Appelation)
)

### 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
### Database Schema
CREATE TABLE "station" (
"Station_ID" int,
"Name" text,
"Annual_entry_exit" real,
"Annual_interchanges" real,
"Total_Passengers" real,
"Location" text,
"Main_Services" text,
"Number_of_Platforms" int,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Name" text,
"Time" text,
"Service" text,
PRIMARY KEY ("Train_ID")
)

CREATE TABLE "train_station" (
"Train_ID" int,
"Station_ID" int,	
PRIMARY KEY ("Train_ID","Station_ID"),
FOREIGN KEY ("Train_ID") REFERENCES "train"("Train_ID"),
FOREIGN KEY ("Station_ID") REFERENCES "station"("Station_ID")
)

### Question
How many train stations are there?

### SQL
SELECT count(*) FROM station
### Database Schema
CREATE TABLE "station" (
"Station_ID" int,
"Name" text,
"Annual_entry_exit" real,
"Annual_interchanges" real,
"Total_Passengers" real,
"Location" text,
"Main_Services" text,
"Number_of_Platforms" int,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Name" text,
"Time" text,
"Service" text,
PRIMARY KEY ("Train_ID")
)

CREATE TABLE "train_station" (
"Train_ID" int,
"Station_ID" int,	
PRIMARY KEY ("Train_ID","Station_ID"),
FOREIGN KEY ("Train_ID") REFERENCES "train"("Train_ID"),
FOREIGN KEY ("Station_ID") REFERENCES "station"("Station_ID")
)

### Question
Show the name, location, and number of platforms for all stations.

### SQL
SELECT name ,  LOCATION ,  number_of_platforms FROM station
### Database Schema
CREATE TABLE "station" (
"Station_ID" int,
"Name" text,
"Annual_entry_exit" real,
"Annual_interchanges" real,
"Total_Passengers" real,
"Location" text,
"Main_Services" text,
"Number_of_Platforms" int,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Name" text,
"Time" text,
"Service" text,
PRIMARY KEY ("Train_ID")
)

CREATE TABLE "train_station" (
"Train_ID" int,
"Station_ID" int,	
PRIMARY KEY ("Train_ID","Station_ID"),
FOREIGN KEY ("Train_ID") REFERENCES "train"("Train_ID"),
FOREIGN KEY ("Station_ID") REFERENCES "station"("Station_ID")
)

### Question
What are all locations of train stations?

### SQL
SELECT DISTINCT LOCATION FROM station
### Database Schema
CREATE TABLE "station" (
"Station_ID" int,
"Name" text,
"Annual_entry_exit" real,
"Annual_interchanges" real,
"Total_Passengers" real,
"Location" text,
"Main_Services" text,
"Number_of_Platforms" int,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Name" text,
"Time" text,
"Service" text,
PRIMARY KEY ("Train_ID")
)

CREATE TABLE "train_station" (
"Train_ID" int,
"Station_ID" int,	
PRIMARY KEY ("Train_ID","Station_ID"),
FOREIGN KEY ("Train_ID") REFERENCES "train"("Train_ID"),
FOREIGN KEY ("Station_ID") REFERENCES "station"("Station_ID")
)

### Question
Show the names and total passengers for all train stations not in London.

### SQL
SELECT name ,  total_passengers FROM station WHERE LOCATION != 'London'
### Database Schema
CREATE TABLE "station" (
"Station_ID" int,
"Name" text,
"Annual_entry_exit" real,
"Annual_interchanges" real,
"Total_Passengers" real,
"Location" text,
"Main_Services" text,
"Number_of_Platforms" int,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Name" text,
"Time" text,
"Service" text,
PRIMARY KEY ("Train_ID")
)

CREATE TABLE "train_station" (
"Train_ID" int,
"Station_ID" int,	
PRIMARY KEY ("Train_ID","Station_ID"),
FOREIGN KEY ("Train_ID") REFERENCES "train"("Train_ID"),
FOREIGN KEY ("Station_ID") REFERENCES "station"("Station_ID")
)

### Question
Show the names and main services for train stations that have the top three total number of passengers.

### SQL
SELECT name ,  main_services FROM station ORDER BY total_passengers DESC LIMIT 3
### Database Schema
CREATE TABLE "station" (
"Station_ID" int,
"Name" text,
"Annual_entry_exit" real,
"Annual_interchanges" real,
"Total_Passengers" real,
"Location" text,
"Main_Services" text,
"Number_of_Platforms" int,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Name" text,
"Time" text,
"Service" text,
PRIMARY KEY ("Train_ID")
)

CREATE TABLE "train_station" (
"Train_ID" int,
"Station_ID" int,	
PRIMARY KEY ("Train_ID","Station_ID"),
FOREIGN KEY ("Train_ID") REFERENCES "train"("Train_ID"),
FOREIGN KEY ("Station_ID") REFERENCES "station"("Station_ID")
)

### Question
What is the average and maximum number of total passengers for train stations in London or Glasgow?

### SQL
SELECT avg(total_passengers) ,  max(total_passengers) FROM station WHERE LOCATION  =  'London' OR LOCATION  =  'Glasgow'
### Database Schema
CREATE TABLE "station" (
"Station_ID" int,
"Name" text,
"Annual_entry_exit" real,
"Annual_interchanges" real,
"Total_Passengers" real,
"Location" text,
"Main_Services" text,
"Number_of_Platforms" int,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Name" text,
"Time" text,
"Service" text,
PRIMARY KEY ("Train_ID")
)

CREATE TABLE "train_station" (
"Train_ID" int,
"Station_ID" int,	
PRIMARY KEY ("Train_ID","Station_ID"),
FOREIGN KEY ("Train_ID") REFERENCES "train"("Train_ID"),
FOREIGN KEY ("Station_ID") REFERENCES "station"("Station_ID")
)

### Question
Show all locations and the total number of platforms and passengers for all train stations in each location.

### SQL
SELECT LOCATION ,  sum(number_of_platforms) ,  sum(total_passengers) FROM station GROUP BY LOCATION
### Database Schema
CREATE TABLE "station" (
"Station_ID" int,
"Name" text,
"Annual_entry_exit" real,
"Annual_interchanges" real,
"Total_Passengers" real,
"Location" text,
"Main_Services" text,
"Number_of_Platforms" int,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Name" text,
"Time" text,
"Service" text,
PRIMARY KEY ("Train_ID")
)

CREATE TABLE "train_station" (
"Train_ID" int,
"Station_ID" int,	
PRIMARY KEY ("Train_ID","Station_ID"),
FOREIGN KEY ("Train_ID") REFERENCES "train"("Train_ID"),
FOREIGN KEY ("Station_ID") REFERENCES "station"("Station_ID")
)

### Question
Show all locations that have train stations with at least 15 platforms and train stations with more than 25 total passengers.

### SQL
SELECT DISTINCT LOCATION FROM station WHERE number_of_platforms  >=  15 AND total_passengers  >  25
### Database Schema
CREATE TABLE "station" (
"Station_ID" int,
"Name" text,
"Annual_entry_exit" real,
"Annual_interchanges" real,
"Total_Passengers" real,
"Location" text,
"Main_Services" text,
"Number_of_Platforms" int,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Name" text,
"Time" text,
"Service" text,
PRIMARY KEY ("Train_ID")
)

CREATE TABLE "train_station" (
"Train_ID" int,
"Station_ID" int,	
PRIMARY KEY ("Train_ID","Station_ID"),
FOREIGN KEY ("Train_ID") REFERENCES "train"("Train_ID"),
FOREIGN KEY ("Station_ID") REFERENCES "station"("Station_ID")
)

### Question
Show all locations which don't have a train station with at least 15 platforms.

### SQL
SELECT LOCATION FROM station EXCEPT SELECT LOCATION FROM station WHERE number_of_platforms  >=  15
### Database Schema
CREATE TABLE "station" (
"Station_ID" int,
"Name" text,
"Annual_entry_exit" real,
"Annual_interchanges" real,
"Total_Passengers" real,
"Location" text,
"Main_Services" text,
"Number_of_Platforms" int,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Name" text,
"Time" text,
"Service" text,
PRIMARY KEY ("Train_ID")
)

CREATE TABLE "train_station" (
"Train_ID" int,
"Station_ID" int,	
PRIMARY KEY ("Train_ID","Station_ID"),
FOREIGN KEY ("Train_ID") REFERENCES "train"("Train_ID"),
FOREIGN KEY ("Station_ID") REFERENCES "station"("Station_ID")
)

### Question
Show the location with most number of train stations.

### SQL
SELECT LOCATION FROM station GROUP BY LOCATION ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "station" (
"Station_ID" int,
"Name" text,
"Annual_entry_exit" real,
"Annual_interchanges" real,
"Total_Passengers" real,
"Location" text,
"Main_Services" text,
"Number_of_Platforms" int,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Name" text,
"Time" text,
"Service" text,
PRIMARY KEY ("Train_ID")
)

CREATE TABLE "train_station" (
"Train_ID" int,
"Station_ID" int,	
PRIMARY KEY ("Train_ID","Station_ID"),
FOREIGN KEY ("Train_ID") REFERENCES "train"("Train_ID"),
FOREIGN KEY ("Station_ID") REFERENCES "station"("Station_ID")
)

### Question
Show the name, time, and service for all trains.

### SQL
SELECT name ,  TIME ,  service FROM train
### Database Schema
CREATE TABLE "station" (
"Station_ID" int,
"Name" text,
"Annual_entry_exit" real,
"Annual_interchanges" real,
"Total_Passengers" real,
"Location" text,
"Main_Services" text,
"Number_of_Platforms" int,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Name" text,
"Time" text,
"Service" text,
PRIMARY KEY ("Train_ID")
)

CREATE TABLE "train_station" (
"Train_ID" int,
"Station_ID" int,	
PRIMARY KEY ("Train_ID","Station_ID"),
FOREIGN KEY ("Train_ID") REFERENCES "train"("Train_ID"),
FOREIGN KEY ("Station_ID") REFERENCES "station"("Station_ID")
)

### Question
Show the number of trains

### SQL
SELECT count(*) FROM train
### Database Schema
CREATE TABLE "station" (
"Station_ID" int,
"Name" text,
"Annual_entry_exit" real,
"Annual_interchanges" real,
"Total_Passengers" real,
"Location" text,
"Main_Services" text,
"Number_of_Platforms" int,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Name" text,
"Time" text,
"Service" text,
PRIMARY KEY ("Train_ID")
)

CREATE TABLE "train_station" (
"Train_ID" int,
"Station_ID" int,	
PRIMARY KEY ("Train_ID","Station_ID"),
FOREIGN KEY ("Train_ID") REFERENCES "train"("Train_ID"),
FOREIGN KEY ("Station_ID") REFERENCES "station"("Station_ID")
)

### Question
Show the name and service for all trains in order by time.

### SQL
SELECT name ,  service FROM train ORDER BY TIME
### Database Schema
CREATE TABLE "station" (
"Station_ID" int,
"Name" text,
"Annual_entry_exit" real,
"Annual_interchanges" real,
"Total_Passengers" real,
"Location" text,
"Main_Services" text,
"Number_of_Platforms" int,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Name" text,
"Time" text,
"Service" text,
PRIMARY KEY ("Train_ID")
)

CREATE TABLE "train_station" (
"Train_ID" int,
"Station_ID" int,	
PRIMARY KEY ("Train_ID","Station_ID"),
FOREIGN KEY ("Train_ID") REFERENCES "train"("Train_ID"),
FOREIGN KEY ("Station_ID") REFERENCES "station"("Station_ID")
)

### Question
Show the station name and number of trains in each station.

### SQL
SELECT T2.name ,  count(*) FROM train_station AS T1 JOIN station AS T2 ON T1.station_id  =  T2.station_id GROUP BY T1.station_id
### Database Schema
CREATE TABLE "station" (
"Station_ID" int,
"Name" text,
"Annual_entry_exit" real,
"Annual_interchanges" real,
"Total_Passengers" real,
"Location" text,
"Main_Services" text,
"Number_of_Platforms" int,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Name" text,
"Time" text,
"Service" text,
PRIMARY KEY ("Train_ID")
)

CREATE TABLE "train_station" (
"Train_ID" int,
"Station_ID" int,	
PRIMARY KEY ("Train_ID","Station_ID"),
FOREIGN KEY ("Train_ID") REFERENCES "train"("Train_ID"),
FOREIGN KEY ("Station_ID") REFERENCES "station"("Station_ID")
)

### Question
show the train name and station name for each train.

### SQL
SELECT T2.name ,  T3.name FROM train_station AS T1 JOIN station AS T2 ON T1.station_id  =  T2.station_id JOIN train AS T3 ON T3.train_id  =  T1.train_id
### Database Schema
CREATE TABLE "station" (
"Station_ID" int,
"Name" text,
"Annual_entry_exit" real,
"Annual_interchanges" real,
"Total_Passengers" real,
"Location" text,
"Main_Services" text,
"Number_of_Platforms" int,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Name" text,
"Time" text,
"Service" text,
PRIMARY KEY ("Train_ID")
)

CREATE TABLE "train_station" (
"Train_ID" int,
"Station_ID" int,	
PRIMARY KEY ("Train_ID","Station_ID"),
FOREIGN KEY ("Train_ID") REFERENCES "train"("Train_ID"),
FOREIGN KEY ("Station_ID") REFERENCES "station"("Station_ID")
)

### Question
Show all train names and times in stations in London in descending order by train time.

### SQL
SELECT T3.name ,  T3.time FROM train_station AS T1 JOIN station AS T2 ON T1.station_id  =  T2.station_id JOIN train AS T3 ON T3.train_id  =  T1.train_id WHERE T2.location  =  'London' ORDER BY T3.time DESC
### Database Schema
CREATE TABLE "station" (
"Station_ID" int,
"Name" text,
"Annual_entry_exit" real,
"Annual_interchanges" real,
"Total_Passengers" real,
"Location" text,
"Main_Services" text,
"Number_of_Platforms" int,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Name" text,
"Time" text,
"Service" text,
PRIMARY KEY ("Train_ID")
)

CREATE TABLE "train_station" (
"Train_ID" int,
"Station_ID" int,	
PRIMARY KEY ("Train_ID","Station_ID"),
FOREIGN KEY ("Train_ID") REFERENCES "train"("Train_ID"),
FOREIGN KEY ("Station_ID") REFERENCES "station"("Station_ID")
)

### Question
Show the station name with greatest number of trains.

### SQL
SELECT T2.name FROM train_station AS T1 JOIN station AS T2 ON T1.station_id  =  T2.station_id GROUP BY T1.station_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "station" (
"Station_ID" int,
"Name" text,
"Annual_entry_exit" real,
"Annual_interchanges" real,
"Total_Passengers" real,
"Location" text,
"Main_Services" text,
"Number_of_Platforms" int,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Name" text,
"Time" text,
"Service" text,
PRIMARY KEY ("Train_ID")
)

CREATE TABLE "train_station" (
"Train_ID" int,
"Station_ID" int,	
PRIMARY KEY ("Train_ID","Station_ID"),
FOREIGN KEY ("Train_ID") REFERENCES "train"("Train_ID"),
FOREIGN KEY ("Station_ID") REFERENCES "station"("Station_ID")
)

### Question
Show the station name with at least two trains.

### SQL
SELECT T2.name FROM train_station AS T1 JOIN station AS T2 ON T1.station_id  =  T2.station_id GROUP BY T1.station_id HAVING count(*)  >=  2
### Database Schema
CREATE TABLE "station" (
"Station_ID" int,
"Name" text,
"Annual_entry_exit" real,
"Annual_interchanges" real,
"Total_Passengers" real,
"Location" text,
"Main_Services" text,
"Number_of_Platforms" int,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Name" text,
"Time" text,
"Service" text,
PRIMARY KEY ("Train_ID")
)

CREATE TABLE "train_station" (
"Train_ID" int,
"Station_ID" int,	
PRIMARY KEY ("Train_ID","Station_ID"),
FOREIGN KEY ("Train_ID") REFERENCES "train"("Train_ID"),
FOREIGN KEY ("Station_ID") REFERENCES "station"("Station_ID")
)

### Question
Show all locations with only 1 station.

### SQL
SELECT LOCATION FROM station GROUP BY LOCATION HAVING count(*)  =  1
### Database Schema
CREATE TABLE "station" (
"Station_ID" int,
"Name" text,
"Annual_entry_exit" real,
"Annual_interchanges" real,
"Total_Passengers" real,
"Location" text,
"Main_Services" text,
"Number_of_Platforms" int,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Name" text,
"Time" text,
"Service" text,
PRIMARY KEY ("Train_ID")
)

CREATE TABLE "train_station" (
"Train_ID" int,
"Station_ID" int,	
PRIMARY KEY ("Train_ID","Station_ID"),
FOREIGN KEY ("Train_ID") REFERENCES "train"("Train_ID"),
FOREIGN KEY ("Station_ID") REFERENCES "station"("Station_ID")
)

### Question
Show station names without any trains.

### SQL
SELECT name FROM station WHERE station_id NOT IN (SELECT station_id FROM train_station)
### Database Schema
CREATE TABLE "station" (
"Station_ID" int,
"Name" text,
"Annual_entry_exit" real,
"Annual_interchanges" real,
"Total_Passengers" real,
"Location" text,
"Main_Services" text,
"Number_of_Platforms" int,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Name" text,
"Time" text,
"Service" text,
PRIMARY KEY ("Train_ID")
)

CREATE TABLE "train_station" (
"Train_ID" int,
"Station_ID" int,	
PRIMARY KEY ("Train_ID","Station_ID"),
FOREIGN KEY ("Train_ID") REFERENCES "train"("Train_ID"),
FOREIGN KEY ("Station_ID") REFERENCES "station"("Station_ID")
)

### Question
What are the names of the stations which serve both "Ananthapuri Express" and "Guruvayur Express" trains?

### SQL
SELECT T2.name FROM train_station AS T1 JOIN station AS T2 ON T1.station_id  =  T2.station_id JOIN train AS T3 ON T3.train_id  =  T1.train_id WHERE T3.Name  =  "Ananthapuri Express" INTERSECT SELECT T2.name FROM train_station AS T1 JOIN station AS T2 ON T1.station_id  =  T2.station_id JOIN train AS T3 ON T3.train_id  =  T1.train_id WHERE T3.Name  =  "Guruvayur Express"
### Database Schema
CREATE TABLE "station" (
"Station_ID" int,
"Name" text,
"Annual_entry_exit" real,
"Annual_interchanges" real,
"Total_Passengers" real,
"Location" text,
"Main_Services" text,
"Number_of_Platforms" int,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Name" text,
"Time" text,
"Service" text,
PRIMARY KEY ("Train_ID")
)

CREATE TABLE "train_station" (
"Train_ID" int,
"Station_ID" int,	
PRIMARY KEY ("Train_ID","Station_ID"),
FOREIGN KEY ("Train_ID") REFERENCES "train"("Train_ID"),
FOREIGN KEY ("Station_ID") REFERENCES "station"("Station_ID")
)

### Question
Find the names of the trains that do not pass any station located in London.

### SQL
SELECT T2.name FROM train_station AS T1 JOIN train AS T2 ON T1.train_id  =  T2.train_id WHERE T1.station_id NOT IN (SELECT T4.station_id FROM train_station AS T3 JOIN station AS T4 ON T3.station_id  =  T4.station_id WHERE t4.location  =  "London")
### Database Schema
CREATE TABLE "station" (
"Station_ID" int,
"Name" text,
"Annual_entry_exit" real,
"Annual_interchanges" real,
"Total_Passengers" real,
"Location" text,
"Main_Services" text,
"Number_of_Platforms" int,
PRIMARY KEY ("Station_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Name" text,
"Time" text,
"Service" text,
PRIMARY KEY ("Train_ID")
)

CREATE TABLE "train_station" (
"Train_ID" int,
"Station_ID" int,	
PRIMARY KEY ("Train_ID","Station_ID"),
FOREIGN KEY ("Train_ID") REFERENCES "train"("Train_ID"),
FOREIGN KEY ("Station_ID") REFERENCES "station"("Station_ID")
)

### Question
List the names and locations of all stations ordered by their yearly entry exit and interchange amounts.

### SQL
SELECT name ,  LOCATION FROM station ORDER BY Annual_entry_exit ,  Annual_interchanges
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`staff_address_id` INTEGER NOT NULL,
`nickname` VARCHAR(80),
`first_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`last_name` VARCHAR(80),
`date_of_birth` DATETIME,
`date_joined_staff` DATETIME,
`date_left_staff` DATETIME,
FOREIGN KEY (`staff_address_id` ) REFERENCES `Addresses`(`address_id` )
)

CREATE TABLE `Vehicles` (
`vehicle_id` INTEGER PRIMARY KEY,
`vehicle_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_address_id` INTEGER NOT NULL,
`customer_status_code` VARCHAR(15) NOT NULL,
`date_became_customer` DATETIME,
`date_of_birth` DATETIME,
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`amount_outstanding` DOUBLE NULL,
`email_address` VARCHAR(250),
`phone_number` VARCHAR(255),
`cell_mobile_phone_number` VARCHAR(255),
FOREIGN KEY (`customer_address_id` ) REFERENCES `Addresses`(`address_id` )
)

CREATE TABLE `Customer_Payments` (
`customer_id` INTEGER NOT NULL,
`datetime_payment` DATETIME NOT NULL,
`payment_method_code` VARCHAR(10) NOT NULL,
`amount_payment` DOUBLE NULL,
PRIMARY KEY (`customer_id`,`datetime_payment`),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Lessons` (
`lesson_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`lesson_status_code` VARCHAR(15) NOT NULL,
`staff_id` INTEGER,
`vehicle_id` INTEGER NOT NULL,
`lesson_date` DATETIME,
`lesson_time` VARCHAR(10),
`price` DOUBLE NULL,
FOREIGN KEY (`vehicle_id` ) REFERENCES `Vehicles`(`vehicle_id` ),
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
List all vehicle id

### SQL
SELECT vehicle_id FROM Vehicles;
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`staff_address_id` INTEGER NOT NULL,
`nickname` VARCHAR(80),
`first_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`last_name` VARCHAR(80),
`date_of_birth` DATETIME,
`date_joined_staff` DATETIME,
`date_left_staff` DATETIME,
FOREIGN KEY (`staff_address_id` ) REFERENCES `Addresses`(`address_id` )
)

CREATE TABLE `Vehicles` (
`vehicle_id` INTEGER PRIMARY KEY,
`vehicle_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_address_id` INTEGER NOT NULL,
`customer_status_code` VARCHAR(15) NOT NULL,
`date_became_customer` DATETIME,
`date_of_birth` DATETIME,
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`amount_outstanding` DOUBLE NULL,
`email_address` VARCHAR(250),
`phone_number` VARCHAR(255),
`cell_mobile_phone_number` VARCHAR(255),
FOREIGN KEY (`customer_address_id` ) REFERENCES `Addresses`(`address_id` )
)

CREATE TABLE `Customer_Payments` (
`customer_id` INTEGER NOT NULL,
`datetime_payment` DATETIME NOT NULL,
`payment_method_code` VARCHAR(10) NOT NULL,
`amount_payment` DOUBLE NULL,
PRIMARY KEY (`customer_id`,`datetime_payment`),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Lessons` (
`lesson_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`lesson_status_code` VARCHAR(15) NOT NULL,
`staff_id` INTEGER,
`vehicle_id` INTEGER NOT NULL,
`lesson_date` DATETIME,
`lesson_time` VARCHAR(10),
`price` DOUBLE NULL,
FOREIGN KEY (`vehicle_id` ) REFERENCES `Vehicles`(`vehicle_id` ),
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
What are the ids of all vehicles?

### SQL
SELECT vehicle_id FROM Vehicles;
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`staff_address_id` INTEGER NOT NULL,
`nickname` VARCHAR(80),
`first_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`last_name` VARCHAR(80),
`date_of_birth` DATETIME,
`date_joined_staff` DATETIME,
`date_left_staff` DATETIME,
FOREIGN KEY (`staff_address_id` ) REFERENCES `Addresses`(`address_id` )
)

CREATE TABLE `Vehicles` (
`vehicle_id` INTEGER PRIMARY KEY,
`vehicle_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_address_id` INTEGER NOT NULL,
`customer_status_code` VARCHAR(15) NOT NULL,
`date_became_customer` DATETIME,
`date_of_birth` DATETIME,
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`amount_outstanding` DOUBLE NULL,
`email_address` VARCHAR(250),
`phone_number` VARCHAR(255),
`cell_mobile_phone_number` VARCHAR(255),
FOREIGN KEY (`customer_address_id` ) REFERENCES `Addresses`(`address_id` )
)

CREATE TABLE `Customer_Payments` (
`customer_id` INTEGER NOT NULL,
`datetime_payment` DATETIME NOT NULL,
`payment_method_code` VARCHAR(10) NOT NULL,
`amount_payment` DOUBLE NULL,
PRIMARY KEY (`customer_id`,`datetime_payment`),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Lessons` (
`lesson_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`lesson_status_code` VARCHAR(15) NOT NULL,
`staff_id` INTEGER,
`vehicle_id` INTEGER NOT NULL,
`lesson_date` DATETIME,
`lesson_time` VARCHAR(10),
`price` DOUBLE NULL,
FOREIGN KEY (`vehicle_id` ) REFERENCES `Vehicles`(`vehicle_id` ),
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
How many vehicle in total?

### SQL
SELECT count(*) FROM Vehicles;
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`staff_address_id` INTEGER NOT NULL,
`nickname` VARCHAR(80),
`first_name` VARCHAR(80),
`middle_name` VARCHAR(80),
`last_name` VARCHAR(80),
`date_of_birth` DATETIME,
`date_joined_staff` DATETIME,
`date_left_staff` DATETIME,
FOREIGN KEY (`staff_address_id` ) REFERENCES `Addresses`(`address_id` )
)

CREATE TABLE `Vehicles` (
`vehicle_id` INTEGER PRIMARY KEY,
`vehicle_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_address_id` INTEGER NOT NULL,
`customer_status_code` VARCHAR(15) NOT NULL,
`date_became_customer` DATETIME,
`date_of_birth` DATETIME,
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`amount_outstanding` DOUBLE NULL,
`email_address` VARCHAR(250),
`phone_number` VARCHAR(255),
`cell_mobile_phone_number` VARCHAR(255),
FOREIGN KEY (`customer_address_id` ) REFERENCES `Addresses`(`address_id` )
)

CREATE TABLE `Customer_Payments` (
`customer_id` INTEGER NOT NULL,
`datetime_payment` DATETIME NOT NULL,
`payment_method_code` VARCHAR(10) NOT NULL,
`amount_payment` DOUBLE NULL,
PRIMARY KEY (`customer_id`,`datetime_payment`),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Lessons` (
`lesson_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`lesson_status_code` VARCHAR(15) NOT NULL,
`staff_id` INTEGER,
`vehicle_id` INTEGER NOT NULL,
`lesson_date` DATETIME,
`lesson_time` VARCHAR(10),
`price` DOUBLE NULL,
FOREIGN KEY (`vehicle_id` ) REFERENCES `Vehicles`(`vehicle_id` ),
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
How many vehicles exist?

### SQL
SELECT count(*) FROM Vehicles;