lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
Sort all captain names by their ages from old to young.

### SQL
SELECT name FROM captain ORDER BY age DESC
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
What are the names of captains, sorted by age descending?

### SQL
SELECT name FROM captain ORDER BY age DESC
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
Find the name, class and rank of all captains.

### SQL
SELECT name ,  CLASS ,  rank FROM captain
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
What are the names, classes, and ranks of all captains?

### SQL
SELECT name ,  CLASS ,  rank FROM captain
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
Which rank is the most common among captains?

### SQL
SELECT rank FROM captain GROUP BY rank ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
Return the rank for which there are the fewest captains.

### SQL
SELECT rank FROM captain GROUP BY rank ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
Which classes have more than two captains?

### SQL
SELECT CLASS FROM captain GROUP BY CLASS HAVING count(*)  >  2
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
Give the classes that have more than two captains.

### SQL
SELECT CLASS FROM captain GROUP BY CLASS HAVING count(*)  >  2
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
Find the name of captains whose rank are either Midshipman or Lieutenant.

### SQL
SELECT name FROM captain WHERE rank  =  'Midshipman' OR rank  =  'Lieutenant'
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
What are the names of captains that have either the rank Midshipman or Lieutenant?

### SQL
SELECT name FROM captain WHERE rank  =  'Midshipman' OR rank  =  'Lieutenant'
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
What are the average and minimum age of captains in different class?

### SQL
SELECT avg(age) ,  min(age) ,  CLASS FROM captain GROUP BY CLASS
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
Return the average and minimum age of captains in each class.

### SQL
SELECT avg(age) ,  min(age) ,  CLASS FROM captain GROUP BY CLASS
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
Find the captain rank that has some captains in both Cutter and Armed schooner classes.

### SQL
SELECT rank FROM captain WHERE CLASS  =  'Cutter' INTERSECT SELECT rank FROM captain WHERE CLASS  =  'Armed schooner'
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
What are the ranks of captains that are both in the Cutter and Armed schooner classes?

### SQL
SELECT rank FROM captain WHERE CLASS  =  'Cutter' INTERSECT SELECT rank FROM captain WHERE CLASS  =  'Armed schooner'
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
Find the captain rank that has no captain in Third-rate ship of the line class.

### SQL
SELECT rank FROM captain EXCEPT SELECT rank FROM captain WHERE CLASS  =  'Third-rate ship of the line'
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
What are the ranks of captains that have no captain that are in the Third-rate ship of the line class?

### SQL
SELECT rank FROM captain EXCEPT SELECT rank FROM captain WHERE CLASS  =  'Third-rate ship of the line'
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
What is the name of the youngest captain?

### SQL
SELECT name FROM captain ORDER BY age LIMIT 1
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
Return the name of the youngest captain.

### SQL
SELECT name FROM captain ORDER BY age LIMIT 1
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
how many ships are there?

### SQL
SELECT count(*) FROM ship
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
Count the number of ships.

### SQL
SELECT count(*) FROM ship
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
Find the name, type, and flag of the ship that is built in the most recent year.

### SQL
SELECT name ,  TYPE ,  flag FROM ship ORDER BY built_year DESC LIMIT 1
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
What is the name, type, and flag of the ship that was built in the most recent year?

### SQL
SELECT name ,  TYPE ,  flag FROM ship ORDER BY built_year DESC LIMIT 1
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
Group by ships by flag, and return number of ships that have each flag.

### SQL
SELECT count(*) ,  flag FROM ship GROUP BY flag
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
What are the different ship flags, and how many ships have each?

### SQL
SELECT count(*) ,  flag FROM ship GROUP BY flag
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
Which flag is most widely used among all ships?

### SQL
SELECT flag FROM ship GROUP BY flag ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
Return the flag that is most common among all ships.

### SQL
SELECT flag FROM ship GROUP BY flag ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
List all ship names in the order of built year and class.

### SQL
SELECT name FROM ship ORDER BY built_year ,  CLASS
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
What are the names of ships, ordered by year they were built and their class?

### SQL
SELECT name FROM ship ORDER BY built_year ,  CLASS
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
Find the ship type that are used by both ships with Panama and Malta flags.

### SQL
SELECT TYPE FROM ship WHERE flag  =  'Panama' INTERSECT SELECT TYPE FROM ship WHERE flag  =  'Malta'
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
What types of ships have both ships that have Panama Flags and Malta flags?

### SQL
SELECT TYPE FROM ship WHERE flag  =  'Panama' INTERSECT SELECT TYPE FROM ship WHERE flag  =  'Malta'
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
In which year were most of ships built?

### SQL
SELECT built_year FROM ship GROUP BY built_year ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
What is the year in which most ships were built?

### SQL
SELECT built_year FROM ship GROUP BY built_year ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
Find the name of the ships that have more than one captain.

### SQL
SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id GROUP BY t2.ship_id HAVING count(*)  >  1
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
What are the names of ships that have more than one captain?

### SQL
SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id GROUP BY t2.ship_id HAVING count(*)  >  1
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
what are the names and classes of the ships that do not have any captain yet?

### SQL
SELECT name ,  CLASS FROM ship WHERE ship_id NOT IN (SELECT ship_id FROM captain)
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
Return the names and classes of ships that do not have a captain?

### SQL
SELECT name ,  CLASS FROM ship WHERE ship_id NOT IN (SELECT ship_id FROM captain)
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
Find the name of the ship that is steered by the youngest captain.

### SQL
SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id ORDER BY t2.age LIMIT 1
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
What is the name of the ship that is commanded by the youngest captain?

### SQL
SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id ORDER BY t2.age LIMIT 1
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
Find the name and flag of ships that are not steered by any captain with Midshipman rank.

### SQL
SELECT name ,  flag FROM ship WHERE ship_id NOT IN (SELECT ship_id FROM captain WHERE rank  =  'Midshipman')
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
What are the names and flags of ships that do not have a captain with the rank of Midshipman?

### SQL
SELECT name ,  flag FROM ship WHERE ship_id NOT IN (SELECT ship_id FROM captain WHERE rank  =  'Midshipman')
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
Find the name of the ships that are steered by both a captain with Midshipman rank and a captain with Lieutenant rank.

### SQL
SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id WHERE t2.rank  =  'Midshipman' INTERSECT SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id WHERE t2.rank  =  'Lieutenant'
### Database Schema
CREATE TABLE "captain" (
"Captain_ID" int,
"Name" text,
"Ship_ID" int,
"age" text,
"Class" text,
"Rank" text,
PRIMARY KEY ("Captain_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES "Ship"("Ship_ID")
)

CREATE TABLE "Ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Built_Year" real,
"Class" text,
"Flag" text,
PRIMARY KEY ("Ship_ID")
)

### Question
What are the names of ships that are commanded by both captains with the rank of Midshipman and captains with the rank of Lieutenant?

### SQL
SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id WHERE t2.rank  =  'Midshipman' INTERSECT SELECT t1.name FROM ship AS t1 JOIN captain AS t2 ON t1.ship_id  =  t2.ship_id WHERE t2.rank  =  'Lieutenant'
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
What is id of the city that hosted events in the most recent year?

### SQL
SELECT host_city FROM hosting_city ORDER BY YEAR DESC LIMIT 1
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
Find the city that hosted some events in the most recent year. What is the id of this city?

### SQL
SELECT host_city FROM hosting_city ORDER BY YEAR DESC LIMIT 1
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
Find the match ids of the cities that hosted competition "1994 FIFA World Cup qualification"?

### SQL
SELECT match_id FROM MATCH WHERE competition = "1994 FIFA World Cup qualification"
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
What is the match id of the competition called "1994 FIFA World Cup qualification"?

### SQL
SELECT match_id FROM MATCH WHERE competition = "1994 FIFA World Cup qualification"
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
Find the cities which were once a host city after 2010?

### SQL
SELECT T1.city FROM city AS T1 JOIN hosting_city AS T2 ON T1.city_id = T2.host_city WHERE T2.year  >  2010
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
Which cities served as a host city after 2010?

### SQL
SELECT T1.city FROM city AS T1 JOIN hosting_city AS T2 ON T1.city_id = T2.host_city WHERE T2.year  >  2010
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
Which city has hosted the most events?

### SQL
SELECT T1.city FROM city AS T1 JOIN hosting_city AS T2 ON T1.city_id  =  T2.host_city GROUP BY T2.host_city ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
Find the city that hosted the most events.

### SQL
SELECT T1.city FROM city AS T1 JOIN hosting_city AS T2 ON T1.city_id  =  T2.host_city GROUP BY T2.host_city ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
What is the venue of the competition "1994 FIFA World Cup qualification" hosted by "Nanjing ( Jiangsu )"?

### SQL
SELECT T3.venue FROM city AS T1 JOIN hosting_city AS T2 ON T1.city_id = T2.host_city JOIN MATCH AS T3 ON T2.match_id = T3.match_id WHERE T1.city = "Nanjing ( Jiangsu )" AND T3.competition = "1994 FIFA World Cup qualification"
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
Find the venue of the competition "1994 FIFA World Cup qualification" which was hosted by "Nanjing ( Jiangsu )".

### SQL
SELECT T3.venue FROM city AS T1 JOIN hosting_city AS T2 ON T1.city_id = T2.host_city JOIN MATCH AS T3 ON T2.match_id = T3.match_id WHERE T1.city = "Nanjing ( Jiangsu )" AND T3.competition = "1994 FIFA World Cup qualification"
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
Give me the temperature of Shanghai in January.

### SQL
SELECT T2.Jan FROM city AS T1 JOIN temperature AS T2 ON T1.city_id = T2.city_id WHERE T1.city = "Shanghai"
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
What is the temperature of "Shanghai" city in January?

### SQL
SELECT T2.Jan FROM city AS T1 JOIN temperature AS T2 ON T1.city_id = T2.city_id WHERE T1.city = "Shanghai"
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
What is the host year of city "Taizhou ( Zhejiang )"?

### SQL
SELECT T2.year FROM city AS T1 JOIN hosting_city AS T2 ON T1.city_id  =  T2.host_city WHERE T1.city  =  "Taizhou ( Zhejiang )"
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
IN which year did city "Taizhou ( Zhejiang )" serve as a host city?

### SQL
SELECT T2.year FROM city AS T1 JOIN hosting_city AS T2 ON T1.city_id  =  T2.host_city WHERE T1.city  =  "Taizhou ( Zhejiang )"
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
Which three cities have the largest regional population?

### SQL
SELECT city FROM city ORDER BY regional_population DESC LIMIT 3
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
What are the three largest cities in terms of regional population?

### SQL
SELECT city FROM city ORDER BY regional_population DESC LIMIT 3
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
Which city has the lowest GDP? Please list the city name and its GDP.

### SQL
SELECT city ,  GDP FROM city ORDER BY GDP LIMIT 1
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
What is the city with the smallest GDP? Return the city and its GDP.

### SQL
SELECT city ,  GDP FROM city ORDER BY GDP LIMIT 1
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
Which city has the highest temperature in February?

### SQL
SELECT T1.city FROM city AS T1 JOIN temperature AS T2 ON T1.city_id  =  T2.city_id ORDER BY T2.Feb DESC LIMIT 1
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
In February, which city marks the highest temperature?

### SQL
SELECT T1.city FROM city AS T1 JOIN temperature AS T2 ON T1.city_id  =  T2.city_id ORDER BY T2.Feb DESC LIMIT 1
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
Give me a list of cities whose temperature in March is lower than that in July or higher than that in Oct?

### SQL
SELECT T1.city FROM city AS T1 JOIN temperature AS T2 ON T1.city_id  =  T2.city_id WHERE T2.Mar  <  T2.Jul OR T2.Mar  >  T2.Oct
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
Which cities' temperature in March is lower than that in July or higher than that in Oct?

### SQL
SELECT T1.city FROM city AS T1 JOIN temperature AS T2 ON T1.city_id  =  T2.city_id WHERE T2.Mar  <  T2.Jul OR T2.Mar  >  T2.Oct
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
Give me a list of cities whose temperature in Mar is lower than that in July and which have also served as host cities?

### SQL
SELECT T1.city FROM city AS T1 JOIN temperature AS T2 ON T1.city_id  =  T2.city_id WHERE T2.Mar  <  T2.Jul INTERSECT SELECT T3.city FROM city AS T3 JOIN hosting_city AS T4 ON T3.city_id  =  T4.host_city
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
Which cities have lower temperature in March than in July and have been once host cities?

### SQL
SELECT T1.city FROM city AS T1 JOIN temperature AS T2 ON T1.city_id  =  T2.city_id WHERE T2.Mar  <  T2.Jul INTERSECT SELECT T3.city FROM city AS T3 JOIN hosting_city AS T4 ON T3.city_id  =  T4.host_city
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
Give me a list of cities whose temperature in Mar is lower than that in Dec and which have never been host cities.

### SQL
SELECT T1.city FROM city AS T1 JOIN temperature AS T2 ON T1.city_id  =  T2.city_id WHERE T2.Mar  <  T2.Dec EXCEPT SELECT T3.city FROM city AS T3 JOIN hosting_city AS T4 ON T3.city_id  =  T4.host_city
### Database Schema
CREATE TABLE "city" (
"City_ID" int,
"City" text,
"Hanzi" text,
"Hanyu_Pinyin" text,
"Regional_Population" int,
"GDP" real,
PRIMARY KEY ("City_ID")
)

CREATE TABLE "match" (
"Match_ID" int,
"Date" text,
"Venue" text,
"Score" text,
"Result" text,
"Competition" text,
PRIMARY KEY ("Match_ID")
)

CREATE TABLE "temperature" (
"City_ID" int,
"Jan" real,
"Feb" real,
"Mar" real,
"Apr" real,
"Jun" real,
"Jul" real,
"Aug" real,
"Sep" real,
"Oct" real,
"Nov" real,
"Dec" real,
PRIMARY KEY ("City_ID"),
FOREIGN KEY (`City_ID`) REFERENCES `city`(`City_ID`)
)

CREATE TABLE "hosting_city" (
  "Year" int,
  "Match_ID" int,
  "Host_City" text,
  PRIMARY KEY ("Year"),
  FOREIGN KEY (`Host_City`) REFERENCES `city`(`City_ID`),
  FOREIGN KEY (`Match_ID`) REFERENCES `match`(`Match_ID`)
)

### Question
Which cities have lower temperature in March than in Dec and have never served as host cities?

### SQL
SELECT T1.city FROM city AS T1 JOIN temperature AS T2 ON T1.city_id  =  T2.city_id WHERE T2.Mar  <  T2.Dec EXCEPT SELECT T3.city FROM city AS T3 JOIN hosting_city AS T4 ON T3.city_id  =  T4.host_city