Referring to the running example of schema Figure 2.15 (page 54) in the textbook

Question

Referring to the running example of schema Figure 2.15 (page 54) in the textbook, please write both relational algebra expression and SQL for each of the following queries.

Branch(branch_name, branch_city, assets)

Customer(customer_name, customer_street, customer_city)

Loan(loan_number, branch_name, amount)

Borrower(customer_name, loan_number)

Account(account_number, branch_name, balance)

Depositor(customer_name, account_number)

List all the customers who live in Greensboro together with their account balances.

2. List all branches whose average account balances are more than $10,000.

3. List the customers who have account(s) in a branch city that is different where he lives.

4. List the customers who have both loan and account in the same branch.

5. List the customers who have both loan and account in the bank.

Explanation / Answer

1. Select customer_name from Customer where customer_stree='Greensboro'

2. select branch_name, avg (balance) from account group by branch_name having avg (balance) > 10000

3. Select customer_name from Customer where Customer.Customer_street != branch.branch_city

4. select distinct customer_name from Borrower where customer_name in (select customer_name from depositor)

5. (select customer_name from depositor)
intersect
(select customer_name from borrower)

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

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