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 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
human
Show the transaction type code that occurs the most frequently.
assistant
SELECT transaction_type_code FROM TRANSACTIONS GROUP BY transaction_type_code ORDER BY COUNT(*) DESC LIMIT 1
human
Show the transaction type code that occurs the fewest times.
assistant
SELECT transaction_type_code FROM TRANSACTIONS GROUP BY transaction_type_code ORDER BY COUNT(*) ASC LIMIT 1
human
What are the details of the lots which are not used in any transactions?
assistant
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
human
Show the description of the transaction type that occurs most frequently.
assistant
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
human
How many distinct transaction types are used in the transactions?
assistant
SELECT COUNT( DISTINCT transaction_type_code ) FROM TRANSACTIONS ;