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: Find the state and country of all cities with post code starting with 4.
SQL: SELECT state_province_county ,  country FROM addresses WHERE zip_postcode LIKE "4%"

Question: What are the state and country of all the cities that have post codes starting with 4.\
SQL: SELECT state_province_county ,  country FROM addresses WHERE zip_postcode LIKE "4%"

Question: List the countries having more than 4 addresses listed.
SQL: SELECT country FROM addresses GROUP BY country HAVING count(address_id)  >  4

Question: For which countries are there more than four distinct addresses listed?
SQL: SELECT country FROM addresses GROUP BY country HAVING count(address_id)  >  4

Question: List all the contact channel codes that were used less than 5 times.
SQL: SELECT channel_code FROM customer_contact_channels GROUP BY channel_code HAVING count(customer_id)  <  5

Question: Which contact channel codes were used less than 5 times?
SQL: SELECT channel_code FROM customer_contact_channels GROUP BY channel_code HAVING count(customer_id)  <  5

Question: Which contact channel has been used by the customer with name "Tillman Ernser"?
SQL: SELECT DISTINCT channel_code FROM customers AS t1 JOIN customer_contact_channels AS t2 ON t1.customer_id  =  t2.customer_id WHERE t1.customer_name  =  "Tillman Ernser"

Question: Find the contact channel code that was used by the customer named "Tillman Ernser".
SQL: SELECT DISTINCT channel_code FROM customers AS t1 JOIN customer_contact_channels AS t2 ON t1.customer_id  =  t2.customer_id WHERE t1.customer_name  =  "Tillman Ernser"

Question: What is the "active to date" of the latest contact channel used by "Tillman Ernser"?
SQL: SELECT max(t2.active_to_date) FROM customers AS t1 JOIN customer_contact_channels AS t2 ON t1.customer_id  =  t2.customer_id WHERE t1.customer_name  =  "Tillman Ernser"

Question: Return the the "active to date" of the latest contact channel used by the customer named "Tillman Ernser".
SQL: SELECT max(t2.active_to_date) FROM customers AS t1 JOIN customer_contact_channels AS t2 ON t1.customer_id  =  t2.customer_id WHERE t1.customer_name  =  "Tillman Ernser"

Question: What is the average time span of contact channels in the database?
SQL: SELECT avg(active_to_date - active_from_date) FROM customer_contact_channels

Question: Compute the average active time span of contact channels.
SQL: SELECT avg(active_to_date - active_from_date) FROM customer_contact_channels

Question: What is the channel code and contact number of the customer contact channel that was active for the longest time?
SQL: SELECT channel_code ,  contact_number FROM customer_contact_channels WHERE active_to_date - active_from_date  =  (SELECT active_to_date - active_from_date FROM customer_contact_channels ORDER BY (active_to_date - active_from_date) DESC LIMIT 1)

Question: Return the channel code and contact number of the customer contact channel whose active duration was the longest.
SQL: SELECT channel_code ,  contact_number FROM customer_contact_channels WHERE active_to_date - active_from_date  =  (SELECT active_to_date - active_from_date FROM customer_contact_channels ORDER BY (active_to_date - active_from_date) DESC LIMIT 1)

Question: Find the name and active date of the customer that use email as the contact channel.
SQL: SELECT t1.customer_name ,  t2.active_from_date FROM customers AS t1 JOIN customer_contact_channels AS t2 ON t1.customer_id  =  t2.customer_id WHERE t2.channel_code  =  'Email'

Question: What are the name and active date of the customers whose contact channel code is email?
SQL: SELECT t1.customer_name ,  t2.active_from_date FROM customers AS t1 JOIN customer_contact_channels AS t2 ON t1.customer_id  =  t2.customer_id WHERE t2.channel_code  =  'Email'

Question: What is the name of the customer that made the order with the largest quantity?
SQL: 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 WHERE t3.order_quantity  =  ( SELECT max(order_quantity) FROM order_items)

Question: Find the name of the customer who made the order of the largest amount of goods.
SQL: 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 WHERE t3.order_quantity  =  ( SELECT max(order_quantity) FROM order_items)

Question: What is the name of the customer that has purchased the most items?
SQL: 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 GROUP BY t1.customer_name ORDER BY sum(t3.order_quantity) DESC LIMIT 1

Question: Give me the name of the customer who ordered the most items in total.
SQL: 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 GROUP BY t1.customer_name ORDER BY sum(t3.order_quantity) DESC LIMIT 1

Question: What is the payment method of the customer that has purchased the least quantity of items?
SQL: SELECT t1.payment_method 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 GROUP BY t1.customer_name ORDER BY sum(t3.order_quantity) LIMIT 1

Question: Tell me the payment method used by the customer who ordered the least amount of goods in total.
SQL: SELECT t1.payment_method 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 GROUP BY t1.customer_name ORDER BY sum(t3.order_quantity) LIMIT 1

Question: How many types of products have Rodrick Heaney bought in total?
SQL: SELECT count(DISTINCT t3.product_id) 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: Find the number of distinct products Rodrick Heaney has bought so far.
SQL: SELECT count(DISTINCT t3.product_id) 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"