CREATE TABLE Investors (
    investor_id NUMBER PRIMARY KEY,
    Investor_details TEXT
);
CREATE TABLE Lots (
    lot_id NUMBER PRIMARY KEY,
    investor_id NUMBER,
    lot_details TEXT,
    FOREIGN KEY ("investor_id") REFERENCES Investors("investor_id")
);
CREATE TABLE Ref_Transaction_Types (
    transaction_type_code TEXT PRIMARY KEY,
    transaction_type_description TEXT
);
CREATE TABLE Transactions (
    transaction_id NUMBER PRIMARY KEY,
    investor_id NUMBER,
    transaction_type_code TEXT,
    date_of_transaction TIME,
    amount_of_transaction NUMBER,
    share_count TEXT,
    other_details TEXT,
    FOREIGN KEY ("transaction_type_code") REFERENCES Ref_Transaction_Types("transaction_type_code"),
    FOREIGN KEY ("investor_id") REFERENCES Investors("investor_id")
);
CREATE TABLE Sales (
    sales_transaction_id NUMBER PRIMARY KEY,
    sales_details TEXT,
    FOREIGN KEY ("sales_transaction_id") REFERENCES Transactions("transaction_id")
);
CREATE TABLE Purchases (
    purchase_transaction_id NUMBER,
    purchase_details TEXT,
    FOREIGN KEY ("purchase_transaction_id") REFERENCES Transactions("transaction_id")
);
CREATE TABLE Transactions_Lots (
    transaction_id NUMBER,
    lot_id NUMBER,
    FOREIGN KEY ("transaction_id") REFERENCES Transactions("transaction_id"),
    FOREIGN KEY ("lot_id") REFERENCES Lots("lot_id")
);

Question: Find the number of investors in total.
SQL: SELECT count(*) FROM INVESTORS

Question: Show all investor details.
SQL: SELECT Investor_details FROM INVESTORS

Question: Show all distinct lot details.
SQL: SELECT DISTINCT lot_details FROM LOTS

Question: Show the maximum amount of transaction.
SQL: SELECT max(amount_of_transaction) FROM TRANSACTIONS

Question: Show all date and share count of transactions.
SQL: SELECT date_of_transaction ,  share_count FROM TRANSACTIONS

Question: What is the total share of transactions?
SQL: SELECT sum(share_count) FROM TRANSACTIONS

Question: Show all transaction ids with transaction code 'PUR'.
SQL: SELECT transaction_id FROM TRANSACTIONS WHERE transaction_type_code  =  'PUR'

Question: Show all dates of transactions whose type code is "SALE".
SQL: SELECT date_of_transaction FROM TRANSACTIONS WHERE transaction_type_code  =  "SALE"

Question: Show the average amount of transactions with type code "SALE".
SQL: SELECT avg(amount_of_transaction) FROM TRANSACTIONS WHERE transaction_type_code  =  "SALE"

Question: Show the description of transaction type with code "PUR".
SQL: SELECT transaction_type_description FROM Ref_Transaction_Types WHERE transaction_type_code	  =  "PUR"

Question: Show the minimum amount of transactions whose type code is "PUR" and whose share count is bigger than 50.
SQL: SELECT min(amount_of_transaction) FROM TRANSACTIONS WHERE transaction_type_code  =  "PUR" AND share_count  >  50

Question: Show the maximum share count of transactions where the amount is smaller than 10000
SQL: SELECT max(share_count) FROM TRANSACTIONS WHERE amount_of_transaction  <  10000

Question: Show the dates of transactions if the share count is bigger than 100 or the amount is bigger than 1000.
SQL: SELECT date_of_transaction FROM TRANSACTIONS WHERE share_count  >  100 OR amount_of_transaction  >  1000

Question: Show the transaction type descriptions and dates if the share count is smaller than 10.
SQL: SELECT T1.transaction_type_description ,  T2.date_of_transaction FROM Ref_Transaction_Types AS T1 JOIN TRANSACTIONS AS T2 ON T1.transaction_type_code  =  T2.transaction_type_code WHERE T2.share_count  <  10

Question: Show details of all investors if they make any transaction with share count greater than 100.
SQL: SELECT T1.Investor_details FROM  INVESTORS AS T1 JOIN TRANSACTIONS AS T2 ON T1.investor_id  =  T2.investor_id WHERE T2.share_count  >  100

Question: How many distinct transaction types are used in the transactions?
SQL: SELECT COUNT(DISTINCT transaction_type_code) FROM TRANSACTIONS

Question: Return the lot details and investor ids.
SQL: SELECT lot_details ,  investor_id FROM LOTS

Question: Return the lot details of lots that belong to investors with details "l"?
SQL: SELECT T2.lot_details FROM INVESTORS AS T1 JOIN LOTS AS T2 ON  T1.investor_id  =  T2.investor_id WHERE T1.Investor_details  =  "l"

Question: What are the purchase details of transactions with amount bigger than 10000?
SQL: SELECT T1.purchase_details FROM PURCHASES AS T1 JOIN TRANSACTIONS AS T2 ON T1.purchase_transaction_id  =  T2.transaction_id WHERE T2.amount_of_transaction  >  10000

Question: What are the sale details and dates of transactions with amount smaller than 3000?
SQL: SELECT T1.sales_details ,  T2.date_of_transaction FROM SALES AS T1 JOIN TRANSACTIONS AS T2 ON T1.sales_transaction_id  =  T2.transaction_id WHERE T2.amount_of_transaction  <  3000

Question: What are the lot details of lots associated with transactions with share count smaller than 50?
SQL: SELECT T1.lot_details FROM LOTS AS T1 JOIN TRANSACTIONS_LOTS AS T2 ON T1.lot_id  =  T2.transaction_id JOIN TRANSACTIONS AS T3 ON T2.transaction_id  =  T3.transaction_id WHERE T3.share_count  <  50

Question: What are the lot details of lots associated with transactions whose share count is bigger than 100 and whose type code is "PUR"?
SQL: SELECT T1.lot_details FROM LOTS AS T1 JOIN TRANSACTIONS_LOTS AS T2 ON  T1.lot_id  =  T2.transaction_id JOIN TRANSACTIONS AS T3 ON T2.transaction_id  =  T3.transaction_id WHERE T3.share_count  >  100 AND T3.transaction_type_code  =  "PUR"

Question: Show the average transaction amount for different transaction types.
SQL: SELECT transaction_type_code ,  avg(amount_of_transaction) FROM TRANSACTIONS GROUP BY transaction_type_code

Question: Show the maximum and minimum share count of different transaction types.
SQL: SELECT transaction_type_code ,  max(share_count) ,  min(share_count) FROM TRANSACTIONS GROUP BY transaction_type_code

Question: Show the average share count of transactions for different investors.
SQL: SELECT investor_id ,  avg(share_count) FROM TRANSACTIONS GROUP BY investor_id

Question: Show the average share count of transactions each each investor, ordered by average share count.
SQL: SELECT investor_id ,  avg(share_count) FROM TRANSACTIONS GROUP BY investor_id ORDER BY avg(share_count)

Question: Show the average amount of transactions for different investors.
SQL: SELECT investor_id ,  avg(amount_of_transaction) FROM TRANSACTIONS GROUP BY investor_id

Question: Show the average amount of transactions for different lots.
SQL: SELECT T2.lot_id ,  avg(amount_of_transaction) FROM TRANSACTIONS AS T1 JOIN Transactions_Lots AS T2 ON T1.transaction_id  =  T2.transaction_id GROUP BY T2.lot_id

Question: Show the average amount of transactions for different lots, ordered by average amount of transactions.
SQL: SELECT T2.lot_id ,  avg(amount_of_transaction) FROM TRANSACTIONS AS T1 JOIN Transactions_Lots AS T2 ON T1.transaction_id  =  T2.transaction_id GROUP BY T2.lot_id ORDER BY avg(amount_of_transaction)

Question: Show the number of transactions with transaction type code "SALE" for different investors if it is larger than 0.
SQL: SELECT investor_id ,  COUNT(*) FROM TRANSACTIONS WHERE transaction_type_code  =  "SALE" GROUP BY investor_id

Question: Show the number of transactions for different investors.
SQL: SELECT investor_id ,  COUNT(*) FROM TRANSACTIONS GROUP BY investor_id

Question: Show the transaction type code that occurs the fewest times.
SQL: SELECT transaction_type_code FROM TRANSACTIONS GROUP BY transaction_type_code ORDER BY COUNT(*) ASC LIMIT 1

Question: Show the transaction type code that occurs the most frequently.
SQL: SELECT transaction_type_code FROM TRANSACTIONS GROUP BY transaction_type_code ORDER BY COUNT(*) DESC LIMIT 1