Question

In: Computer Science

List the owner's name of all male customers in the bank who have a ’Checking’ account....

  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.

Bank.sql is under this statement.

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

Solutions

Expert Solution

List the owner's name of all male customers in the bank who have a ’Checking’ account

SELECT customer.name FROM customer INNER JOIN account ON account.owner_ssn = customer.ssn WHERE customer.sex = 'M' and account.type = 'Checking';

Find all accounts associated with ’Alexander Felix’.

SELECT * FROM account JOIN customer ON account.owner_ssn = customer.ssn WHERE customer.name = 'Alexander Felix'

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).

SELECT account.type ,customer.name , account.number , SUM(transaction.amount)
FROM account
INNER JOIN customer ON customer.ssn = account.owner_ssn
JOIN transaction
ON transaction.account_num = account.number
WHERE customer.name = 'Alexander Felix' AND account.type = 'Checking' UNION
SELECT account.type ,customer.name , account.number , SUM(transaction.amount)
FROM account
INNER JOIN customer ON customer.ssn = account.owner_ssn
JOIN transaction
ON transaction.account_num = account.number
WHERE customer.name = 'Alexander Felix' AND account.type = 'Saving'

The list of customer names that have transactions greater than or equal to one thousand dollars.

SELECT customer.name FROM customer , account WHERE customer.ssn = account.owner_ssn AND account.number IN (SELECT transaction.account_num FROM transaction WHERE transaction.amount >= 1000 )


Related Solutions

List the owner's name of all male customers in the bank who have a ’Checking’ account....
List the owner's name of all male customers in the bank who have a ’Checking’ account. Find all accounts associated with ’Alexander Felix’. 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). 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...
Clearwater National Bank wants to compare the account checking practices by the customers at two of...
Clearwater National Bank wants to compare the account checking practices by the customers at two of its branch banks – Cherry Grove Branch and Beechmont Branch. A random sample of 28 and 22 checking accounts is selected from these branches respectively. The sample statistics are shown on the next slide Cherry Grove                                  Beechmont n                                        28                                            22 x                                        $1025                                       $910 s                                         $150                                         $125 Let us develop a 99% confidence interval estimate of the difference between the population mean checking account...
Customers as a Cost Object Morrisom National Bank has requested an analysis of checking account profitability...
Customers as a Cost Object Morrisom National Bank has requested an analysis of checking account profitability by customer type. Customers are categorized according to the size of their account: low balances, medium balances, and high balances. The activities associated with the three different customer categories and their associated annual costs are as follows: Opening and closing accounts $300,000 Issuing monthly statements 450,000 Processing transactions 3,075,000 Customer inquiries 600,000 Providing automatic teller machine (ATM) services 1,680,000 Total cost $6,105,000 Additional data...
The First National Bank of Wilson has 740 checking account customers. A recent sample of 60...
The First National Bank of Wilson has 740 checking account customers. A recent sample of 60 of these customers showed 36 have a Visa card with the bank. Construct the 98% confidence interval for the proportion of checking account customers who have a Visa card with the bank. (Use z Distribution Table.) (Round your answers to 3 decimal places. Confidence interval for the proportion is between and .
Morrisom National Bank has requested an analysis of checking account profitability by customer type. Customers are...
Morrisom National Bank has requested an analysis of checking account profitability by customer type. Customers are categorized according to the size of their account: low balances, medium balances, and high balances. The activities associated with the three different customer categories and their associated annual costs are as follows: Opening and closing accounts $300,000 Issuing monthly statements 450,000 Processing transactions 3,075,000 Customer inquiries 600,000 Providing automatic teller machine (ATM) services 1,680,000 Total cost $6,105,000 Additional data concerning the usage of the...
The First National Bank of Wilson has 660 checking account customers. A recent sample of 50...
The First National Bank of Wilson has 660 checking account customers. A recent sample of 50 of these customers showed 19 have a Visa card with the bank. Construct the 99% confidence interval for the proportion of checking account customers who have a Visa card with the bank. (Use z Distribution Table.) (Round your answers to 3 decimal places.) Confidence interval _______________ and ________________
A bank randomly selected 249 checking account customers and found that 113 of them also had...
A bank randomly selected 249 checking account customers and found that 113 of them also had savings accounts at this same bank. Construct a 95% confidence interval for the true proportion of checking account customers who also have savings accounts. (Round your answers to three decimal places.) lower limit     upper limit    
1. A bank randomly selected 242 checking account customers and found that 113 of them also...
1. A bank randomly selected 242 checking account customers and found that 113 of them also had savings accounts at this same bank. Construct a 90% confidence interval for the true proportion of checking account customers who also have savings accounts. (Give your answers correct to three decimal places.) 2. In a sample of 62 randomly selected students, 29 favored the amount being budgeted for next year's intramural and interscholastic sports. Construct a 90% confidence interval for the proportion of...
The First National Bank of Wilson has 620 checking account customers. A recent sample of 60...
The First National Bank of Wilson has 620 checking account customers. A recent sample of 60 of these customers showed 28 have a Visa card with the bank. Construct the 95% confidence interval for the proportion of checking account customers who have a Visa card with the bank. (Use z Distribution Table.) (Round your answers to 3 decimal places.) Confidence interval for the proportion is between and .
Customers as a Cost Object Morrisom National Bank has requested an analysis of checking account profitability...
Customers as a Cost Object Morrisom National Bank has requested an analysis of checking account profitability by customer type. Customers are categorized according to the size of their account: low balances, medium balances, and high balances. The activities associated with the three different customer categories and their associated annual costs are as follows: Opening and closing accounts $300,000 Issuing monthly statements 450,000 Processing transactions 3,075,000 Customer inquiries 600,000 Providing automatic teller machine (ATM) services 1,680,000 Total cost $6,105,000 Additional data...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT