Consider the banking example we used in lecture: branch (branch-name, branch-cit
ID: 3730992 • Letter: C
Question
Consider the banking example we used in lecture:
branch (branch-name, branch-city, assets)
customer (customer-name, customer-street, customer-city)
account (account-number, branch-name, balance)
loan (loan-number, branch-name, amount)
depositor (customer-name, account-number)
borrower (customer-name, loan-number)
Write and execute the following queries in SQL:
1. Find the names of branches with average balance above 700.
2. Find the names of customers with accounts at a branch where Hayes has an account.
3. Find the name and average balance for customers who live in Harrison and have at
least 2 accounts.
Explanation / Answer
/*1)
Find the names of branches with average balance above 700.
*/
select branch-name, avg(balance)
from account
group by branch-name
having avg(balance) > 700;
/*2)
Find the names of customers with accounts at a branch where Hayes has an account
*/
select distinct D.customer-name
from depositor D, account A
where D.account-number = A.account-number and
branch-name in
(select branch-name
from account Ah,depositor Dh
where Dh.account-number = Ah.account-number and
D.customer-name = 'Hayes');
/*3)
Find the name and average balance for customers who live in Harrison and have at
least 2 accounts.
*/
select avg(balance)
from depositor, account, customer
where depositor.customer-name = customer.customer-name and
depositor.account-number = account.account-number and
customer-city = 'Harrison'
group by depositor.customer-name
having count (distinct account.account-number) >= 2;