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 the ids of the top three products that were purchased in the largest amount?
SQL: SELECT product_id FROM product_suppliers ORDER BY total_amount_purchased DESC LIMIT 3

Question: Give the ids of the three products purchased in the largest amounts.
SQL: SELECT product_id FROM product_suppliers ORDER BY total_amount_purchased DESC LIMIT 3

Question: What are the product id and product type of the cheapest product?
SQL: SELECT product_id ,  product_type_code FROM products ORDER BY product_price LIMIT 1

Question: Give the id and product type of the product with the lowest price.
SQL: SELECT product_id ,  product_type_code FROM products ORDER BY product_price LIMIT 1

Question: Find the number of different product types.
SQL: SELECT count(DISTINCT product_type_code) FROM products

Question: Count the number of distinct product types.
SQL: SELECT count(DISTINCT product_type_code) FROM products

Question: Return the address of customer 10.
SQL: SELECT T1.address_details FROM addresses AS T1 JOIN customer_addresses AS T2 ON T1.address_id  =  T2.address_id WHERE T2.customer_id  =  10

Question: What is the address for the customer with id 10?
SQL: SELECT T1.address_details FROM addresses AS T1 JOIN customer_addresses AS T2 ON T1.address_id  =  T2.address_id WHERE T2.customer_id  =  10

Question: What are the staff ids and genders of all staffs whose job title is Department Manager?
SQL: SELECT T1.staff_id ,  T1.staff_gender FROM staff AS T1 JOIN staff_department_assignments AS T2 ON T1.staff_id  =  T2.staff_id WHERE T2.job_title_code  =  "Department Manager"

Question: Return the staff ids and genders for any staff with the title Department Manager.
SQL: SELECT T1.staff_id ,  T1.staff_gender FROM staff AS T1 JOIN staff_department_assignments AS T2 ON T1.staff_id  =  T2.staff_id WHERE T2.job_title_code  =  "Department Manager"

Question: For each payment method, return how many customers use it.
SQL: SELECT payment_method_code ,  count(*) FROM customers GROUP BY payment_method_code

Question: How many customers use each payment method?
SQL: SELECT payment_method_code ,  count(*) FROM customers GROUP BY payment_method_code

Question: What is the id of the product that was ordered the most often?
SQL: SELECT product_id FROM order_items GROUP BY product_id ORDER BY count(*) DESC LIMIT 1

Question: Give the product id for the product that was ordered most frequently.
SQL: SELECT product_id FROM order_items GROUP BY product_id ORDER BY count(*) DESC LIMIT 1

Question: What are the name, phone number and email address of the customer who made the largest number of orders?
SQL: SELECT T1.customer_name ,  T1.customer_phone ,  T1.customer_email FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T2.customer_id ORDER BY count(*) DESC LIMIT 1

Question: Return the name, phone number and email address for the customer with the most orders.
SQL: SELECT T1.customer_name ,  T1.customer_phone ,  T1.customer_email FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T2.customer_id ORDER BY count(*) DESC LIMIT 1

Question: What is the average price for each type of product?
SQL: SELECT product_type_code ,  avg(product_price) FROM products GROUP BY product_type_code

Question: Return the average price for each product type.
SQL: SELECT product_type_code ,  avg(product_price) FROM products GROUP BY product_type_code

Question: How many department stores does the store chain South have?
SQL: SELECT count(*) FROM department_stores AS T1 JOIN department_store_chain AS T2 ON T1.dept_store_chain_id  =  T2.dept_store_chain_id WHERE T2.dept_store_chain_name  =  "South"

Question: Count the number of stores the chain South has.
SQL: SELECT count(*) FROM department_stores AS T1 JOIN department_store_chain AS T2 ON T1.dept_store_chain_id  =  T2.dept_store_chain_id WHERE T2.dept_store_chain_name  =  "South"

Question: What is the name and job title of the staff who was assigned the latest?
SQL: SELECT T1.staff_name ,  T2.job_title_code FROM staff AS T1 JOIN staff_department_assignments AS T2 ON T1.staff_id  =  T2.staff_id ORDER BY T2.date_assigned_to DESC LIMIT 1

Question: Return the name and job title of the staff with the latest date assigned.
SQL: SELECT T1.staff_name ,  T2.job_title_code FROM staff AS T1 JOIN staff_department_assignments AS T2 ON T1.staff_id  =  T2.staff_id ORDER BY T2.date_assigned_to DESC LIMIT 1

Question: Give me the product type, name and price for all the products supplied by supplier id 3.
SQL: SELECT T2.product_type_code ,  T2.product_name ,  T2.product_price FROM product_suppliers AS T1 JOIN products AS T2 ON T1.product_id  =  T2.product_id WHERE T1.supplier_id  =  3

Question: Return the product type, name, and price for products supplied by supplier 3.
SQL: SELECT T2.product_type_code ,  T2.product_name ,  T2.product_price FROM product_suppliers AS T1 JOIN products AS T2 ON T1.product_id  =  T2.product_id WHERE T1.supplier_id  =  3