Question

1. List the owner's name of all male customers in the bank who have a ’Checking’ account.
2. Find all accounts associated with ’Alexander Felix’.
3. 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).
4. 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.

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');

Answer 1

Ans.1) Select name from CUSTOMER LEFT JOIN ACCOUNT ON Customer.ssn=Account.owner_ssn where Sex="M" and type="Checking";

Here to fetch the data of all the name of the customers who are males as well as who are having the account as checking.

Fetch the data from two tables CUSTOMER and ACCOUNT in which sex is equals to Male and type of account should be checking.

AND condition is used in order to satisfy both the condition by using LEFT OUTER JOIN having interrelated field as ssn of CUSTOMER table and owner_ssn ACCOUNT table

Ans.2) Select Number,type from ACCOUNT LEFT JOIN CUSTOMER ON Account.owner_ssn=Customer.ssn where name="Alexander Felix";

Fetch the data of all the accounts which are associated with Alexander Felix so by using LEFT OUTER JOIN in ACCOUNT and CUSTOMER table by the relation of ssn from customer table and owner_ssn from ACCOUNT table.

Ans.3) Select name, Account.number, Account.type ,SUM(amount) from CUSTOMER LEFT JOIN ACCOUNT ON Account.owner_ssn=Customer.ssn LEFT JOIN TRANSACTION ON Account.number=Transaction.account_num where name="Alexander Felix";

In order to find all the accounts which are associated with Alexander Felix and then the transaction of all that fetch account should be SUM as from AMOUNT field of TRANSACTION table. By doing so, SUM function is used in order to add the total amount of accounts associated with Alexander Felix.

Select name from CUSTOMER where name="Alexander Felix";

UNION

Select number,type from ACCOUNT;

UNION

Select SUM(amount) from Transaction;

By using union the queries are separated in three tables that each query associated with a particular table.

In first, fetch name from CUSTOMER table where name is equals to Alexander Felix then number and type from ACCOUNT table and from account number and it's associated type and then SUM of AMOUNT has to be calculated from TRANSACTION table and all three queries are union with each other.

Ans.4) Select Customer.name from CUSTOMER LEFT JOIN ACCOUNT ON Account.owner_ssn=Customer.ssn LEFT JOIN TRANSACTION ON Account.number=Transaction.account_num where amount>=1000;

In order to fetch the CUSTOMER name having the total amount greater than $1000, LEFT OUTER JOIN is used in order to join 3 tables by their respective fields and primary key and foreign key with each other and by using where clause condition having greater than $1000.