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: Return the distinct name of customers whose order status is Pending, in the order of customer id.
SQL: SELECT DISTINCT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.order_status_code  =  "Pending" ORDER BY T2.customer_id

Question: What are the distinct names of customers with an order status of Pending, sorted by customer id?
SQL: SELECT DISTINCT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.order_status_code  =  "Pending" ORDER BY T2.customer_id

Question: Find the name and address of the customers who have both New and Pending orders.
SQL: SELECT T1.customer_name ,  T1.customer_address FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.order_status_code  =  "New" INTERSECT SELECT T1.customer_name ,  T1.customer_address FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.order_status_code  =  "Pending"

Question: What are the names and addressed of customers who have both New and Pending orders?
SQL: SELECT T1.customer_name ,  T1.customer_address FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.order_status_code  =  "New" INTERSECT SELECT T1.customer_name ,  T1.customer_address FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id WHERE T2.order_status_code  =  "Pending"

Question: Return ids of all the products that are supplied by supplier id 2 and are more expensive than the average price of all products.
SQL: SELECT T1.product_id FROM product_suppliers AS T1 JOIN products AS T2 ON T1.product_id  =  T2.product_id WHERE T1.supplier_id  =  2 AND T2.product_price  >  (SELECT avg(product_price) FROM products)

Question: What are the ids of products from the supplier with id 2, which are more expensive than the average price across all products?
SQL: SELECT T1.product_id FROM product_suppliers AS T1 JOIN products AS T2 ON T1.product_id  =  T2.product_id WHERE T1.supplier_id  =  2 AND T2.product_price  >  (SELECT avg(product_price) FROM products)

Question: What is the id and name of the department store that has both marketing and managing department?
SQL: SELECT T2.dept_store_id ,  T2.store_name FROM departments AS T1 JOIN department_stores AS T2 ON T1.dept_store_id  =  T2.dept_store_id WHERE T1.department_name  =  "marketing" INTERSECT SELECT T2.dept_store_id ,  T2.store_name FROM departments AS T1 JOIN department_stores AS T2 ON T1.dept_store_id  =  T2.dept_store_id WHERE T1.department_name  =  "managing"

Question: What are the ids and names of department stores with both marketing and managing departments?
SQL: SELECT T2.dept_store_id ,  T2.store_name FROM departments AS T1 JOIN department_stores AS T2 ON T1.dept_store_id  =  T2.dept_store_id WHERE T1.department_name  =  "marketing" INTERSECT SELECT T2.dept_store_id ,  T2.store_name FROM departments AS T1 JOIN department_stores AS T2 ON T1.dept_store_id  =  T2.dept_store_id WHERE T1.department_name  =  "managing"

Question: What are the ids of the two department store chains with the largest number of department stores?
SQL: SELECT dept_store_chain_id FROM department_stores GROUP BY dept_store_chain_id ORDER BY count(*) DESC LIMIT 2

Question: Return the ids of the two department store chains with the most department stores.
SQL: SELECT dept_store_chain_id FROM department_stores GROUP BY dept_store_chain_id ORDER BY count(*) DESC LIMIT 2

Question: What is the id of the department with the least number of staff?
SQL: SELECT department_id FROM staff_department_assignments GROUP BY department_id ORDER BY count(*) LIMIT 1

Question: Return the id of the department with the fewest staff assignments.
SQL: SELECT department_id FROM staff_department_assignments GROUP BY department_id ORDER BY count(*) LIMIT 1

Question: For each product type, return the maximum and minimum price.
SQL: SELECT product_type_code ,  max(product_price) ,  min(product_price) FROM products GROUP BY product_type_code

Question: What are the maximum and minimum product prices for each product type?
SQL: SELECT product_type_code ,  max(product_price) ,  min(product_price) FROM products GROUP BY product_type_code

Question: Find the product type whose average price is higher than the average price of all products.
SQL: SELECT product_type_code FROM products GROUP BY product_type_code HAVING avg(product_price)  >  (SELECT avg(product_price) FROM products)

Question: What is the code of the product type with an average price higher than the average price of all products?
SQL: SELECT product_type_code FROM products GROUP BY product_type_code HAVING avg(product_price)  >  (SELECT avg(product_price) FROM products)