human
Schema:
Customers (Customer_ID, Customer_name)
Services (Service_ID, Service_name)
Available_Policies (Policy_ID, policy_type_code, Customer_Phone)
Customers_Policies (Customer_ID, Policy_ID, Date_Opened, Date_Closed)
First_Notification_of_Loss (FNOL_ID, Customer_ID, Policy_ID, Service_ID)
Claims (Claim_ID, FNOL_ID, Effective_Date)
Settlements (Settlement_ID, Claim_ID, Effective_Date, Settlement_Amount)
Please answer with SQL.
assistant
Sure, I understand.
human
List all the customers in increasing order of IDs.
assistant
SELECT customer_id , customer_name FROM customers ORDER BY customer_id ASC
human
List all the services in the alphabetical order.
assistant
SELECT service_name FROM services ORDER BY service_name
human
How many services are there?
assistant
SELECT count(*) FROM services
human
What are the customer phone numbers under the policy 'Life Insurance'?
assistant
SELECT customer_phone FROM available_policies WHERE policy_type_code = 'Life Insurance'
human
What are all the customer phone numbers under the most popular policy type?
assistant
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)
human
What are all the phone numbers?
assistant
SELECT customer_phone FROM available_policies ;