CREATE TABLE Customers (
    customer_id NUMBER PRIMARY KEY,
    customer_name TEXT,
    customer_details TEXT
);
CREATE TABLE Invoices (
    invoice_number NUMBER PRIMARY KEY,
    invoice_date TIME,
    invoice_details TEXT
);
CREATE TABLE Orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    order_status TEXT,
    date_order_placed TIME,
    order_details TEXT,
    FOREIGN KEY ("customer_id") REFERENCES Customers("customer_id")
);
CREATE TABLE Products (
    product_id NUMBER PRIMARY KEY,
    product_name TEXT,
    product_details TEXT
);
CREATE TABLE Order_Items (
    order_item_id NUMBER PRIMARY KEY,
    product_id NUMBER,
    order_id NUMBER,
    order_item_status TEXT,
    order_item_details TEXT,
    FOREIGN KEY ("product_id") REFERENCES Products("product_id"),
    FOREIGN KEY ("order_id") REFERENCES Orders("order_id")
);
CREATE TABLE Shipments (
    shipment_id NUMBER PRIMARY KEY,
    order_id NUMBER,
    invoice_number NUMBER,
    shipment_tracking_number TEXT,
    shipment_date TIME,
    other_shipment_details TEXT,
    FOREIGN KEY ("invoice_number") REFERENCES Invoices("invoice_number"),
    FOREIGN KEY ("order_id") REFERENCES Orders("order_id")
);
CREATE TABLE Shipment_Items (
    shipment_id NUMBER,
    order_item_id NUMBER,
    FOREIGN KEY ("shipment_id") REFERENCES Shipments("shipment_id"),
    FOREIGN KEY ("order_item_id") REFERENCES Order_Items("order_item_id")
);

Question: Which customers have both "On Road" and "Shipped" as order status? List the customer ids.
SQL: SELECT T1.customer_id FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T2.order_status = "On Road" INTERSECT SELECT T1.customer_id FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T2.order_status = "Shipped"

Question: When was the order placed whose shipment tracking number is 3452? Give me the date.
SQL: SELECT T1.date_order_placed FROM orders AS T1 JOIN shipments AS T2 ON T1.order_id = T2.order_id WHERE T2.shipment_tracking_number = 3452

Question: On which day was the order placed whose shipment tracking number is 3452?
SQL: SELECT T1.date_order_placed FROM orders AS T1 JOIN shipments AS T2 ON T1.order_id = T2.order_id WHERE T2.shipment_tracking_number = 3452

Question: What is the placement date of the order whose invoice number is 10?
SQL: SELECT T1.date_order_placed FROM orders AS T1 JOIN shipments AS T2 ON T1.order_id = T2.order_id WHERE T2.invoice_number = 10

Question: On what day was the order with invoice number 10 placed?
SQL: SELECT T1.date_order_placed FROM orders AS T1 JOIN shipments AS T2 ON T1.order_id = T2.order_id WHERE T2.invoice_number = 10

Question: List the count and id of each product in all the orders.
SQL: SELECT count(*) ,  T3.product_id FROM orders AS T1 JOIN order_items AS T2 JOIN products AS T3 ON T1.order_id = T2.order_id AND T2.product_id = T3.product_id GROUP BY T3.product_id

Question: For each product, return its id and the number of times it was ordered.
SQL: SELECT count(*) ,  T3.product_id FROM orders AS T1 JOIN order_items AS T2 JOIN products AS T3 ON T1.order_id = T2.order_id AND T2.product_id = T3.product_id GROUP BY T3.product_id

Question: List the name and count of each product in all orders.
SQL: SELECT T3.product_name ,  count(*) FROM orders AS T1 JOIN order_items AS T2 JOIN products AS T3 ON T1.order_id = T2.order_id AND T2.product_id = T3.product_id GROUP BY T3.product_id

Question: For each product, show its name and the number of times it was ordered.
SQL: SELECT T3.product_name ,  count(*) FROM orders AS T1 JOIN order_items AS T2 JOIN products AS T3 ON T1.order_id = T2.order_id AND T2.product_id = T3.product_id GROUP BY T3.product_id

Question: Find the ids of orders which are shipped after 2000-01-01.
SQL: SELECT order_id FROM shipments WHERE shipment_date > "2000-01-01"

Question: Which orders have shipment after 2000-01-01? Give me the order ids.
SQL: SELECT order_id FROM shipments WHERE shipment_date > "2000-01-01"

Question: Find the id of the order which is shipped most recently.
SQL: SELECT order_id FROM shipments WHERE shipment_date  =  (SELECT max(shipment_date) FROM shipments)

Question: Which order has the most recent shipment? Give me the order id.
SQL: SELECT order_id FROM shipments WHERE shipment_date  =  (SELECT max(shipment_date) FROM shipments)

Question: List the names of all distinct products in alphabetical order.
SQL: SELECT DISTINCT product_name FROM products ORDER BY product_name

Question: Sort all the distinct products in alphabetical order.
SQL: SELECT DISTINCT product_name FROM products ORDER BY product_name

Question: List the ids of all distinct orders ordered by placed date.
SQL: SELECT DISTINCT order_id FROM orders ORDER BY date_order_placed

Question: What are ids of the all distinct orders, sorted by placement date?
SQL: SELECT DISTINCT order_id FROM orders ORDER BY date_order_placed

Question: What is the id of the order which has the most items?
SQL: SELECT T1.order_id FROM orders AS T1 JOIN order_items AS T2 ON T1.order_id = T2.order_id GROUP BY T1.order_id ORDER BY count(*) DESC LIMIT 1

Question: Which order deals with the most items? Return the order id.
SQL: SELECT T1.order_id FROM orders AS T1 JOIN order_items AS T2 ON T1.order_id = T2.order_id GROUP BY T1.order_id ORDER BY count(*) DESC LIMIT 1

Question: What is the name of the customer who has the largest number of orders?
SQL: SELECT T1.customer_name FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id GROUP BY T1.customer_id ORDER BY count(*) DESC LIMIT 1

Question: Find the name of the customer who made the most orders.
SQL: SELECT T1.customer_name FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id GROUP BY T1.customer_id ORDER BY count(*) DESC LIMIT 1

Question: Find the invoice numbers which are created before 1989-09-03 or after 2007-12-25.
SQL: SELECT invoice_number FROM invoices WHERE invoice_date < "1989-09-03" OR invoice_date > "2007-12-25"

Question: What are the invoice numbers created before 1989-09-03 or after 2007-12-25?
SQL: SELECT invoice_number FROM invoices WHERE invoice_date < "1989-09-03" OR invoice_date > "2007-12-25"

Question: Find the distinct details of invoices which are created before 1989-09-03 or after 2007-12-25.
SQL: SELECT DISTINCT invoice_details FROM invoices WHERE invoice_date < "1989-09-03" OR invoice_date > "2007-12-25"

Question: What are the distinct details of invoices created before 1989-09-03 or after 2007-12-25?
SQL: SELECT DISTINCT invoice_details FROM invoices WHERE invoice_date < "1989-09-03" OR invoice_date > "2007-12-25"

Question: For each customer who has at least two orders, find the customer name and number of orders made.
SQL: SELECT T2.customer_name ,  count(*) FROM orders AS T1 JOIN customers AS T2 ON T1.customer_id = T2.customer_id GROUP BY T2.customer_id HAVING count(*)  >=  2

Question: Which customers have made at least two orders? Give me each customer name and number of orders made.
SQL: SELECT T2.customer_name ,  count(*) FROM orders AS T1 JOIN customers AS T2 ON T1.customer_id = T2.customer_id GROUP BY T2.customer_id HAVING count(*)  >=  2

Question: Find the name of the customers who have at most two orders.
SQL: SELECT T2.customer_name FROM orders AS T1 JOIN customers AS T2 ON T1.customer_id = T2.customer_id GROUP BY T2.customer_id HAVING count(*)  <=  2

Question: What are the names of the customers who have made two or less orders?
SQL: SELECT T2.customer_name FROM orders AS T1 JOIN customers AS T2 ON T1.customer_id = T2.customer_id GROUP BY T2.customer_id HAVING count(*)  <=  2