lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"District" text,
"Name" text,
"Party" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "debate" (
"Debate_ID" int,
"Date" text,
"Venue" text,
"Num_of_Audience" int,
PRIMARY KEY ("Debate_ID")
)

CREATE TABLE "debate_people" (
"Debate_ID" int,
"Affirmative" int,
"Negative" int,
"If_Affirmative_Win" bool,
PRIMARY KEY ("Debate_ID","Affirmative","Negative"),
FOREIGN KEY ("Debate_ID") REFERENCES `debate`("Debate_ID"),
FOREIGN KEY ("Affirmative") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Negative") REFERENCES `people`("People_ID")
)

### Question
Show the party that has the most people.

### SQL
SELECT Party FROM people GROUP BY Party ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"District" text,
"Name" text,
"Party" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "debate" (
"Debate_ID" int,
"Date" text,
"Venue" text,
"Num_of_Audience" int,
PRIMARY KEY ("Debate_ID")
)

CREATE TABLE "debate_people" (
"Debate_ID" int,
"Affirmative" int,
"Negative" int,
"If_Affirmative_Win" bool,
PRIMARY KEY ("Debate_ID","Affirmative","Negative"),
FOREIGN KEY ("Debate_ID") REFERENCES `debate`("Debate_ID"),
FOREIGN KEY ("Affirmative") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Negative") REFERENCES `people`("People_ID")
)

### Question
Show the distinct venues of debates

### SQL
SELECT DISTINCT Venue FROM debate
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"District" text,
"Name" text,
"Party" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "debate" (
"Debate_ID" int,
"Date" text,
"Venue" text,
"Num_of_Audience" int,
PRIMARY KEY ("Debate_ID")
)

CREATE TABLE "debate_people" (
"Debate_ID" int,
"Affirmative" int,
"Negative" int,
"If_Affirmative_Win" bool,
PRIMARY KEY ("Debate_ID","Affirmative","Negative"),
FOREIGN KEY ("Debate_ID") REFERENCES `debate`("Debate_ID"),
FOREIGN KEY ("Affirmative") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Negative") REFERENCES `people`("People_ID")
)

### Question
Show the names of people, and dates and venues of debates they are on the affirmative side.

### SQL
SELECT T3.Name ,  T2.Date ,  T2.Venue FROM debate_people AS T1 JOIN debate AS T2 ON T1.Debate_ID  =  T2.Debate_ID JOIN people AS T3 ON T1.Affirmative  =  T3.People_ID
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"District" text,
"Name" text,
"Party" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "debate" (
"Debate_ID" int,
"Date" text,
"Venue" text,
"Num_of_Audience" int,
PRIMARY KEY ("Debate_ID")
)

CREATE TABLE "debate_people" (
"Debate_ID" int,
"Affirmative" int,
"Negative" int,
"If_Affirmative_Win" bool,
PRIMARY KEY ("Debate_ID","Affirmative","Negative"),
FOREIGN KEY ("Debate_ID") REFERENCES `debate`("Debate_ID"),
FOREIGN KEY ("Affirmative") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Negative") REFERENCES `people`("People_ID")
)

### Question
Show the names of people, and dates and venues of debates they are on the negative side, ordered in ascending alphabetical order of name.

### SQL
SELECT T3.Name ,  T2.Date ,  T2.Venue FROM debate_people AS T1 JOIN debate AS T2 ON T1.Debate_ID  =  T2.Debate_ID JOIN people AS T3 ON T1.Negative  =  T3.People_ID ORDER BY T3.Name ASC
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"District" text,
"Name" text,
"Party" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "debate" (
"Debate_ID" int,
"Date" text,
"Venue" text,
"Num_of_Audience" int,
PRIMARY KEY ("Debate_ID")
)

CREATE TABLE "debate_people" (
"Debate_ID" int,
"Affirmative" int,
"Negative" int,
"If_Affirmative_Win" bool,
PRIMARY KEY ("Debate_ID","Affirmative","Negative"),
FOREIGN KEY ("Debate_ID") REFERENCES `debate`("Debate_ID"),
FOREIGN KEY ("Affirmative") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Negative") REFERENCES `people`("People_ID")
)

### Question
Show the names of people that are on affirmative side of debates with number of audience bigger than 200.

### SQL
SELECT T3.Name FROM debate_people AS T1 JOIN debate AS T2 ON T1.Debate_ID  =  T2.Debate_ID JOIN people AS T3 ON T1.Affirmative  =  T3.People_ID WHERE T2.Num_of_Audience  >  200
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"District" text,
"Name" text,
"Party" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "debate" (
"Debate_ID" int,
"Date" text,
"Venue" text,
"Num_of_Audience" int,
PRIMARY KEY ("Debate_ID")
)

CREATE TABLE "debate_people" (
"Debate_ID" int,
"Affirmative" int,
"Negative" int,
"If_Affirmative_Win" bool,
PRIMARY KEY ("Debate_ID","Affirmative","Negative"),
FOREIGN KEY ("Debate_ID") REFERENCES `debate`("Debate_ID"),
FOREIGN KEY ("Affirmative") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Negative") REFERENCES `people`("People_ID")
)

### Question
Show the names of people and the number of times they have been on the affirmative side of debates.

### SQL
SELECT T2.Name ,  COUNT(*) FROM debate_people AS T1 JOIN people AS T2 ON T1.Affirmative  =  T2.People_ID GROUP BY T2.Name
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"District" text,
"Name" text,
"Party" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "debate" (
"Debate_ID" int,
"Date" text,
"Venue" text,
"Num_of_Audience" int,
PRIMARY KEY ("Debate_ID")
)

CREATE TABLE "debate_people" (
"Debate_ID" int,
"Affirmative" int,
"Negative" int,
"If_Affirmative_Win" bool,
PRIMARY KEY ("Debate_ID","Affirmative","Negative"),
FOREIGN KEY ("Debate_ID") REFERENCES `debate`("Debate_ID"),
FOREIGN KEY ("Affirmative") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Negative") REFERENCES `people`("People_ID")
)

### Question
Show the names of people who have been on the negative side of debates at least twice.

### SQL
SELECT T2.Name FROM debate_people AS T1 JOIN people AS T2 ON T1.Negative  =  T2.People_ID GROUP BY T2.Name HAVING COUNT(*)  >=  2
### Database Schema
CREATE TABLE "people" (
"People_ID" int,
"District" text,
"Name" text,
"Party" text,
"Age" int,
PRIMARY KEY ("People_ID")
)

CREATE TABLE "debate" (
"Debate_ID" int,
"Date" text,
"Venue" text,
"Num_of_Audience" int,
PRIMARY KEY ("Debate_ID")
)

CREATE TABLE "debate_people" (
"Debate_ID" int,
"Affirmative" int,
"Negative" int,
"If_Affirmative_Win" bool,
PRIMARY KEY ("Debate_ID","Affirmative","Negative"),
FOREIGN KEY ("Debate_ID") REFERENCES `debate`("Debate_ID"),
FOREIGN KEY ("Affirmative") REFERENCES `people`("People_ID"),
FOREIGN KEY ("Negative") REFERENCES `people`("People_ID")
)

### Question
List the names of people that have not been on the affirmative side of debates.

### SQL
SELECT Name FROM people WHERE People_id NOT IN (SELECT Affirmative FROM debate_people)
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Staff (
Staff_ID INTEGER NOT NULL,
Staff_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Staff_ID)
)

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

CREATE TABLE Claim_Headers (
Claim_Header_ID INTEGER NOT NULL,
Claim_Status_Code CHAR(15) NOT NULL,
Claim_Type_Code CHAR(15) NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_of_Claim DATETIME,
Date_of_Settlement DATETIME,
Amount_Claimed DECIMAL(20,4),
Amount_Piad DECIMAL(20,4),
PRIMARY KEY (Claim_Header_ID),
FOREIGN KEY (Policy_ID) REFERENCES Policies (Policy_ID)
)

CREATE TABLE Claims_Documents (
Claim_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Created_by_Staff_ID INTEGER,
Created_Date INTEGER,
PRIMARY KEY (Claim_ID, Document_Type_Code),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Created_by_Staff_ID) REFERENCES Staff (Staff_ID)
)

CREATE TABLE Claims_Processing_Stages (
Claim_Stage_ID INTEGER NOT NULL,
Next_Claim_Stage_ID INTEGER,
Claim_Status_Name VARCHAR(255) NOT NULL,
Claim_Status_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Claim_Stage_ID)
)

CREATE TABLE Claims_Processing (
Claim_Processing_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Claim_Outcome_Code CHAR(15) NOT NULL,
Claim_Stage_ID INTEGER NOT NULL,
Staff_ID INTEGER,
PRIMARY KEY (Claim_Processing_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Staff_ID) REFERENCES Staff (Staff_ID)
)

### Question
List the names of all the customers in alphabetical order.

### SQL
SELECT customer_details FROM customers ORDER BY customer_details
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Staff (
Staff_ID INTEGER NOT NULL,
Staff_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Staff_ID)
)

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

CREATE TABLE Claim_Headers (
Claim_Header_ID INTEGER NOT NULL,
Claim_Status_Code CHAR(15) NOT NULL,
Claim_Type_Code CHAR(15) NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_of_Claim DATETIME,
Date_of_Settlement DATETIME,
Amount_Claimed DECIMAL(20,4),
Amount_Piad DECIMAL(20,4),
PRIMARY KEY (Claim_Header_ID),
FOREIGN KEY (Policy_ID) REFERENCES Policies (Policy_ID)
)

CREATE TABLE Claims_Documents (
Claim_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Created_by_Staff_ID INTEGER,
Created_Date INTEGER,
PRIMARY KEY (Claim_ID, Document_Type_Code),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Created_by_Staff_ID) REFERENCES Staff (Staff_ID)
)

CREATE TABLE Claims_Processing_Stages (
Claim_Stage_ID INTEGER NOT NULL,
Next_Claim_Stage_ID INTEGER,
Claim_Status_Name VARCHAR(255) NOT NULL,
Claim_Status_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Claim_Stage_ID)
)

CREATE TABLE Claims_Processing (
Claim_Processing_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Claim_Outcome_Code CHAR(15) NOT NULL,
Claim_Stage_ID INTEGER NOT NULL,
Staff_ID INTEGER,
PRIMARY KEY (Claim_Processing_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Staff_ID) REFERENCES Staff (Staff_ID)
)

### Question
Sort the customer names in alphabetical order.

### SQL
SELECT customer_details FROM customers ORDER BY customer_details
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Staff (
Staff_ID INTEGER NOT NULL,
Staff_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Staff_ID)
)

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

CREATE TABLE Claim_Headers (
Claim_Header_ID INTEGER NOT NULL,
Claim_Status_Code CHAR(15) NOT NULL,
Claim_Type_Code CHAR(15) NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_of_Claim DATETIME,
Date_of_Settlement DATETIME,
Amount_Claimed DECIMAL(20,4),
Amount_Piad DECIMAL(20,4),
PRIMARY KEY (Claim_Header_ID),
FOREIGN KEY (Policy_ID) REFERENCES Policies (Policy_ID)
)

CREATE TABLE Claims_Documents (
Claim_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Created_by_Staff_ID INTEGER,
Created_Date INTEGER,
PRIMARY KEY (Claim_ID, Document_Type_Code),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Created_by_Staff_ID) REFERENCES Staff (Staff_ID)
)

CREATE TABLE Claims_Processing_Stages (
Claim_Stage_ID INTEGER NOT NULL,
Next_Claim_Stage_ID INTEGER,
Claim_Status_Name VARCHAR(255) NOT NULL,
Claim_Status_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Claim_Stage_ID)
)

CREATE TABLE Claims_Processing (
Claim_Processing_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Claim_Outcome_Code CHAR(15) NOT NULL,
Claim_Stage_ID INTEGER NOT NULL,
Staff_ID INTEGER,
PRIMARY KEY (Claim_Processing_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Staff_ID) REFERENCES Staff (Staff_ID)
)

### Question
Find all the policy type codes associated with the customer "Dayana Robel"

### SQL
SELECT policy_type_code FROM policies AS t1 JOIN customers AS t2 ON t1.customer_id  =  t2.customer_id WHERE t2.customer_details  =  "Dayana Robel"
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Staff (
Staff_ID INTEGER NOT NULL,
Staff_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Staff_ID)
)

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

CREATE TABLE Claim_Headers (
Claim_Header_ID INTEGER NOT NULL,
Claim_Status_Code CHAR(15) NOT NULL,
Claim_Type_Code CHAR(15) NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_of_Claim DATETIME,
Date_of_Settlement DATETIME,
Amount_Claimed DECIMAL(20,4),
Amount_Piad DECIMAL(20,4),
PRIMARY KEY (Claim_Header_ID),
FOREIGN KEY (Policy_ID) REFERENCES Policies (Policy_ID)
)

CREATE TABLE Claims_Documents (
Claim_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Created_by_Staff_ID INTEGER,
Created_Date INTEGER,
PRIMARY KEY (Claim_ID, Document_Type_Code),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Created_by_Staff_ID) REFERENCES Staff (Staff_ID)
)

CREATE TABLE Claims_Processing_Stages (
Claim_Stage_ID INTEGER NOT NULL,
Next_Claim_Stage_ID INTEGER,
Claim_Status_Name VARCHAR(255) NOT NULL,
Claim_Status_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Claim_Stage_ID)
)

CREATE TABLE Claims_Processing (
Claim_Processing_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Claim_Outcome_Code CHAR(15) NOT NULL,
Claim_Stage_ID INTEGER NOT NULL,
Staff_ID INTEGER,
PRIMARY KEY (Claim_Processing_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Staff_ID) REFERENCES Staff (Staff_ID)
)

### Question
What are the type codes of the policies used by the customer "Dayana Robel"?

### SQL
SELECT policy_type_code FROM policies AS t1 JOIN customers AS t2 ON t1.customer_id  =  t2.customer_id WHERE t2.customer_details  =  "Dayana Robel"
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Staff (
Staff_ID INTEGER NOT NULL,
Staff_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Staff_ID)
)

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

CREATE TABLE Claim_Headers (
Claim_Header_ID INTEGER NOT NULL,
Claim_Status_Code CHAR(15) NOT NULL,
Claim_Type_Code CHAR(15) NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_of_Claim DATETIME,
Date_of_Settlement DATETIME,
Amount_Claimed DECIMAL(20,4),
Amount_Piad DECIMAL(20,4),
PRIMARY KEY (Claim_Header_ID),
FOREIGN KEY (Policy_ID) REFERENCES Policies (Policy_ID)
)

CREATE TABLE Claims_Documents (
Claim_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Created_by_Staff_ID INTEGER,
Created_Date INTEGER,
PRIMARY KEY (Claim_ID, Document_Type_Code),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Created_by_Staff_ID) REFERENCES Staff (Staff_ID)
)

CREATE TABLE Claims_Processing_Stages (
Claim_Stage_ID INTEGER NOT NULL,
Next_Claim_Stage_ID INTEGER,
Claim_Status_Name VARCHAR(255) NOT NULL,
Claim_Status_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Claim_Stage_ID)
)

CREATE TABLE Claims_Processing (
Claim_Processing_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Claim_Outcome_Code CHAR(15) NOT NULL,
Claim_Stage_ID INTEGER NOT NULL,
Staff_ID INTEGER,
PRIMARY KEY (Claim_Processing_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Staff_ID) REFERENCES Staff (Staff_ID)
)

### Question
Which type of policy is most frequently used? Give me the policy type code.

### SQL
SELECT policy_type_code FROM policies GROUP BY policy_type_code ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Staff (
Staff_ID INTEGER NOT NULL,
Staff_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Staff_ID)
)

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

CREATE TABLE Claim_Headers (
Claim_Header_ID INTEGER NOT NULL,
Claim_Status_Code CHAR(15) NOT NULL,
Claim_Type_Code CHAR(15) NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_of_Claim DATETIME,
Date_of_Settlement DATETIME,
Amount_Claimed DECIMAL(20,4),
Amount_Piad DECIMAL(20,4),
PRIMARY KEY (Claim_Header_ID),
FOREIGN KEY (Policy_ID) REFERENCES Policies (Policy_ID)
)

CREATE TABLE Claims_Documents (
Claim_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Created_by_Staff_ID INTEGER,
Created_Date INTEGER,
PRIMARY KEY (Claim_ID, Document_Type_Code),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Created_by_Staff_ID) REFERENCES Staff (Staff_ID)
)

CREATE TABLE Claims_Processing_Stages (
Claim_Stage_ID INTEGER NOT NULL,
Next_Claim_Stage_ID INTEGER,
Claim_Status_Name VARCHAR(255) NOT NULL,
Claim_Status_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Claim_Stage_ID)
)

CREATE TABLE Claims_Processing (
Claim_Processing_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Claim_Outcome_Code CHAR(15) NOT NULL,
Claim_Stage_ID INTEGER NOT NULL,
Staff_ID INTEGER,
PRIMARY KEY (Claim_Processing_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Staff_ID) REFERENCES Staff (Staff_ID)
)

### Question
Find the type code of the most frequently used policy.

### SQL
SELECT policy_type_code FROM policies GROUP BY policy_type_code ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Staff (
Staff_ID INTEGER NOT NULL,
Staff_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Staff_ID)
)

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

CREATE TABLE Claim_Headers (
Claim_Header_ID INTEGER NOT NULL,
Claim_Status_Code CHAR(15) NOT NULL,
Claim_Type_Code CHAR(15) NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_of_Claim DATETIME,
Date_of_Settlement DATETIME,
Amount_Claimed DECIMAL(20,4),
Amount_Piad DECIMAL(20,4),
PRIMARY KEY (Claim_Header_ID),
FOREIGN KEY (Policy_ID) REFERENCES Policies (Policy_ID)
)

CREATE TABLE Claims_Documents (
Claim_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Created_by_Staff_ID INTEGER,
Created_Date INTEGER,
PRIMARY KEY (Claim_ID, Document_Type_Code),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Created_by_Staff_ID) REFERENCES Staff (Staff_ID)
)

CREATE TABLE Claims_Processing_Stages (
Claim_Stage_ID INTEGER NOT NULL,
Next_Claim_Stage_ID INTEGER,
Claim_Status_Name VARCHAR(255) NOT NULL,
Claim_Status_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Claim_Stage_ID)
)

CREATE TABLE Claims_Processing (
Claim_Processing_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Claim_Outcome_Code CHAR(15) NOT NULL,
Claim_Stage_ID INTEGER NOT NULL,
Staff_ID INTEGER,
PRIMARY KEY (Claim_Processing_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Staff_ID) REFERENCES Staff (Staff_ID)
)

### Question
Find all the policy types that are used by more than 2 customers.

### SQL
SELECT policy_type_code FROM policies GROUP BY policy_type_code HAVING count(*)  >  2
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Staff (
Staff_ID INTEGER NOT NULL,
Staff_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Staff_ID)
)

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

CREATE TABLE Claim_Headers (
Claim_Header_ID INTEGER NOT NULL,
Claim_Status_Code CHAR(15) NOT NULL,
Claim_Type_Code CHAR(15) NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_of_Claim DATETIME,
Date_of_Settlement DATETIME,
Amount_Claimed DECIMAL(20,4),
Amount_Piad DECIMAL(20,4),
PRIMARY KEY (Claim_Header_ID),
FOREIGN KEY (Policy_ID) REFERENCES Policies (Policy_ID)
)

CREATE TABLE Claims_Documents (
Claim_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Created_by_Staff_ID INTEGER,
Created_Date INTEGER,
PRIMARY KEY (Claim_ID, Document_Type_Code),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Created_by_Staff_ID) REFERENCES Staff (Staff_ID)
)

CREATE TABLE Claims_Processing_Stages (
Claim_Stage_ID INTEGER NOT NULL,
Next_Claim_Stage_ID INTEGER,
Claim_Status_Name VARCHAR(255) NOT NULL,
Claim_Status_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Claim_Stage_ID)
)

CREATE TABLE Claims_Processing (
Claim_Processing_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Claim_Outcome_Code CHAR(15) NOT NULL,
Claim_Stage_ID INTEGER NOT NULL,
Staff_ID INTEGER,
PRIMARY KEY (Claim_Processing_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Staff_ID) REFERENCES Staff (Staff_ID)
)

### Question
Which types of policy are chosen by more than 2 customers? Give me the policy type codes.

### SQL
SELECT policy_type_code FROM policies GROUP BY policy_type_code HAVING count(*)  >  2
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Staff (
Staff_ID INTEGER NOT NULL,
Staff_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Staff_ID)
)

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

CREATE TABLE Claim_Headers (
Claim_Header_ID INTEGER NOT NULL,
Claim_Status_Code CHAR(15) NOT NULL,
Claim_Type_Code CHAR(15) NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_of_Claim DATETIME,
Date_of_Settlement DATETIME,
Amount_Claimed DECIMAL(20,4),
Amount_Piad DECIMAL(20,4),
PRIMARY KEY (Claim_Header_ID),
FOREIGN KEY (Policy_ID) REFERENCES Policies (Policy_ID)
)

CREATE TABLE Claims_Documents (
Claim_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Created_by_Staff_ID INTEGER,
Created_Date INTEGER,
PRIMARY KEY (Claim_ID, Document_Type_Code),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Created_by_Staff_ID) REFERENCES Staff (Staff_ID)
)

CREATE TABLE Claims_Processing_Stages (
Claim_Stage_ID INTEGER NOT NULL,
Next_Claim_Stage_ID INTEGER,
Claim_Status_Name VARCHAR(255) NOT NULL,
Claim_Status_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Claim_Stage_ID)
)

CREATE TABLE Claims_Processing (
Claim_Processing_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Claim_Outcome_Code CHAR(15) NOT NULL,
Claim_Stage_ID INTEGER NOT NULL,
Staff_ID INTEGER,
PRIMARY KEY (Claim_Processing_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Staff_ID) REFERENCES Staff (Staff_ID)
)

### Question
Find the total and average amount paid in claim headers.

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

CREATE TABLE Staff (
Staff_ID INTEGER NOT NULL,
Staff_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Staff_ID)
)

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

CREATE TABLE Claim_Headers (
Claim_Header_ID INTEGER NOT NULL,
Claim_Status_Code CHAR(15) NOT NULL,
Claim_Type_Code CHAR(15) NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_of_Claim DATETIME,
Date_of_Settlement DATETIME,
Amount_Claimed DECIMAL(20,4),
Amount_Piad DECIMAL(20,4),
PRIMARY KEY (Claim_Header_ID),
FOREIGN KEY (Policy_ID) REFERENCES Policies (Policy_ID)
)

CREATE TABLE Claims_Documents (
Claim_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Created_by_Staff_ID INTEGER,
Created_Date INTEGER,
PRIMARY KEY (Claim_ID, Document_Type_Code),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Created_by_Staff_ID) REFERENCES Staff (Staff_ID)
)

CREATE TABLE Claims_Processing_Stages (
Claim_Stage_ID INTEGER NOT NULL,
Next_Claim_Stage_ID INTEGER,
Claim_Status_Name VARCHAR(255) NOT NULL,
Claim_Status_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Claim_Stage_ID)
)

CREATE TABLE Claims_Processing (
Claim_Processing_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Claim_Outcome_Code CHAR(15) NOT NULL,
Claim_Stage_ID INTEGER NOT NULL,
Staff_ID INTEGER,
PRIMARY KEY (Claim_Processing_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Staff_ID) REFERENCES Staff (Staff_ID)
)

### Question
What are the total amount and average amount paid in claim headers?

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

CREATE TABLE Staff (
Staff_ID INTEGER NOT NULL,
Staff_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Staff_ID)
)

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

CREATE TABLE Claim_Headers (
Claim_Header_ID INTEGER NOT NULL,
Claim_Status_Code CHAR(15) NOT NULL,
Claim_Type_Code CHAR(15) NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_of_Claim DATETIME,
Date_of_Settlement DATETIME,
Amount_Claimed DECIMAL(20,4),
Amount_Piad DECIMAL(20,4),
PRIMARY KEY (Claim_Header_ID),
FOREIGN KEY (Policy_ID) REFERENCES Policies (Policy_ID)
)

CREATE TABLE Claims_Documents (
Claim_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Created_by_Staff_ID INTEGER,
Created_Date INTEGER,
PRIMARY KEY (Claim_ID, Document_Type_Code),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Created_by_Staff_ID) REFERENCES Staff (Staff_ID)
)

CREATE TABLE Claims_Processing_Stages (
Claim_Stage_ID INTEGER NOT NULL,
Next_Claim_Stage_ID INTEGER,
Claim_Status_Name VARCHAR(255) NOT NULL,
Claim_Status_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Claim_Stage_ID)
)

CREATE TABLE Claims_Processing (
Claim_Processing_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Claim_Outcome_Code CHAR(15) NOT NULL,
Claim_Stage_ID INTEGER NOT NULL,
Staff_ID INTEGER,
PRIMARY KEY (Claim_Processing_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Staff_ID) REFERENCES Staff (Staff_ID)
)

### Question
Find the total amount claimed in the most recently created document.

### SQL
SELECT sum(t1.amount_claimed) FROM claim_headers AS t1 JOIN claims_documents AS t2 ON t1.claim_header_id  =  t2.claim_id WHERE t2.created_date  =  (SELECT created_date FROM claims_documents ORDER BY created_date LIMIT 1)
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Staff (
Staff_ID INTEGER NOT NULL,
Staff_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Staff_ID)
)

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

CREATE TABLE Claim_Headers (
Claim_Header_ID INTEGER NOT NULL,
Claim_Status_Code CHAR(15) NOT NULL,
Claim_Type_Code CHAR(15) NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_of_Claim DATETIME,
Date_of_Settlement DATETIME,
Amount_Claimed DECIMAL(20,4),
Amount_Piad DECIMAL(20,4),
PRIMARY KEY (Claim_Header_ID),
FOREIGN KEY (Policy_ID) REFERENCES Policies (Policy_ID)
)

CREATE TABLE Claims_Documents (
Claim_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Created_by_Staff_ID INTEGER,
Created_Date INTEGER,
PRIMARY KEY (Claim_ID, Document_Type_Code),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Created_by_Staff_ID) REFERENCES Staff (Staff_ID)
)

CREATE TABLE Claims_Processing_Stages (
Claim_Stage_ID INTEGER NOT NULL,
Next_Claim_Stage_ID INTEGER,
Claim_Status_Name VARCHAR(255) NOT NULL,
Claim_Status_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Claim_Stage_ID)
)

CREATE TABLE Claims_Processing (
Claim_Processing_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Claim_Outcome_Code CHAR(15) NOT NULL,
Claim_Stage_ID INTEGER NOT NULL,
Staff_ID INTEGER,
PRIMARY KEY (Claim_Processing_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Staff_ID) REFERENCES Staff (Staff_ID)
)

### Question
How much amount in total were claimed in the most recently created document?

### SQL
SELECT sum(t1.amount_claimed) FROM claim_headers AS t1 JOIN claims_documents AS t2 ON t1.claim_header_id  =  t2.claim_id WHERE t2.created_date  =  (SELECT created_date FROM claims_documents ORDER BY created_date LIMIT 1)
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Staff (
Staff_ID INTEGER NOT NULL,
Staff_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Staff_ID)
)

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

CREATE TABLE Claim_Headers (
Claim_Header_ID INTEGER NOT NULL,
Claim_Status_Code CHAR(15) NOT NULL,
Claim_Type_Code CHAR(15) NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_of_Claim DATETIME,
Date_of_Settlement DATETIME,
Amount_Claimed DECIMAL(20,4),
Amount_Piad DECIMAL(20,4),
PRIMARY KEY (Claim_Header_ID),
FOREIGN KEY (Policy_ID) REFERENCES Policies (Policy_ID)
)

CREATE TABLE Claims_Documents (
Claim_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Created_by_Staff_ID INTEGER,
Created_Date INTEGER,
PRIMARY KEY (Claim_ID, Document_Type_Code),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Created_by_Staff_ID) REFERENCES Staff (Staff_ID)
)

CREATE TABLE Claims_Processing_Stages (
Claim_Stage_ID INTEGER NOT NULL,
Next_Claim_Stage_ID INTEGER,
Claim_Status_Name VARCHAR(255) NOT NULL,
Claim_Status_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Claim_Stage_ID)
)

CREATE TABLE Claims_Processing (
Claim_Processing_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Claim_Outcome_Code CHAR(15) NOT NULL,
Claim_Stage_ID INTEGER NOT NULL,
Staff_ID INTEGER,
PRIMARY KEY (Claim_Processing_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Staff_ID) REFERENCES Staff (Staff_ID)
)

### Question
What is the name of the customer who has made the largest amount of claim in a single claim?

### SQL
SELECT t3.customer_details FROM claim_headers AS t1 JOIN policies AS t2 ON t1.policy_id  =  t2.policy_id JOIN customers AS t3 ON t2.customer_id  =  t3.customer_id WHERE t1.amount_claimed  =  (SELECT max(amount_claimed) FROM claim_headers)
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Staff (
Staff_ID INTEGER NOT NULL,
Staff_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Staff_ID)
)

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

CREATE TABLE Claim_Headers (
Claim_Header_ID INTEGER NOT NULL,
Claim_Status_Code CHAR(15) NOT NULL,
Claim_Type_Code CHAR(15) NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_of_Claim DATETIME,
Date_of_Settlement DATETIME,
Amount_Claimed DECIMAL(20,4),
Amount_Piad DECIMAL(20,4),
PRIMARY KEY (Claim_Header_ID),
FOREIGN KEY (Policy_ID) REFERENCES Policies (Policy_ID)
)

CREATE TABLE Claims_Documents (
Claim_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Created_by_Staff_ID INTEGER,
Created_Date INTEGER,
PRIMARY KEY (Claim_ID, Document_Type_Code),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Created_by_Staff_ID) REFERENCES Staff (Staff_ID)
)

CREATE TABLE Claims_Processing_Stages (
Claim_Stage_ID INTEGER NOT NULL,
Next_Claim_Stage_ID INTEGER,
Claim_Status_Name VARCHAR(255) NOT NULL,
Claim_Status_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Claim_Stage_ID)
)

CREATE TABLE Claims_Processing (
Claim_Processing_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Claim_Outcome_Code CHAR(15) NOT NULL,
Claim_Stage_ID INTEGER NOT NULL,
Staff_ID INTEGER,
PRIMARY KEY (Claim_Processing_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Staff_ID) REFERENCES Staff (Staff_ID)
)

### Question
Which customer made the largest amount of claim in a single claim? Return the customer details.

### SQL
SELECT t3.customer_details FROM claim_headers AS t1 JOIN policies AS t2 ON t1.policy_id  =  t2.policy_id JOIN customers AS t3 ON t2.customer_id  =  t3.customer_id WHERE t1.amount_claimed  =  (SELECT max(amount_claimed) FROM claim_headers)
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Staff (
Staff_ID INTEGER NOT NULL,
Staff_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Staff_ID)
)

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

CREATE TABLE Claim_Headers (
Claim_Header_ID INTEGER NOT NULL,
Claim_Status_Code CHAR(15) NOT NULL,
Claim_Type_Code CHAR(15) NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_of_Claim DATETIME,
Date_of_Settlement DATETIME,
Amount_Claimed DECIMAL(20,4),
Amount_Piad DECIMAL(20,4),
PRIMARY KEY (Claim_Header_ID),
FOREIGN KEY (Policy_ID) REFERENCES Policies (Policy_ID)
)

CREATE TABLE Claims_Documents (
Claim_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Created_by_Staff_ID INTEGER,
Created_Date INTEGER,
PRIMARY KEY (Claim_ID, Document_Type_Code),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Created_by_Staff_ID) REFERENCES Staff (Staff_ID)
)

CREATE TABLE Claims_Processing_Stages (
Claim_Stage_ID INTEGER NOT NULL,
Next_Claim_Stage_ID INTEGER,
Claim_Status_Name VARCHAR(255) NOT NULL,
Claim_Status_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Claim_Stage_ID)
)

CREATE TABLE Claims_Processing (
Claim_Processing_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Claim_Outcome_Code CHAR(15) NOT NULL,
Claim_Stage_ID INTEGER NOT NULL,
Staff_ID INTEGER,
PRIMARY KEY (Claim_Processing_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Staff_ID) REFERENCES Staff (Staff_ID)
)

### Question
What is the name of the customer who has made the minimum amount of payment in one claim?

### SQL
SELECT t3.customer_details FROM claim_headers AS t1 JOIN policies AS t2 ON t1.policy_id  =  t2.policy_id JOIN customers AS t3 ON t2.customer_id  =  t3.customer_id WHERE t1.amount_piad  =  (SELECT min(amount_piad) FROM claim_headers)
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Staff (
Staff_ID INTEGER NOT NULL,
Staff_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Staff_ID)
)

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

CREATE TABLE Claim_Headers (
Claim_Header_ID INTEGER NOT NULL,
Claim_Status_Code CHAR(15) NOT NULL,
Claim_Type_Code CHAR(15) NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_of_Claim DATETIME,
Date_of_Settlement DATETIME,
Amount_Claimed DECIMAL(20,4),
Amount_Piad DECIMAL(20,4),
PRIMARY KEY (Claim_Header_ID),
FOREIGN KEY (Policy_ID) REFERENCES Policies (Policy_ID)
)

CREATE TABLE Claims_Documents (
Claim_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Created_by_Staff_ID INTEGER,
Created_Date INTEGER,
PRIMARY KEY (Claim_ID, Document_Type_Code),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Created_by_Staff_ID) REFERENCES Staff (Staff_ID)
)

CREATE TABLE Claims_Processing_Stages (
Claim_Stage_ID INTEGER NOT NULL,
Next_Claim_Stage_ID INTEGER,
Claim_Status_Name VARCHAR(255) NOT NULL,
Claim_Status_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Claim_Stage_ID)
)

CREATE TABLE Claims_Processing (
Claim_Processing_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Claim_Outcome_Code CHAR(15) NOT NULL,
Claim_Stage_ID INTEGER NOT NULL,
Staff_ID INTEGER,
PRIMARY KEY (Claim_Processing_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Staff_ID) REFERENCES Staff (Staff_ID)
)

### Question
Which customer made the smallest amount of claim in one claim? Return the customer details.

### SQL
SELECT t3.customer_details FROM claim_headers AS t1 JOIN policies AS t2 ON t1.policy_id  =  t2.policy_id JOIN customers AS t3 ON t2.customer_id  =  t3.customer_id WHERE t1.amount_piad  =  (SELECT min(amount_piad) FROM claim_headers)
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Staff (
Staff_ID INTEGER NOT NULL,
Staff_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Staff_ID)
)

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

CREATE TABLE Claim_Headers (
Claim_Header_ID INTEGER NOT NULL,
Claim_Status_Code CHAR(15) NOT NULL,
Claim_Type_Code CHAR(15) NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_of_Claim DATETIME,
Date_of_Settlement DATETIME,
Amount_Claimed DECIMAL(20,4),
Amount_Piad DECIMAL(20,4),
PRIMARY KEY (Claim_Header_ID),
FOREIGN KEY (Policy_ID) REFERENCES Policies (Policy_ID)
)

CREATE TABLE Claims_Documents (
Claim_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Created_by_Staff_ID INTEGER,
Created_Date INTEGER,
PRIMARY KEY (Claim_ID, Document_Type_Code),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Created_by_Staff_ID) REFERENCES Staff (Staff_ID)
)

CREATE TABLE Claims_Processing_Stages (
Claim_Stage_ID INTEGER NOT NULL,
Next_Claim_Stage_ID INTEGER,
Claim_Status_Name VARCHAR(255) NOT NULL,
Claim_Status_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Claim_Stage_ID)
)

CREATE TABLE Claims_Processing (
Claim_Processing_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Claim_Outcome_Code CHAR(15) NOT NULL,
Claim_Stage_ID INTEGER NOT NULL,
Staff_ID INTEGER,
PRIMARY KEY (Claim_Processing_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Staff_ID) REFERENCES Staff (Staff_ID)
)

### Question
Find the names of customers who have no policies associated.

### SQL
SELECT customer_details FROM customers EXCEPT SELECT t2.customer_details FROM policies AS t1 JOIN customers 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 Staff (
Staff_ID INTEGER NOT NULL,
Staff_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Staff_ID)
)

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

CREATE TABLE Claim_Headers (
Claim_Header_ID INTEGER NOT NULL,
Claim_Status_Code CHAR(15) NOT NULL,
Claim_Type_Code CHAR(15) NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_of_Claim DATETIME,
Date_of_Settlement DATETIME,
Amount_Claimed DECIMAL(20,4),
Amount_Piad DECIMAL(20,4),
PRIMARY KEY (Claim_Header_ID),
FOREIGN KEY (Policy_ID) REFERENCES Policies (Policy_ID)
)

CREATE TABLE Claims_Documents (
Claim_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Created_by_Staff_ID INTEGER,
Created_Date INTEGER,
PRIMARY KEY (Claim_ID, Document_Type_Code),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Created_by_Staff_ID) REFERENCES Staff (Staff_ID)
)

CREATE TABLE Claims_Processing_Stages (
Claim_Stage_ID INTEGER NOT NULL,
Next_Claim_Stage_ID INTEGER,
Claim_Status_Name VARCHAR(255) NOT NULL,
Claim_Status_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Claim_Stage_ID)
)

CREATE TABLE Claims_Processing (
Claim_Processing_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Claim_Outcome_Code CHAR(15) NOT NULL,
Claim_Stage_ID INTEGER NOT NULL,
Staff_ID INTEGER,
PRIMARY KEY (Claim_Processing_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Staff_ID) REFERENCES Staff (Staff_ID)
)

### Question
What are the names of customers who do not have any policies?

### SQL
SELECT customer_details FROM customers EXCEPT SELECT t2.customer_details FROM policies AS t1 JOIN customers 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 Staff (
Staff_ID INTEGER NOT NULL,
Staff_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Staff_ID)
)

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

CREATE TABLE Claim_Headers (
Claim_Header_ID INTEGER NOT NULL,
Claim_Status_Code CHAR(15) NOT NULL,
Claim_Type_Code CHAR(15) NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_of_Claim DATETIME,
Date_of_Settlement DATETIME,
Amount_Claimed DECIMAL(20,4),
Amount_Piad DECIMAL(20,4),
PRIMARY KEY (Claim_Header_ID),
FOREIGN KEY (Policy_ID) REFERENCES Policies (Policy_ID)
)

CREATE TABLE Claims_Documents (
Claim_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Created_by_Staff_ID INTEGER,
Created_Date INTEGER,
PRIMARY KEY (Claim_ID, Document_Type_Code),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Created_by_Staff_ID) REFERENCES Staff (Staff_ID)
)

CREATE TABLE Claims_Processing_Stages (
Claim_Stage_ID INTEGER NOT NULL,
Next_Claim_Stage_ID INTEGER,
Claim_Status_Name VARCHAR(255) NOT NULL,
Claim_Status_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Claim_Stage_ID)
)

CREATE TABLE Claims_Processing (
Claim_Processing_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Claim_Outcome_Code CHAR(15) NOT NULL,
Claim_Stage_ID INTEGER NOT NULL,
Staff_ID INTEGER,
PRIMARY KEY (Claim_Processing_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Staff_ID) REFERENCES Staff (Staff_ID)
)

### Question
How many claim processing stages are there in total?

### SQL
SELECT count(*) FROM claims_processing_stages
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Staff (
Staff_ID INTEGER NOT NULL,
Staff_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Staff_ID)
)

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

CREATE TABLE Claim_Headers (
Claim_Header_ID INTEGER NOT NULL,
Claim_Status_Code CHAR(15) NOT NULL,
Claim_Type_Code CHAR(15) NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_of_Claim DATETIME,
Date_of_Settlement DATETIME,
Amount_Claimed DECIMAL(20,4),
Amount_Piad DECIMAL(20,4),
PRIMARY KEY (Claim_Header_ID),
FOREIGN KEY (Policy_ID) REFERENCES Policies (Policy_ID)
)

CREATE TABLE Claims_Documents (
Claim_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Created_by_Staff_ID INTEGER,
Created_Date INTEGER,
PRIMARY KEY (Claim_ID, Document_Type_Code),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Created_by_Staff_ID) REFERENCES Staff (Staff_ID)
)

CREATE TABLE Claims_Processing_Stages (
Claim_Stage_ID INTEGER NOT NULL,
Next_Claim_Stage_ID INTEGER,
Claim_Status_Name VARCHAR(255) NOT NULL,
Claim_Status_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Claim_Stage_ID)
)

CREATE TABLE Claims_Processing (
Claim_Processing_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Claim_Outcome_Code CHAR(15) NOT NULL,
Claim_Stage_ID INTEGER NOT NULL,
Staff_ID INTEGER,
PRIMARY KEY (Claim_Processing_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Staff_ID) REFERENCES Staff (Staff_ID)
)

### Question
Find the number of distinct stages in claim processing.

### SQL
SELECT count(*) FROM claims_processing_stages
### Database Schema
CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
)

CREATE TABLE Staff (
Staff_ID INTEGER NOT NULL,
Staff_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Staff_ID)
)

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

CREATE TABLE Claim_Headers (
Claim_Header_ID INTEGER NOT NULL,
Claim_Status_Code CHAR(15) NOT NULL,
Claim_Type_Code CHAR(15) NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_of_Claim DATETIME,
Date_of_Settlement DATETIME,
Amount_Claimed DECIMAL(20,4),
Amount_Piad DECIMAL(20,4),
PRIMARY KEY (Claim_Header_ID),
FOREIGN KEY (Policy_ID) REFERENCES Policies (Policy_ID)
)

CREATE TABLE Claims_Documents (
Claim_ID INTEGER NOT NULL,
Document_Type_Code CHAR(15) NOT NULL,
Created_by_Staff_ID INTEGER,
Created_Date INTEGER,
PRIMARY KEY (Claim_ID, Document_Type_Code),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Created_by_Staff_ID) REFERENCES Staff (Staff_ID)
)

CREATE TABLE Claims_Processing_Stages (
Claim_Stage_ID INTEGER NOT NULL,
Next_Claim_Stage_ID INTEGER,
Claim_Status_Name VARCHAR(255) NOT NULL,
Claim_Status_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Claim_Stage_ID)
)

CREATE TABLE Claims_Processing (
Claim_Processing_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Claim_Outcome_Code CHAR(15) NOT NULL,
Claim_Stage_ID INTEGER NOT NULL,
Staff_ID INTEGER,
PRIMARY KEY (Claim_Processing_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claim_Headers (Claim_Header_ID),
FOREIGN KEY (Staff_ID) REFERENCES Staff (Staff_ID)
)

### Question
What is the name of the claim processing stage that most of the claims are on?

### SQL
SELECT t2.claim_status_name FROM claims_processing AS t1 JOIN claims_processing_stages AS t2 ON t1.claim_stage_id  =  t2.claim_stage_id GROUP BY t1.claim_stage_id ORDER BY count(*) DESC LIMIT 1