human
Schema:
Products (product_id, product_name, product_price, product_description)
Addresses (address_id, address_details, city, zip_postcode, state_province_county, country)
Customers (customer_id, payment_method, customer_name, customer_phone, customer_email, date_became_customer)
Regular_Orders (regular_order_id, distributer_id)
Regular_Order_Products (regular_order_id, product_id)
Actual_Orders (actual_order_id, order_status_code, regular_order_id, actual_order_date)
Actual_Order_Products (actual_order_id, product_id)
Customer_Addresses (customer_id, address_id, date_from, address_type, date_to)
Delivery_Routes (route_id, route_name, other_route_details)
Delivery_Route_Locations (location_code, route_id, location_address_id, location_name)
Trucks (truck_id, truck_licence_number, truck_details)
Employees (employee_id, employee_address_id, employee_name, employee_phone)
Order_Deliveries (location_code, actual_order_id, delivery_status_code, driver_employee_id, truck_id, delivery_date)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the number of customers in total.
assistant
SELECT count(*) FROM customers
human
List the names and emails of customers who payed by Visa card.
assistant
SELECT customer_email , customer_name FROM customers WHERE payment_method = 'Visa'
human
List the state names and the number of customers living in each state.
assistant
SELECT t2.state_province_county , count(*) FROM customer_addresses AS t1 JOIN addresses AS t2 ON t1.address_id = t2.address_id GROUP BY t2.state_province_county
human
Find the names and phone numbers of customers living in California state.
assistant
SELECT t1.customer_name , t1.customer_phone FROM customers AS t1 JOIN customer_addresses AS t2 ON t1.customer_id = t2.customer_id JOIN addresses AS t3 ON t2.address_id = t3.address_id WHERE t3.state_province_county = 'California'
human
Find the name of the first 5 customers.
assistant
SELECT customer_name FROM Customers ORDER BY date_became_customer LIMIT 5
human
List the names, phone numbers, and emails of all customers sorted by their dates of becoming customers.
assistant
SELECT customer_name , customer_phone , customer_email FROM Customers ORDER BY date_became_customer ;