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 share count of transactions where the amount is smaller than 10000
assistant
SELECT max(share_count) FROM TRANSACTIONS WHERE amount_of_transaction < 10000
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
What is the total share of transactions?
assistant
SELECT sum(share_count) FROM TRANSACTIONS
human
What are the dates of transactions with at least 100 share count or amount bigger than 100?
assistant
SELECT date_of_transaction FROM TRANSACTIONS WHERE share_count >= 100 OR amount_of_transaction >= 100
human
What are the lot details of lots associated with transactions whose share count is bigger than 100 and whose type code is 'PUR'?
assistant
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'
human
What are the lot details of lots associated with transactions with share count smaller than 50?
assistant
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 ;