Database: customers_and_products_contacts

CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`line_1_number_building` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50)
);
CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15),
`product_name` VARCHAR(80),
`product_price` DOUBLE NULL
);
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method_code` VARCHAR(15),
`customer_number` VARCHAR(20),
`customer_name` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80)
);
CREATE TABLE `Contacts` (
`contact_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(50),
`contact_phone` VARCHAR(80)
);
CREATE TABLE `Customer_Address_History` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_from` DATETIME NOT NULL,
`date_to` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` )
);
CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_date` DATETIME NOT NULL,
`order_status_code` VARCHAR(15),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
);
CREATE TABLE `Order_Items` (
`order_item_id` INTEGER NOT NULL ,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
);

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