
CREATE TABLE "Accounts" (
	account_id INTEGER, 
	customer_id INTEGER NOT NULL, 
	account_name VARCHAR(50), 
	other_account_details VARCHAR(255), 
	PRIMARY KEY (account_id)
)


CREATE TABLE "Customers" (
	customer_id INTEGER, 
	customer_first_name VARCHAR(20), 
	customer_last_name VARCHAR(20), 
	customer_address VARCHAR(255), 
	customer_phone VARCHAR(255), 
	customer_email VARCHAR(255), 
	other_customer_details VARCHAR(255), 
	PRIMARY KEY (customer_id)
)


CREATE TABLE "Customers_Cards" (
	card_id INTEGER, 
	customer_id INTEGER NOT NULL, 
	card_type_code VARCHAR(15) NOT NULL, 
	card_number VARCHAR(80), 
	date_valid_from DATETIME, 
	date_valid_to DATETIME, 
	other_card_details VARCHAR(255), 
	PRIMARY KEY (card_id)
)


CREATE TABLE "Financial_Transactions" (
	transaction_id INTEGER NOT NULL, 
	previous_transaction_id INTEGER, 
	account_id INTEGER NOT NULL, 
	card_id INTEGER NOT NULL, 
	transaction_type VARCHAR(15) NOT NULL, 
	transaction_date DATETIME, 
	transaction_amount DOUBLE, 
	transaction_comment VARCHAR(255), 
	other_transaction_details VARCHAR(255), 
	FOREIGN KEY(account_id) REFERENCES "Accounts" (account_id), 
	FOREIGN KEY(card_id) REFERENCES "Customers_Cards" (card_id)
)

CREATE TABLE "Accounts" (
	account_id INTEGER, 
	customer_id INTEGER NOT NULL, 
	account_name VARCHAR(50), 
	other_account_details VARCHAR(255), 
	PRIMARY KEY (account_id)
)

/*
2 rows from Accounts table:
account_id	customer_id	account_name	other_account_details
1	6	338	Regular
2	14	562	VIP
*/


CREATE TABLE "Customers" (
	customer_id INTEGER, 
	customer_first_name VARCHAR(20), 
	customer_last_name VARCHAR(20), 
	customer_address VARCHAR(255), 
	customer_phone VARCHAR(255), 
	customer_email VARCHAR(255), 
	other_customer_details VARCHAR(255), 
	PRIMARY KEY (customer_id)
)

/*
2 rows from Customers table:
customer_id	customer_first_name	customer_last_name	customer_address	customer_phone	customer_email	other_customer_details
1	Aniyah	Feest	55975 Theodore Estates
Lake Brody, VT 57078	(673)872-5338	fahey.dorian@example.com	None
2	Susie	Wiza	6478 Moen Isle Suite 910
Schimmelmouth, VT 96364-4898	679-845-8645x94312	idickinson@example.com	None
*/


CREATE TABLE "Customers_Cards" (
	card_id INTEGER, 
	customer_id INTEGER NOT NULL, 
	card_type_code VARCHAR(15) NOT NULL, 
	card_number VARCHAR(80), 
	date_valid_from DATETIME, 
	date_valid_to DATETIME, 
	other_card_details VARCHAR(255), 
	PRIMARY KEY (card_id)
)

/*
2 rows from Customers_Cards table:
card_id	customer_id	card_type_code	card_number	date_valid_from	date_valid_to	other_card_details
1	14	Credit	4560596484842	2011-04-17 09:05:28	2018-03-07 17:06:19	5567915676420343
2	9	Credit	4859448397570735	2012-05-22 02:05:41	2018-02-25 15:43:32	4539333582760
*/


CREATE TABLE "Financial_Transactions" (
	transaction_id INTEGER NOT NULL, 
	previous_transaction_id INTEGER, 
	account_id INTEGER NOT NULL, 
	card_id INTEGER NOT NULL, 
	transaction_type VARCHAR(15) NOT NULL, 
	transaction_date DATETIME, 
	transaction_amount DOUBLE, 
	transaction_comment VARCHAR(255), 
	other_transaction_details VARCHAR(255), 
	FOREIGN KEY(account_id) REFERENCES "Accounts" (account_id), 
	FOREIGN KEY(card_id) REFERENCES "Customers_Cards" (card_id)
)

/*
2 rows from Financial_Transactions table:
transaction_id	previous_transaction_id	account_id	card_id	transaction_type	transaction_date	transaction_amount	transaction_comment	other_transaction_details
1	925	15	1	Payment	2018-03-24 06:41:41	1701.23	None	None
2	494	3	2	Refund	2018-03-24 12:08:55	1931.76	None	None
*/
How many accounts are there for each customer id?
SELECT customer_id ,  count(*) FROM Accounts GROUP BY customer_id
[{'customer_id': 2, 'count(*)': 1}, {'customer_id': 4, 'count(*)': 3}, {'customer_id': 6, 'count(*)': 1}, {'customer_id': 8, 'count(*)': 1}, {'customer_id': 9, 'count(*)': 4}, {'customer_id': 10, 'count(*)': 1}, {'customer_id': 12, 'count(*)': 2}, {'customer_id': 13, 'count(*)': 1}, {'customer_id': 14, 'count(*)': 1}]