CREATE TABLE Addresses (
    address_id NUMBER PRIMARY KEY,
    address_content TEXT,
    city TEXT,
    zip_postcode TEXT,
    state_province_county TEXT,
    country TEXT,
    other_address_details TEXT
);
CREATE TABLE Products (
    product_id NUMBER PRIMARY KEY,
    product_details TEXT
);
CREATE TABLE Customers (
    customer_id NUMBER PRIMARY KEY,
    payment_method TEXT,
    customer_name TEXT,
    date_became_customer TIME,
    other_customer_details TEXT
);
CREATE TABLE Customer_Addresses (
    customer_id NUMBER,
    address_id NUMBER,
    date_address_from TIME,
    address_type TEXT,
    date_address_to TIME,
    FOREIGN KEY ("customer_id") REFERENCES Customers("customer_id"),
    FOREIGN KEY ("address_id") REFERENCES Addresses("address_id")
);
CREATE TABLE Customer_Contact_Channels (
    customer_id NUMBER,
    channel_code TEXT,
    active_from_date TIME,
    active_to_date TIME,
    contact_number TEXT,
    FOREIGN KEY ("customer_id") REFERENCES Customers("customer_id")
);
CREATE TABLE Customer_Orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    order_status TEXT,
    order_date TIME,
    order_details TEXT,
    FOREIGN KEY ("customer_id") REFERENCES Customers("customer_id")
);
CREATE TABLE Order_Items (
    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: What is the total quantity of products purchased by "Rodrick Heaney"?
SQL: SELECT sum(t3.order_quantity) FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id JOIN order_items AS t3 ON t2.order_id  =  t3.order_id WHERE t1.customer_name  =  "Rodrick Heaney"

Question: Tell me the total quantity of products bought by the customer called "Rodrick Heaney".
SQL: SELECT sum(t3.order_quantity) FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id JOIN order_items AS t3 ON t2.order_id  =  t3.order_id WHERE t1.customer_name  =  "Rodrick Heaney"

Question: How many customers have at least one order with status "Cancelled"?
SQL: SELECT count(DISTINCT customer_id) FROM customer_orders WHERE order_status  =  "Cancelled"

Question: Return the number of customers who have at least one order with "Cancelled" status.
SQL: SELECT count(DISTINCT customer_id) FROM customer_orders WHERE order_status  =  "Cancelled"

Question: How many orders have detail "Second time"?
SQL: SELECT count(*) FROM customer_orders WHERE order_details  =  "Second time"

Question: Tell me the number of orders with "Second time" as order detail.
SQL: SELECT count(*) FROM customer_orders WHERE order_details  =  "Second time"

Question: Find the customer name and date of the orders that have the status "Delivered".
SQL: SELECT t1.customer_name ,  t2.order_date FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id WHERE order_status  =  "Delivered"

Question: What are the customer name and date of the orders whose status is "Delivered".
SQL: SELECT t1.customer_name ,  t2.order_date FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id WHERE order_status  =  "Delivered"

Question: What is the total number of products that are in orders with status "Cancelled"?
SQL: SELECT sum(t2.order_quantity) FROM customer_orders AS t1 JOIN order_items AS t2 ON t1.order_id  =  t2.order_id WHERE t1.order_status  =  "Cancelled"

Question: Find the total quantity of products associated with the orders in the "Cancelled" status.
SQL: SELECT sum(t2.order_quantity) FROM customer_orders AS t1 JOIN order_items AS t2 ON t1.order_id  =  t2.order_id WHERE t1.order_status  =  "Cancelled"

Question: Find the total amount of products ordered before 2018-03-17 07:13:53.
SQL: SELECT sum(t2.order_quantity) FROM customer_orders AS t1 JOIN order_items AS t2 ON t1.order_id  =  t2.order_id WHERE t1.order_date  <  "2018-03-17 07:13:53"

Question: What is the total amount of products purchased before 2018-03-17 07:13:53?
SQL: SELECT sum(t2.order_quantity) FROM customer_orders AS t1 JOIN order_items AS t2 ON t1.order_id  =  t2.order_id WHERE t1.order_date  <  "2018-03-17 07:13:53"

Question: Who made the latest order?
SQL: SELECT t1.customer_name FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id ORDER BY t2.order_date DESC LIMIT 1

Question: Find the name of the customer who made an order most recently.
SQL: SELECT t1.customer_name FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id ORDER BY t2.order_date DESC LIMIT 1

Question: Which product has been ordered most number of times?
SQL: SELECT t2.product_details FROM order_items AS t1 JOIN products AS t2 ON t1.product_id  =  t2.product_id GROUP BY t1.product_id ORDER BY count(*) DESC LIMIT 1

Question: What is the most frequently ordered product? Tell me the detail of the product
SQL: SELECT t2.product_details FROM order_items AS t1 JOIN products AS t2 ON t1.product_id  =  t2.product_id GROUP BY t1.product_id ORDER BY count(*) DESC LIMIT 1

Question: Find the name and ID of the product whose total order quantity is the largest.
SQL: SELECT t2.product_details ,  t2.product_id FROM order_items AS t1 JOIN products AS t2 ON t1.product_id  =  t2.product_id GROUP BY t1.product_id ORDER BY sum(t1.order_quantity) LIMIT 1

Question: What are the name and ID of the product bought the most.
SQL: SELECT t2.product_details ,  t2.product_id FROM order_items AS t1 JOIN products AS t2 ON t1.product_id  =  t2.product_id GROUP BY t1.product_id ORDER BY sum(t1.order_quantity) LIMIT 1

Question: Find all the addresses in East Julianaside, Texas or in Gleasonmouth, Arizona.
SQL: SELECT address_content FROM addresses WHERE city  =  "East Julianaside" AND state_province_county  =  "Texas" UNION SELECT address_content FROM addresses WHERE city  =  "Gleasonmouth" AND state_province_county  =  "Arizona"

Question: What are all the addresses in East Julianaside, Texas or in Gleasonmouth, Arizona.
SQL: SELECT address_content FROM addresses WHERE city  =  "East Julianaside" AND state_province_county  =  "Texas" UNION SELECT address_content FROM addresses WHERE city  =  "Gleasonmouth" AND state_province_county  =  "Arizona"

Question: Find the name of customers who did not pay with Cash.
SQL: SELECT customer_name FROM customers WHERE payment_method != 'Cash'

Question: What is the name of customers who do not use Cash as payment method.
SQL: SELECT customer_name FROM customers WHERE payment_method != 'Cash'

Question: Find the names of customers who never ordered product Latte.
SQL: SELECT customer_name FROM customers EXCEPT SELECT t1.customer_name FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id JOIN order_items AS t3 ON t2.order_id  =  t3.order_id JOIN products AS t4 ON t3.product_id  =  t4.product_id WHERE t4.product_details  =  'Latte'

Question: What are names of customers who never ordered product Latte.
SQL: SELECT customer_name FROM customers EXCEPT SELECT t1.customer_name FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id JOIN order_items AS t3 ON t2.order_id  =  t3.order_id JOIN products AS t4 ON t3.product_id  =  t4.product_id WHERE t4.product_details  =  'Latte'

Question: Find the names of customers who never placed an order.
SQL: SELECT customer_name FROM customers EXCEPT SELECT t1.customer_name FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id

Question: What are the names of customers who never made an order.
SQL: SELECT customer_name FROM customers EXCEPT SELECT t1.customer_name FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id