
CREATE TABLE "Investors" (
	investor_id INTEGER, 
	"Investor_details" VARCHAR(255), 
	PRIMARY KEY (investor_id)
)


CREATE TABLE "Lots" (
	lot_id INTEGER, 
	investor_id INTEGER NOT NULL, 
	lot_details VARCHAR(255), 
	PRIMARY KEY (lot_id), 
	FOREIGN KEY(investor_id) REFERENCES "Investors" (investor_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 "Ref_Transaction_Types" (
	transaction_type_code VARCHAR(10), 
	transaction_type_description VARCHAR(80) NOT NULL, 
	PRIMARY KEY (transaction_type_code)
)


CREATE TABLE "Sales" (
	sales_transaction_id INTEGER, 
	sales_details VARCHAR(255), 
	PRIMARY KEY (sales_transaction_id), 
	FOREIGN KEY(sales_transaction_id) REFERENCES "Transactions" (transaction_id)
)


CREATE TABLE "Transactions" (
	transaction_id INTEGER, 
	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), 
	PRIMARY KEY (transaction_id), 
	FOREIGN KEY(transaction_type_code) REFERENCES "Ref_Transaction_Types" (transaction_type_code), 
	FOREIGN KEY(investor_id) REFERENCES "Investors" (investor_id)
)


CREATE TABLE "Transactions_Lots" (
	transaction_id INTEGER NOT NULL, 
	lot_id INTEGER NOT NULL, 
	FOREIGN KEY(transaction_id) REFERENCES "Transactions" (transaction_id), 
	FOREIGN KEY(lot_id) REFERENCES "Lots" (lot_id)
)

CREATE TABLE "Investors" (
	investor_id INTEGER, 
	"Investor_details" VARCHAR(255), 
	PRIMARY KEY (investor_id)
)

/*
2 rows from Investors table:
investor_id	Investor_details
1	z
2	z
*/


CREATE TABLE "Lots" (
	lot_id INTEGER, 
	investor_id INTEGER NOT NULL, 
	lot_details VARCHAR(255), 
	PRIMARY KEY (lot_id), 
	FOREIGN KEY(investor_id) REFERENCES "Investors" (investor_id)
)

/*
2 rows from Lots table:
lot_id	investor_id	lot_details
1	13	r
2	16	z
*/


CREATE TABLE "Purchases" (
	purchase_transaction_id INTEGER NOT NULL, 
	purchase_details VARCHAR(255) NOT NULL, 
	FOREIGN KEY(purchase_transaction_id) REFERENCES "Transactions" (transaction_id)
)

/*
2 rows from Purchases table:
purchase_transaction_id	purchase_details
1	c
2	y
*/


CREATE TABLE "Ref_Transaction_Types" (
	transaction_type_code VARCHAR(10), 
	transaction_type_description VARCHAR(80) NOT NULL, 
	PRIMARY KEY (transaction_type_code)
)

/*
2 rows from Ref_Transaction_Types table:
transaction_type_code	transaction_type_description
SALE	Sale
PUR	Purchase
*/


CREATE TABLE "Sales" (
	sales_transaction_id INTEGER, 
	sales_details VARCHAR(255), 
	PRIMARY KEY (sales_transaction_id), 
	FOREIGN KEY(sales_transaction_id) REFERENCES "Transactions" (transaction_id)
)

/*
2 rows from Sales table:
sales_transaction_id	sales_details
1	x
2	o
*/


CREATE TABLE "Transactions" (
	transaction_id INTEGER, 
	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), 
	PRIMARY KEY (transaction_id), 
	FOREIGN KEY(transaction_type_code) REFERENCES "Ref_Transaction_Types" (transaction_type_code), 
	FOREIGN KEY(investor_id) REFERENCES "Investors" (investor_id)
)

/*
2 rows from Transactions table:
transaction_id	investor_id	transaction_type_code	date_of_transaction	amount_of_transaction	share_count	other_details
1	6	SALE	1988-09-16 19:02:51	302507.6996	8718572	None
2	18	PUR	1982-06-06 17:19:00	27.2570	9	None
*/


CREATE TABLE "Transactions_Lots" (
	transaction_id INTEGER NOT NULL, 
	lot_id INTEGER NOT NULL, 
	FOREIGN KEY(transaction_id) REFERENCES "Transactions" (transaction_id), 
	FOREIGN KEY(lot_id) REFERENCES "Lots" (lot_id)
)

/*
2 rows from Transactions_Lots table:
transaction_id	lot_id
3	11
3	8
*/
Show all dates of transactions whose type code is "SALE".
SELECT date_of_transaction FROM TRANSACTIONS WHERE transaction_type_code  =  "SALE"
[{'date_of_transaction': '1988-09-16 19:02:51'}, {'date_of_transaction': '1979-04-27 06:03:59'}, {'date_of_transaction': '2004-01-18 20:37:50'}, {'date_of_transaction': '1981-01-28 08:07:03'}, {'date_of_transaction': '2000-04-03 20:55:43'}, {'date_of_transaction': '1983-11-01 17:57:27'}, {'date_of_transaction': '2002-04-07 20:28:37'}]