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: 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%"