CREATE TABLE Customers (
    Customer_ID NUMBER PRIMARY KEY,
    Customer_Details TEXT
);
CREATE TABLE Customer_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 Claims (
    Claim_ID NUMBER PRIMARY KEY,
    Policy_ID NUMBER,
    Date_Claim_Made TIME,
    Date_Claim_Settled TIME,
    Amount_Claimed NUMBER,
    Amount_Settled NUMBER,
    FOREIGN KEY ("Policy_ID") REFERENCES Customer_Policies("Policy_ID")
);
CREATE TABLE Settlements (
    Settlement_ID NUMBER PRIMARY KEY,
    Claim_ID NUMBER,
    Date_Claim_Made TIME,
    Date_Claim_Settled TIME,
    Amount_Claimed NUMBER,
    Amount_Settled NUMBER,
    Customer_Policy_ID NUMBER,
    FOREIGN KEY ("Claim_ID") REFERENCES Claims("Claim_ID")
);
CREATE TABLE Payments (
    Payment_ID NUMBER PRIMARY KEY,
    Settlement_ID NUMBER,
    Payment_Method_Code TEXT,
    Date_Payment_Made TIME,
    Amount_Payment NUMBER,
    FOREIGN KEY ("Settlement_ID") REFERENCES Settlements("Settlement_ID")
);

Question: Which claims caused more than 2 settlements or have the maximum claim value? List the date the claim was made and the claim id.
SQL: 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 )

Question: Find the claims that led to more than two settlements or have the maximum claim value. For each of them, return the date the claim was made and the id of the claim.
SQL: 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 )

Question: Which customer had at least 2 policies but did not file any claims? List the customer details and id.
SQL: 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

Question: Give me the the customer details and id for the customers who had two or more policies but did not file any claims.
SQL: 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

Question: List the method, date and amount of all the payments, in ascending order of date.
SQL: SELECT Payment_Method_Code ,  Date_Payment_Made ,  Amount_Payment FROM Payments ORDER BY Date_Payment_Made ASC

Question: What are the method, date and amount of each payment? Sort the list in ascending order of date.
SQL: SELECT Payment_Method_Code ,  Date_Payment_Made ,  Amount_Payment FROM Payments ORDER BY Date_Payment_Made ASC

Question: Among all the claims, what is the settlement amount of the claim with the largest claim amount? List both the settlement amount and claim amount.
SQL: SELECT Amount_Settled ,  Amount_Claimed FROM Claims ORDER BY Amount_Claimed DESC LIMIT 1

Question: Find the settlement amount of the claim with the largest claim amount. Show both the settlement amount and claim amount.
SQL: SELECT Amount_Settled ,  Amount_Claimed FROM Claims ORDER BY Amount_Claimed DESC LIMIT 1

Question: Among all the claims, what is the amount claimed in the claim with the least amount settled? List both the settlement amount and claim amount.
SQL: SELECT Amount_Settled ,  Amount_Claimed FROM Claims ORDER BY Amount_Settled ASC LIMIT 1

Question: Find the claimed amount in the claim with the least amount settled. Show both the settlement amount and claim amount.
SQL: SELECT Amount_Settled ,  Amount_Claimed FROM Claims ORDER BY Amount_Settled ASC LIMIT 1

Question: 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.
SQL: SELECT Date_Claim_Made ,  Date_Claim_Settled FROM Claims WHERE Amount_Claimed  >  ( SELECT avg(Amount_Claimed) FROM Claims )

Question: Give me the claim date, settlement date for all the claims whose claimed amount is larger than the average.
SQL: SELECT Date_Claim_Made ,  Date_Claim_Settled FROM Claims WHERE Amount_Claimed  >  ( SELECT avg(Amount_Claimed) FROM Claims )

Question: Among all the claims, which settlements have a claimed amount that is no more than the average? List the claim start date.
SQL: SELECT Date_Claim_Made FROM Claims WHERE Amount_Settled  <=  ( SELECT avg(Amount_Settled) FROM Claims )

Question: Return the claim start date for the claims whose claimed amount is no more than the average
SQL: SELECT Date_Claim_Made FROM Claims WHERE Amount_Settled  <=  ( SELECT avg(Amount_Settled) FROM Claims )

Question: How many settlements does each claim correspond to? List the claim id and the number of settlements.
SQL: SELECT T1.Claim_id ,  count(*) FROM Claims AS T1 JOIN Settlements AS T2 ON T1.claim_id  =  T2.claim_id GROUP BY T1.claim_id

Question: Find the number of settlements each claim corresponds to. Show the number together with the claim id.
SQL: SELECT T1.Claim_id ,  count(*) FROM Claims AS T1 JOIN Settlements AS T2 ON T1.claim_id  =  T2.claim_id GROUP BY T1.claim_id

Question: Which claim incurred the most number of settlements? List the claim id, the date the claim was made, and the number.
SQL: SELECT T1.claim_id ,  T1.date_claim_made ,  count(*) FROM Claims AS T1 JOIN Settlements AS T2 ON T1.claim_id  =  T2.claim_id GROUP BY T1.claim_id ORDER BY count(*) DESC LIMIT 1

Question: Find the claim id and claim date of the claim that incurred the most settlement count. Also tell me the count.
SQL: SELECT T1.claim_id ,  T1.date_claim_made ,  count(*) FROM Claims AS T1 JOIN Settlements AS T2 ON T1.claim_id  =  T2.claim_id GROUP BY T1.claim_id ORDER BY count(*) DESC LIMIT 1

Question: How many settlements were made on the claim with the most recent claim settlement date? List the number and the claim id.
SQL: SELECT count(*) ,  T1.claim_id FROM Claims AS T1 JOIN Settlements AS T2 ON T1.claim_id  =  T2.claim_id GROUP BY T1.claim_id ORDER BY T1.Date_Claim_Settled DESC LIMIT 1

Question: Find the claim id and the number of settlements made for the claim with the most recent settlement date.
SQL: SELECT count(*) ,  T1.claim_id FROM Claims AS T1 JOIN Settlements AS T2 ON T1.claim_id  =  T2.claim_id GROUP BY T1.claim_id ORDER BY T1.Date_Claim_Settled DESC LIMIT 1

Question: Of all the claims, what was the earliest date when any claim was made?
SQL: SELECT Date_Claim_Made FROM Claims ORDER BY Date_Claim_Made ASC LIMIT 1

Question: Tell me the the date when the first claim was made.
SQL: SELECT Date_Claim_Made FROM Claims ORDER BY Date_Claim_Made ASC LIMIT 1

Question: What is the total amount of settlement made for all the settlements?
SQL: SELECT sum(Amount_Settled) FROM Settlements

Question: Compute the total amount of settlement across all the settlements.
SQL: SELECT sum(Amount_Settled) FROM Settlements