human
Schema:
Investors (investor_id, Investor_details)
Lots (lot_id, investor_id, lot_details)
Ref_Transaction_Types (transaction_type_code, transaction_type_description)
Transactions (transaction_id, investor_id, transaction_type_code, date_of_transaction, amount_of_transaction, share_count, other_details)
Sales (sales_transaction_id, sales_details)
Purchases (purchase_transaction_id, purchase_details)
Transactions_Lots (transaction_id, lot_id)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the average share count of transactions for different investors.
assistant
SELECT investor_id , avg(share_count) FROM TRANSACTIONS GROUP BY investor_id
human
Show all date and share count of transactions.
assistant
SELECT date_of_transaction , share_count FROM TRANSACTIONS
human
Show the transaction type descriptions and dates if the share count is smaller than 10.
assistant
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
human
Show the minimum amount of transactions whose type code is 'PUR' and whose share count is bigger than 50.
assistant
SELECT min(amount_of_transaction) FROM TRANSACTIONS WHERE transaction_type_code = 'PUR' AND share_count > 50
human
Show the maximum share count of transactions where the amount is smaller than 10000
assistant
SELECT max(share_count) FROM TRANSACTIONS WHERE amount_of_transaction < 10000
human
Show the maximum and minimum share count of different transaction types.
assistant
SELECT transaction_type_code , MAX( share_count ) , MIN( share_count ) FROM TRANSACTIONS GROUP BY transaction_type_code ;