Database schema:
CREATE TABLE Products (
  product_id NUMBER PRIMARY KEY,
  parent_product_id NUMBER,
  product_name TEXT,
  product_price NUMBER,
  product_color TEXT,
  product_size TEXT,
  product_description TEXT
);
CREATE TABLE Customers (
  customer_id NUMBER PRIMARY KEY,
  gender_code TEXT,
  customer_first_name TEXT,
  customer_middle_initial TEXT,
  customer_last_name TEXT,
  email_address TEXT,
  login_name TEXT,
  login_password TEXT,
  phone_number TEXT,
  address_line_1 TEXT,
  town_city TEXT,
  county TEXT,
  country TEXT
);
CREATE TABLE Customer_Payment_Methods (
  customer_id NUMBER,
  payment_method_code TEXT,
  FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);
CREATE TABLE Invoices (
  invoice_number NUMBER PRIMARY KEY,
  invoice_status_code TEXT,
  invoice_date TIME
);
CREATE TABLE Orders (
  order_id NUMBER PRIMARY KEY,
  customer_id NUMBER,
  order_status_code TEXT,
  date_order_placed TIME,
  FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);
CREATE TABLE Order_Items (
  order_item_id NUMBER PRIMARY KEY,
  product_id NUMBER,
  order_id NUMBER,
  order_item_status_code TEXT,
  FOREIGN KEY (order_id) REFERENCES Orders(order_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);
CREATE TABLE Shipments (
  shipment_id NUMBER PRIMARY KEY,
  order_id NUMBER,
  invoice_number NUMBER,
  shipment_tracking_number TEXT,
  shipment_date TIME,
  FOREIGN KEY (order_id) REFERENCES Orders(order_id),
  FOREIGN KEY (invoice_number) REFERENCES Invoices(invoice_number)
);
CREATE TABLE Shipment_Items (
  shipment_id NUMBER PRIMARY KEY,
  order_item_id NUMBER,
  FOREIGN KEY (order_item_id) REFERENCES Order_Items(order_item_id),
  FOREIGN KEY (shipment_id) REFERENCES Shipments(shipment_id)
);

Question: What are the first names of all buyers and what products did they buy? List them in pairs.
SQL: SELECT T1.customer_first_name ,  T4.product_name FROM Customers AS T1 JOIN 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

Question: List the address, town and county information of the customers who live in the USA.
SQL: SELECT address_line_1 ,  town_city ,  county FROM Customers WHERE Country  =  'USA'

Question: How many different item status codes are there listed in ordered items?
SQL: SELECT count(DISTINCT order_item_status_code) FROM Order_items

Question: What are invoices status of all the orders which have not been shipped?
SQL: SELECT invoice_status_code FROM Invoices WHERE invoice_number NOT IN ( SELECT invoice_number FROM Shipments )

Question: what are all shipment tracking numbers and shipment dates?
SQL: SELECT shipment_tracking_number ,  shipment_date FROM Shipments

Question: For every order, how many products does it contain, and what are the orders' statuses and ids?
SQL: SELECT T1.order_id ,  T1.order_status_code ,  count(*) FROM Orders AS T1 JOIN Order_items AS T2 ON T1.order_id  =  T2.order_id GROUP BY T1.order_id

Question: List the id, first name and last name of the customers who both have placed more than 2 orders and have bought at least 3 items.
SQL: SELECT T1.customer_id ,  T1.customer_first_name ,  T1.customer_last_name FROM Customers AS T1 JOIN Orders AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id HAVING count(*)  >  2 INTERSECT SELECT T1.customer_id ,  T1.customer_first_name ,  T1.customer_last_name FROM Customers AS T1 JOIN Orders AS T2 ON T1.customer_id  =  T2.customer_id JOIN Order_items AS T3 ON T2.order_id  =  T3.order_id GROUP BY T1.customer_id HAVING count(*)  >= 3

Question: What is the number of products that have not been ordered yet?
SQL: SELECT count(*) FROM Products WHERE product_id NOT IN ( SELECT product_id FROM Order_items )

Question: What are the ids , names , prices , and colors of all products that have been listed in less than two orders ?
SQL: select t1.product_id ,  t1.product_name ,  t1.product_price ,  t1.product_color from products as t1 join order_items as t2 on t1.product_id  =  t2.product_id join orders as t3 on t2.order_id  =  t3.order_id group by t1.product_id having count(*) < 2

Question: How many item states are there in the orders?
SQL: SELECT count(DISTINCT order_item_status_code) FROM Order_items

Question: Which orders have at least 2 products on it? List the order id and date.
SQL: SELECT T1.order_id ,  T1.date_order_placed FROM Orders AS T1 JOIN Order_items AS T2 ON T1.order_id  =  T2.order_id GROUP BY T1.order_id HAVING count(*)  >=  2

Question: What are the names and colors of all products that have been shipped?
SQL: SELECT T1.product_name ,  T1.product_color FROM Products AS T1 JOIN Order_items AS T2 ON T1.product_id  =  T2.product_id JOIN Shipment_Items AS T3 ON T2.order_item_id  =  T3.order_item_id JOIN Shipments AS T4 ON T3.shipment_id  =  T4.shipment_id

Question: What is the price of all products being ordered on average?
SQL: SELECT avg(T1.product_price) FROM Products AS T1 JOIN Order_items AS T2 ON T1.product_id  =  T2.product_id

Question: What is the payment method that most customers use?
SQL: SELECT Payment_method_code FROM Customer_Payment_Methods GROUP BY Payment_method_code ORDER BY count(*) DESC LIMIT 1

Question: For all the products sold for more than 3 times, list their id and description.
SQL: SELECT T1.product_id ,  T1.product_description FROM Products AS T1 JOIN Order_items AS T2 ON T1.product_id  =  T2.product_id GROUP BY T1.product_id HAVING count(*)  >  3

Question: What are the prices and sizes of all products whose price is above the mean?
SQL: SELECT product_price ,  product_size FROM Products WHERE product_price  >  ( SELECT avg(product_price) FROM Products )