Database: customers_campaigns_ecommerce

CREATE TABLE `Premises` (
`premise_id` INTEGER PRIMARY KEY,
`premises_type` VARCHAR(15) NOT NULL,
`premise_details` VARCHAR(255)
);
CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80)
);
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`customer_phone` VARCHAR(80),
`customer_email` VARCHAR(80),
`customer_address` VARCHAR(255),
`customer_login` VARCHAR(80),
`customer_password` VARCHAR(10)
);
CREATE TABLE `Mailshot_Campaigns` (
`mailshot_id` INTEGER PRIMARY KEY,
`product_category` VARCHAR(15),
`mailshot_name` VARCHAR(80),
`mailshot_start_date` DATETIME,
`mailshot_end_date` DATETIME
);
CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`premise_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type_code` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`premise_id` ) REFERENCES `Premises`(`premise_id` )
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
);
CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status_code` VARCHAR(15) NOT NULL,
`shipping_method_code` VARCHAR(15) NOT NULL,
`order_placed_datetime` DATETIME NOT NULL,
`order_delivered_datetime` DATETIME,
`order_shipping_charges` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
);
CREATE TABLE `Mailshot_Customers` (
`mailshot_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`outcome_code` VARCHAR(15) NOT NULL,
`mailshot_customer_date` DATETIME,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` ),
FOREIGN KEY (`mailshot_id` ) REFERENCES `Mailshot_Campaigns`(`mailshot_id` )
);
CREATE TABLE `Order_Items` (
`item_id` INTEGER NOT NULL ,
`order_item_status_code` VARCHAR(15) NOT NULL,
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`item_status_code` VARCHAR(15),
`item_delivered_datetime` DATETIME,
`item_order_quantity` VARCHAR(80),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
);

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

Question: What are all the distinct premise types?
SQL: SELECT DISTINCT premises_type FROM premises

Question: Find the types and details for all premises and order by the premise type.
SQL: SELECT premises_type ,  premise_details FROM premises ORDER BY premises_type

Question: Show each premise type and the number of premises in that type.
SQL: SELECT premises_type ,  count(*) FROM premises GROUP BY premises_type

Question: Show all distinct product categories along with the number of mailshots in each category.
SQL: SELECT product_category ,  count(*) FROM mailshot_campaigns GROUP BY product_category

Question: Show the name and phone of the customer without any mailshot.
SQL: SELECT customer_name ,  customer_phone FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM mailshot_customers)

Question: Show the name and phone for customers with a mailshot with outcome code 'No Response'.
SQL: SELECT T1.customer_name ,  T1.customer_phone FROM customers AS T1 JOIN mailshot_customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.outcome_code  =  'No Response'

Question: Show the outcome code of mailshots along with the number of mailshots in each outcome code.
SQL: SELECT outcome_code ,  count(*) FROM mailshot_customers GROUP BY outcome_code

Question: Show the names of customers who have at least 2 mailshots with outcome code 'Order'.
SQL: SELECT T2.customer_name FROM mailshot_customers AS T1 JOIN customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE outcome_code  =  'Order' GROUP BY T1.customer_id HAVING count(*)  >=  2

Question: Show the names of customers who have the most mailshots.
SQL: SELECT T2.customer_name FROM mailshot_customers AS T1 JOIN customers AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id ORDER BY count(*) DESC LIMIT 1

Question: What are the name and payment method of customers who have both mailshots in 'Order' outcome and mailshots in 'No Response' outcome.
SQL: SELECT T2.customer_name ,  T2.payment_method FROM mailshot_customers AS T1 JOIN customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T1.outcome_code  =  'Order' INTERSECT SELECT T2.customer_name ,  T2.payment_method FROM mailshot_customers AS T1 JOIN customers AS T2 ON T1.customer_id  =  T2.customer_id WHERE T1.outcome_code  =  'No Response'

Question: Show the premise type and address type code for all customer addresses.
SQL: SELECT T2.premises_type ,  T1.address_type_code FROM customer_addresses AS T1 JOIN premises AS T2 ON T1.premise_id  =  T2.premise_id

Question: What are the distinct address type codes for all customer addresses?
SQL: SELECT DISTINCT address_type_code FROM customer_addresses

Question: Show the shipping charge and customer id for customer orders with order status Cancelled or Paid.
SQL: SELECT order_shipping_charges ,  customer_id FROM customer_orders WHERE order_status_code  =  'Cancelled' OR order_status_code  =  'Paid'

Question: Show the names of customers having an order with shipping method FedEx and order status Paid.
SQL: SELECT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id WHERE shipping_method_code  =  'FedEx' AND order_status_code  =  'Paid'