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: What is the id of the most recent order?
SQL: SELECT order_id FROM orders ORDER BY date_order_placed DESC LIMIT 1

Question: Find the id of the order made most recently.
SQL: SELECT order_id FROM orders ORDER BY date_order_placed DESC LIMIT 1

Question: what are the order id and customer id of the oldest order?
SQL: SELECT order_id ,  customer_id FROM orders ORDER BY date_order_placed LIMIT 1

Question: Find the order id and customer id associated with the oldest order.
SQL: SELECT order_id ,  customer_id FROM orders ORDER BY date_order_placed LIMIT 1

Question: Find the id of the order whose shipment tracking number is "3452".
SQL: SELECT order_id FROM shipments WHERE shipment_tracking_number = "3452"

Question: Which order's shipment tracking number is "3452"? Give me the id of the order.
SQL: SELECT order_id FROM shipments WHERE shipment_tracking_number = "3452"

Question: Find the ids of all the order items whose product id is 11.
SQL: SELECT order_item_id FROM order_items WHERE product_id = 11

Question: Find all the order items whose product id is 11. What are the order item ids?
SQL: SELECT order_item_id FROM order_items WHERE product_id = 11

Question: List the name of all the distinct customers who have orders with status "Packing".
SQL: SELECT DISTINCT T1.customer_name FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T2.order_status = "Packing"

Question: Which customers have orders with status "Packing"? Give me the customer names.
SQL: SELECT DISTINCT T1.customer_name FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T2.order_status = "Packing"

Question: Find the details of all the distinct customers who have orders with status "On Road".
SQL: SELECT DISTINCT T1.customer_details FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T2.order_status = "On Road"

Question: What are the distinct customers who have orders with status "On Road"? Give me the customer details?
SQL: SELECT DISTINCT T1.customer_details FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T2.order_status = "On Road"

Question: What is the name of the customer who has 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: Which customer made the most orders? Find the customer name.
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: What is the customer id of the customer who has the most orders?
SQL: SELECT T1.customer_id 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 id of the customer who made the most orders.
SQL: SELECT T1.customer_id 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: Give me a list of id and status of orders which belong to the customer named "Jeramie".
SQL: SELECT T2.order_id ,  T2.order_status FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T1.customer_name = "Jeramie"

Question: Which orders are made by the customer named "Jeramie"? Give me the order ids and status.
SQL: SELECT T2.order_id ,  T2.order_status FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T1.customer_name = "Jeramie"

Question: Find the dates of orders which belong to the customer named "Jeramie".
SQL: SELECT T2.date_order_placed FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T1.customer_name = "Jeramie"

Question: What are the dates of the orders made by the customer named "Jeramie"?
SQL: SELECT T2.date_order_placed FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T1.customer_name = "Jeramie"

Question: Give me the names of customers who have placed orders between 2009-01-01 and 2010-01-01.
SQL: SELECT T1.customer_name FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T2.date_order_placed >= "2009-01-01" AND T2.date_order_placed <= "2010-01-01"

Question: Which customers made orders between 2009-01-01 and 2010-01-01? Find their names.
SQL: SELECT T1.customer_name FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T2.date_order_placed >= "2009-01-01" AND T2.date_order_placed <= "2010-01-01"

Question: Give me a list of distinct product ids from orders placed between 1975-01-01 and 1976-01-01?
SQL: SELECT DISTINCT T2.product_id FROM orders AS T1 JOIN order_items AS T2 ON T1.order_id = T2.order_id WHERE T1.date_order_placed >= "1975-01-01" AND T1.date_order_placed <= "1976-01-01"

Question: What are the distinct ids of products ordered between 1975-01-01 and 1976-01-01??
SQL: SELECT DISTINCT T2.product_id FROM orders AS T1 JOIN order_items AS T2 ON T1.order_id = T2.order_id WHERE T1.date_order_placed >= "1975-01-01" AND T1.date_order_placed <= "1976-01-01"

Question: Find the names of the customers who have order status both "On Road" and "Shipped".
SQL: SELECT T1.customer_name 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_name FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T2.order_status = "Shipped"

Question: Which customers have both "On Road" and "Shipped" as order status? List the customer names.
SQL: SELECT T1.customer_name 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_name FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T2.order_status = "Shipped"

Question: Find the id of the customers who have order status both "On Road" and "Shipped".
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"