human
Schema:
Customers (Customer_ID, Customer_Details)
Customer_Policies (Policy_ID, Customer_ID, Policy_Type_Code, Start_Date, End_Date)
Claims (Claim_ID, Policy_ID, Date_Claim_Made, Date_Claim_Settled, Amount_Claimed, Amount_Settled)
Settlements (Settlement_ID, Claim_ID, Date_Claim_Made, Date_Claim_Settled, Amount_Claimed, Amount_Settled, Customer_Policy_ID)
Payments (Payment_ID, Settlement_ID, Payment_Method_Code, Date_Payment_Made, Amount_Payment)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the total claimed amount of all the claims.
assistant
SELECT sum(Amount_Claimed) FROM Claims
human
List the date the claim was made, the date it was settled and the amount settled for all the claims which had exactly one settlement.
assistant
SELECT T1.claim_id , T1.date_claim_made , T1.Date_Claim_Settled FROM Claims AS T1 JOIN Settlements AS T2 ON T1.Claim_id = T2.Claim_id GROUP BY T1.claim_id HAVING count(*) = 1
human
List the details of the customers who do not have any policies.
assistant
SELECT customer_details FROM Customers EXCEPT SELECT T1.customer_details FROM Customers AS T1 JOIN Customer_Policies AS T2 ON T1.customer_id = T2.customer_id
human
Who are the customers that had more than 1 policy? List the customer details and id.
assistant
SELECT T1.customer_details , T1.customer_id FROM Customers AS T1 JOIN Customer_Policies AS T2 ON T1.Customer_id = T2.Customer_id GROUP BY T1.customer_id HAVING count(*) > 1
human
Which customer had at least 2 policies but did not file any claims? List the customer details and id.
assistant
SELECT T1.customer_details , T1.customer_id FROM Customers AS T1 JOIN Customer_Policies AS T2 ON T1.Customer_id = T2.customer_id GROUP BY T1.customer_id HAVING count(*) >= 2 EXCEPT SELECT T1.customer_details , T1.customer_id FROM Customers AS T1 JOIN Customer_Policies AS T2 ON T1.Customer_id = T2.customer_id JOIN Claims AS T3 ON T2.policy_id = T3.policy_id
human
Return the distinct customer details.
assistant
SELECT DISTINCT customer_details FROM Customers ;