Database: insurance_and_eClaims

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

Question: Sort the customer names in alphabetical order.
SQL: SELECT customer_details FROM customers ORDER BY customer_details

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"

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"

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

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

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

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

Question: Find the total and average amount paid in claim headers.
SQL: SELECT sum(amount_piad) ,  avg(amount_piad) FROM claim_headers

Question: What are the total amount and average amount paid in claim headers?
SQL: SELECT sum(amount_piad) ,  avg(amount_piad) FROM claim_headers

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)

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)

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)

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)

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)

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)

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

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

Question: How many claim processing stages are there in total?
SQL: SELECT count(*) FROM claims_processing_stages

Question: Find the number of distinct stages in claim processing.
SQL: SELECT count(*) FROM claims_processing_stages

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

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

Question: Find the names of customers whose name contains "Diana".
SQL: SELECT customer_details FROM customers WHERE customer_details LIKE "%Diana%"

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%"

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"

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"

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"

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"

Question: Find the names of all the customers and staff members.
SQL: SELECT customer_details FROM customers UNION SELECT staff_details FROM staff

Question: What are the names of the customers and staff members?
SQL: SELECT customer_details FROM customers UNION SELECT staff_details FROM staff

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

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

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

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

Question: What is the description of the claim status "Open"?
SQL: SELECT claim_status_description FROM claims_processing_stages WHERE claim_status_name  =  "Open"

Question: Find the description of the claim status "Open".
SQL: SELECT claim_status_description FROM claims_processing_stages WHERE claim_status_name  =  "Open"

Question: How many distinct claim outcome codes are there?
SQL: SELECT count(DISTINCT claim_outcome_code) FROM claims_processing

Question: Count the number of distinct claim outcome codes.
SQL: SELECT count(DISTINCT claim_outcome_code) FROM claims_processing

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)

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)