(a) Find the names of branches with average branch balance above 700. (b.) Find

Question

(a) Find the names of branches with average branch balance above 700.

(b.) Find all customers who have a loan, an account, or both

(c) Find the average account balance at the “Perryridge” branch.

In-class exercise 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:

Explanation / Answer

Solutions.

a) Names of branches with average branch balance above 700.

select a.branch-name,avg(b.balance) as average-balance
from branch a
left join account b
on a.branch-name = b.branch-name
group by a.branch-name;

b) Customers who have a loan, an account or both.

select customer-name from customer
inner join depositor b
on a.customer-name = b.customer-name
inner join borrower c
on a.customer-name = c.customer-name
where account-number is not null or loan-number is not null;

c) Average account balance at the Perryridge branch.

select a.branch-name, avg(b.balance) as average-balance
from branch a
left join account b
on a.branch-name = b.branch-name
where a.branch-name = 'Perryridge';

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

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