Database: customers_and_products_contacts
Schema: Addresses: address_id (number), line_1_number_building (text), city (text), zip_postcode (text), state_province_county (text), country (text) | Products: product_id (number), product_type_code (text), product_name (text), product_price (number) | Customers: customer_id (number), payment_method_code (text), customer_number (text), customer_name (text), customer_address (text), customer_phone (text), customer_email (text) | Contacts: contact_id (number), customer_id (number), gender (text), first_name (text), last_name (text), contact_phone (text) | Customer_Address_History: customer_id (number), address_id (number), date_from (time), date_to (time) | Customer_Orders: order_id (number), customer_id (number), order_date (time), order_status_code (text) | Order_Items: order_item_id (number), order_id (number), product_id (number), order_quantity (text)

Question: How many addresses are there in country USA?
SQL: SELECT count(*) FROM addresses WHERE country  =  'USA'

Question: Show all distinct cities in the address record.
SQL: SELECT DISTINCT city FROM addresses

Question: Show each state and the number of addresses in each state.
SQL: SELECT state_province_county ,  count(*) FROM addresses GROUP BY state_province_county

Question: Show names and phones of customers who do not have address information.
SQL: SELECT customer_name ,  customer_phone FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM customer_address_history)

Question: Show the name of the customer who has the most orders.
SQL: SELECT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id ORDER BY count(*) DESC LIMIT 1

Question: Show the product type codes which have at least two products.
SQL: SELECT product_type_code FROM products GROUP BY product_type_code HAVING count(*)  >=  2

Question: Show the names of customers who have both an order in completed status and an order in part status.
SQL: SELECT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.order_status_code  =  'Completed' INTERSECT SELECT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.order_status_code  =  'Part'

Question: Show the name, phone, and payment method code for all customers in descending order of customer number.
SQL: SELECT customer_name ,  customer_phone ,  payment_method_code FROM customers ORDER BY customer_number DESC

Question: Show the product name and total order quantity for each product.
SQL: SELECT T1.product_name ,  sum(T2.order_quantity) FROM products AS T1 JOIN order_items AS T2 ON T1.product_id  =  T2.product_id GROUP BY T1.product_id

Question: Show the minimum, maximum, average price for all products.
SQL: SELECT min(product_price) ,  max(product_price) ,  avg(product_price) FROM products

Question: How many products have a price higher than the average?
SQL: SELECT count(*) FROM products WHERE product_price  >  (SELECT avg(product_price) FROM products)

Question: Show the customer name, customer address city, date from, and date to for each customer address history.
SQL: SELECT T2.customer_name ,  T3.city ,  T1.date_from ,  T1.date_to FROM customer_address_history AS T1 JOIN customers AS T2 ON T1.customer_id  =  T2.customer_id JOIN addresses AS T3 ON T1.address_id  =  T3.address_id

Question: Show the names of customers who use Credit Card payment method and have more than 2 orders.
SQL: SELECT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id WHERE T1.payment_method_code  =  'Credit Card' GROUP BY T1.customer_id HAVING count(*)  >  2

Question: What are the name and phone of the customer with the most ordered product quantity?
SQL: SELECT  T1.customer_name ,  T1.customer_phone FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id JOIN order_items AS T3 ON T3.order_id  =  T2.order_id GROUP BY T1.customer_id ORDER BY sum(T3.order_quantity) DESC LIMIT 1

Question: Show the product type and name for the products with price higher than 1000 or lower than 500.
SQL: SELECT product_type_code ,  product_name FROM products WHERE product_price  >  1000 OR product_price  <  500