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: Show the description of the transaction type that occurs most frequently.
SQL: SELECT T1.transaction_type_description FROM Ref_Transaction_Types AS T1 JOIN TRANSACTIONS AS T2 ON T1.transaction_type_code  =  T2.transaction_type_code GROUP BY T1.transaction_type_code ORDER BY COUNT(*) DESC LIMIT 1

Question: Show the id and details of the investor that has the largest number of transactions.
SQL: SELECT T2.investor_id ,  T1.Investor_details FROM INVESTORS AS T1 JOIN TRANSACTIONS AS T2 ON T1.investor_id  =  T2.investor_id GROUP BY T2.investor_id ORDER BY COUNT(*) DESC LIMIT 1

Question: Show the id and details for the investors who have the top 3 number of transactions.
SQL: SELECT T2.investor_id ,  T1.Investor_details FROM INVESTORS AS T1 JOIN TRANSACTIONS AS T2 ON T1.investor_id  =  T2.investor_id GROUP BY T2.investor_id ORDER BY COUNT(*) DESC LIMIT 3

Question: Show the ids of the investors who have at least two transactions.
SQL: SELECT T2.investor_id FROM INVESTORS AS T1 JOIN TRANSACTIONS AS T2 ON T1.investor_id  =  T2.investor_id GROUP BY T2.investor_id HAVING COUNT(*)  >=  2

Question: Show the ids and details of the investors who have at least two transactions with type code "SALE".
SQL: SELECT T2.investor_id ,  T1.Investor_details FROM INVESTORS AS T1 JOIN TRANSACTIONS AS T2 ON T1.investor_id  =  T2.investor_id WHERE T2.transaction_type_code  =  "SALE" GROUP BY T2.investor_id HAVING COUNT(*)  >=  2

Question: What are the dates of transactions with at least 100 share count or amount bigger than 100?
SQL: SELECT date_of_transaction FROM TRANSACTIONS WHERE share_count  >=  100 OR amount_of_transaction  >=  100

Question: What are the details of all sales and purchases?
SQL: SELECT sales_details FROM sales UNION SELECT purchase_details FROM purchases

Question: What are the details of the lots which are not used in any transactions?
SQL: SELECT lot_details FROM Lots EXCEPT SELECT T1.lot_details FROM Lots AS T1 JOIN transactions_lots AS T2 ON T1.lot_id  =  T2.lot_id