CREATE TABLE Addresses (
    address_id NUMBER PRIMARY KEY,
    address_details TEXT
);
CREATE TABLE Staff (
    staff_id NUMBER PRIMARY KEY,
    staff_gender TEXT,
    staff_name TEXT
);
CREATE TABLE Suppliers (
    supplier_id NUMBER PRIMARY KEY,
    supplier_name TEXT,
    supplier_phone TEXT
);
CREATE TABLE Department_Store_Chain (
    dept_store_chain_id NUMBER PRIMARY KEY,
    dept_store_chain_name TEXT
);
CREATE TABLE Customers (
    customer_id NUMBER PRIMARY KEY,
    payment_method_code TEXT,
    customer_code TEXT,
    customer_name TEXT,
    customer_address TEXT,
    customer_phone TEXT,
    customer_email TEXT
);
CREATE TABLE Products (
    product_id NUMBER PRIMARY KEY,
    product_type_code TEXT,
    product_name TEXT,
    product_price NUMBER
);
CREATE TABLE Supplier_Addresses (
    supplier_id NUMBER PRIMARY KEY,
    address_id NUMBER,
    date_from TIME,
    date_to TIME,
    FOREIGN KEY ("supplier_id") REFERENCES Suppliers("supplier_id"),
    FOREIGN KEY ("address_id") REFERENCES Addresses("address_id")
);
CREATE TABLE Customer_Addresses (
    customer_id NUMBER PRIMARY KEY,
    address_id NUMBER,
    date_from TIME,
    date_to TIME,
    FOREIGN KEY ("customer_id") REFERENCES Customers("customer_id"),
    FOREIGN KEY ("address_id") REFERENCES Addresses("address_id")
);
CREATE TABLE Customer_Orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    order_status_code TEXT,
    order_date TIME,
    FOREIGN KEY ("customer_id") REFERENCES Customers("customer_id")
);
CREATE TABLE Department_Stores (
    dept_store_id NUMBER PRIMARY KEY,
    dept_store_chain_id NUMBER,
    store_name TEXT,
    store_address TEXT,
    store_phone TEXT,
    store_email TEXT,
    FOREIGN KEY ("dept_store_chain_id") REFERENCES Department_Store_Chain("dept_store_chain_id")
);
CREATE TABLE Departments (
    department_id NUMBER PRIMARY KEY,
    dept_store_id NUMBER,
    department_name TEXT,
    FOREIGN KEY ("dept_store_id") REFERENCES Department_Stores("dept_store_id")
);
CREATE TABLE Order_Items (
    order_item_id NUMBER PRIMARY KEY,
    order_id NUMBER,
    product_id NUMBER,
    FOREIGN KEY ("product_id") REFERENCES Products("product_id"),
    FOREIGN KEY ("order_id") REFERENCES Customer_Orders("order_id")
);
CREATE TABLE Product_Suppliers (
    product_id NUMBER PRIMARY KEY,
    supplier_id NUMBER,
    date_supplied_from TIME,
    date_supplied_to TIME,
    total_amount_purchased TEXT,
    total_value_purchased NUMBER,
    FOREIGN KEY ("product_id") REFERENCES Products("product_id"),
    FOREIGN KEY ("supplier_id") REFERENCES Suppliers("supplier_id")
);
CREATE TABLE Staff_Department_Assignments (
    staff_id NUMBER PRIMARY KEY,
    department_id NUMBER,
    date_assigned_from TIME,
    job_title_code TEXT,
    date_assigned_to TIME,
    FOREIGN KEY ("staff_id") REFERENCES Staff("staff_id"),
    FOREIGN KEY ("department_id") REFERENCES Departments("department_id")
);

Question: Find the id and name of the staff who has been assigned for the shortest period.
SQL: SELECT T1.staff_id ,  T1.staff_name FROM staff AS T1 JOIN Staff_Department_Assignments AS T2 ON T1.staff_id  =  T2.staff_id ORDER BY date_assigned_to - date_assigned_from LIMIT 1

Question: What is the id and name of the staff who has been assigned for the least amount of time?
SQL: SELECT T1.staff_id ,  T1.staff_name FROM staff AS T1 JOIN Staff_Department_Assignments AS T2 ON T1.staff_id  =  T2.staff_id ORDER BY date_assigned_to - date_assigned_from LIMIT 1

Question: Return the names and ids of all products whose price is between 600 and 700.
SQL: SELECT product_name ,  product_id FROM products WHERE product_price BETWEEN 600 AND 700

Question: What are the names and ids of products costing between 600 and 700?
SQL: SELECT product_name ,  product_id FROM products WHERE product_price BETWEEN 600 AND 700

Question: Find the ids of all distinct customers who made order after some orders that were Cancelled.
SQL: SELECT DISTINCT customer_id FROM Customer_Orders WHERE order_date  >  (SELECT min(order_date) FROM Customer_Orders WHERE order_status_code  =  "Cancelled")

Question: What are the distinct ids of customers who made an order after any order that was Cancelled?
SQL: SELECT DISTINCT customer_id FROM Customer_Orders WHERE order_date  >  (SELECT min(order_date) FROM Customer_Orders WHERE order_status_code  =  "Cancelled")

Question: What is id of the staff who had a Staff Department Assignment earlier than any Clerical Staff?
SQL: SELECT staff_id FROM Staff_Department_Assignments WHERE date_assigned_to  <  (SELECT max(date_assigned_to) FROM Staff_Department_Assignments WHERE job_title_code  =  'Clerical Staff')

Question: Return the id of the staff whose Staff Department Assignment was earlier than that of any Clerical Staff.
SQL: SELECT staff_id FROM Staff_Department_Assignments WHERE date_assigned_to  <  (SELECT max(date_assigned_to) FROM Staff_Department_Assignments WHERE job_title_code  =  'Clerical Staff')

Question: What are the names and ids of customers whose address contains TN?
SQL: SELECT customer_name ,  customer_id FROM customers WHERE customer_address LIKE "%TN%"

Question: Return the names and ids of customers who have TN in their address.
SQL: SELECT customer_name ,  customer_id FROM customers WHERE customer_address LIKE "%TN%"

Question: Return the name and gender of the staff who was assigned in 2016.
SQL: SELECT T1.staff_name ,  T1.staff_gender FROM staff AS T1 JOIN staff_department_assignments AS T2 ON T1.staff_id  =  T2.staff_id WHERE T2.date_assigned_from LIKE "2016%"

Question: What are the names and genders of staff who were assigned in 2016?
SQL: SELECT T1.staff_name ,  T1.staff_gender FROM staff AS T1 JOIN staff_department_assignments AS T2 ON T1.staff_id  =  T2.staff_id WHERE T2.date_assigned_from LIKE "2016%"

Question: List the name of staff who has been assigned multiple jobs.
SQL: SELECT T1.staff_name FROM staff AS T1 JOIN staff_department_assignments AS T2 ON T1.staff_id  =  T2.staff_id GROUP BY T2.staff_id HAVING COUNT (*)  >  1

Question: What are the names of staff who have been assigned multiple jobs?
SQL: SELECT T1.staff_name FROM staff AS T1 JOIN staff_department_assignments AS T2 ON T1.staff_id  =  T2.staff_id GROUP BY T2.staff_id HAVING COUNT (*)  >  1

Question: List the name and phone number of all suppliers in the alphabetical order of their addresses.
SQL: SELECT T1.supplier_name ,  T1.supplier_phone FROM Suppliers AS T1 JOIN supplier_addresses AS T2 ON T1.supplier_id  =  T2.supplier_id JOIN addresses AS T3 ON T2.address_id  =  T3.address_id ORDER BY T3.address_details

Question: What are the names and phone numbers for all suppliers, sorted in alphabetical order of their addressed?
SQL: SELECT T1.supplier_name ,  T1.supplier_phone FROM Suppliers AS T1 JOIN supplier_addresses AS T2 ON T1.supplier_id  =  T2.supplier_id JOIN addresses AS T3 ON T2.address_id  =  T3.address_id ORDER BY T3.address_details

Question: What are the phone numbers of all customers and suppliers.
SQL: SELECT customer_phone FROM customers UNION SELECT supplier_phone FROM suppliers

Question: Return the phone numbers for all customers and suppliers.
SQL: SELECT customer_phone FROM customers UNION SELECT supplier_phone FROM suppliers

Question: Return the ids of all products that were ordered more than three times or supplied more than 80000.
SQL: SELECT product_id FROM Order_Items GROUP BY product_id HAVING count(*)  >  3 UNION SELECT product_id FROM Product_Suppliers GROUP BY product_id HAVING sum(total_amount_purchased)  >  80000

Question: What are the ids of all products that were either ordered more than 3 times or have a cumulative amount purchased of above 80000?
SQL: SELECT product_id FROM Order_Items GROUP BY product_id HAVING count(*)  >  3 UNION SELECT product_id FROM Product_Suppliers GROUP BY product_id HAVING sum(total_amount_purchased)  >  80000