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: Show first name and id for all customers with at least 2 accounts.
SQL: SELECT T2.customer_first_name ,  T1.customer_id FROM Accounts AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id HAVING count(*)  >=  2

Question: What are the first names and ids for customers who have two or more accounts?
SQL: SELECT T2.customer_first_name ,  T1.customer_id FROM Accounts AS T1 JOIN Customers AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id HAVING count(*)  >=  2

Question: Show the number of customers.
SQL: SELECT count(*) FROM Customers

Question: Count the number of customers.
SQL: SELECT count(*) FROM Customers

Question: Show the number of customers for each gender.
SQL: SELECT gender ,  count(*) FROM Customers GROUP BY gender

Question: How many customers are there of each gender?
SQL: SELECT gender ,  count(*) FROM Customers GROUP BY gender

Question: How many transactions do we have?
SQL: SELECT count(*) FROM Financial_transactions

Question: Count the number of transactions.
SQL: SELECT count(*) FROM Financial_transactions

Question: How many transaction does each account have? Show the number and account id.
SQL: SELECT count(*) ,  account_id FROM Financial_transactions

Question: Count the number of financial transactions that correspond to each account id.
SQL: SELECT count(*) ,  account_id FROM Financial_transactions

Question: How many transaction does account with name 337 have?
SQL: SELECT count(*) FROM Financial_transactions AS T1 JOIN Accounts AS T2 ON T1.account_id  =  T2.account_id WHERE T2.account_name  =  "337"

Question: Count the number of financial transactions that the account with the name 337 has.
SQL: SELECT count(*) FROM Financial_transactions AS T1 JOIN Accounts AS T2 ON T1.account_id  =  T2.account_id WHERE T2.account_name  =  "337"

Question: What is the average, minimum, maximum, and total transaction amount?
SQL: SELECT avg(transaction_amount) ,  min(transaction_amount) ,  max(transaction_amount) ,   sum(transaction_amount) FROM Financial_transactions

Question: Return the average, minimum, maximum, and total transaction amounts.
SQL: SELECT avg(transaction_amount) ,  min(transaction_amount) ,  max(transaction_amount) ,   sum(transaction_amount) FROM Financial_transactions

Question: Show ids for all transactions whose amounts are greater than the average.
SQL: SELECT transaction_id FROM Financial_transactions WHERE transaction_amount  >  (SELECT avg(transaction_amount) FROM Financial_transactions)

Question: What are the ids for transactions that have an amount greater than the average amount of a transaction?
SQL: SELECT transaction_id FROM Financial_transactions WHERE transaction_amount  >  (SELECT avg(transaction_amount) FROM Financial_transactions)

Question: Show the transaction types and the total amount of transactions.
SQL: SELECT transaction_type ,  sum(transaction_amount) FROM Financial_transactions GROUP BY transaction_type

Question: What are total transaction amounts for each transaction type?
SQL: SELECT transaction_type ,  sum(transaction_amount) FROM Financial_transactions GROUP BY transaction_type

Question: Show the account name, id and the number of transactions for each account.
SQL: SELECT T2.account_name ,  T1.account_id ,  count(*) FROM Financial_transactions AS T1 JOIN Accounts AS T2 ON T1.account_id  =  T2.account_id GROUP BY T1.account_id

Question: Return the names and ids of each account, as well as the number of transactions.
SQL: SELECT T2.account_name ,  T1.account_id ,  count(*) FROM Financial_transactions AS T1 JOIN Accounts AS T2 ON T1.account_id  =  T2.account_id GROUP BY T1.account_id

Question: Show the account id with most number of transactions.
SQL: SELECT account_id FROM Financial_transactions GROUP BY account_id ORDER BY count(*) DESC LIMIT 1

Question: What is the id of the account with the most transactions?
SQL: SELECT account_id FROM Financial_transactions GROUP BY account_id ORDER BY count(*) DESC LIMIT 1

Question: Show the account id and name with at least 4 transactions.
SQL: SELECT T1.account_id ,  T2.account_name FROM Financial_transactions AS T1 JOIN Accounts AS T2 ON T1.account_id  =  T2.account_id GROUP BY T1.account_id HAVING count(*)  >=  4

Question: What are the ids and names of accounts with 4 or more transactions?
SQL: SELECT T1.account_id ,  T2.account_name FROM Financial_transactions AS T1 JOIN Accounts AS T2 ON T1.account_id  =  T2.account_id GROUP BY T1.account_id HAVING count(*)  >=  4

Question: Show all product sizes.
SQL: SELECT DISTINCT product_size FROM Products

Question: What are the different product sizes?
SQL: SELECT DISTINCT product_size FROM Products

Question: Show all product colors.
SQL: SELECT DISTINCT product_color FROM Products

Question: What are the different product colors?
SQL: SELECT DISTINCT product_color FROM Products

Question: Show the invoice number and the number of transactions for each invoice.
SQL: SELECT invoice_number ,  count(*) FROM Financial_transactions GROUP BY invoice_number

Question: How many transactions correspond to each invoice number?
SQL: SELECT invoice_number ,  count(*) FROM Financial_transactions GROUP BY invoice_number