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
Return the lot details and investor ids.
assistant
SELECT lot_details , investor_id FROM LOTS
human
Show the average amount of transactions for different lots, ordered by average amount of transactions.
assistant
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)
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
human
Show the average amount of transactions for different lots.
assistant
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
human
Return the lot details of lots that belong to investors with details 'l'?
assistant
SELECT T2.lot_details FROM INVESTORS AS T1 JOIN LOTS AS T2 ON T1.investor_id = T2.investor_id WHERE T1.Investor_details = 'l'
human
Show all distinct lot details.
assistant
SELECT DISTINCT lot_details FROM LOTS ;