Consider the banking example we used in lecture: branch (branch-name, branch-cit

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;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.