DROP DATABASE IF EXISTS Bank; CREATE DATABASE Bank; USE Bank;

DROP TABLE IF EXISTS transaction; DROP TABLE IF EXISTS customer; DROP TABLE IF EXISTS account;

CREATE TABLE customer ( name VARCHAR(20), sex CHAR(1), ssn CHAR(9) NOT NULL, phone CHAR(15), dob DATE, address VARCHAR(50), PRIMARY KEY(ssn)

); CREATE TABLE account ( number CHAR(16) UNIQUE NOT NULL, open_date DATE, type CHAR(20), owner_ssn CHAR(9) NOT NULL, PRIMARY KEY(number) ); CREATE TABLE transaction ( id INT(20) UNIQUE NOT NULL, amount DECIMAL(9,2), tdate DATE, type CHAR(10), account_num CHAR(16), PRIMARY KEY(id) );

INSERT INTO customer VALUE ('John Adam', 'M', '512432341', '(438) 321-2553', '1987-11-15',NULL); INSERT INTO customer VALUE ('Alexander Felix', 'M', '724432341', '(541) 321-8553', '1991-05-22', NULL); INSERT INTO customer VALUE ('Andrew William', 'M', '861894272', '(308) 692-1110', '1995-01-04', NULL); INSERT INTO customer VALUE ('Ana Bert', 'F', '844192241', '(203) 932-7000', '1982-12-07', '23 Boston Post Rd, West Haven, CT 06516');

INSERT INTO account VALUE ('1111222233331441', '2018-12-03', 'Checking', '861894272'); INSERT INTO account VALUE ('2111222233332442', '2019-01-06', 'Saving', '512432341'); INSERT INTO account VALUE ('3111222233333443', '2017-09-22', 'Checking', '844192241'); INSERT INTO account VALUE ('4111222233335444', '2016-04-11', 'Checking', '724432341'); INSERT INTO account VALUE ('5111222233339445', '2018-11-05', 'Saving', '724432341'); INSERT INTO transaction VALUE (1001, 202.50, '2019-08-15', 'Deposit', '5111222233339445'); INSERT INTO transaction VALUE (1002, 100.00, '2019-09-21', 'Deposit','2111222233332442'); INSERT INTO transaction VALUE (1003, 200.00, '2019-09-29', 'Deposit', '2111222233332442'); INSERT INTO transaction VALUE (1004, 50.00, '2019-09-29', 'Deposit', '2111222233332442'); INSERT INTO transaction VALUE (1005, 1000.00, '2019-09-29', 'Deposit','3111222233333443'); INSERT INTO transaction VALUE (1006, -202.50, '2019-08-29', 'Withdraw', '5111222233339445'); INSERT INTO transaction VALUE (1007, 50.00, '2019-09-29', 'Deposit', '2111222233332442'); INSERT INTO transaction VALUE (1008, 50.00, '2019-09-29', 'Deposit', '2111222233332442'); INSERT INTO transaction VALUE (1009, -10.00, '2019-09-26', 'Withdraw', '2111222233332442'); INSERT INTO transaction VALUE (1010, 50.00, '2019-09-29', 'Deposit', '4111222233335444'); INSERT INTO transaction VALUE (1011, 320.00, '2019-09-29', 'Deposit', '5111222233339445'); INSERT INTO transaction VALUE (1012, 50.00, '2019-09-18', 'Deposit', '4111222233335444'); INSERT INTO transaction VALUE (1013, 5000.00, '2019-06-21', 'Deposit', '1111222233331441'); INSERT INTO transaction VALUE (1014, -100.00, '2019-09-02', 'Withdraw', '1111222233331441'); INSERT INTO transaction VALUE (1015, -200.00, '2019-09-08', 'Withdraw', '1111222233331441');

Consider modeling a bank:

• The bank keeps the personal information of each customer including name, sex, address, phone number, social security number, and date of birth.
• Each customer owns one or more bank accounts (The bank does not support a joint account shared with multiple customers).
• Each bank account is specified with the account number, open date, and type.
• An account may have several transactions (deposit/withdrawal). A transaction has a unique ID, amount, date, and type.

First, draw an (E)ER that models the given requirements.

Next, answer the following questions using the given snapshot of the bank database.

Import and source Bankdb.sql

Write your queries in bank-query.sql.

1. Find the total amount of Deposit transactions at the bank.
2. Find the list of transactions (statement) of September 2019 (09/01/2019 to 09/30/2019) for account 1111222233331441 (note: look at the date format).
3. Find the balance of 1111222233331441 before 09/01/2019 ((not including 09/01/2019).
4. Find the name of the customer that deposited the highest amount with one transaction (include the transaction amount).
5. List the owner's name of all male customers in the bank who have a Checking account.
6. Find all accounts associated with Alexander Felix.
7. For each account of the previous question, compute the Balance, and return a table that shows the account number, type, and balance for each account (hint: use UNION).
8. The list of customer names that have transactions greater than or equal to one thousand dollars.

A) Answer this question using only nested queries (i.e., each select is over only one table).

B) Answer this query using joins.

9. The list of customer names that have at least two deposit transactions

Hint1: The customer may deposit using different accounts

Hint2: You need to use subquery and aggregation + having (this concept will be covered in the next module