CREATE TABLE Addresses (
    address_id NUMBER PRIMARY KEY,
    line_1_number_building TEXT,
    city TEXT,
    zip_postcode TEXT,
    state_province_county TEXT,
    country TEXT
);
CREATE TABLE Products (
    product_id NUMBER PRIMARY KEY,
    product_type_code TEXT,
    product_name TEXT,
    product_price NUMBER
);
CREATE TABLE Customers (
    customer_id NUMBER PRIMARY KEY,
    payment_method_code TEXT,
    customer_number TEXT,
    customer_name TEXT,
    customer_address TEXT,
    customer_phone TEXT,
    customer_email TEXT
);
CREATE TABLE Contacts (
    contact_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    gender TEXT,
    first_name TEXT,
    last_name TEXT,
    contact_phone TEXT
);
CREATE TABLE Customer_Address_History (
    customer_id NUMBER,
    address_id NUMBER,
    date_from TIME,
    date_to TIME,
    FOREIGN KEY ("address_id") REFERENCES Addresses("address_id"),
    FOREIGN KEY ("customer_id") REFERENCES Customers("customer_id")
);
CREATE TABLE Customer_Orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    order_date TIME,
    order_status_code TEXT,
    FOREIGN KEY ("customer_id") REFERENCES Customers("customer_id")
);
CREATE TABLE Order_Items (
    order_item_id NUMBER,
    order_id NUMBER,
    product_id NUMBER,
    order_quantity TEXT,
    FOREIGN KEY ("order_id") REFERENCES Customer_Orders("order_id"),
    FOREIGN KEY ("product_id") REFERENCES Products("product_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