Database: customers_and_addresses

CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
);
CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
);
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
);
CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
);
CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
);
CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
);
CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
);

Question: Find the name of all customers.
SQL: SELECT customer_name FROM customers

Question: What are the names of all the customers?
SQL: SELECT customer_name FROM customers

Question: How many customers are there?
SQL: SELECT count(*) FROM customers

Question: Return the total number of distinct customers.
SQL: SELECT count(*) FROM customers

Question: What is the average amount of items ordered in each order?
SQL: SELECT avg(order_quantity) FROM order_items

Question: Find the average order quantity per order.
SQL: SELECT avg(order_quantity) FROM order_items

Question: What are the names of customers who use payment method "Cash"?
SQL: SELECT customer_name FROM customers WHERE payment_method  =  "Cash"

Question: Which customers use "Cash" for payment method? Return the customer names.
SQL: SELECT customer_name FROM customers WHERE payment_method  =  "Cash"

Question: Find the "date became customers" of the customers whose ID is between 10 and 20.
SQL: SELECT date_became_customer FROM customers WHERE customer_id BETWEEN 10 AND 20

Question: What are the dates when customers with ids between 10 and 20 became customers?
SQL: SELECT date_became_customer FROM customers WHERE customer_id BETWEEN 10 AND 20

Question: Which payment method is used by most customers?
SQL: SELECT payment_method FROM customers GROUP BY payment_method ORDER BY count(*) DESC LIMIT 1

Question: Find the payment method that is used most frequently.
SQL: SELECT payment_method FROM customers GROUP BY payment_method ORDER BY count(*) DESC LIMIT 1

Question: What are the names of customers using the most popular payment method?
SQL: SELECT customer_name FROM customers WHERE payment_method  =  (SELECT payment_method FROM customers GROUP BY payment_method ORDER BY count(*) DESC LIMIT 1)

Question: Find the name of the customers who use the most frequently used payment method.
SQL: SELECT customer_name FROM customers WHERE payment_method  =  (SELECT payment_method FROM customers GROUP BY payment_method ORDER BY count(*) DESC LIMIT 1)

Question: What are all the payment methods?
SQL: SELECT DISTINCT payment_method FROM customers

Question: Return all the distinct payment methods used by customers.
SQL: SELECT DISTINCT payment_method FROM customers

Question: What are the details of all products?
SQL: SELECT DISTINCT product_details FROM products

Question: Return the the details of all products.
SQL: SELECT DISTINCT product_details FROM products

Question: Find the name of all customers whose name contains "Alex".
SQL: SELECT customer_name FROM customers WHERE customer_name LIKE "%Alex%"

Question: Which customer's name contains "Alex"? Find the full name.
SQL: SELECT customer_name FROM customers WHERE customer_name LIKE "%Alex%"

Question: Find the detail of products whose detail contains the word "Latte" or the word "Americano"
SQL: SELECT product_details FROM products WHERE product_details LIKE "%Latte%" OR product_details LIKE "%Americano%"

Question: Which product's detail contains the word "Latte" or "Americano"? Return the full detail.
SQL: SELECT product_details FROM products WHERE product_details LIKE "%Latte%" OR product_details LIKE "%Americano%"

Question: What is the address content of the customer named "Maudie Kertzmann"?
SQL: SELECT t3.address_content FROM customers AS t1 JOIN customer_addresses AS t2 ON t1.customer_id  =  t2.customer_id JOIN addresses AS t3 ON t2.address_id  =  t3.address_id WHERE t1.customer_name  =  "Maudie Kertzmann"

Question: Return the address content for the customer whose name is "Maudie Kertzmann".
SQL: SELECT t3.address_content FROM customers AS t1 JOIN customer_addresses AS t2 ON t1.customer_id  =  t2.customer_id JOIN addresses AS t3 ON t2.address_id  =  t3.address_id WHERE t1.customer_name  =  "Maudie Kertzmann"

Question: How many customers are living in city "Lake Geovannyton"?
SQL: SELECT count(*) FROM customers AS t1 JOIN customer_addresses AS t2 ON t1.customer_id  =  t2.customer_id JOIN addresses AS t3 ON t2.address_id  =  t3.address_id WHERE t3.city  =  "Lake Geovannyton"

Question: Find the number of customers who live in the city called Lake Geovannyton.
SQL: SELECT count(*) FROM customers AS t1 JOIN customer_addresses AS t2 ON t1.customer_id  =  t2.customer_id JOIN addresses AS t3 ON t2.address_id  =  t3.address_id WHERE t3.city  =  "Lake Geovannyton"

Question: Find the name of customers who are living in Colorado?
SQL: SELECT t1.customer_name FROM customers AS t1 JOIN customer_addresses AS t2 ON t1.customer_id  =  t2.customer_id JOIN addresses AS t3 ON t2.address_id  =  t3.address_id WHERE t3.state_province_county  =  "Colorado"

Question: What are the names of customers who live in Colorado state?
SQL: SELECT t1.customer_name FROM customers AS t1 JOIN customer_addresses AS t2 ON t1.customer_id  =  t2.customer_id JOIN addresses AS t3 ON t2.address_id  =  t3.address_id WHERE t3.state_province_county  =  "Colorado"

Question: Find the list of cities that no customer is living in.
SQL: SELECT city FROM addresses WHERE city NOT IN ( SELECT DISTINCT t3.city FROM customers AS t1 JOIN customer_addresses AS t2 ON t1.customer_id  =  t2.customer_id JOIN addresses AS t3 ON t2.address_id  =  t3.address_id)

Question: What are the cities no customers live in?
SQL: SELECT city FROM addresses WHERE city NOT IN ( SELECT DISTINCT t3.city FROM customers AS t1 JOIN customer_addresses AS t2 ON t1.customer_id  =  t2.customer_id JOIN addresses AS t3 ON t2.address_id  =  t3.address_id)

Question: Which city has the most customers living in?
SQL: SELECT t3.city FROM customers AS t1 JOIN customer_addresses AS t2 ON t1.customer_id  =  t2.customer_id JOIN addresses AS t3 ON t2.address_id  =  t3.address_id GROUP BY t3.city ORDER BY count(*) DESC LIMIT 1

Question: Find the city where the most customers live.
SQL: SELECT t3.city FROM customers AS t1 JOIN customer_addresses AS t2 ON t1.customer_id  =  t2.customer_id JOIN addresses AS t3 ON t2.address_id  =  t3.address_id GROUP BY t3.city ORDER BY count(*) DESC LIMIT 1

Question: Retrieve the list of all cities.
SQL: SELECT DISTINCT city FROM addresses

Question: List all the distinct cities
SQL: SELECT DISTINCT city FROM addresses

Question: Find the city with post code 255.
SQL: SELECT city FROM addresses WHERE zip_postcode  =  255

Question: Which city is post code 255 located in?
SQL: SELECT city FROM addresses WHERE zip_postcode  =  255

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"

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

Question: Find the names of customers who ordered both products Latte and Americano.
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 JOIN products AS t4 ON t3.product_id  =  t4.product_id WHERE t4.product_details  =  'Latte' INTERSECT 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  =  'Americano'

Question: What are the names of customers who have purchased both products Latte and Americano?
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 JOIN products AS t4 ON t3.product_id  =  t4.product_id WHERE t4.product_details  =  'Latte' INTERSECT 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  =  'Americano'