Question

Consider the schema below that models a bank’s branches, customers and their accounts and loans. Provide SQL queries to answer the questions that follow.

Branch (bank_name, addr, city, assets) // Primary Key=bank_name

Customer (cust_name, addr, city) // Primary Key=cust_name

Account (acc_num, cust_name, bank_name, type, balance) // Primary Key=acc_num

Loan (loan_num, cust_name, bank_name, type, amount)  // Primary Key=loan_num

*****Use a nested subquery if possible*****

-- 3.   Find the bank with the largest total account balance, i.e., the bank

--    that has the most funds in accounts with it.

Answer 1

SELECT bank_name FROM Branch WHERE

(SELECT MAX
(
(SELECT SUM(balance) FROM Account WHERE Branch.bank_name = Account.bank_name)
-
(SELECT SUM(amount) FROM Loan WHERE Branch.bank_name = Loan.bank_name)
)
AS Total_Fund
) 

Here I have indented the query into different lines so that it would be easy to understand

First of all, I have selected the bank name from the branch and then I have nested two subqueries to find the difference between the sum of the account balance and sum of all loans and them find the maximum from the difference that was generated from the two subqueries.

I hope that you can understand what I want to explain.