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: What are id and name of the products whose price is lower than 600 or higher than 900?
SQL: SELECT product_id ,  product_name FROM products WHERE product_price  <  600 OR product_price  >  900

Question: Give the ids and names of products with price lower than 600 or higher than 900.
SQL: SELECT product_id ,  product_name FROM products WHERE product_price  <  600 OR product_price  >  900

Question: Find the id of suppliers whose average amount purchased for each product is above 50000 or below 30000.
SQL: SELECT supplier_id FROM Product_Suppliers GROUP BY supplier_id HAVING avg(total_amount_purchased)  >  50000 OR avg(total_amount_purchased)  <  30000

Question: What are the ids of suppliers which have an average amount purchased of above 50000 or below 30000?
SQL: SELECT supplier_id FROM Product_Suppliers GROUP BY supplier_id HAVING avg(total_amount_purchased)  >  50000 OR avg(total_amount_purchased)  <  30000

Question: What are the average amount purchased and value purchased for the supplier who supplies the most products.
SQL: SELECT avg(total_amount_purchased) ,  avg(total_value_purchased) FROM Product_Suppliers WHERE supplier_id  =  (SELECT supplier_id FROM Product_Suppliers GROUP BY supplier_id ORDER BY count(*) DESC LIMIT 1)

Question: Return the average total amount purchased and total value purchased for the supplier who supplies the greatest number of products.
SQL: SELECT avg(total_amount_purchased) ,  avg(total_value_purchased) FROM Product_Suppliers WHERE supplier_id  =  (SELECT supplier_id FROM Product_Suppliers GROUP BY supplier_id ORDER BY count(*) DESC LIMIT 1)

Question: What is the largest and smallest customer codes?
SQL: SELECT max(customer_code) ,  min(customer_code) FROM Customers

Question: Return the maximum and minimum customer codes.
SQL: SELECT max(customer_code) ,  min(customer_code) FROM Customers

Question: List the names of all the distinct customers who bought a keyboard.
SQL: SELECT DISTINCT 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_name  =  "keyboard"

Question: What are the distinct names of customers who have purchased a keyboard?
SQL: SELECT DISTINCT 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_name  =  "keyboard"

Question: List the names and phone numbers of all the distinct suppliers who supply red jeans.
SQL: SELECT DISTINCT T1.supplier_name ,  T1.supplier_phone FROM suppliers AS T1 JOIN product_suppliers AS T2 ON T1.supplier_id  =  T2.supplier_id JOIN products AS T3 ON T2.product_id  =  T3.product_id WHERE T3.product_name  =  "red jeans"

Question: What are the distinct names and phone numbers for suppliers who have red jeans?
SQL: SELECT DISTINCT T1.supplier_name ,  T1.supplier_phone FROM suppliers AS T1 JOIN product_suppliers AS T2 ON T1.supplier_id  =  T2.supplier_id JOIN products AS T3 ON T2.product_id  =  T3.product_id WHERE T3.product_name  =  "red jeans"

Question: What are the highest and lowest prices of products, grouped by and alphabetically ordered by product type?
SQL: SELECT max(product_price) ,  min(product_price) ,  product_type_code FROM products GROUP BY product_type_code ORDER BY product_type_code

Question: Give the maximum and minimum product prices for each product type, grouped and ordered by product type.
SQL: SELECT max(product_price) ,  min(product_price) ,  product_type_code FROM products GROUP BY product_type_code ORDER BY product_type_code

Question: List the order id, customer id for orders in Cancelled status, ordered by their order dates.
SQL: SELECT order_id ,  customer_id FROM customer_orders WHERE order_status_code  =   "Cancelled" ORDER BY order_date

Question: What are the order ids and customer ids for orders that have been Cancelled, sorted by their order dates?
SQL: SELECT order_id ,  customer_id FROM customer_orders WHERE order_status_code  =   "Cancelled" ORDER BY order_date

Question: Find the names of products that were bought by at least two distinct customers.
SQL: SELECT DISTINCT T3.product_name FROM customer_orders AS T1 JOIN order_items AS T2 ON T1.order_id  =  T2.order_id JOIN products AS T3 ON T2.product_id  =  T3.product_id GROUP BY T3.product_id HAVING COUNT (DISTINCT T1.customer_id)  >=  2

Question: What are the distinct names of products purchased by at least two different customers?
SQL: SELECT DISTINCT T3.product_name FROM customer_orders AS T1 JOIN order_items AS T2 ON T1.order_id  =  T2.order_id JOIN products AS T3 ON T2.product_id  =  T3.product_id GROUP BY T3.product_id HAVING COUNT (DISTINCT T1.customer_id)  >=  2