Database: tracking_share_transactions

CREATE TABLE `Investors` (
`investor_id` INTEGER PRIMARY KEY,
`Investor_details` VARCHAR(255)
);
CREATE TABLE `Lots` (
`lot_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`lot_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` )
);
CREATE TABLE `Ref_Transaction_Types` (
`transaction_type_code` VARCHAR(10) PRIMARY KEY,
`transaction_type_description` VARCHAR(80) NOT NULL
);
CREATE TABLE `Transactions` (
`transaction_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`transaction_type_code` VARCHAR(10) NOT NULL,
`date_of_transaction` DATETIME,
`amount_of_transaction` DECIMAL(19,4),
`share_count` VARCHAR(40),
`other_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` ),FOREIGN KEY (`transaction_type_code` ) REFERENCES `Ref_Transaction_Types`(`transaction_type_code` )
);
CREATE TABLE `Sales` (
`sales_transaction_id` INTEGER PRIMARY KEY,
`sales_details` VARCHAR(255),
FOREIGN KEY (`sales_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
);
CREATE TABLE `Purchases` (
`purchase_transaction_id` INTEGER NOT NULL,
`purchase_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`purchase_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
);
CREATE TABLE `Transactions_Lots` (
`transaction_id` INTEGER NOT NULL,
`lot_id` INTEGER NOT NULL,
FOREIGN KEY (`lot_id` ) REFERENCES `Lots`(`lot_id` ),
FOREIGN KEY (`transaction_id` ) REFERENCES `Transactions`(`transaction_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

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