Database: insurance_policies

CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_Details VARCHAR(255) NOT NULL,
PRIMARY KEY (Customer_ID)
);
CREATE TABLE Customer_Policies (
Policy_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_Type_Code CHAR(15) NOT NULL,
Start_Date DATE,
End_Date DATE,
PRIMARY KEY (Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID)
);
CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
PRIMARY KEY (Claim_ID),
FOREIGN KEY (Policy_ID) REFERENCES Customer_Policies (Policy_ID)
);
CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER NOT NULL,
Date_Claim_Made DATE,
Date_Claim_Settled DATE,
Amount_Claimed INTEGER,
Amount_Settled INTEGER,
Customer_Policy_ID INTEGER NOT NULL,
PRIMARY KEY (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
);
CREATE TABLE Payments (
Payment_ID INTEGER NOT NULL,
Settlement_ID INTEGER NOT NULL,
Payment_Method_Code VARCHAR(255),
Date_Payment_Made DATE,
Amount_Payment INTEGER,
PRIMARY KEY (Payment_ID),
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

Question: Who are the customers that had more than 1 policy? 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(*)  >  1

Question: Find the the customer details and id for the customers who had more than one policy.
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(*)  >  1

Question: What are the claim dates and settlement dates of all the settlements?
SQL: SELECT Date_Claim_Made ,  Date_Claim_Settled FROM Settlements

Question: Tell me the the claim date and settlement date for each settlement case.
SQL: SELECT Date_Claim_Made ,  Date_Claim_Settled FROM Settlements

Question: What is the most popular payment method?
SQL: SELECT Payment_Method_Code FROM Payments GROUP BY Payment_Method_Code ORDER BY count(*) DESC LIMIT 1

Question: Which payment method is used the most often?
SQL: SELECT Payment_Method_Code FROM Payments GROUP BY Payment_Method_Code ORDER BY count(*) DESC LIMIT 1

Question: With which kind of payment method were the least number of payments processed?
SQL: SELECT Payment_Method_Code FROM Payments GROUP BY Payment_Method_Code ORDER BY count(*) ASC LIMIT 1

Question: What is the payment method that were used the least often?
SQL: SELECT Payment_Method_Code FROM Payments GROUP BY Payment_Method_Code ORDER BY count(*) ASC LIMIT 1

Question: What is the total amount of payment?
SQL: SELECT sum(Amount_Payment) FROM Payments

Question: Compute the total amount of payment processed.
SQL: SELECT sum(Amount_Payment) FROM Payments

Question: What are all the distinct details of the customers?
SQL: SELECT DISTINCT customer_details FROM Customers

Question: Return the distinct customer details.
SQL: SELECT DISTINCT customer_details FROM Customers

Question: Which kind of policy type was chosen by the most customers?
SQL: SELECT Policy_Type_Code FROM Customer_Policies GROUP BY Policy_Type_Code ORDER BY count(*) DESC LIMIT 1

Question: Find the policy type the most customers choose.
SQL: SELECT Policy_Type_Code FROM Customer_Policies GROUP BY Policy_Type_Code ORDER BY count(*) DESC LIMIT 1

Question: How many settlements are there in total?
SQL: SELECT count(*) FROM Settlements

Question: Count the total number of settlements made.
SQL: SELECT count(*) FROM Settlements

Question: Which Payments were processed with Visa? List the payment Id, the date and the amount.
SQL: SELECT Payment_ID ,  Date_Payment_Made ,  Amount_Payment FROM Payments WHERE Payment_Method_Code  =  'Visa'

Question: Give me the payment Id, the date and the amount for all the payments processed with Visa.
SQL: SELECT Payment_ID ,  Date_Payment_Made ,  Amount_Payment FROM Payments WHERE Payment_Method_Code  =  'Visa'

Question: List the details of the customers who do not have any policies.
SQL: 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

Question: Which customers do not have any policies? Find the details of these customers.
SQL: 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

Question: 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.
SQL: 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

Question: Which claims had exactly one settlement? For each, tell me the the date the claim was made, the date it was settled and the amount settled.
SQL: 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

Question: Find the total claimed amount of all the claims.
SQL: SELECT sum(Amount_Claimed) FROM Claims

Question: What is total amount claimed summed across all the claims?
SQL: SELECT sum(Amount_Claimed) FROM Claims