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
Among all the claims, which settlements have a claimed amount that is no more than the average? List the claim start date.
assistant
SELECT Date_Claim_Made FROM Claims WHERE Amount_Settled <= ( SELECT avg(Amount_Settled) FROM Claims )
human
Which claims caused more than 2 settlements or have the maximum claim value? List the date the claim was made and the claim id.
assistant
SELECT T1.Date_Claim_Made , T1.Claim_id FROM Claims AS T1 JOIN Settlements AS T2 ON T1.Claim_id = T2.Claim_id GROUP BY T1.Claim_id HAVING count(*) > 2 UNION SELECT T1.Date_Claim_Made , T1.Claim_id FROM Claims AS T1 JOIN Settlements AS T2 ON T1.Claim_id = T2.Claim_id WHERE T1.Amount_Claimed = ( SELECT max(Amount_Claimed) FROM Claims )
human
Among all the claims, which claims have a claimed amount larger than the average? List the date the claim was made and the date it was settled.
assistant
SELECT Date_Claim_Made , Date_Claim_Settled FROM Claims WHERE Amount_Claimed > ( SELECT avg(Amount_Claimed) FROM Claims )
human
What are the claim dates and settlement dates of all the settlements?
assistant
SELECT Date_Claim_Made , Date_Claim_Settled FROM Settlements
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
Tell me the the date when the first claim was made.
assistant
SELECT Date_Claim_Made FROM Claims ORDER BY Date_Claim_Made ASC LIMIT 1 ;