lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
)

CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
)

CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
)

CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
)

### Question
Find the country that has the most stadiums.

### SQL
SELECT country FROM stadium GROUP BY country ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
)

CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
)

CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
)

CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
)

### Question
Which country has at most 3 stadiums listed?

### SQL
SELECT country FROM stadium GROUP BY country HAVING count(*)  <=  3
### Database Schema
CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
)

CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
)

CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
)

CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
)

### Question
Which country has both stadiums with capacity greater than 60000 and stadiums with capacity less than 50000?

### SQL
SELECT country FROM stadium WHERE capacity  >  60000 INTERSECT SELECT country FROM stadium WHERE capacity  <  50000
### Database Schema
CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
)

CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
)

CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
)

CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
)

### Question
How many cities have a stadium that was opened before the year of 2006?

### SQL
SELECT count(DISTINCT city) FROM stadium WHERE opening_year  <  2006
### Database Schema
CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
)

CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
)

CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
)

CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
)

### Question
How many stadiums does each country have?

### SQL
SELECT country ,  count(*) FROM stadium GROUP BY country
### Database Schema
CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
)

CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
)

CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
)

CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
)

### Question
Which countries do not have a stadium that was opened after 2006?

### SQL
SELECT country FROM stadium EXCEPT SELECT country FROM stadium WHERE opening_year  >  2006
### Database Schema
CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
)

CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
)

CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
)

CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
)

### Question
How many stadiums are not in country "Russia"?

### SQL
SELECT count(*) FROM stadium WHERE country != 'Russia'
### Database Schema
CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
)

CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
)

CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
)

CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
)

### Question
Find the names of all swimmers, sorted by their 100 meter scores in ascending order.

### SQL
SELECT name FROM swimmer ORDER BY meter_100
### Database Schema
CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
)

CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
)

CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
)

CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
)

### Question
How many different countries are all the swimmers from?

### SQL
SELECT count(DISTINCT nationality) FROM swimmer
### Database Schema
CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
)

CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
)

CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
)

CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
)

### Question
List countries that have more than one swimmer.

### SQL
SELECT nationality ,  count(*) FROM swimmer GROUP BY nationality HAVING count(*)  >  1
### Database Schema
CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
)

CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
)

CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
)

CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
)

### Question
Find all 200 meter and 300 meter results of swimmers with nationality "Australia".

### SQL
SELECT meter_200 ,  meter_300 FROM swimmer WHERE nationality  =  'Australia'
### Database Schema
CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
)

CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
)

CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
)

CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
)

### Question
Find the names of swimmers who has a result of "win".

### SQL
SELECT t1.name FROM swimmer AS t1 JOIN record AS t2 ON t1.id  =  t2.swimmer_id WHERE RESULT  =  'Win'
### Database Schema
CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
)

CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
)

CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
)

CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
)

### Question
What is the name of the stadium which held the most events?

### SQL
SELECT t1.name FROM stadium AS t1 JOIN event AS t2 ON t1.id  =  t2.stadium_id GROUP BY t2.stadium_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
)

CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
)

CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
)

CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
)

### Question
Find the name and capacity of the stadium where the event named "World Junior" happened.

### SQL
SELECT t1.name ,  t1.capacity FROM stadium AS t1 JOIN event AS t2 ON t1.id  =  t2.stadium_id WHERE t2.name  =  'World Junior'
### Database Schema
CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
)

CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
)

CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
)

CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
)

### Question
Find the names of stadiums which have never had any event.

### SQL
SELECT name FROM stadium WHERE id NOT IN (SELECT stadium_id FROM event)
### Database Schema
CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
)

CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
)

CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
)

CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
)

### Question
Find the name of the swimmer who has the most records.

### SQL
SELECT t1.name FROM swimmer AS t1 JOIN record AS t2 ON t1.id  =  t2.swimmer_id GROUP BY t2.swimmer_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
)

CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
)

CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
)

CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
)

### Question
Find the name of the swimmer who has at least 2 records.

### SQL
SELECT t1.name FROM swimmer AS t1 JOIN record AS t2 ON t1.id  =  t2.swimmer_id GROUP BY t2.swimmer_id HAVING count(*)  >=  2
### Database Schema
CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
)

CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
)

CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
)

CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
)

### Question
Find the name and nationality of the swimmer who has won (i.e., has a result of "win") more than 1 time.

### SQL
SELECT t1.name ,  t1.nationality FROM swimmer AS t1 JOIN record AS t2 ON t1.id  =  t2.swimmer_id WHERE RESULT  =  'Win' GROUP BY t2.swimmer_id HAVING count(*)  >  1
### Database Schema
CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
)

CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
)

CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
)

CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
)

### Question
Find the names of the swimmers who have no record.

### SQL
SELECT name FROM swimmer WHERE id NOT IN (SELECT swimmer_id FROM record)
### Database Schema
CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
)

CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
)

CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
)

CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
)

### Question
Find the names of the swimmers who have both "win" and "loss" results in the record.

### SQL
SELECT t1.name FROM swimmer AS t1 JOIN record AS t2 ON t1.id  =  t2.swimmer_id WHERE RESULT  =  'Win' INTERSECT SELECT t1.name FROM swimmer AS t1 JOIN record AS t2 ON t1.id  =  t2.swimmer_id WHERE RESULT  =  'Loss'
### Database Schema
CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
)

CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
)

CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
)

CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
)

### Question
Find the names of stadiums that some Australian swimmers have been to.

### SQL
SELECT t4.name FROM swimmer AS t1 JOIN record AS t2 ON t1.id  =  t2.swimmer_id JOIN event AS t3 ON t2.event_id  =  t3.id JOIN stadium AS t4 ON t4.id  =  t3.stadium_id WHERE t1.nationality  =  'Australia'
### Database Schema
CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
)

CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
)

CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
)

CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
)

### Question
Find the names of stadiums that the most swimmers have been to.

### SQL
SELECT t3.name FROM record AS t1 JOIN event AS t2 ON t1.event_id  =  t2.id JOIN stadium AS t3 ON t3.id  =  t2.stadium_id GROUP BY t2.stadium_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
)

CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
)

CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
)

CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
)

### Question
Find all details for each swimmer.

### SQL
SELECT * FROM swimmer
### Database Schema
CREATE TABLE "swimmer" (
"ID" int,
"name" text,
"Nationality" text,
"meter_100" real,
"meter_200" text,
"meter_300" text,
"meter_400" text,
"meter_500" text,
"meter_600" text,
"meter_700" text,
"Time" text,
PRIMARY KEY ("ID")
)

CREATE TABLE "stadium" (
"ID" int,
"name" text,
"Capacity" int,
"City" text,
"Country" text,
"Opening_year" int,
PRIMARY KEY ("ID")
)

CREATE TABLE "event" (
"ID" int,
"Name" text,
"Stadium_ID" int,
"Year" text,
PRIMARY KEY ("ID"),
FOREIGN KEY (`Stadium_ID`) REFERENCES `stadium`(`ID`)
)

CREATE TABLE "record" (
"ID" int,
"Result" text,
"Swimmer_ID" int,
"Event_ID" int,
PRIMARY KEY ("Swimmer_ID","Event_ID"),
FOREIGN KEY (`Event_ID`) REFERENCES `event`(`ID`),
FOREIGN KEY (`Swimmer_ID`) REFERENCES `swimmer`(`ID`)
)

### Question
What is the average capacity of the stadiums that were opened in year 2005?

### SQL
SELECT avg(capacity) FROM stadium WHERE opening_year  =  2005
### Database Schema
CREATE TABLE "railway" (
"Railway_ID" int,
"Railway" text,
"Builder" text,
"Built" text,
"Wheels" text,
"Location" text,
"ObjectNumber" text,
PRIMARY KEY ("Railway_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Train_Num" text,
"Name" text,
"From" text,
"Arrival" text,
"Railway_ID" int,
PRIMARY KEY ("Train_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES `railway`("Railway_ID")
)

CREATE TABLE "manager" (
"Manager_ID" int,
"Name" text,
"Country" text,
"Working_year_starts" text,
"Age" int,
"Level" int,
PRIMARY KEY ("Manager_ID")
)

CREATE TABLE "railway_manage" (
"Railway_ID" int,
"Manager_ID" int,
"From_Year" text,
PRIMARY KEY ("Railway_ID","Manager_ID"),
FOREIGN KEY ("Manager_ID") REFERENCES "manager"("Manager_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES "railway"("Railway_ID")
)

### Question
How many railways are there?

### SQL
SELECT count(*) FROM railway
### Database Schema
CREATE TABLE "railway" (
"Railway_ID" int,
"Railway" text,
"Builder" text,
"Built" text,
"Wheels" text,
"Location" text,
"ObjectNumber" text,
PRIMARY KEY ("Railway_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Train_Num" text,
"Name" text,
"From" text,
"Arrival" text,
"Railway_ID" int,
PRIMARY KEY ("Train_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES `railway`("Railway_ID")
)

CREATE TABLE "manager" (
"Manager_ID" int,
"Name" text,
"Country" text,
"Working_year_starts" text,
"Age" int,
"Level" int,
PRIMARY KEY ("Manager_ID")
)

CREATE TABLE "railway_manage" (
"Railway_ID" int,
"Manager_ID" int,
"From_Year" text,
PRIMARY KEY ("Railway_ID","Manager_ID"),
FOREIGN KEY ("Manager_ID") REFERENCES "manager"("Manager_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES "railway"("Railway_ID")
)

### Question
List the builders of railways in ascending alphabetical order.

### SQL
SELECT Builder FROM railway ORDER BY Builder ASC
### Database Schema
CREATE TABLE "railway" (
"Railway_ID" int,
"Railway" text,
"Builder" text,
"Built" text,
"Wheels" text,
"Location" text,
"ObjectNumber" text,
PRIMARY KEY ("Railway_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Train_Num" text,
"Name" text,
"From" text,
"Arrival" text,
"Railway_ID" int,
PRIMARY KEY ("Train_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES `railway`("Railway_ID")
)

CREATE TABLE "manager" (
"Manager_ID" int,
"Name" text,
"Country" text,
"Working_year_starts" text,
"Age" int,
"Level" int,
PRIMARY KEY ("Manager_ID")
)

CREATE TABLE "railway_manage" (
"Railway_ID" int,
"Manager_ID" int,
"From_Year" text,
PRIMARY KEY ("Railway_ID","Manager_ID"),
FOREIGN KEY ("Manager_ID") REFERENCES "manager"("Manager_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES "railway"("Railway_ID")
)

### Question
List the wheels and locations of the railways.

### SQL
SELECT Wheels ,  LOCATION FROM railway
### Database Schema
CREATE TABLE "railway" (
"Railway_ID" int,
"Railway" text,
"Builder" text,
"Built" text,
"Wheels" text,
"Location" text,
"ObjectNumber" text,
PRIMARY KEY ("Railway_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Train_Num" text,
"Name" text,
"From" text,
"Arrival" text,
"Railway_ID" int,
PRIMARY KEY ("Train_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES `railway`("Railway_ID")
)

CREATE TABLE "manager" (
"Manager_ID" int,
"Name" text,
"Country" text,
"Working_year_starts" text,
"Age" int,
"Level" int,
PRIMARY KEY ("Manager_ID")
)

CREATE TABLE "railway_manage" (
"Railway_ID" int,
"Manager_ID" int,
"From_Year" text,
PRIMARY KEY ("Railway_ID","Manager_ID"),
FOREIGN KEY ("Manager_ID") REFERENCES "manager"("Manager_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES "railway"("Railway_ID")
)

### Question
What is the maximum level of managers in countries that are not "Australia"?

### SQL
SELECT max(LEVEL) FROM manager WHERE Country != "Australia	"
### Database Schema
CREATE TABLE "railway" (
"Railway_ID" int,
"Railway" text,
"Builder" text,
"Built" text,
"Wheels" text,
"Location" text,
"ObjectNumber" text,
PRIMARY KEY ("Railway_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Train_Num" text,
"Name" text,
"From" text,
"Arrival" text,
"Railway_ID" int,
PRIMARY KEY ("Train_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES `railway`("Railway_ID")
)

CREATE TABLE "manager" (
"Manager_ID" int,
"Name" text,
"Country" text,
"Working_year_starts" text,
"Age" int,
"Level" int,
PRIMARY KEY ("Manager_ID")
)

CREATE TABLE "railway_manage" (
"Railway_ID" int,
"Manager_ID" int,
"From_Year" text,
PRIMARY KEY ("Railway_ID","Manager_ID"),
FOREIGN KEY ("Manager_ID") REFERENCES "manager"("Manager_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES "railway"("Railway_ID")
)

### Question
What is the average age for all managers?

### SQL
SELECT avg(Age) FROM manager
### Database Schema
CREATE TABLE "railway" (
"Railway_ID" int,
"Railway" text,
"Builder" text,
"Built" text,
"Wheels" text,
"Location" text,
"ObjectNumber" text,
PRIMARY KEY ("Railway_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Train_Num" text,
"Name" text,
"From" text,
"Arrival" text,
"Railway_ID" int,
PRIMARY KEY ("Train_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES `railway`("Railway_ID")
)

CREATE TABLE "manager" (
"Manager_ID" int,
"Name" text,
"Country" text,
"Working_year_starts" text,
"Age" int,
"Level" int,
PRIMARY KEY ("Manager_ID")
)

CREATE TABLE "railway_manage" (
"Railway_ID" int,
"Manager_ID" int,
"From_Year" text,
PRIMARY KEY ("Railway_ID","Manager_ID"),
FOREIGN KEY ("Manager_ID") REFERENCES "manager"("Manager_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES "railway"("Railway_ID")
)

### Question
What are the names of managers in ascending order of level?

### SQL
SELECT Name FROM manager ORDER BY LEVEL ASC
### Database Schema
CREATE TABLE "railway" (
"Railway_ID" int,
"Railway" text,
"Builder" text,
"Built" text,
"Wheels" text,
"Location" text,
"ObjectNumber" text,
PRIMARY KEY ("Railway_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Train_Num" text,
"Name" text,
"From" text,
"Arrival" text,
"Railway_ID" int,
PRIMARY KEY ("Train_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES `railway`("Railway_ID")
)

CREATE TABLE "manager" (
"Manager_ID" int,
"Name" text,
"Country" text,
"Working_year_starts" text,
"Age" int,
"Level" int,
PRIMARY KEY ("Manager_ID")
)

CREATE TABLE "railway_manage" (
"Railway_ID" int,
"Manager_ID" int,
"From_Year" text,
PRIMARY KEY ("Railway_ID","Manager_ID"),
FOREIGN KEY ("Manager_ID") REFERENCES "manager"("Manager_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES "railway"("Railway_ID")
)

### Question
What are the names and arrival times of trains?

### SQL
SELECT Name ,  Arrival FROM train
### Database Schema
CREATE TABLE "railway" (
"Railway_ID" int,
"Railway" text,
"Builder" text,
"Built" text,
"Wheels" text,
"Location" text,
"ObjectNumber" text,
PRIMARY KEY ("Railway_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Train_Num" text,
"Name" text,
"From" text,
"Arrival" text,
"Railway_ID" int,
PRIMARY KEY ("Train_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES `railway`("Railway_ID")
)

CREATE TABLE "manager" (
"Manager_ID" int,
"Name" text,
"Country" text,
"Working_year_starts" text,
"Age" int,
"Level" int,
PRIMARY KEY ("Manager_ID")
)

CREATE TABLE "railway_manage" (
"Railway_ID" int,
"Manager_ID" int,
"From_Year" text,
PRIMARY KEY ("Railway_ID","Manager_ID"),
FOREIGN KEY ("Manager_ID") REFERENCES "manager"("Manager_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES "railway"("Railway_ID")
)

### Question
What is the name of the oldest manager?

### SQL
SELECT Name FROM manager ORDER BY Age DESC LIMIT 1
### Database Schema
CREATE TABLE "railway" (
"Railway_ID" int,
"Railway" text,
"Builder" text,
"Built" text,
"Wheels" text,
"Location" text,
"ObjectNumber" text,
PRIMARY KEY ("Railway_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Train_Num" text,
"Name" text,
"From" text,
"Arrival" text,
"Railway_ID" int,
PRIMARY KEY ("Train_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES `railway`("Railway_ID")
)

CREATE TABLE "manager" (
"Manager_ID" int,
"Name" text,
"Country" text,
"Working_year_starts" text,
"Age" int,
"Level" int,
PRIMARY KEY ("Manager_ID")
)

CREATE TABLE "railway_manage" (
"Railway_ID" int,
"Manager_ID" int,
"From_Year" text,
PRIMARY KEY ("Railway_ID","Manager_ID"),
FOREIGN KEY ("Manager_ID") REFERENCES "manager"("Manager_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES "railway"("Railway_ID")
)

### Question
Show the names of trains and locations of railways they are in.

### SQL
SELECT T2.Name ,  T1.Location FROM railway AS T1 JOIN train AS T2 ON T1.Railway_ID  =  T2.Railway_ID
### Database Schema
CREATE TABLE "railway" (
"Railway_ID" int,
"Railway" text,
"Builder" text,
"Built" text,
"Wheels" text,
"Location" text,
"ObjectNumber" text,
PRIMARY KEY ("Railway_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Train_Num" text,
"Name" text,
"From" text,
"Arrival" text,
"Railway_ID" int,
PRIMARY KEY ("Train_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES `railway`("Railway_ID")
)

CREATE TABLE "manager" (
"Manager_ID" int,
"Name" text,
"Country" text,
"Working_year_starts" text,
"Age" int,
"Level" int,
PRIMARY KEY ("Manager_ID")
)

CREATE TABLE "railway_manage" (
"Railway_ID" int,
"Manager_ID" int,
"From_Year" text,
PRIMARY KEY ("Railway_ID","Manager_ID"),
FOREIGN KEY ("Manager_ID") REFERENCES "manager"("Manager_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES "railway"("Railway_ID")
)

### Question
Show the builder of railways associated with the trains named "Andaman Exp".

### SQL
SELECT T1.Builder FROM railway AS T1 JOIN train AS T2 ON T1.Railway_ID  =  T2.Railway_ID WHERE T2.Name  =  "Andaman Exp"
### Database Schema
CREATE TABLE "railway" (
"Railway_ID" int,
"Railway" text,
"Builder" text,
"Built" text,
"Wheels" text,
"Location" text,
"ObjectNumber" text,
PRIMARY KEY ("Railway_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Train_Num" text,
"Name" text,
"From" text,
"Arrival" text,
"Railway_ID" int,
PRIMARY KEY ("Train_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES `railway`("Railway_ID")
)

CREATE TABLE "manager" (
"Manager_ID" int,
"Name" text,
"Country" text,
"Working_year_starts" text,
"Age" int,
"Level" int,
PRIMARY KEY ("Manager_ID")
)

CREATE TABLE "railway_manage" (
"Railway_ID" int,
"Manager_ID" int,
"From_Year" text,
PRIMARY KEY ("Railway_ID","Manager_ID"),
FOREIGN KEY ("Manager_ID") REFERENCES "manager"("Manager_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES "railway"("Railway_ID")
)

### Question
Show id and location of railways that are associated with more than one train.

### SQL
SELECT T2.Railway_ID ,  T1.Location FROM railway AS T1 JOIN train AS T2 ON T1.Railway_ID  =  T2.Railway_ID GROUP BY T2.Railway_ID HAVING COUNT(*)  >  1
### Database Schema
CREATE TABLE "railway" (
"Railway_ID" int,
"Railway" text,
"Builder" text,
"Built" text,
"Wheels" text,
"Location" text,
"ObjectNumber" text,
PRIMARY KEY ("Railway_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Train_Num" text,
"Name" text,
"From" text,
"Arrival" text,
"Railway_ID" int,
PRIMARY KEY ("Train_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES `railway`("Railway_ID")
)

CREATE TABLE "manager" (
"Manager_ID" int,
"Name" text,
"Country" text,
"Working_year_starts" text,
"Age" int,
"Level" int,
PRIMARY KEY ("Manager_ID")
)

CREATE TABLE "railway_manage" (
"Railway_ID" int,
"Manager_ID" int,
"From_Year" text,
PRIMARY KEY ("Railway_ID","Manager_ID"),
FOREIGN KEY ("Manager_ID") REFERENCES "manager"("Manager_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES "railway"("Railway_ID")
)

### Question
Show the id and builder of the railway that are associated with the most trains.

### SQL
SELECT T2.Railway_ID ,  T1.Builder FROM railway AS T1 JOIN train AS T2 ON T1.Railway_ID  =  T2.Railway_ID GROUP BY T2.Railway_ID ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "railway" (
"Railway_ID" int,
"Railway" text,
"Builder" text,
"Built" text,
"Wheels" text,
"Location" text,
"ObjectNumber" text,
PRIMARY KEY ("Railway_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Train_Num" text,
"Name" text,
"From" text,
"Arrival" text,
"Railway_ID" int,
PRIMARY KEY ("Train_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES `railway`("Railway_ID")
)

CREATE TABLE "manager" (
"Manager_ID" int,
"Name" text,
"Country" text,
"Working_year_starts" text,
"Age" int,
"Level" int,
PRIMARY KEY ("Manager_ID")
)

CREATE TABLE "railway_manage" (
"Railway_ID" int,
"Manager_ID" int,
"From_Year" text,
PRIMARY KEY ("Railway_ID","Manager_ID"),
FOREIGN KEY ("Manager_ID") REFERENCES "manager"("Manager_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES "railway"("Railway_ID")
)

### Question
Show different builders of railways, along with the corresponding number of railways using each builder.

### SQL
SELECT Builder ,  COUNT(*) FROM railway GROUP BY Builder
### Database Schema
CREATE TABLE "railway" (
"Railway_ID" int,
"Railway" text,
"Builder" text,
"Built" text,
"Wheels" text,
"Location" text,
"ObjectNumber" text,
PRIMARY KEY ("Railway_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Train_Num" text,
"Name" text,
"From" text,
"Arrival" text,
"Railway_ID" int,
PRIMARY KEY ("Train_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES `railway`("Railway_ID")
)

CREATE TABLE "manager" (
"Manager_ID" int,
"Name" text,
"Country" text,
"Working_year_starts" text,
"Age" int,
"Level" int,
PRIMARY KEY ("Manager_ID")
)

CREATE TABLE "railway_manage" (
"Railway_ID" int,
"Manager_ID" int,
"From_Year" text,
PRIMARY KEY ("Railway_ID","Manager_ID"),
FOREIGN KEY ("Manager_ID") REFERENCES "manager"("Manager_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES "railway"("Railway_ID")
)

### Question
Show the most common builder of railways.

### SQL
SELECT Builder FROM railway GROUP BY Builder ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "railway" (
"Railway_ID" int,
"Railway" text,
"Builder" text,
"Built" text,
"Wheels" text,
"Location" text,
"ObjectNumber" text,
PRIMARY KEY ("Railway_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Train_Num" text,
"Name" text,
"From" text,
"Arrival" text,
"Railway_ID" int,
PRIMARY KEY ("Train_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES `railway`("Railway_ID")
)

CREATE TABLE "manager" (
"Manager_ID" int,
"Name" text,
"Country" text,
"Working_year_starts" text,
"Age" int,
"Level" int,
PRIMARY KEY ("Manager_ID")
)

CREATE TABLE "railway_manage" (
"Railway_ID" int,
"Manager_ID" int,
"From_Year" text,
PRIMARY KEY ("Railway_ID","Manager_ID"),
FOREIGN KEY ("Manager_ID") REFERENCES "manager"("Manager_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES "railway"("Railway_ID")
)

### Question
Show different locations of railways along with the corresponding number of railways at each location.

### SQL
SELECT LOCATION ,  COUNT(*) FROM railway GROUP BY LOCATION
### Database Schema
CREATE TABLE "railway" (
"Railway_ID" int,
"Railway" text,
"Builder" text,
"Built" text,
"Wheels" text,
"Location" text,
"ObjectNumber" text,
PRIMARY KEY ("Railway_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Train_Num" text,
"Name" text,
"From" text,
"Arrival" text,
"Railway_ID" int,
PRIMARY KEY ("Train_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES `railway`("Railway_ID")
)

CREATE TABLE "manager" (
"Manager_ID" int,
"Name" text,
"Country" text,
"Working_year_starts" text,
"Age" int,
"Level" int,
PRIMARY KEY ("Manager_ID")
)

CREATE TABLE "railway_manage" (
"Railway_ID" int,
"Manager_ID" int,
"From_Year" text,
PRIMARY KEY ("Railway_ID","Manager_ID"),
FOREIGN KEY ("Manager_ID") REFERENCES "manager"("Manager_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES "railway"("Railway_ID")
)

### Question
Show the locations that have more than one railways.

### SQL
SELECT LOCATION FROM railway GROUP BY LOCATION HAVING COUNT(*)  >  1
### Database Schema
CREATE TABLE "railway" (
"Railway_ID" int,
"Railway" text,
"Builder" text,
"Built" text,
"Wheels" text,
"Location" text,
"ObjectNumber" text,
PRIMARY KEY ("Railway_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Train_Num" text,
"Name" text,
"From" text,
"Arrival" text,
"Railway_ID" int,
PRIMARY KEY ("Train_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES `railway`("Railway_ID")
)

CREATE TABLE "manager" (
"Manager_ID" int,
"Name" text,
"Country" text,
"Working_year_starts" text,
"Age" int,
"Level" int,
PRIMARY KEY ("Manager_ID")
)

CREATE TABLE "railway_manage" (
"Railway_ID" int,
"Manager_ID" int,
"From_Year" text,
PRIMARY KEY ("Railway_ID","Manager_ID"),
FOREIGN KEY ("Manager_ID") REFERENCES "manager"("Manager_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES "railway"("Railway_ID")
)

### Question
List the object number of railways that do not have any trains.

### SQL
SELECT ObjectNumber FROM railway WHERE Railway_ID NOT IN (SELECT Railway_ID FROM train)
### Database Schema
CREATE TABLE "railway" (
"Railway_ID" int,
"Railway" text,
"Builder" text,
"Built" text,
"Wheels" text,
"Location" text,
"ObjectNumber" text,
PRIMARY KEY ("Railway_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Train_Num" text,
"Name" text,
"From" text,
"Arrival" text,
"Railway_ID" int,
PRIMARY KEY ("Train_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES `railway`("Railway_ID")
)

CREATE TABLE "manager" (
"Manager_ID" int,
"Name" text,
"Country" text,
"Working_year_starts" text,
"Age" int,
"Level" int,
PRIMARY KEY ("Manager_ID")
)

CREATE TABLE "railway_manage" (
"Railway_ID" int,
"Manager_ID" int,
"From_Year" text,
PRIMARY KEY ("Railway_ID","Manager_ID"),
FOREIGN KEY ("Manager_ID") REFERENCES "manager"("Manager_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES "railway"("Railway_ID")
)

### Question
Show the countries that have both managers of age above 50 and managers of age below 46.

### SQL
SELECT Country FROM manager WHERE Age  >  50 INTERSECT SELECT Country FROM manager WHERE Age  <  46
### Database Schema
CREATE TABLE "railway" (
"Railway_ID" int,
"Railway" text,
"Builder" text,
"Built" text,
"Wheels" text,
"Location" text,
"ObjectNumber" text,
PRIMARY KEY ("Railway_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Train_Num" text,
"Name" text,
"From" text,
"Arrival" text,
"Railway_ID" int,
PRIMARY KEY ("Train_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES `railway`("Railway_ID")
)

CREATE TABLE "manager" (
"Manager_ID" int,
"Name" text,
"Country" text,
"Working_year_starts" text,
"Age" int,
"Level" int,
PRIMARY KEY ("Manager_ID")
)

CREATE TABLE "railway_manage" (
"Railway_ID" int,
"Manager_ID" int,
"From_Year" text,
PRIMARY KEY ("Railway_ID","Manager_ID"),
FOREIGN KEY ("Manager_ID") REFERENCES "manager"("Manager_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES "railway"("Railway_ID")
)

### Question
Show the distinct countries of managers.

### SQL
SELECT DISTINCT Country FROM manager
### Database Schema
CREATE TABLE "railway" (
"Railway_ID" int,
"Railway" text,
"Builder" text,
"Built" text,
"Wheels" text,
"Location" text,
"ObjectNumber" text,
PRIMARY KEY ("Railway_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Train_Num" text,
"Name" text,
"From" text,
"Arrival" text,
"Railway_ID" int,
PRIMARY KEY ("Train_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES `railway`("Railway_ID")
)

CREATE TABLE "manager" (
"Manager_ID" int,
"Name" text,
"Country" text,
"Working_year_starts" text,
"Age" int,
"Level" int,
PRIMARY KEY ("Manager_ID")
)

CREATE TABLE "railway_manage" (
"Railway_ID" int,
"Manager_ID" int,
"From_Year" text,
PRIMARY KEY ("Railway_ID","Manager_ID"),
FOREIGN KEY ("Manager_ID") REFERENCES "manager"("Manager_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES "railway"("Railway_ID")
)

### Question
Show the working years of managers in descending order of their level.

### SQL
SELECT Working_year_starts FROM manager ORDER BY LEVEL DESC
### Database Schema
CREATE TABLE "railway" (
"Railway_ID" int,
"Railway" text,
"Builder" text,
"Built" text,
"Wheels" text,
"Location" text,
"ObjectNumber" text,
PRIMARY KEY ("Railway_ID")
)

CREATE TABLE "train" (
"Train_ID" int,
"Train_Num" text,
"Name" text,
"From" text,
"Arrival" text,
"Railway_ID" int,
PRIMARY KEY ("Train_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES `railway`("Railway_ID")
)

CREATE TABLE "manager" (
"Manager_ID" int,
"Name" text,
"Country" text,
"Working_year_starts" text,
"Age" int,
"Level" int,
PRIMARY KEY ("Manager_ID")
)

CREATE TABLE "railway_manage" (
"Railway_ID" int,
"Manager_ID" int,
"From_Year" text,
PRIMARY KEY ("Railway_ID","Manager_ID"),
FOREIGN KEY ("Manager_ID") REFERENCES "manager"("Manager_ID"),
FOREIGN KEY ("Railway_ID") REFERENCES "railway"("Railway_ID")
)

### Question
Show the countries that have managers of age above 50 or below 46.

### SQL
SELECT Country FROM manager WHERE Age  >  50 OR Age  <  46
### 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 `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15),
`product_name` VARCHAR(80),
`product_price` DOUBLE NULL
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15),
`customer_number` VARCHAR(20),
`customer_name` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80)
)

CREATE TABLE `Contacts` (
`contact_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(50),
`contact_phone` VARCHAR(80)
)

CREATE TABLE `Customer_Address_History` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_from` DATETIME NOT NULL,
`date_to` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_date` DATETIME NOT NULL,
`order_status_code` VARCHAR(15),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER NOT NULL ,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
How many addresses are there in country USA?

### SQL
SELECT count(*) FROM addresses WHERE country  =  'USA'
### 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 `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15),
`product_name` VARCHAR(80),
`product_price` DOUBLE NULL
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15),
`customer_number` VARCHAR(20),
`customer_name` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80)
)

CREATE TABLE `Contacts` (
`contact_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(50),
`contact_phone` VARCHAR(80)
)

CREATE TABLE `Customer_Address_History` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_from` DATETIME NOT NULL,
`date_to` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_date` DATETIME NOT NULL,
`order_status_code` VARCHAR(15),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER NOT NULL ,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Show all distinct cities in the address record.

### SQL
SELECT DISTINCT city FROM addresses
### 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 `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15),
`product_name` VARCHAR(80),
`product_price` DOUBLE NULL
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15),
`customer_number` VARCHAR(20),
`customer_name` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80)
)

CREATE TABLE `Contacts` (
`contact_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(50),
`contact_phone` VARCHAR(80)
)

CREATE TABLE `Customer_Address_History` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_from` DATETIME NOT NULL,
`date_to` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_date` DATETIME NOT NULL,
`order_status_code` VARCHAR(15),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER NOT NULL ,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Show each state and the number of addresses in each state.

### SQL
SELECT state_province_county ,  count(*) FROM addresses GROUP BY state_province_county