lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
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
Show all dates of transactions whose type code is "SALE".

### SQL
SELECT date_of_transaction FROM TRANSACTIONS WHERE transaction_type_code  =  "SALE"
### Database Schema
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
Show the average amount of transactions with type code "SALE".

### SQL
SELECT avg(amount_of_transaction) FROM TRANSACTIONS WHERE transaction_type_code  =  "SALE"
### Database Schema
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
Show the description of transaction type with code "PUR".

### SQL
SELECT transaction_type_description FROM Ref_Transaction_Types WHERE transaction_type_code	  =  "PUR"
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
How many distinct transaction types are used in the transactions?

### SQL
SELECT COUNT(DISTINCT transaction_type_code) FROM TRANSACTIONS
### Database Schema
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
Return the lot details and investor ids.

### SQL
SELECT lot_details ,  investor_id FROM LOTS
### Database Schema
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
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"
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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"
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
Show the average share count of transactions for different investors.

### SQL
SELECT investor_id ,  avg(share_count) FROM TRANSACTIONS GROUP BY investor_id
### Database Schema
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
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)
### Database Schema
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
Show the average amount of transactions for different investors.

### SQL
SELECT investor_id ,  avg(amount_of_transaction) FROM TRANSACTIONS GROUP BY investor_id
### Database Schema
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
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
### Database Schema
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
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)
### Database Schema
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
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
### Database Schema
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
Show the number of transactions for different investors.

### SQL
SELECT investor_id ,  COUNT(*) FROM TRANSACTIONS GROUP BY investor_id
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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