Database: customer_deliveries
Schema: Products: product_id (number), product_name (text), product_price (number), product_description (text) | Addresses: address_id (number), address_details (text), city (text), zip_postcode (text), state_province_county (text), country (text) | Customers: customer_id (number), payment_method (text), customer_name (text), customer_phone (text), customer_email (text), date_became_customer (time) | Regular_Orders: regular_order_id (number), distributer_id (number) | Regular_Order_Products: regular_order_id (number), product_id (number) | Actual_Orders: actual_order_id (number), order_status_code (text), regular_order_id (number), actual_order_date (time) | Actual_Order_Products: actual_order_id (number), product_id (number) | Customer_Addresses: customer_id (number), address_id (number), date_from (time), address_type (text), date_to (time) | Delivery_Routes: route_id (number), route_name (text), other_route_details (text) | Delivery_Route_Locations: location_code (text), route_id (number), location_address_id (number), location_name (text) | Trucks: truck_id (number), truck_licence_number (text), truck_details (text) | Employees: employee_id (number), employee_address_id (number), employee_name (text), employee_phone (text) | Order_Deliveries: location_code (text), actual_order_id (number), delivery_status_code (text), driver_employee_id (number), truck_id (number), delivery_date (time)

Question: Find the ids of orders whose status is 'Success'.
SQL: SELECT actual_order_id FROM actual_orders WHERE order_status_code  =  'Success'

Question: Find the name and price of the product that has been ordered the greatest number of times.
SQL: SELECT t1.product_name ,   t1.product_price FROM products AS t1 JOIN regular_order_products AS t2 ON t1.product_id  =  t2.product_id GROUP BY t2.product_id ORDER BY count(*) DESC LIMIT 1

Question: Find the number of customers in total.
SQL: SELECT count(*) FROM customers

Question: How many different payment methods are there?
SQL: SELECT count(DISTINCT payment_method) FROM customers

Question: Show the details of all trucks in the order of their license number.
SQL: SELECT truck_details FROM trucks ORDER BY truck_licence_number

Question: Find the name of the most expensive product.
SQL: SELECT product_name FROM products ORDER BY product_price DESC LIMIT 1

Question: Find the names of customers who are not living in the state of California.
SQL: SELECT customer_name FROM customers EXCEPT SELECT t1.customer_name 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'

Question: List the names and emails of customers who payed by Visa card.
SQL: SELECT customer_email ,  customer_name FROM customers WHERE payment_method  =  'Visa'

Question: Find the names and phone numbers of customers living in California state.
SQL: 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'

Question: Find the states which do not have any employee in their record.
SQL: SELECT state_province_county FROM addresses WHERE address_id NOT IN (SELECT employee_address_id FROM Employees)

Question: List the names, phone numbers, and emails of all customers sorted by their dates of becoming customers.
SQL: SELECT customer_name ,  customer_phone ,  customer_email FROM Customers ORDER BY date_became_customer

Question: Find the name of the first 5 customers.
SQL: SELECT customer_name FROM Customers ORDER BY date_became_customer LIMIT 5

Question: Find the payment method that is used most frequently.
SQL: SELECT payment_method FROM Customers GROUP BY payment_method ORDER BY count(*) DESC LIMIT 1

Question: List the names of all routes in alphabetic order.
SQL: SELECT route_name FROM Delivery_Routes ORDER BY route_name

Question: Find the name of route that has the highest number of deliveries.
SQL: SELECT t1.route_name FROM Delivery_Routes AS t1 JOIN Delivery_Route_Locations AS t2 ON t1.route_id  =  t2.route_id GROUP BY t1.route_id ORDER BY count(*) DESC LIMIT 1

Question: List the state names and the number of customers living in each state.
SQL: 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