CREATE TABLE Premises (
    premise_id NUMBER PRIMARY KEY,
    premises_type TEXT,
    premise_details TEXT
);
CREATE TABLE Products (
    product_id NUMBER PRIMARY KEY,
    product_category TEXT,
    product_name TEXT
);
CREATE TABLE Customers (
    customer_id NUMBER PRIMARY KEY,
    payment_method TEXT,
    customer_name TEXT,
    customer_phone TEXT,
    customer_email TEXT,
    customer_address TEXT,
    customer_login TEXT,
    customer_password TEXT
);
CREATE TABLE Mailshot_Campaigns (
    mailshot_id NUMBER PRIMARY KEY,
    product_category TEXT,
    mailshot_name TEXT,
    mailshot_start_date TIME,
    mailshot_end_date TIME
);
CREATE TABLE Customer_Addresses (
    customer_id NUMBER,
    premise_id NUMBER,
    date_address_from TIME,
    address_type_code TEXT,
    date_address_to TIME,
    FOREIGN KEY ("customer_id") REFERENCES Customers("customer_id"),
    FOREIGN KEY ("premise_id") REFERENCES Premises("premise_id")
);
CREATE TABLE Customer_Orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    order_status_code TEXT,
    shipping_method_code TEXT,
    order_placed_datetime TIME,
    order_delivered_datetime TIME,
    order_shipping_charges TEXT,
    FOREIGN KEY ("customer_id") REFERENCES Customers("customer_id")
);
CREATE TABLE Mailshot_Customers (
    mailshot_id NUMBER,
    customer_id NUMBER,
    outcome_code TEXT,
    mailshot_customer_date TIME,
    FOREIGN KEY ("mailshot_id") REFERENCES Mailshot_Campaigns("mailshot_id"),
    FOREIGN KEY ("customer_id") REFERENCES Customers("customer_id")
);
CREATE TABLE Order_Items (
    item_id NUMBER,
    order_item_status_code TEXT,
    order_id NUMBER,
    product_id NUMBER,
    item_status_code TEXT,
    item_delivered_datetime TIME,
    item_order_quantity TEXT,
    FOREIGN KEY ("order_id") REFERENCES Customer_Orders("order_id"),
    FOREIGN KEY ("product_id") REFERENCES Products("product_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'