HW3ReportFile
docx
keyboard_arrow_up
School
South Piedmont Community College *
*We aren’t endorsed by this school
Course
120
Subject
Industrial Engineering
Date
Dec 6, 2023
Type
docx
Pages
4
Uploaded by ChancellorMoleMaster282
George Viveros-Zavaleta
HW3 (Bank):
EER
1. Find the total amount of Deposits using the sum function
SELECT SUM(amount) AS total_deposit_amount
FROM transaction
WHERE type = 'Deposit';
2. Find the list of transactions by checking date and account number to match 1111222233331441 and September 2019 (09/01/2019 to 09/30/2019)
SELECT *
FROM transaction
WHERE account_num = '1111222233331441'
AND tdate BETWEEN '2019-09-01' AND '2019-09-30';
3. Find the balance of 1111222233331441 using the sum function of all transactions before 09/01/2019 ((not including 09/01/2019).
SELECT SUM(amount) AS balance_before_0901
FROM transaction
WHERE account_num = '1111222233331441'
AND tdate < '2019-09-01';
4. Find the name of the customer that deposited the highest amount with one transaction.
SELECT c.name, t.amount AS deposit_amount
FROM customer c
INNER JOIN account a ON c.ssn = a.owner_ssn
INNER JOIN transaction t ON a.number = t.account_num
WHERE t.type = 'Deposit'
ORDER BY deposit_amount DESC
LIMIT 1;
5. List the owner's name of all male customers in the bank who have a 'Checking' account.
SELECT DISTINCT c.name
FROM customer c
INNER JOIN account a ON c.ssn = a.owner_ssn
WHERE c.sex = 'M'
AND a.type = 'Checking';
6. Find all accounts associated with 'Alexander Felix'.
SELECT a.number, a.type
FROM account a
INNER JOIN customer c ON a.owner_ssn = c.ssn
WHERE c.name = 'Alexander Felix';
7. For each account of the previous question, compute the Balance and return a table that shows the account number, account type, and balance for each account.
SELECT a.number, a.type, SUM(t.amount) AS balance
FROM account a
LEFT JOIN transaction t ON a.number = t.account_num
GROUP BY a.number, a.type;
8a. For each account of the previous question, compute the Balance and return a table that shows the account number, account type, and balance for each account.
SELECT a.number, a.type, SUM(t.amount) AS balance
FROM account a
LEFT JOIN transaction t ON a.number = t.account_num
GROUP BY a.number, a.type;
8b. The list of customer names that have transactions greater than or equal to one thousand dollars using joins.
SELECT DISTINCT c.name
FROM customer c
INNER JOIN account a ON c.ssn = a.owner_ssn
INNER JOIN transaction t ON a.number = t.account_num
WHERE t.amount >= 1000;
9. The list of customer names that have at least two deposit transactions.
SELECT DISTINCT c.name
FROM customer c
INNER JOIN account a ON c.ssn = a.owner_ssn
INNER JOIN transaction t ON a.number = t.account_num
WHERE t.type = 'Deposit'
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
GROUP BY c.name
HAVING COUNT(t.id) >= 2;