CREATE TABLE Customers (
    customer_id NUMBER PRIMARY KEY,
    customer_first_name TEXT,
    customer_middle_initial TEXT,
    customer_last_name TEXT,
    gender TEXT,
    email_address TEXT,
    login_name TEXT,
    login_password TEXT,
    phone_number TEXT,
    town_city TEXT,
    state_county_province TEXT,
    country TEXT
);
CREATE TABLE Orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    date_order_placed TIME,
    order_details TEXT,
    FOREIGN KEY ("customer_id") REFERENCES Customers("customer_id")
);
CREATE TABLE Invoices (
    invoice_number NUMBER PRIMARY KEY,
    order_id NUMBER,
    invoice_date TIME,
    FOREIGN KEY ("order_id") REFERENCES Orders("order_id")
);
CREATE TABLE Accounts (
    account_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    date_account_opened TIME,
    account_name TEXT,
    other_account_details TEXT,
    FOREIGN KEY ("customer_id") REFERENCES Customers("customer_id")
);
CREATE TABLE Product_Categories (
    production_type_code TEXT PRIMARY KEY,
    product_type_description TEXT,
    vat_rating NUMBER
);
CREATE TABLE Products (
    product_id NUMBER PRIMARY KEY,
    parent_product_id NUMBER,
    production_type_code TEXT,
    unit_price NUMBER,
    product_name TEXT,
    product_color TEXT,
    product_size TEXT,
    FOREIGN KEY ("production_type_code") REFERENCES Product_Categories("production_type_code")
);
CREATE TABLE Financial_Transactions (
    transaction_id NUMBER,
    account_id NUMBER,
    invoice_number NUMBER,
    transaction_type TEXT,
    transaction_date TIME,
    transaction_amount NUMBER,
    transaction_comment TEXT,
    other_transaction_details TEXT,
    FOREIGN KEY ("account_id") REFERENCES Accounts("account_id"),
    FOREIGN KEY ("invoice_number") REFERENCES Invoices("invoice_number")
);
CREATE TABLE Order_Items (
    order_item_id NUMBER PRIMARY KEY,
    order_id NUMBER,
    product_id NUMBER,
    product_quantity TEXT,
    other_order_item_details TEXT,
    FOREIGN KEY ("order_id") REFERENCES Orders("order_id"),
    FOREIGN KEY ("product_id") REFERENCES Products("product_id")
);
CREATE TABLE Invoice_Line_Items (
    order_item_id NUMBER,
    invoice_number NUMBER,
    product_id NUMBER,
    product_title TEXT,
    product_quantity TEXT,
    product_price NUMBER,
    derived_product_cost NUMBER,
    derived_vat_payable NUMBER,
    derived_total_cost NUMBER,
    FOREIGN KEY ("product_id") REFERENCES Products("product_id"),
    FOREIGN KEY ("invoice_number") REFERENCES Invoices("invoice_number"),
    FOREIGN KEY ("order_item_id") REFERENCES Order_Items("order_item_id")
);

Question: What is the invoice number and invoice date for the invoice with most number of transactions?
SQL: SELECT T2.invoice_number ,  T2.invoice_date FROM Financial_transactions AS T1 JOIN Invoices AS T2 ON T1.invoice_number  =  T2.invoice_number GROUP BY T1.invoice_number ORDER BY count(*) DESC LIMIT 1

Question: What is the invoice number and invoice date corresponding to the invoice with the greatest number of transactions?
SQL: SELECT T2.invoice_number ,  T2.invoice_date FROM Financial_transactions AS T1 JOIN Invoices AS T2 ON T1.invoice_number  =  T2.invoice_number GROUP BY T1.invoice_number ORDER BY count(*) DESC LIMIT 1

Question: How many invoices do we have?
SQL: SELECT count(*) FROM Invoices

Question: Count the number of invoices.
SQL: SELECT count(*) FROM Invoices

Question: Show invoice dates and order id and details for all invoices.
SQL: SELECT T1.invoice_date ,  T1.order_id ,  T2.order_details FROM Invoices AS T1 JOIN Orders AS T2 ON T1.order_id  =  T2.order_id

Question: What are the invoice dates, order ids, and order details for all invoices?
SQL: SELECT T1.invoice_date ,  T1.order_id ,  T2.order_details FROM Invoices AS T1 JOIN Orders AS T2 ON T1.order_id  =  T2.order_id

Question: Show the order ids and the number of invoices for each order.
SQL: SELECT order_id ,  count(*) FROM Invoices GROUP BY order_id

Question: How many invoices correspond to each order id?
SQL: SELECT order_id ,  count(*) FROM Invoices GROUP BY order_id

Question: What is the order id and order details for the order more than two invoices.
SQL: SELECT T2.order_id ,  T2.order_details FROM Invoices AS T1 JOIN Orders AS T2 ON T1.order_id  =  T2.order_id GROUP BY T2.order_id HAVING count(*)  >  2

Question: Return the order ids and details for orderes with two or more invoices.
SQL: SELECT T2.order_id ,  T2.order_details FROM Invoices AS T1 JOIN Orders AS T2 ON T1.order_id  =  T2.order_id GROUP BY T2.order_id HAVING count(*)  >  2

Question: What is the customer last name, id and phone number with most number of orders?
SQL: SELECT T2.customer_last_name ,  T1.customer_id ,  T2.phone_number FROM Orders AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id ORDER BY count(*) DESC LIMIT 1

Question: Return the last name, id and phone number of the customer who has made the greatest number of orders.
SQL: SELECT T2.customer_last_name ,  T1.customer_id ,  T2.phone_number FROM Orders AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id ORDER BY count(*) DESC LIMIT 1

Question: Show all product names without an order.
SQL: SELECT product_name FROM Products EXCEPT SELECT T1.product_name FROM Products AS T1 JOIN Order_items AS T2 ON T1.product_id  =  T2.product_id

Question: What are the names of products that have never been ordered?
SQL: SELECT product_name FROM Products EXCEPT SELECT T1.product_name FROM Products AS T1 JOIN Order_items AS T2 ON T1.product_id  =  T2.product_id

Question: Show all product names and the total quantity ordered for each product name.
SQL: SELECT T2.product_name ,  sum(T1.product_quantity) FROM Order_items AS T1 JOIN Products AS T2 ON T1.product_id  =  T2.product_id GROUP BY T2.product_name

Question: What are the different product names, and what is the sum of quantity ordered for each product?
SQL: SELECT T2.product_name ,  sum(T1.product_quantity) FROM Order_items AS T1 JOIN Products AS T2 ON T1.product_id  =  T2.product_id GROUP BY T2.product_name

Question: Show the order ids and the number of items in each order.
SQL: SELECT order_id ,  count(*) FROM Order_items GROUP BY order_id

Question: How many order items correspond to each order id?
SQL: SELECT order_id ,  count(*) FROM Order_items GROUP BY order_id

Question: Show the product ids and the number of unique orders containing each product.
SQL: SELECT product_id ,  count(DISTINCT order_id) FROM Order_items GROUP BY product_id

Question: How many distinct order ids correspond to each product?
SQL: SELECT product_id ,  count(DISTINCT order_id) FROM Order_items GROUP BY product_id

Question: Show all product names and the number of customers having an order on each product.
SQL: SELECT T2.product_name ,  count(*) FROM Order_items AS T1 JOIN Products AS T2 ON T1.product_id  =  T2.product_id JOIN Orders AS T3 ON T3.order_id  =  T1.order_id GROUP BY T2.product_name

Question: What are teh names of the different products, as well as the number of customers who have ordered each product.
SQL: SELECT T2.product_name ,  count(*) FROM Order_items AS T1 JOIN Products AS T2 ON T1.product_id  =  T2.product_id JOIN Orders AS T3 ON T3.order_id  =  T1.order_id GROUP BY T2.product_name

Question: Show order ids and the number of products in each order.
SQL: SELECT order_id ,  count(DISTINCT product_id) FROM Order_items GROUP BY order_id

Question: How many different products correspond to each order id?
SQL: SELECT order_id ,  count(DISTINCT product_id) FROM Order_items GROUP BY order_id

Question: Show order ids and the total quantity in each order.
SQL: SELECT order_id ,  sum(product_quantity) FROM Order_items GROUP BY order_id

Question: Give the order ids for all orders, as well as the total product quantity in each.
SQL: SELECT order_id ,  sum(product_quantity) FROM Order_items GROUP BY order_id