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 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 claim processing stage has the most claims? Show the claim status name.

### 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
### 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 whose name contains "Diana".

### SQL
SELECT customer_details FROM customers WHERE customer_details LIKE "%Diana%"
### 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 customers have the substring "Diana" in their names? Return the customer details.

### SQL
SELECT customer_details FROM customers WHERE customer_details LIKE "%Diana%"
### 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 the customers who have an deputy policy.

### SQL
SELECT DISTINCT t2.customer_details FROM policies AS t1 JOIN customers AS t2 ON t1.customer_id  =  t2.customer_id WHERE t1.policy_type_code  =  "Deputy"
### 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 customers have an insurance policy with the type code "Deputy"? Give me the customer details.

### SQL
SELECT DISTINCT t2.customer_details FROM policies AS t1 JOIN customers AS t2 ON t1.customer_id  =  t2.customer_id WHERE t1.policy_type_code  =  "Deputy"
### 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 either have an deputy policy or uniformed policy.

### SQL
SELECT DISTINCT t2.customer_details FROM policies AS t1 JOIN customers AS t2 ON t1.customer_id  =  t2.customer_id WHERE t1.policy_type_code  =  "Deputy" OR t1.policy_type_code  =  "Uniform"
### 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 customers have an insurance policy with the type code "Deputy" or "Uniform"? Return the customer details.

### SQL
SELECT DISTINCT t2.customer_details FROM policies AS t1 JOIN customers AS t2 ON t1.customer_id  =  t2.customer_id WHERE t1.policy_type_code  =  "Deputy" OR t1.policy_type_code  =  "Uniform"
### 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 all the customers and staff members.

### SQL
SELECT customer_details FROM customers UNION SELECT staff_details FROM staff
### 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 the customers and staff members?

### SQL
SELECT customer_details FROM customers UNION SELECT staff_details FROM staff
### 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 records of each policy type and its type code.

### SQL
SELECT policy_type_code ,  count(*) FROM policies GROUP BY policy_type_code
### 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
For each policy type, return its type code and its count in the record.

### SQL
SELECT policy_type_code ,  count(*) FROM policies GROUP BY policy_type_code
### 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 name of the customer that has been involved in the most policies.

### SQL
SELECT t2.customer_details FROM policies AS t1 JOIN customers AS t2 ON t1.customer_id  =  t2.customer_id GROUP BY t2.customer_details 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
Which customer have the most policies? Give me the customer details.

### SQL
SELECT t2.customer_details FROM policies AS t1 JOIN customers AS t2 ON t1.customer_id  =  t2.customer_id GROUP BY t2.customer_details 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
What is the description of the claim status "Open"?

### SQL
SELECT claim_status_description FROM claims_processing_stages WHERE claim_status_name  =  "Open"
### 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 description of the claim status "Open".

### SQL
SELECT claim_status_description FROM claims_processing_stages WHERE claim_status_name  =  "Open"
### 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 distinct claim outcome codes are there?

### SQL
SELECT count(DISTINCT claim_outcome_code) FROM claims_processing
### 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
Count the number of distinct claim outcome codes.

### SQL
SELECT count(DISTINCT claim_outcome_code) FROM claims_processing
### 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 is associated with the latest policy?

### SQL
SELECT t2.customer_details FROM policies AS t1 JOIN customers AS t2 ON t1.customer_id  =  t2.customer_id WHERE t1.start_date  =  (SELECT max(start_date) FROM policies)
### 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 customer who started a policy most recently.

### SQL
SELECT t2.customer_details FROM policies AS t1 JOIN customers AS t2 ON t1.customer_id  =  t2.customer_id WHERE t1.start_date  =  (SELECT max(start_date) FROM policies)
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(50),
`customer_middle_initial` VARCHAR(1),
`customer_last_name` VARCHAR(50),
`gender` VARCHAR(1),
`email_address` VARCHAR(255),
`login_name` VARCHAR(80),
`login_password` VARCHAR(20),
`phone_number` VARCHAR(255),
`town_city` VARCHAR(50),
`state_county_province` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_date` DATETIME,
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_account_opened` DATETIME,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Product_Categories` (
`production_type_code` VARCHAR(15) PRIMARY KEY,
`product_type_description` VARCHAR(80),
`vat_rating` DECIMAL(19,4)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`production_type_code` VARCHAR(15) NOT NULL,
`unit_price` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_color` VARCHAR(20),
`product_size` VARCHAR(20),
FOREIGN KEY (`production_type_code` ) REFERENCES `Product_Categories`(`production_type_code` )
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`account_id` INTEGER NOT NULL,
`invoice_number` INTEGER,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DECIMAL(19,4),
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_quantity` VARCHAR(50),
`other_order_item_details` VARCHAR(255),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Invoice_Line_Items` (
`order_item_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_title` VARCHAR(80),
`product_quantity` VARCHAR(50),
`product_price` DECIMAL(19,4),
`derived_product_cost` DECIMAL(19,4),
`derived_vat_payable` DECIMAL(19,4),
`derived_total_cost` DECIMAL(19,4),
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
Show the number of accounts.

### SQL
SELECT count(*) FROM Accounts
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(50),
`customer_middle_initial` VARCHAR(1),
`customer_last_name` VARCHAR(50),
`gender` VARCHAR(1),
`email_address` VARCHAR(255),
`login_name` VARCHAR(80),
`login_password` VARCHAR(20),
`phone_number` VARCHAR(255),
`town_city` VARCHAR(50),
`state_county_province` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_date` DATETIME,
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_account_opened` DATETIME,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Product_Categories` (
`production_type_code` VARCHAR(15) PRIMARY KEY,
`product_type_description` VARCHAR(80),
`vat_rating` DECIMAL(19,4)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`production_type_code` VARCHAR(15) NOT NULL,
`unit_price` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_color` VARCHAR(20),
`product_size` VARCHAR(20),
FOREIGN KEY (`production_type_code` ) REFERENCES `Product_Categories`(`production_type_code` )
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`account_id` INTEGER NOT NULL,
`invoice_number` INTEGER,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DECIMAL(19,4),
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_quantity` VARCHAR(50),
`other_order_item_details` VARCHAR(255),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Invoice_Line_Items` (
`order_item_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_title` VARCHAR(80),
`product_quantity` VARCHAR(50),
`product_price` DECIMAL(19,4),
`derived_product_cost` DECIMAL(19,4),
`derived_vat_payable` DECIMAL(19,4),
`derived_total_cost` DECIMAL(19,4),
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
How many accounts are there?

### SQL
SELECT count(*) FROM Accounts
### Database Schema
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_first_name` VARCHAR(50),
`customer_middle_initial` VARCHAR(1),
`customer_last_name` VARCHAR(50),
`gender` VARCHAR(1),
`email_address` VARCHAR(255),
`login_name` VARCHAR(80),
`login_password` VARCHAR(20),
`phone_number` VARCHAR(255),
`town_city` VARCHAR(50),
`state_county_province` VARCHAR(50),
`country` VARCHAR(50)
)

CREATE TABLE `Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_order_placed` DATETIME NOT NULL,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Invoices` (
`invoice_number` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`invoice_date` DATETIME,
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Accounts` (
`account_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`date_account_opened` DATETIME,
`account_name` VARCHAR(50),
`other_account_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Product_Categories` (
`production_type_code` VARCHAR(15) PRIMARY KEY,
`product_type_description` VARCHAR(80),
`vat_rating` DECIMAL(19,4)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`production_type_code` VARCHAR(15) NOT NULL,
`unit_price` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_color` VARCHAR(20),
`product_size` VARCHAR(20),
FOREIGN KEY (`production_type_code` ) REFERENCES `Product_Categories`(`production_type_code` )
)

CREATE TABLE `Financial_Transactions` (
`transaction_id` INTEGER NOT NULL ,
`account_id` INTEGER NOT NULL,
`invoice_number` INTEGER,
`transaction_type` VARCHAR(15) NOT NULL,
`transaction_date` DATETIME,
`transaction_amount` DECIMAL(19,4),
`transaction_comment` VARCHAR(255),
`other_transaction_details` VARCHAR(255),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`account_id` ) REFERENCES `Accounts`(`account_id` )
)

CREATE TABLE `Order_Items` (
`order_item_id` INTEGER PRIMARY KEY,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_quantity` VARCHAR(50),
`other_order_item_details` VARCHAR(255),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Orders`(`order_id` )
)

CREATE TABLE `Invoice_Line_Items` (
`order_item_id` INTEGER NOT NULL,
`invoice_number` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`product_title` VARCHAR(80),
`product_quantity` VARCHAR(50),
`product_price` DECIMAL(19,4),
`derived_product_cost` DECIMAL(19,4),
`derived_vat_payable` DECIMAL(19,4),
`derived_total_cost` DECIMAL(19,4),
FOREIGN KEY (`order_item_id` ) REFERENCES `Order_Items`(`order_item_id` ),
FOREIGN KEY (`invoice_number` ) REFERENCES `Invoices`(`invoice_number` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
How many customers have opened an account?

### SQL
SELECT count(DISTINCT customer_id) FROM Accounts