Database: insurance_fnol

CREATE TABLE Customers (
Customer_ID INTEGER NOT NULL,
Customer_name VARCHAR(40),
PRIMARY KEY (Customer_ID)
);
CREATE TABLE Services (
Service_ID INTEGER NOT NULL,
Service_name VARCHAR(40),
PRIMARY KEY (Service_ID)
);
CREATE TABLE Available_Policies (
Policy_ID INTEGER NOT NULL,
policy_type_code CHAR(15),
Customer_Phone VARCHAR(255),
PRIMARY KEY (Policy_ID),
UNIQUE (Policy_ID)
);
CREATE TABLE Customers_Policies (
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Date_Opened DATE,
Date_Closed DATE,
PRIMARY KEY (Customer_ID, Policy_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID),
FOREIGN KEY (Policy_ID) REFERENCES Available_Policies (Policy_ID)
);
CREATE TABLE First_Notification_of_Loss (
FNOL_ID INTEGER NOT NULL,
Customer_ID INTEGER NOT NULL,
Policy_ID INTEGER NOT NULL,
Service_ID INTEGER NOT NULL,
PRIMARY KEY (FNOL_ID),
UNIQUE (FNOL_ID),
FOREIGN KEY (Service_ID) REFERENCES Services (Service_ID),
FOREIGN KEY (Customer_ID, Policy_ID) REFERENCES Customers_Policies (Customer_ID,Policy_ID)
);
CREATE TABLE Claims (
Claim_ID INTEGER NOT NULL,
FNOL_ID INTEGER NOT NULL,
Effective_Date DATE,
PRIMARY KEY (Claim_ID),
UNIQUE (Claim_ID),
FOREIGN KEY (FNOL_ID) REFERENCES First_Notification_of_Loss (FNOL_ID)
);
CREATE TABLE Settlements (
Settlement_ID INTEGER NOT NULL,
Claim_ID INTEGER,
Effective_Date DATE,
Settlement_Amount REAL,
PRIMARY KEY (Settlement_ID),
UNIQUE (Settlement_ID),
FOREIGN KEY (Claim_ID) REFERENCES Claims (Claim_ID)
);

Question: Find all the phone numbers.
SQL: SELECT customer_phone FROM available_policies

Question: What are all the phone numbers?
SQL: SELECT customer_phone FROM available_policies

Question: What are the customer phone numbers under the policy "Life Insurance"?
SQL: SELECT customer_phone FROM available_policies WHERE policy_type_code  =  "Life Insurance"

Question: What are the phone numbers of customers using the policy with the code "Life Insurance"?
SQL: SELECT customer_phone FROM available_policies WHERE policy_type_code  =  "Life Insurance"

Question: Which policy type has the most records in the database?
SQL: SELECT policy_type_code FROM available_policies GROUP BY policy_type_code ORDER BY count(*) DESC LIMIT 1

Question: Which policy type appears most frequently in the available policies?
SQL: SELECT policy_type_code FROM available_policies GROUP BY policy_type_code ORDER BY count(*) DESC LIMIT 1

Question: What are all the customer phone numbers under the most popular policy type?
SQL: SELECT customer_phone FROM available_policies WHERE policy_type_code  =  (SELECT policy_type_code FROM available_policies GROUP BY policy_type_code ORDER BY count(*) DESC LIMIT 1)

Question: Find the phone numbers of customers using the most common policy type among the available policies.
SQL: SELECT customer_phone FROM available_policies WHERE policy_type_code  =  (SELECT policy_type_code FROM available_policies GROUP BY policy_type_code ORDER BY count(*) DESC LIMIT 1)

Question: Find the policy type used by more than 4 customers.
SQL: SELECT policy_type_code FROM available_policies GROUP BY policy_type_code HAVING count(*)  >  4

Question: Find the policy types more than 4 customers use. Show their type code.
SQL: SELECT policy_type_code FROM available_policies GROUP BY policy_type_code HAVING count(*)  >  4

Question: Find the total and average amount of settlements.
SQL: SELECT sum(settlement_amount) ,  avg(settlement_amount) FROM settlements

Question: Return the sum and average of all settlement amounts.
SQL: SELECT sum(settlement_amount) ,  avg(settlement_amount) FROM settlements

Question: Find the name of services that have been used for more than 2 times in first notification of loss.
SQL: SELECT t2.service_name FROM first_notification_of_loss AS t1 JOIN services AS t2 ON t1.service_id  =  t2.service_id GROUP BY t1.service_id HAVING count(*)  >  2

Question: Which services have been used more than twice in first notification of loss? Return the service name.
SQL: SELECT t2.service_name FROM first_notification_of_loss AS t1 JOIN services AS t2 ON t1.service_id  =  t2.service_id GROUP BY t1.service_id HAVING count(*)  >  2

Question: What is the effective date of the claim that has the largest amount of total settlement?
SQL: SELECT t1.Effective_Date FROM claims AS t1 JOIN settlements AS t2 ON t1.claim_id  =  t2.claim_id GROUP BY t1.claim_id ORDER BY sum(t2.settlement_amount) DESC LIMIT 1

Question: Find the claim that has the largest total settlement amount. Return the effective date of the claim.
SQL: SELECT t1.Effective_Date FROM claims AS t1 JOIN settlements AS t2 ON t1.claim_id  =  t2.claim_id GROUP BY t1.claim_id ORDER BY sum(t2.settlement_amount) DESC LIMIT 1

Question: How many policies are listed for the customer named "Dayana Robel"?
SQL: SELECT count(*) FROM customers AS t1 JOIN customers_policies AS t2 ON t1.customer_id  =  t2.customer_id WHERE t1.customer_name  =  "Dayana Robel"

Question: Count the total number of policies used by the customer named "Dayana Robel".
SQL: SELECT count(*) FROM customers AS t1 JOIN customers_policies AS t2 ON t1.customer_id  =  t2.customer_id WHERE t1.customer_name  =  "Dayana Robel"

Question: What is the name of the customer who has the most policies listed?
SQL: SELECT t1.customer_name FROM customers AS t1 JOIN customers_policies AS t2 ON t1.customer_id  =  t2.customer_id GROUP BY t1.customer_name ORDER BY count(*) DESC LIMIT 1

Question: Which customer uses the most policies? Give me the customer name.
SQL: SELECT t1.customer_name FROM customers AS t1 JOIN customers_policies AS t2 ON t1.customer_id  =  t2.customer_id GROUP BY t1.customer_name ORDER BY count(*) DESC LIMIT 1

Question: What are all the policy types of the customer named "Dayana Robel"?
SQL: SELECT DISTINCT t3.policy_type_code FROM customers AS t1 JOIN customers_policies AS t2 ON t1.customer_id  =  t2.customer_id JOIN available_policies AS t3 ON t2.policy_id  =  t3.policy_id WHERE t1.customer_name  =  "Dayana Robel"

Question: Tell me the types of the policy used by the customer named "Dayana Robel".
SQL: SELECT DISTINCT t3.policy_type_code FROM customers AS t1 JOIN customers_policies AS t2 ON t1.customer_id  =  t2.customer_id JOIN available_policies AS t3 ON t2.policy_id  =  t3.policy_id WHERE t1.customer_name  =  "Dayana Robel"

Question: What are all the policy types of the customer that has the most policies listed?
SQL: SELECT DISTINCT t3.policy_type_code FROM customers AS t1 JOIN customers_policies AS t2 ON t1.customer_id  =  t2.customer_id JOIN available_policies AS t3 ON t2.policy_id  =  t3.policy_id WHERE t1.customer_name  =  (SELECT t1.customer_name FROM customers AS t1 JOIN customers_policies AS t2 ON t1.customer_id  =  t2.customer_id GROUP BY t1.customer_name ORDER BY count(*) DESC LIMIT 1)

Question: List all the policy types used by the customer enrolled in the most policies.
SQL: SELECT DISTINCT t3.policy_type_code FROM customers AS t1 JOIN customers_policies AS t2 ON t1.customer_id  =  t2.customer_id JOIN available_policies AS t3 ON t2.policy_id  =  t3.policy_id WHERE t1.customer_name  =  (SELECT t1.customer_name FROM customers AS t1 JOIN customers_policies AS t2 ON t1.customer_id  =  t2.customer_id GROUP BY t1.customer_name ORDER BY count(*) DESC LIMIT 1)

Question: List all the services in the alphabetical order.
SQL: SELECT service_name FROM services ORDER BY service_name

Question: Give me a list of all the service names sorted alphabetically.
SQL: SELECT service_name FROM services ORDER BY service_name

Question: How many services are there?
SQL: SELECT count(*) FROM services

Question: Count the total number of available services.
SQL: SELECT count(*) FROM services

Question: Find the names of users who do not have a first notification of loss record.
SQL: SELECT customer_name FROM customers EXCEPT SELECT t1.customer_name FROM customers AS t1 JOIN first_notification_of_loss AS t2 ON t1.customer_id  =  t2.customer_id

Question: Which customers do not have a first notification of loss record? Give me the customer names.
SQL: SELECT customer_name FROM customers EXCEPT SELECT t1.customer_name FROM customers AS t1 JOIN first_notification_of_loss AS t2 ON t1.customer_id  =  t2.customer_id

Question: Find the names of customers who have used either the service "Close a policy" or the service "Upgrade a policy".
SQL: SELECT t1.customer_name FROM customers AS t1 JOIN first_notification_of_loss AS t2 ON t1.customer_id  =  t2.customer_id JOIN services AS t3 ON t2.service_id  =  t3.service_id WHERE t3.service_name  =  "Close a policy" OR t3.service_name  =  "Upgrade a policy"

Question: Which customers have used the service named "Close a policy" or "Upgrade a policy"? Give me the customer names.
SQL: SELECT t1.customer_name FROM customers AS t1 JOIN first_notification_of_loss AS t2 ON t1.customer_id  =  t2.customer_id JOIN services AS t3 ON t2.service_id  =  t3.service_id WHERE t3.service_name  =  "Close a policy" OR t3.service_name  =  "Upgrade a policy"

Question: Find the names of customers who have used both the service "Close a policy" and the service "New policy application".
SQL: SELECT t1.customer_name FROM customers AS t1 JOIN first_notification_of_loss AS t2 ON t1.customer_id  =  t2.customer_id JOIN services AS t3 ON t2.service_id  =  t3.service_id WHERE t3.service_name  =  "Close a policy" INTERSECT SELECT t1.customer_name FROM customers AS t1 JOIN first_notification_of_loss AS t2 ON t1.customer_id  =  t2.customer_id JOIN services AS t3 ON t2.service_id  =  t3.service_id WHERE t3.service_name  =  "New policy application"

Question: Which customers have used both the service named "Close a policy" and the service named "Upgrade a policy"? Give me the customer names.
SQL: SELECT t1.customer_name FROM customers AS t1 JOIN first_notification_of_loss AS t2 ON t1.customer_id  =  t2.customer_id JOIN services AS t3 ON t2.service_id  =  t3.service_id WHERE t3.service_name  =  "Close a policy" INTERSECT SELECT t1.customer_name FROM customers AS t1 JOIN first_notification_of_loss AS t2 ON t1.customer_id  =  t2.customer_id JOIN services AS t3 ON t2.service_id  =  t3.service_id WHERE t3.service_name  =  "New policy application"

Question: Find the IDs of customers whose name contains "Diana".
SQL: SELECT customer_id FROM customers WHERE customer_name LIKE "%Diana%"

Question: What are the IDs of customers who have "Diana" in part of their names?
SQL: SELECT customer_id FROM customers WHERE customer_name LIKE "%Diana%"

Question: What are the maximum and minimum settlement amount on record?
SQL: SELECT max(settlement_amount) ,  min(settlement_amount) FROM settlements

Question: Find the maximum and minimum settlement amount.
SQL: SELECT max(settlement_amount) ,  min(settlement_amount) FROM settlements

Question: List all the customers in increasing order of IDs.
SQL: SELECT customer_id ,  customer_name FROM customers ORDER BY customer_id ASC

Question: What is the ordered list of customer ids?
SQL: SELECT customer_id ,  customer_name FROM customers ORDER BY customer_id ASC

Question: Retrieve the open and close dates of all the policies associated with the customer whose name contains "Diana"
SQL: SELECT t2.date_opened ,  t2.date_closed FROM customers AS t1 JOIN customers_policies AS t2 ON t1.customer_id  =  t2.customer_id WHERE t1.customer_name LIKE "%Diana%"

Question: What are the open and close dates of all the policies used by the customer who have "Diana" in part of their names?
SQL: SELECT t2.date_opened ,  t2.date_closed FROM customers AS t1 JOIN customers_policies AS t2 ON t1.customer_id  =  t2.customer_id WHERE t1.customer_name LIKE "%Diana%"