lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Customer_Policies (
Policy_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_Type_Code CHAR(15) NOT NULL,
Start_Date DATE,
End_Date DATE,
PRIMARY KEY (Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
PRIMARY KEY (Claim_ID),
FOREIGN KEY (Policy_ID) REFERENCES Customer_Policies (Policy_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
Customer_Policy_ID INTEGER NOT NULL,
PRIMARY KEY (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

CREATE TABLE Payments (
Payment_ID INTEGER NOT NULL,
Settlement_ID INTEGER NOT NULL,
Payment_Method_Code VARCHAR(255),
Date_Payment_Made DATE,
Amount_Payment INTEGER,
PRIMARY KEY (Payment_ID),
FOREIGN KEY (Settlement_ID) REFERENCES Settlements (Settlement_ID)
)

### Question
Give me the payment Id, the date and the amount for all the payments processed with Visa.

### SQL
SELECT Payment_ID ,  Date_Payment_Made ,  Amount_Payment FROM Payments WHERE Payment_Method_Code  =  'Visa'
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Customer_Policies (
Policy_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_Type_Code CHAR(15) NOT NULL,
Start_Date DATE,
End_Date DATE,
PRIMARY KEY (Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
PRIMARY KEY (Claim_ID),
FOREIGN KEY (Policy_ID) REFERENCES Customer_Policies (Policy_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
Customer_Policy_ID INTEGER NOT NULL,
PRIMARY KEY (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

CREATE TABLE Payments (
Payment_ID INTEGER NOT NULL,
Settlement_ID INTEGER NOT NULL,
Payment_Method_Code VARCHAR(255),
Date_Payment_Made DATE,
Amount_Payment INTEGER,
PRIMARY KEY (Payment_ID),
FOREIGN KEY (Settlement_ID) REFERENCES Settlements (Settlement_ID)
)

### Question
List the details of the customers who do not have any policies.

### SQL
SELECT customer_details FROM Customers EXCEPT SELECT T1.customer_details FROM Customers AS T1 JOIN Customer_Policies AS T2 ON T1.customer_id  =  T2.customer_id
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Customer_Policies (
Policy_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_Type_Code CHAR(15) NOT NULL,
Start_Date DATE,
End_Date DATE,
PRIMARY KEY (Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
PRIMARY KEY (Claim_ID),
FOREIGN KEY (Policy_ID) REFERENCES Customer_Policies (Policy_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
Customer_Policy_ID INTEGER NOT NULL,
PRIMARY KEY (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

CREATE TABLE Payments (
Payment_ID INTEGER NOT NULL,
Settlement_ID INTEGER NOT NULL,
Payment_Method_Code VARCHAR(255),
Date_Payment_Made DATE,
Amount_Payment INTEGER,
PRIMARY KEY (Payment_ID),
FOREIGN KEY (Settlement_ID) REFERENCES Settlements (Settlement_ID)
)

### Question
Which customers do not have any policies? Find the details of these customers.

### SQL
SELECT customer_details FROM Customers EXCEPT SELECT T1.customer_details FROM Customers AS T1 JOIN Customer_Policies AS T2 ON T1.customer_id  =  T2.customer_id
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Customer_Policies (
Policy_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_Type_Code CHAR(15) NOT NULL,
Start_Date DATE,
End_Date DATE,
PRIMARY KEY (Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
PRIMARY KEY (Claim_ID),
FOREIGN KEY (Policy_ID) REFERENCES Customer_Policies (Policy_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
Customer_Policy_ID INTEGER NOT NULL,
PRIMARY KEY (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

CREATE TABLE Payments (
Payment_ID INTEGER NOT NULL,
Settlement_ID INTEGER NOT NULL,
Payment_Method_Code VARCHAR(255),
Date_Payment_Made DATE,
Amount_Payment INTEGER,
PRIMARY KEY (Payment_ID),
FOREIGN KEY (Settlement_ID) REFERENCES Settlements (Settlement_ID)
)

### Question
List the date the claim was made, the date it was settled and the amount settled for all the claims which had exactly one settlement.

### SQL
SELECT T1.claim_id ,  T1.date_claim_made ,  T1.Date_Claim_Settled FROM Claims AS T1 JOIN Settlements AS T2 ON T1.Claim_id  =  T2.Claim_id GROUP BY T1.claim_id HAVING count(*)  =  1
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Customer_Policies (
Policy_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_Type_Code CHAR(15) NOT NULL,
Start_Date DATE,
End_Date DATE,
PRIMARY KEY (Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
PRIMARY KEY (Claim_ID),
FOREIGN KEY (Policy_ID) REFERENCES Customer_Policies (Policy_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
Customer_Policy_ID INTEGER NOT NULL,
PRIMARY KEY (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

CREATE TABLE Payments (
Payment_ID INTEGER NOT NULL,
Settlement_ID INTEGER NOT NULL,
Payment_Method_Code VARCHAR(255),
Date_Payment_Made DATE,
Amount_Payment INTEGER,
PRIMARY KEY (Payment_ID),
FOREIGN KEY (Settlement_ID) REFERENCES Settlements (Settlement_ID)
)

### Question
Which claims had exactly one settlement? For each, tell me the the date the claim was made, the date it was settled and the amount settled.

### SQL
SELECT T1.claim_id ,  T1.date_claim_made ,  T1.Date_Claim_Settled FROM Claims AS T1 JOIN Settlements AS T2 ON T1.Claim_id  =  T2.Claim_id GROUP BY T1.claim_id HAVING count(*)  =  1
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Customer_Policies (
Policy_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_Type_Code CHAR(15) NOT NULL,
Start_Date DATE,
End_Date DATE,
PRIMARY KEY (Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
PRIMARY KEY (Claim_ID),
FOREIGN KEY (Policy_ID) REFERENCES Customer_Policies (Policy_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
Customer_Policy_ID INTEGER NOT NULL,
PRIMARY KEY (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

CREATE TABLE Payments (
Payment_ID INTEGER NOT NULL,
Settlement_ID INTEGER NOT NULL,
Payment_Method_Code VARCHAR(255),
Date_Payment_Made DATE,
Amount_Payment INTEGER,
PRIMARY KEY (Payment_ID),
FOREIGN KEY (Settlement_ID) REFERENCES Settlements (Settlement_ID)
)

### Question
Find the total claimed amount of all the claims.

### SQL
SELECT sum(Amount_Claimed) FROM Claims
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Customer_Policies (
Policy_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_Type_Code CHAR(15) NOT NULL,
Start_Date DATE,
End_Date DATE,
PRIMARY KEY (Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID)
)

CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
PRIMARY KEY (Claim_ID),
FOREIGN KEY (Policy_ID) REFERENCES Customer_Policies (Policy_ID)
)

CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
Customer_Policy_ID INTEGER NOT NULL,
PRIMARY KEY (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
)

CREATE TABLE Payments (
Payment_ID INTEGER NOT NULL,
Settlement_ID INTEGER NOT NULL,
Payment_Method_Code VARCHAR(255),
Date_Payment_Made DATE,
Amount_Payment INTEGER,
PRIMARY KEY (Payment_ID),
FOREIGN KEY (Settlement_ID) REFERENCES Settlements (Settlement_ID)
)

### Question
What is total amount claimed summed across all the claims?

### SQL
SELECT sum(Amount_Claimed) FROM Claims
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
How many ships are there?

### SQL
SELECT count(*) FROM ship
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
What is the number of ships?

### SQL
SELECT count(*) FROM ship
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
List the name of ships in ascending order of tonnage.

### SQL
SELECT Name FROM ship ORDER BY Tonnage ASC
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
what are the names of the ships ordered by ascending tonnage?

### SQL
SELECT Name FROM ship ORDER BY Tonnage ASC
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
What are the type and nationality of ships?

### SQL
SELECT TYPE ,  Nationality FROM ship
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
What are the types and nationalities of every ship?

### SQL
SELECT TYPE ,  Nationality FROM ship
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
List the name of ships whose nationality is not "United States".

### SQL
SELECT Name FROM ship WHERE Nationality != "United States"
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
What are the names of the ships that are not from the United States?

### SQL
SELECT Name FROM ship WHERE Nationality != "United States"
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
Show the name of ships whose nationality is either United States or United Kingdom.

### SQL
SELECT Name FROM ship WHERE Nationality  =  "United States" OR Nationality  =  "United Kingdom"
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
What are the names of the ships  that are from either the US or the UK?

### SQL
SELECT Name FROM ship WHERE Nationality  =  "United States" OR Nationality  =  "United Kingdom"
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
What is the name of the ship with the largest tonnage?

### SQL
SELECT Name FROM ship ORDER BY Tonnage DESC LIMIT 1
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
What is the ship with the largest amount of tonnage called?

### SQL
SELECT Name FROM ship ORDER BY Tonnage DESC LIMIT 1
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
Show different types of ships and the number of ships of each type.

### SQL
SELECT TYPE ,  COUNT(*) FROM ship GROUP BY TYPE
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
For each type, how many ships are there?

### SQL
SELECT TYPE ,  COUNT(*) FROM ship GROUP BY TYPE
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
Please show the most common type of ships.

### SQL
SELECT TYPE FROM ship GROUP BY TYPE ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
What is the most common type of ships?

### SQL
SELECT TYPE FROM ship GROUP BY TYPE ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
List the nations that have more than two ships.

### SQL
SELECT Nationality FROM ship GROUP BY Nationality HAVING COUNT(*)  >  2
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
What are the nations that have more than two ships?

### SQL
SELECT Nationality FROM ship GROUP BY Nationality HAVING COUNT(*)  >  2
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
Show different types of ships and the average tonnage of ships of each type.

### SQL
SELECT TYPE ,  avg(Tonnage) FROM ship GROUP BY TYPE
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
For each type, what is the average tonnage?

### SQL
SELECT TYPE ,  avg(Tonnage) FROM ship GROUP BY TYPE
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
Show codes and fates of missions, and names of ships involved.

### SQL
SELECT T1.Code ,  T1.Fate ,  T2.Name FROM mission AS T1 JOIN ship AS T2 ON T1.Ship_ID  =  T2.Ship_ID
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
What are the mission codes, fates, and names of the ships involved?

### SQL
SELECT T1.Code ,  T1.Fate ,  T2.Name FROM mission AS T1 JOIN ship AS T2 ON T1.Ship_ID  =  T2.Ship_ID
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
Show names of ships involved in a mission launched after 1928.

### SQL
SELECT T2.Name FROM mission AS T1 JOIN ship AS T2 ON T1.Ship_ID  =  T2.Ship_ID WHERE T1.Launched_Year  >  1928
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
What are the names of ships that were involved in a mission launched after 1928?

### SQL
SELECT T2.Name FROM mission AS T1 JOIN ship AS T2 ON T1.Ship_ID  =  T2.Ship_ID WHERE T1.Launched_Year  >  1928
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
Show the distinct fate of missions that involve ships with nationality "United States"

### SQL
SELECT DISTINCT T1.Fate FROM mission AS T1 JOIN ship AS T2 ON T1.Ship_ID  =  T2.Ship_ID WHERE T2.Nationality  =  "United States"
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
What are the different fates of the mission that involved ships from the United States?

### SQL
SELECT DISTINCT T1.Fate FROM mission AS T1 JOIN ship AS T2 ON T1.Ship_ID  =  T2.Ship_ID WHERE T2.Nationality  =  "United States"
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
List the name of ships that are not involved in any mission

### SQL
SELECT Name FROM ship WHERE Ship_ID NOT IN (SELECT Ship_ID FROM mission)
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
What are the names of the ships that are not involved in any missions?

### SQL
SELECT Name FROM ship WHERE Ship_ID NOT IN (SELECT Ship_ID FROM mission)
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
Show the types of ships that have both ships with tonnage larger than 6000 and ships with tonnage smaller than 4000.

### SQL
SELECT TYPE FROM ship WHERE Tonnage  >  6000 INTERSECT SELECT TYPE FROM ship WHERE Tonnage  <  4000
### Database Schema
CREATE TABLE "mission" (
"Mission_ID" int,
"Ship_ID" int,
"Code" text,
"Launched_Year" int,
"Location" text,
"Speed_knots" int,
"Fate" text,
PRIMARY KEY ("Mission_ID"),
FOREIGN KEY ("Ship_ID") REFERENCES `ship`("Ship_ID")
)

CREATE TABLE "ship" (
"Ship_ID" int,
"Name" text,
"Type" text,
"Nationality" text,
"Tonnage" int,
PRIMARY KEY ("Ship_ID")
)

### Question
What are the types of the ships that have both shiips with tonnage more than 6000 and those with tonnage less than 4000?

### SQL
SELECT TYPE FROM ship WHERE Tonnage  >  6000 INTERSECT SELECT TYPE FROM ship WHERE Tonnage  <  4000
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Find the number of students in total.

### SQL
SELECT count(*) FROM list
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
How many students are there?

### SQL
SELECT count(*) FROM list
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Find the last names of students studying in room 111.

### SQL
SELECT lastname FROM list WHERE classroom  =  111
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
What are the last names of students in room 111?

### SQL
SELECT lastname FROM list WHERE classroom  =  111
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Find the first names of students studying in room 108.

### SQL
SELECT firstname FROM list WHERE classroom  =  108
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
What are the first names of students in room 108?

### SQL
SELECT firstname FROM list WHERE classroom  =  108
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
What are the first names of students studying in room 107?

### SQL
SELECT DISTINCT firstname FROM list WHERE classroom  =  107
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
List the first names of all the students in room 107.

### SQL
SELECT DISTINCT firstname FROM list WHERE classroom  =  107
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
For each classroom report the grade that is taught in it. Report just the classroom number and the grade number.

### SQL
SELECT DISTINCT classroom ,  grade FROM list
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
What are the grade number and classroom number of each class in the list?

### SQL
SELECT DISTINCT classroom ,  grade FROM list
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Which grade is studying in classroom 103?

### SQL
SELECT DISTINCT grade FROM list WHERE classroom  =  103
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Find the grade taught in classroom 103.

### SQL
SELECT DISTINCT grade FROM list WHERE classroom  =  103
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Find the grade studying in room 105.

### SQL
SELECT DISTINCT grade FROM list WHERE classroom  =  105
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Which grade is studying in room 105?

### SQL
SELECT DISTINCT grade FROM list WHERE classroom  =  105
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Which classrooms are used by grade 4?

### SQL
SELECT DISTINCT classroom FROM list WHERE grade  =  4
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Find the classrooms in which grade 4 is studying.

### SQL
SELECT DISTINCT classroom FROM list WHERE grade  =  4
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Which classrooms are used by grade 5?

### SQL
SELECT DISTINCT classroom FROM list WHERE grade  =  5
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Show me the classrooms grade 5 is using.

### SQL
SELECT DISTINCT classroom FROM list WHERE grade  =  5
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Find the last names of the teachers that teach fifth grade.

### SQL
SELECT DISTINCT T2.lastname FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE grade  =  5
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
what are the last names of the teachers who teach grade 5?

### SQL
SELECT DISTINCT T2.lastname FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE grade  =  5
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Find the first names of the teachers that teach first grade.

### SQL
SELECT DISTINCT T2.firstname FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE grade  =  1
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
What are the first names of the teachers who teach grade 1?

### SQL
SELECT DISTINCT T2.firstname FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE grade  =  1
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Find the first names of all the teachers that teach in classroom 110.

### SQL
SELECT firstname FROM teachers WHERE classroom  =  110
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Which teachers teach in classroom 110? Give me their first names.

### SQL
SELECT firstname FROM teachers WHERE classroom  =  110
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Find the last names of teachers teaching in classroom 109.

### SQL
SELECT lastname FROM teachers WHERE classroom  =  109
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Which teachers teach in classroom 109? Give me their last names.

### SQL
SELECT lastname FROM teachers WHERE classroom  =  109
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Report the first name and last name of all the teachers.

### SQL
SELECT DISTINCT firstname ,  lastname FROM teachers
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
What are the first name and last name of all the teachers?

### SQL
SELECT DISTINCT firstname ,  lastname FROM teachers
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Report the first name and last name of all the students.

### SQL
SELECT DISTINCT firstname ,  lastname FROM list
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Show each student's first name and last name.

### SQL
SELECT DISTINCT firstname ,  lastname FROM list
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Find all students taught by OTHA MOYER. Output the first and last names of the students.

### SQL
SELECT T1.firstname ,  T1.lastname FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE T2.firstname  =  "OTHA" AND T2.lastname  =  "MOYER"
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Which students study under the teacher named OTHA MOYER? Give me the first and last names of the students.

### SQL
SELECT T1.firstname ,  T1.lastname FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE T2.firstname  =  "OTHA" AND T2.lastname  =  "MOYER"
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Find all students taught by MARROTTE KIRK. Output first and last names of students.

### SQL
SELECT T1.firstname ,  T1.lastname FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE T2.firstname  =  "MARROTTE" AND T2.lastname  =  "KIRK"
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Which are the first and last names of the students taught by MARROTTE KIRK?

### SQL
SELECT T1.firstname ,  T1.lastname FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE T2.firstname  =  "MARROTTE" AND T2.lastname  =  "KIRK"
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Find the first and last name of all the teachers that teach EVELINA BROMLEY.

### SQL
SELECT T2.firstname ,  T2.lastname FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE T1.firstname  =  "EVELINA" AND T1.lastname  =  "BROMLEY"
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Which teachers teach the student named EVELINA BROMLEY? Give me the first and last name  of the teachers.

### SQL
SELECT T2.firstname ,  T2.lastname FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE T1.firstname  =  "EVELINA" AND T1.lastname  =  "BROMLEY"
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Find the last names of all the teachers that teach GELL TAMI.

### SQL
SELECT T2.lastname FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE T1.firstname  =  "GELL" AND T1.lastname  =  "TAMI"
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
What are the last names of the teachers who teach the student called GELL TAMI?

### SQL
SELECT T2.lastname FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE T1.firstname  =  "GELL" AND T1.lastname  =  "TAMI"
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
How many students does LORIA ONDERSMA teaches?

### SQL
SELECT count(*) FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE T2.firstname  =  "LORIA" AND T2.lastname  =  "ONDERSMA"
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Count the number of students the teacher LORIA ONDERSMA teaches.

### SQL
SELECT count(*) FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE T2.firstname  =  "LORIA" AND T2.lastname  =  "ONDERSMA"
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
How many students does KAWA GORDON teaches?

### SQL
SELECT count(*) FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE T2.firstname  =  "KAWA" AND T2.lastname  =  "GORDON"
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Find the number of students taught by the teacher KAWA GORDON.

### SQL
SELECT count(*) FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE T2.firstname  =  "KAWA" AND T2.lastname  =  "GORDON"
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Find the number of students taught by TARRING LEIA.

### SQL
SELECT count(*) FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE T2.firstname  =  "TARRING" AND T2.lastname  =  "LEIA"
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
How many students are taught by teacher TARRING LEIA?

### SQL
SELECT count(*) FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE T2.firstname  =  "TARRING" AND T2.lastname  =  "LEIA"
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
How many teachers does the student named CHRISSY NABOZNY have?

### SQL
SELECT count(*) FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE T1.firstname  =  "CHRISSY" AND T1.lastname  =  "NABOZNY"
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Find the number of teachers who teach the student called CHRISSY NABOZNY.

### SQL
SELECT count(*) FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE T1.firstname  =  "CHRISSY" AND T1.lastname  =  "NABOZNY"
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
How many teachers does the student named MADLOCK RAY have?

### SQL
SELECT count(*) FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE T1.firstname  =  "MADLOCK" AND T1.lastname  =  "RAY"
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Find the number of teachers who teach the student called MADLOCK RAY.

### SQL
SELECT count(*) FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE T1.firstname  =  "MADLOCK" AND T1.lastname  =  "RAY"
### Database Schema
CREATE TABLE "list" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Grade" INTEGER, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

CREATE TABLE "teachers" ( 
	"LastName" TEXT, 
	"FirstName" TEXT, 
	"Classroom" INTEGER,
	PRIMARY KEY(LastName, FirstName)
)

### Question
Find all first-grade students who are NOT taught by OTHA MOYER. Report their first and last names.

### SQL
SELECT DISTINCT T1.firstname ,  T1.lastname FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE T1.grade  =  1 EXCEPT SELECT T1.firstname ,  T1.lastname FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE T2.firstname  =  "OTHA" AND T2.lastname  =  "MOYER"