CREATE TABLE Customers (
    Customer_ID NUMBER PRIMARY KEY,
    Customer_Details TEXT
);
CREATE TABLE Staff (
    Staff_ID NUMBER PRIMARY KEY,
    Staff_Details TEXT
);
CREATE TABLE Policies (
    Policy_ID NUMBER PRIMARY KEY,
    Customer_ID NUMBER,
    Policy_Type_Code TEXT,
    Start_Date TIME,
    End_Date TIME,
    FOREIGN KEY ("Customer_ID") REFERENCES Customers("Customer_ID")
);
CREATE TABLE Claim_Headers (
    Claim_Header_ID NUMBER PRIMARY KEY,
    Claim_Status_Code TEXT,
    Claim_Type_Code TEXT,
    Policy_ID NUMBER,
    Date_of_Claim TIME,
    Date_of_Settlement TIME,
    Amount_Claimed NUMBER,
    Amount_Piad NUMBER,
    FOREIGN KEY ("Policy_ID") REFERENCES Policies("Policy_ID")
);
CREATE TABLE Claims_Documents (
    Claim_ID NUMBER PRIMARY KEY,
    Document_Type_Code TEXT,
    Created_by_Staff_ID NUMBER,
    Created_Date NUMBER,
    FOREIGN KEY ("Created_by_Staff_ID") REFERENCES Staff("Staff_ID"),
    FOREIGN KEY ("Claim_ID") REFERENCES Claim_Headers("Claim_Header_ID")
);
CREATE TABLE Claims_Processing_Stages (
    Claim_Stage_ID NUMBER PRIMARY KEY,
    Next_Claim_Stage_ID NUMBER,
    Claim_Status_Name TEXT,
    Claim_Status_Description TEXT
);
CREATE TABLE Claims_Processing (
    Claim_Processing_ID NUMBER PRIMARY KEY,
    Claim_ID NUMBER,
    Claim_Outcome_Code TEXT,
    Claim_Stage_ID NUMBER,
    Staff_ID NUMBER,
    FOREIGN KEY ("Staff_ID") REFERENCES Staff("Staff_ID"),
    FOREIGN KEY ("Claim_ID") REFERENCES Claim_Headers("Claim_Header_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"