lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE `pilot` (
  `Pilot_Id` int(11) NOT NULL,
  `Name` varchar(50) NOT NULL,
  `Age` int(11) NOT NULL,
  PRIMARY KEY (`Pilot_Id`)
)

CREATE TABLE `aircraft` (
  "Aircraft_ID" int(11) NOT NULL,
  "Aircraft" varchar(50) NOT NULL,
  "Description" varchar(50) NOT NULL,
  "Max_Gross_Weight" varchar(50) NOT NULL,
  "Total_disk_area" varchar(50) NOT NULL,
  "Max_disk_Loading" varchar(50) NOT NULL,
  PRIMARY KEY (`Aircraft_ID`)
)

CREATE TABLE `match` (
"Round" real,
"Location" text,
"Country" text,
"Date" text,
"Fastest_Qualifying" text,
"Winning_Pilot" text,
"Winning_Aircraft" text,
PRIMARY KEY ("Round"),
FOREIGN KEY (`Winning_Aircraft`) REFERENCES `aircraft`(`Aircraft_ID`),
FOREIGN KEY (`Winning_Pilot`) REFERENCES `pilot`(`Pilot_Id`)
)

CREATE TABLE `airport` (
"Airport_ID" int,
"Airport_Name" text,
"Total_Passengers" real,
"%_Change_2007" text,
"International_Passengers" real,
"Domestic_Passengers" real,
"Transit_Passengers" real,
"Aircraft_Movements" real,
"Freight_Metric_Tonnes" real,
PRIMARY KEY ("Airport_ID")
)

CREATE TABLE `airport_aircraft` (
"ID" int,
"Airport_ID" int,
"Aircraft_ID" int,
PRIMARY KEY ("Airport_ID","Aircraft_ID"),
FOREIGN KEY ("Airport_ID") REFERENCES `airport`(`Airport_ID`),
FOREIGN KEY ("Aircraft_ID") REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
What are the names of all aicrafts that have never won any match?

### SQL
SELECT Aircraft FROM aircraft WHERE Aircraft_ID NOT IN (SELECT Winning_Aircraft FROM MATCH)
### Database Schema
CREATE TABLE `pilot` (
  `Pilot_Id` int(11) NOT NULL,
  `Name` varchar(50) NOT NULL,
  `Age` int(11) NOT NULL,
  PRIMARY KEY (`Pilot_Id`)
)

CREATE TABLE `aircraft` (
  "Aircraft_ID" int(11) NOT NULL,
  "Aircraft" varchar(50) NOT NULL,
  "Description" varchar(50) NOT NULL,
  "Max_Gross_Weight" varchar(50) NOT NULL,
  "Total_disk_area" varchar(50) NOT NULL,
  "Max_disk_Loading" varchar(50) NOT NULL,
  PRIMARY KEY (`Aircraft_ID`)
)

CREATE TABLE `match` (
"Round" real,
"Location" text,
"Country" text,
"Date" text,
"Fastest_Qualifying" text,
"Winning_Pilot" text,
"Winning_Aircraft" text,
PRIMARY KEY ("Round"),
FOREIGN KEY (`Winning_Aircraft`) REFERENCES `aircraft`(`Aircraft_ID`),
FOREIGN KEY (`Winning_Pilot`) REFERENCES `pilot`(`Pilot_Id`)
)

CREATE TABLE `airport` (
"Airport_ID" int,
"Airport_Name" text,
"Total_Passengers" real,
"%_Change_2007" text,
"International_Passengers" real,
"Domestic_Passengers" real,
"Transit_Passengers" real,
"Aircraft_Movements" real,
"Freight_Metric_Tonnes" real,
PRIMARY KEY ("Airport_ID")
)

CREATE TABLE `airport_aircraft` (
"ID" int,
"Airport_ID" int,
"Aircraft_ID" int,
PRIMARY KEY ("Airport_ID","Aircraft_ID"),
FOREIGN KEY ("Airport_ID") REFERENCES `airport`(`Airport_ID`),
FOREIGN KEY ("Aircraft_ID") REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
Show the names of aircrafts that are associated with both an airport named "London Heathrow" and an airport named "London Gatwick"

### SQL
SELECT T1.Aircraft FROM aircraft AS T1 JOIN airport_aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN airport AS T3 ON T2.Airport_ID  =  T3.Airport_ID WHERE T3.Airport_Name  =  "London Heathrow" INTERSECT SELECT T1.Aircraft FROM aircraft AS T1 JOIN airport_aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN airport AS T3 ON T2.Airport_ID  =  T3.Airport_ID WHERE T3.Airport_Name  =  "London Gatwick"
### Database Schema
CREATE TABLE `pilot` (
  `Pilot_Id` int(11) NOT NULL,
  `Name` varchar(50) NOT NULL,
  `Age` int(11) NOT NULL,
  PRIMARY KEY (`Pilot_Id`)
)

CREATE TABLE `aircraft` (
  "Aircraft_ID" int(11) NOT NULL,
  "Aircraft" varchar(50) NOT NULL,
  "Description" varchar(50) NOT NULL,
  "Max_Gross_Weight" varchar(50) NOT NULL,
  "Total_disk_area" varchar(50) NOT NULL,
  "Max_disk_Loading" varchar(50) NOT NULL,
  PRIMARY KEY (`Aircraft_ID`)
)

CREATE TABLE `match` (
"Round" real,
"Location" text,
"Country" text,
"Date" text,
"Fastest_Qualifying" text,
"Winning_Pilot" text,
"Winning_Aircraft" text,
PRIMARY KEY ("Round"),
FOREIGN KEY (`Winning_Aircraft`) REFERENCES `aircraft`(`Aircraft_ID`),
FOREIGN KEY (`Winning_Pilot`) REFERENCES `pilot`(`Pilot_Id`)
)

CREATE TABLE `airport` (
"Airport_ID" int,
"Airport_Name" text,
"Total_Passengers" real,
"%_Change_2007" text,
"International_Passengers" real,
"Domestic_Passengers" real,
"Transit_Passengers" real,
"Aircraft_Movements" real,
"Freight_Metric_Tonnes" real,
PRIMARY KEY ("Airport_ID")
)

CREATE TABLE `airport_aircraft` (
"ID" int,
"Airport_ID" int,
"Aircraft_ID" int,
PRIMARY KEY ("Airport_ID","Aircraft_ID"),
FOREIGN KEY ("Airport_ID") REFERENCES `airport`(`Airport_ID`),
FOREIGN KEY ("Aircraft_ID") REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
What are the names of all aircrafts that are associated with both London Heathrow and Gatwick airports?

### SQL
SELECT T1.Aircraft FROM aircraft AS T1 JOIN airport_aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN airport AS T3 ON T2.Airport_ID  =  T3.Airport_ID WHERE T3.Airport_Name  =  "London Heathrow" INTERSECT SELECT T1.Aircraft FROM aircraft AS T1 JOIN airport_aircraft AS T2 ON T1.Aircraft_ID  =  T2.Aircraft_ID JOIN airport AS T3 ON T2.Airport_ID  =  T3.Airport_ID WHERE T3.Airport_Name  =  "London Gatwick"
### Database Schema
CREATE TABLE `pilot` (
  `Pilot_Id` int(11) NOT NULL,
  `Name` varchar(50) NOT NULL,
  `Age` int(11) NOT NULL,
  PRIMARY KEY (`Pilot_Id`)
)

CREATE TABLE `aircraft` (
  "Aircraft_ID" int(11) NOT NULL,
  "Aircraft" varchar(50) NOT NULL,
  "Description" varchar(50) NOT NULL,
  "Max_Gross_Weight" varchar(50) NOT NULL,
  "Total_disk_area" varchar(50) NOT NULL,
  "Max_disk_Loading" varchar(50) NOT NULL,
  PRIMARY KEY (`Aircraft_ID`)
)

CREATE TABLE `match` (
"Round" real,
"Location" text,
"Country" text,
"Date" text,
"Fastest_Qualifying" text,
"Winning_Pilot" text,
"Winning_Aircraft" text,
PRIMARY KEY ("Round"),
FOREIGN KEY (`Winning_Aircraft`) REFERENCES `aircraft`(`Aircraft_ID`),
FOREIGN KEY (`Winning_Pilot`) REFERENCES `pilot`(`Pilot_Id`)
)

CREATE TABLE `airport` (
"Airport_ID" int,
"Airport_Name" text,
"Total_Passengers" real,
"%_Change_2007" text,
"International_Passengers" real,
"Domestic_Passengers" real,
"Transit_Passengers" real,
"Aircraft_Movements" real,
"Freight_Metric_Tonnes" real,
PRIMARY KEY ("Airport_ID")
)

CREATE TABLE `airport_aircraft` (
"ID" int,
"Airport_ID" int,
"Aircraft_ID" int,
PRIMARY KEY ("Airport_ID","Aircraft_ID"),
FOREIGN KEY ("Airport_ID") REFERENCES `airport`(`Airport_ID`),
FOREIGN KEY ("Aircraft_ID") REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
Show all information on the airport that has the largest number of international passengers.

### SQL
SELECT * FROM airport ORDER BY International_Passengers DESC LIMIT 1
### Database Schema
CREATE TABLE `pilot` (
  `Pilot_Id` int(11) NOT NULL,
  `Name` varchar(50) NOT NULL,
  `Age` int(11) NOT NULL,
  PRIMARY KEY (`Pilot_Id`)
)

CREATE TABLE `aircraft` (
  "Aircraft_ID" int(11) NOT NULL,
  "Aircraft" varchar(50) NOT NULL,
  "Description" varchar(50) NOT NULL,
  "Max_Gross_Weight" varchar(50) NOT NULL,
  "Total_disk_area" varchar(50) NOT NULL,
  "Max_disk_Loading" varchar(50) NOT NULL,
  PRIMARY KEY (`Aircraft_ID`)
)

CREATE TABLE `match` (
"Round" real,
"Location" text,
"Country" text,
"Date" text,
"Fastest_Qualifying" text,
"Winning_Pilot" text,
"Winning_Aircraft" text,
PRIMARY KEY ("Round"),
FOREIGN KEY (`Winning_Aircraft`) REFERENCES `aircraft`(`Aircraft_ID`),
FOREIGN KEY (`Winning_Pilot`) REFERENCES `pilot`(`Pilot_Id`)
)

CREATE TABLE `airport` (
"Airport_ID" int,
"Airport_Name" text,
"Total_Passengers" real,
"%_Change_2007" text,
"International_Passengers" real,
"Domestic_Passengers" real,
"Transit_Passengers" real,
"Aircraft_Movements" real,
"Freight_Metric_Tonnes" real,
PRIMARY KEY ("Airport_ID")
)

CREATE TABLE `airport_aircraft` (
"ID" int,
"Airport_ID" int,
"Aircraft_ID" int,
PRIMARY KEY ("Airport_ID","Aircraft_ID"),
FOREIGN KEY ("Airport_ID") REFERENCES `airport`(`Airport_ID`),
FOREIGN KEY ("Aircraft_ID") REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
What is all the information on the airport with the largest number of international passengers?

### SQL
SELECT * FROM airport ORDER BY International_Passengers DESC LIMIT 1
### Database Schema
CREATE TABLE `pilot` (
  `Pilot_Id` int(11) NOT NULL,
  `Name` varchar(50) NOT NULL,
  `Age` int(11) NOT NULL,
  PRIMARY KEY (`Pilot_Id`)
)

CREATE TABLE `aircraft` (
  "Aircraft_ID" int(11) NOT NULL,
  "Aircraft" varchar(50) NOT NULL,
  "Description" varchar(50) NOT NULL,
  "Max_Gross_Weight" varchar(50) NOT NULL,
  "Total_disk_area" varchar(50) NOT NULL,
  "Max_disk_Loading" varchar(50) NOT NULL,
  PRIMARY KEY (`Aircraft_ID`)
)

CREATE TABLE `match` (
"Round" real,
"Location" text,
"Country" text,
"Date" text,
"Fastest_Qualifying" text,
"Winning_Pilot" text,
"Winning_Aircraft" text,
PRIMARY KEY ("Round"),
FOREIGN KEY (`Winning_Aircraft`) REFERENCES `aircraft`(`Aircraft_ID`),
FOREIGN KEY (`Winning_Pilot`) REFERENCES `pilot`(`Pilot_Id`)
)

CREATE TABLE `airport` (
"Airport_ID" int,
"Airport_Name" text,
"Total_Passengers" real,
"%_Change_2007" text,
"International_Passengers" real,
"Domestic_Passengers" real,
"Transit_Passengers" real,
"Aircraft_Movements" real,
"Freight_Metric_Tonnes" real,
PRIMARY KEY ("Airport_ID")
)

CREATE TABLE `airport_aircraft` (
"ID" int,
"Airport_ID" int,
"Aircraft_ID" int,
PRIMARY KEY ("Airport_ID","Aircraft_ID"),
FOREIGN KEY ("Airport_ID") REFERENCES `airport`(`Airport_ID`),
FOREIGN KEY ("Aircraft_ID") REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
find the name and age of the pilot who has won the most number of times among the pilots who are younger than 30.

### SQL
SELECT t1.name ,  t1.age FROM pilot AS t1 JOIN MATCH AS t2 ON t1.pilot_id  =  t2.winning_pilot WHERE t1.age  <  30 GROUP BY t2.winning_pilot ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE `pilot` (
  `Pilot_Id` int(11) NOT NULL,
  `Name` varchar(50) NOT NULL,
  `Age` int(11) NOT NULL,
  PRIMARY KEY (`Pilot_Id`)
)

CREATE TABLE `aircraft` (
  "Aircraft_ID" int(11) NOT NULL,
  "Aircraft" varchar(50) NOT NULL,
  "Description" varchar(50) NOT NULL,
  "Max_Gross_Weight" varchar(50) NOT NULL,
  "Total_disk_area" varchar(50) NOT NULL,
  "Max_disk_Loading" varchar(50) NOT NULL,
  PRIMARY KEY (`Aircraft_ID`)
)

CREATE TABLE `match` (
"Round" real,
"Location" text,
"Country" text,
"Date" text,
"Fastest_Qualifying" text,
"Winning_Pilot" text,
"Winning_Aircraft" text,
PRIMARY KEY ("Round"),
FOREIGN KEY (`Winning_Aircraft`) REFERENCES `aircraft`(`Aircraft_ID`),
FOREIGN KEY (`Winning_Pilot`) REFERENCES `pilot`(`Pilot_Id`)
)

CREATE TABLE `airport` (
"Airport_ID" int,
"Airport_Name" text,
"Total_Passengers" real,
"%_Change_2007" text,
"International_Passengers" real,
"Domestic_Passengers" real,
"Transit_Passengers" real,
"Aircraft_Movements" real,
"Freight_Metric_Tonnes" real,
PRIMARY KEY ("Airport_ID")
)

CREATE TABLE `airport_aircraft` (
"ID" int,
"Airport_ID" int,
"Aircraft_ID" int,
PRIMARY KEY ("Airport_ID","Aircraft_ID"),
FOREIGN KEY ("Airport_ID") REFERENCES `airport`(`Airport_ID`),
FOREIGN KEY ("Aircraft_ID") REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
What is the name and age of the pilot younger than 30 who has won the most number of times?

### SQL
SELECT t1.name ,  t1.age FROM pilot AS t1 JOIN MATCH AS t2 ON t1.pilot_id  =  t2.winning_pilot WHERE t1.age  <  30 GROUP BY t2.winning_pilot ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE `pilot` (
  `Pilot_Id` int(11) NOT NULL,
  `Name` varchar(50) NOT NULL,
  `Age` int(11) NOT NULL,
  PRIMARY KEY (`Pilot_Id`)
)

CREATE TABLE `aircraft` (
  "Aircraft_ID" int(11) NOT NULL,
  "Aircraft" varchar(50) NOT NULL,
  "Description" varchar(50) NOT NULL,
  "Max_Gross_Weight" varchar(50) NOT NULL,
  "Total_disk_area" varchar(50) NOT NULL,
  "Max_disk_Loading" varchar(50) NOT NULL,
  PRIMARY KEY (`Aircraft_ID`)
)

CREATE TABLE `match` (
"Round" real,
"Location" text,
"Country" text,
"Date" text,
"Fastest_Qualifying" text,
"Winning_Pilot" text,
"Winning_Aircraft" text,
PRIMARY KEY ("Round"),
FOREIGN KEY (`Winning_Aircraft`) REFERENCES `aircraft`(`Aircraft_ID`),
FOREIGN KEY (`Winning_Pilot`) REFERENCES `pilot`(`Pilot_Id`)
)

CREATE TABLE `airport` (
"Airport_ID" int,
"Airport_Name" text,
"Total_Passengers" real,
"%_Change_2007" text,
"International_Passengers" real,
"Domestic_Passengers" real,
"Transit_Passengers" real,
"Aircraft_Movements" real,
"Freight_Metric_Tonnes" real,
PRIMARY KEY ("Airport_ID")
)

CREATE TABLE `airport_aircraft` (
"ID" int,
"Airport_ID" int,
"Aircraft_ID" int,
PRIMARY KEY ("Airport_ID","Aircraft_ID"),
FOREIGN KEY ("Airport_ID") REFERENCES `airport`(`Airport_ID`),
FOREIGN KEY ("Aircraft_ID") REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
what is the name and age of the youngest winning pilot?

### SQL
SELECT t1.name ,  t1.age FROM pilot AS t1 JOIN MATCH AS t2 ON t1.pilot_id  =  t2.winning_pilot ORDER BY t1.age LIMIT 1
### Database Schema
CREATE TABLE `pilot` (
  `Pilot_Id` int(11) NOT NULL,
  `Name` varchar(50) NOT NULL,
  `Age` int(11) NOT NULL,
  PRIMARY KEY (`Pilot_Id`)
)

CREATE TABLE `aircraft` (
  "Aircraft_ID" int(11) NOT NULL,
  "Aircraft" varchar(50) NOT NULL,
  "Description" varchar(50) NOT NULL,
  "Max_Gross_Weight" varchar(50) NOT NULL,
  "Total_disk_area" varchar(50) NOT NULL,
  "Max_disk_Loading" varchar(50) NOT NULL,
  PRIMARY KEY (`Aircraft_ID`)
)

CREATE TABLE `match` (
"Round" real,
"Location" text,
"Country" text,
"Date" text,
"Fastest_Qualifying" text,
"Winning_Pilot" text,
"Winning_Aircraft" text,
PRIMARY KEY ("Round"),
FOREIGN KEY (`Winning_Aircraft`) REFERENCES `aircraft`(`Aircraft_ID`),
FOREIGN KEY (`Winning_Pilot`) REFERENCES `pilot`(`Pilot_Id`)
)

CREATE TABLE `airport` (
"Airport_ID" int,
"Airport_Name" text,
"Total_Passengers" real,
"%_Change_2007" text,
"International_Passengers" real,
"Domestic_Passengers" real,
"Transit_Passengers" real,
"Aircraft_Movements" real,
"Freight_Metric_Tonnes" real,
PRIMARY KEY ("Airport_ID")
)

CREATE TABLE `airport_aircraft` (
"ID" int,
"Airport_ID" int,
"Aircraft_ID" int,
PRIMARY KEY ("Airport_ID","Aircraft_ID"),
FOREIGN KEY ("Airport_ID") REFERENCES `airport`(`Airport_ID`),
FOREIGN KEY ("Aircraft_ID") REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
How old is the youngest winning pilot and what is their name?

### SQL
SELECT t1.name ,  t1.age FROM pilot AS t1 JOIN MATCH AS t2 ON t1.pilot_id  =  t2.winning_pilot ORDER BY t1.age LIMIT 1
### Database Schema
CREATE TABLE `pilot` (
  `Pilot_Id` int(11) NOT NULL,
  `Name` varchar(50) NOT NULL,
  `Age` int(11) NOT NULL,
  PRIMARY KEY (`Pilot_Id`)
)

CREATE TABLE `aircraft` (
  "Aircraft_ID" int(11) NOT NULL,
  "Aircraft" varchar(50) NOT NULL,
  "Description" varchar(50) NOT NULL,
  "Max_Gross_Weight" varchar(50) NOT NULL,
  "Total_disk_area" varchar(50) NOT NULL,
  "Max_disk_Loading" varchar(50) NOT NULL,
  PRIMARY KEY (`Aircraft_ID`)
)

CREATE TABLE `match` (
"Round" real,
"Location" text,
"Country" text,
"Date" text,
"Fastest_Qualifying" text,
"Winning_Pilot" text,
"Winning_Aircraft" text,
PRIMARY KEY ("Round"),
FOREIGN KEY (`Winning_Aircraft`) REFERENCES `aircraft`(`Aircraft_ID`),
FOREIGN KEY (`Winning_Pilot`) REFERENCES `pilot`(`Pilot_Id`)
)

CREATE TABLE `airport` (
"Airport_ID" int,
"Airport_Name" text,
"Total_Passengers" real,
"%_Change_2007" text,
"International_Passengers" real,
"Domestic_Passengers" real,
"Transit_Passengers" real,
"Aircraft_Movements" real,
"Freight_Metric_Tonnes" real,
PRIMARY KEY ("Airport_ID")
)

CREATE TABLE `airport_aircraft` (
"ID" int,
"Airport_ID" int,
"Aircraft_ID" int,
PRIMARY KEY ("Airport_ID","Aircraft_ID"),
FOREIGN KEY ("Airport_ID") REFERENCES `airport`(`Airport_ID`),
FOREIGN KEY ("Aircraft_ID") REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
find the name of pilots who did not win the matches held in the country of Australia.

### SQL
SELECT name FROM pilot WHERE pilot_id NOT IN (SELECT Winning_Pilot  FROM MATCH WHERE country  =  'Australia')
### Database Schema
CREATE TABLE `pilot` (
  `Pilot_Id` int(11) NOT NULL,
  `Name` varchar(50) NOT NULL,
  `Age` int(11) NOT NULL,
  PRIMARY KEY (`Pilot_Id`)
)

CREATE TABLE `aircraft` (
  "Aircraft_ID" int(11) NOT NULL,
  "Aircraft" varchar(50) NOT NULL,
  "Description" varchar(50) NOT NULL,
  "Max_Gross_Weight" varchar(50) NOT NULL,
  "Total_disk_area" varchar(50) NOT NULL,
  "Max_disk_Loading" varchar(50) NOT NULL,
  PRIMARY KEY (`Aircraft_ID`)
)

CREATE TABLE `match` (
"Round" real,
"Location" text,
"Country" text,
"Date" text,
"Fastest_Qualifying" text,
"Winning_Pilot" text,
"Winning_Aircraft" text,
PRIMARY KEY ("Round"),
FOREIGN KEY (`Winning_Aircraft`) REFERENCES `aircraft`(`Aircraft_ID`),
FOREIGN KEY (`Winning_Pilot`) REFERENCES `pilot`(`Pilot_Id`)
)

CREATE TABLE `airport` (
"Airport_ID" int,
"Airport_Name" text,
"Total_Passengers" real,
"%_Change_2007" text,
"International_Passengers" real,
"Domestic_Passengers" real,
"Transit_Passengers" real,
"Aircraft_Movements" real,
"Freight_Metric_Tonnes" real,
PRIMARY KEY ("Airport_ID")
)

CREATE TABLE `airport_aircraft` (
"ID" int,
"Airport_ID" int,
"Aircraft_ID" int,
PRIMARY KEY ("Airport_ID","Aircraft_ID"),
FOREIGN KEY ("Airport_ID") REFERENCES `airport`(`Airport_ID`),
FOREIGN KEY ("Aircraft_ID") REFERENCES `aircraft`(`Aircraft_ID`)
)

### Question
What are the names of the pilots that have not won any matches in Australia?

### SQL
SELECT name FROM pilot WHERE pilot_id NOT IN (SELECT Winning_Pilot  FROM MATCH WHERE country  =  'Australia')
### Database Schema
CREATE TABLE Customers (
customer_id INTEGER NOT NULL,
customer_details VARCHAR(255),
PRIMARY KEY (customer_id)
)

CREATE TABLE Properties (
property_id INTEGER NOT NULL,
property_type_code CHAR(15) NOT NULL,
property_address VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (property_id)
)

CREATE TABLE Residents (
resident_id INTEGER NOT NULL,
property_id INTEGER NOT NULL,
date_moved_in DATETIME NOT NULL,
date_moved_out DATETIME NOT NULL,
other_details VARCHAR(255),
PRIMARY KEY (resident_id, property_id, date_moved_in),
FOREIGN KEY (property_id) REFERENCES Properties (property_id)
)

CREATE TABLE Organizations (
organization_id INTEGER NOT NULL,
parent_organization_id INTEGER,
organization_details VARCHAR(255),
PRIMARY KEY (organization_id)
)

CREATE TABLE Services (
service_id INTEGER NOT NULL,
organization_id INTEGER NOT NULL,
service_type_code CHAR(15) NOT NULL,
service_details VARCHAR(255),
PRIMARY KEY (service_id),
FOREIGN KEY (organization_id) REFERENCES Organizations (organization_id)
)

CREATE TABLE Residents_Services (
resident_id INTEGER NOT NULL,
service_id INTEGER NOT NULL,
date_moved_in DATETIME,
property_id INTEGER,
date_requested DATETIME,
date_provided DATETIME,
other_details VARCHAR(255),
PRIMARY KEY (resident_id, service_id),
FOREIGN KEY (service_id) REFERENCES Services (service_id),
FOREIGN KEY (resident_id, property_id, date_moved_in) REFERENCES Residents (resident_id,property_id,date_moved_in)
)

CREATE TABLE Things (
thing_id INTEGER NOT NULL,
organization_id INTEGER NOT NULL,
Type_of_Thing_Code CHAR(15) NOT NULL,
service_type_code CHAR(10) NOT NULL,
service_details VARCHAR(255),
PRIMARY KEY (thing_id),
FOREIGN KEY (organization_id) REFERENCES Organizations (organization_id)
)

CREATE TABLE Customer_Events (
Customer_Event_ID INTEGER NOT NULL,
customer_id INTEGER,
date_moved_in DATETIME,
property_id INTEGER,
resident_id INTEGER,
thing_id INTEGER NOT NULL,
PRIMARY KEY (Customer_Event_ID),
FOREIGN KEY (thing_id) REFERENCES Things (thing_id),
FOREIGN KEY (customer_id) REFERENCES Customers (customer_id),
FOREIGN KEY (resident_id, property_id, date_moved_in) REFERENCES Residents (resident_id,property_id,date_moved_in)
)

CREATE TABLE Customer_Event_Notes (
Customer_Event_Note_ID INTEGER NOT NULL,
Customer_Event_ID INTEGER NOT NULL,
service_type_code CHAR(15) NOT NULL,
resident_id INTEGER NOT NULL,
property_id INTEGER NOT NULL,
date_moved_in DATETIME NOT NULL,
PRIMARY KEY (Customer_Event_Note_ID),
FOREIGN KEY (Customer_Event_ID) REFERENCES Customer_Events (Customer_Event_ID)
)

CREATE TABLE Timed_Status_of_Things (
thing_id INTEGER NOT NULL,
Date_and_Date DATETIME NOT NULL,
Status_of_Thing_Code CHAR(15) NOT NULL,
PRIMARY KEY (thing_id, Date_and_Date, Status_of_Thing_Code),
FOREIGN KEY (thing_id) REFERENCES Things (thing_id)
)

CREATE TABLE Timed_Locations_of_Things (
thing_id INTEGER NOT NULL,
Date_and_Time DATETIME NOT NULL,
Location_Code CHAR(15) NOT NULL,
PRIMARY KEY (thing_id, Date_and_Time, Location_Code),
FOREIGN KEY (thing_id) REFERENCES Things (thing_id))

### Question
When is the last day any resident moved in?

### SQL
SELECT max(date_moved_in) FROM Residents
### Database Schema
CREATE TABLE Customers (
customer_id INTEGER NOT NULL,
customer_details VARCHAR(255),
PRIMARY KEY (customer_id)
)

CREATE TABLE Properties (
property_id INTEGER NOT NULL,
property_type_code CHAR(15) NOT NULL,
property_address VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (property_id)
)

CREATE TABLE Residents (
resident_id INTEGER NOT NULL,
property_id INTEGER NOT NULL,
date_moved_in DATETIME NOT NULL,
date_moved_out DATETIME NOT NULL,
other_details VARCHAR(255),
PRIMARY KEY (resident_id, property_id, date_moved_in),
FOREIGN KEY (property_id) REFERENCES Properties (property_id)
)

CREATE TABLE Organizations (
organization_id INTEGER NOT NULL,
parent_organization_id INTEGER,
organization_details VARCHAR(255),
PRIMARY KEY (organization_id)
)

CREATE TABLE Services (
service_id INTEGER NOT NULL,
organization_id INTEGER NOT NULL,
service_type_code CHAR(15) NOT NULL,
service_details VARCHAR(255),
PRIMARY KEY (service_id),
FOREIGN KEY (organization_id) REFERENCES Organizations (organization_id)
)

CREATE TABLE Residents_Services (
resident_id INTEGER NOT NULL,
service_id INTEGER NOT NULL,
date_moved_in DATETIME,
property_id INTEGER,
date_requested DATETIME,
date_provided DATETIME,
other_details VARCHAR(255),
PRIMARY KEY (resident_id, service_id),
FOREIGN KEY (service_id) REFERENCES Services (service_id),
FOREIGN KEY (resident_id, property_id, date_moved_in) REFERENCES Residents (resident_id,property_id,date_moved_in)
)

CREATE TABLE Things (
thing_id INTEGER NOT NULL,
organization_id INTEGER NOT NULL,
Type_of_Thing_Code CHAR(15) NOT NULL,
service_type_code CHAR(10) NOT NULL,
service_details VARCHAR(255),
PRIMARY KEY (thing_id),
FOREIGN KEY (organization_id) REFERENCES Organizations (organization_id)
)

CREATE TABLE Customer_Events (
Customer_Event_ID INTEGER NOT NULL,
customer_id INTEGER,
date_moved_in DATETIME,
property_id INTEGER,
resident_id INTEGER,
thing_id INTEGER NOT NULL,
PRIMARY KEY (Customer_Event_ID),
FOREIGN KEY (thing_id) REFERENCES Things (thing_id),
FOREIGN KEY (customer_id) REFERENCES Customers (customer_id),
FOREIGN KEY (resident_id, property_id, date_moved_in) REFERENCES Residents (resident_id,property_id,date_moved_in)
)

CREATE TABLE Customer_Event_Notes (
Customer_Event_Note_ID INTEGER NOT NULL,
Customer_Event_ID INTEGER NOT NULL,
service_type_code CHAR(15) NOT NULL,
resident_id INTEGER NOT NULL,
property_id INTEGER NOT NULL,
date_moved_in DATETIME NOT NULL,
PRIMARY KEY (Customer_Event_Note_ID),
FOREIGN KEY (Customer_Event_ID) REFERENCES Customer_Events (Customer_Event_ID)
)

CREATE TABLE Timed_Status_of_Things (
thing_id INTEGER NOT NULL,
Date_and_Date DATETIME NOT NULL,
Status_of_Thing_Code CHAR(15) NOT NULL,
PRIMARY KEY (thing_id, Date_and_Date, Status_of_Thing_Code),
FOREIGN KEY (thing_id) REFERENCES Things (thing_id)
)

CREATE TABLE Timed_Locations_of_Things (
thing_id INTEGER NOT NULL,
Date_and_Time DATETIME NOT NULL,
Location_Code CHAR(15) NOT NULL,
PRIMARY KEY (thing_id, Date_and_Time, Location_Code),
FOREIGN KEY (thing_id) REFERENCES Things (thing_id))

### Question
What are the distinct move in dates of the residents?

### SQL
SELECT DISTINCT date_moved_in FROM residents
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
How many schools are there?

### SQL
SELECT count(*) FROM school
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
Count the number of schools.

### SQL
SELECT count(*) FROM school
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
List the locations of schools in ascending order of enrollment.

### SQL
SELECT LOCATION FROM school ORDER BY Enrollment ASC
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
What is the list of school locations sorted in ascending order of school enrollment?

### SQL
SELECT LOCATION FROM school ORDER BY Enrollment ASC
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
List the locations of schools in descending order of founded year.

### SQL
SELECT LOCATION FROM school ORDER BY Founded DESC
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
What is the list of school locations sorted in descending order of school foundation year?

### SQL
SELECT LOCATION FROM school ORDER BY Founded DESC
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
What are the enrollments of schools whose denomination is not "Catholic"?

### SQL
SELECT Enrollment FROM school WHERE Denomination != "Catholic"
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
List the enrollment for each school that does not have "Catholic" as denomination.

### SQL
SELECT Enrollment FROM school WHERE Denomination != "Catholic"
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
What is the average enrollment of schools?

### SQL
SELECT avg(Enrollment) FROM school
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
Take the average of the school enrollment.

### SQL
SELECT avg(Enrollment) FROM school
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
What are the teams of the players, sorted in ascending alphabetical order?

### SQL
SELECT Team FROM player ORDER BY Team ASC
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
Find the team of each player and sort them in ascending alphabetical order.

### SQL
SELECT Team FROM player ORDER BY Team ASC
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
How many different positions of players are there?

### SQL
SELECT count(DISTINCT POSITION) FROM player
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
Count the number of distinct player positions.

### SQL
SELECT count(DISTINCT POSITION) FROM player
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
Find the team of the player of the highest age.

### SQL
SELECT Team FROM player ORDER BY Age DESC LIMIT 1
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
Which team has the oldest player?

### SQL
SELECT Team FROM player ORDER BY Age DESC LIMIT 1
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
List the teams of the players with the top 5 largest ages.

### SQL
SELECT Team FROM player ORDER BY Age DESC LIMIT 5
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
What are the teams that have the 5 oldest players?

### SQL
SELECT Team FROM player ORDER BY Age DESC LIMIT 5
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
For each player, show the team and the location of school they belong to.

### SQL
SELECT T1.Team ,  T2.Location FROM player AS T1 JOIN school AS T2 ON T1.School_ID  =  T2.School_ID
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
What are the team and the location of school each player belongs to?

### SQL
SELECT T1.Team ,  T2.Location FROM player AS T1 JOIN school AS T2 ON T1.School_ID  =  T2.School_ID
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
Show the locations of schools that have more than 1 player.

### SQL
SELECT T2.Location FROM player AS T1 JOIN school AS T2 ON T1.School_ID  =  T2.School_ID GROUP BY T1.School_ID HAVING COUNT(*)  >  1
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
Which schools have more than 1 player? Give me the school locations.

### SQL
SELECT T2.Location FROM player AS T1 JOIN school AS T2 ON T1.School_ID  =  T2.School_ID GROUP BY T1.School_ID HAVING COUNT(*)  >  1
### Database Schema
CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
)

CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
)

### Question
Show the denomination of the school that has the most players.

### SQL
SELECT T2.Denomination FROM player AS T1 JOIN school AS T2 ON T1.School_ID  =  T2.School_ID GROUP BY T1.School_ID ORDER BY COUNT(*) DESC LIMIT 1