CREATE TABLE Customers (
    Customer_ID NUMBER PRIMARY KEY,
    Customer_name TEXT
);
CREATE TABLE Services (
    Service_ID NUMBER PRIMARY KEY,
    Service_name TEXT
);
CREATE TABLE Available_Policies (
    Policy_ID NUMBER PRIMARY KEY,
    policy_type_code TEXT,
    Customer_Phone TEXT
);
CREATE TABLE Customers_Policies (
    Customer_ID NUMBER PRIMARY KEY,
    Policy_ID NUMBER,
    Date_Opened TIME,
    Date_Closed TIME,
    FOREIGN KEY ("Policy_ID") REFERENCES Available_Policies("Policy_ID"),
    FOREIGN KEY ("Customer_ID") REFERENCES Customers("Customer_ID")
);
CREATE TABLE First_Notification_of_Loss (
    FNOL_ID NUMBER PRIMARY KEY,
    Customer_ID NUMBER,
    Policy_ID NUMBER,
    Service_ID NUMBER,
    FOREIGN KEY ("Customer_ID") REFERENCES Customers_Policies("Customer_ID"),
    FOREIGN KEY ("Policy_ID") REFERENCES Customers_Policies("Policy_ID"),
    FOREIGN KEY ("Service_ID") REFERENCES Services("Service_ID")
);
CREATE TABLE Claims (
    Claim_ID NUMBER PRIMARY KEY,
    FNOL_ID NUMBER,
    Effective_Date TIME,
    FOREIGN KEY ("FNOL_ID") REFERENCES First_Notification_of_Loss("FNOL_ID")
);
CREATE TABLE Settlements (
    Settlement_ID NUMBER PRIMARY KEY,
    Claim_ID NUMBER,
    Effective_Date TIME,
    Settlement_Amount NUMBER,
    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