Database: insurance_and_eClaims
Schema: Customers: Customer_ID (number), Customer_Details (text) | Staff: Staff_ID (number), Staff_Details (text) | Policies: Policy_ID (number), Customer_ID (number), Policy_Type_Code (text), Start_Date (time), End_Date (time) | Claim_Headers: Claim_Header_ID (number), 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) | Claims_Documents: Claim_ID (number), Document_Type_Code (text), Created_by_Staff_ID (number), Created_Date (number) | Claims_Processing_Stages: Claim_Stage_ID (number), Next_Claim_Stage_ID (number), Claim_Status_Name (text), Claim_Status_Description (text) | Claims_Processing: Claim_Processing_ID (number), Claim_ID (number), Claim_Outcome_Code (text), Claim_Stage_ID (number), Staff_ID (number)

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)