Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages
Filter by Categories
nmims post
Objective Type Set
Online MCQ Assignment
Question Solution
Solved Question
Uncategorized

1. Find all the customers who have both a loan and an account at the Perryridge branch

View Answer

Answer: select customer_name from loan natural join borrower where branch_name = ‘Perryridge’ and customer_name in (select customer_name from account natural join depositor where branch_name = ‘Perryridge’);

2. Find all customers who do have a loan at the bank, but do not have an account at the bank.

View Answer

Answer: select customer_name from loan natural join borrower where customer_name not in (select customer_name from account natural join depositor);

3. Select the names of customers who have a loan at the bank, and whose names are neither Smith nor Jones

View Answer

Answer: select customer_name from loan natural join borrower where branch_name = ‘Perryridge’ and customer_name not in (‘Smith’, ‘Jones’);

4. Find the names of all branches that have assets greater than those of at least one branch located in Brooklyn.

View Answer

Answer: select branch_name from branch where assets > some(select assets from branch where branch_city = ‘Brooklyn’);

5. Find the names of all branches that have an asset value greater than that of each branch in Brooklyn.

View Answer

Answer: select branch_name from branch where assets > some(select assets from branch where branch_city = ‘Brooklyn’);

6. Find all customers who have both an account and a loan at the bank

View Answer

Answer: select distinct customer_name from borrower as b1 where exists (select customer_name from depositor as b2 where b1.customer_name = b2.customer_name);

7. Find all customers who have an account at all the branches located in Brooklyn.

View Answer

Answer: select customer_name from account as b1 natural join depositor where exists (select * from branch where b1.branch_name = ‘Downtown’ and b1.branch_name=’Brighton’);

8. Find all customers who have at most one account at the Perryridge branch

View Answer

Answer: select customer_name from account natural join depositor where branch_name = ‘Perryridge’ group by customer_name having count(account_number)<=1;

9. Find all customers who have at least two accounts at the perryridge branch

View Answer

Answer: select customer_name from account natural join depositor where branch_name = ‘Perryridge’ group by customer_name having count(account_number)>=2;

Complex queries
10. Find the average account balance of those branches where the account balance is greater than Rs. 1200.

View Answer

Answer: select avg(balance),branch_name from account where balance > 1200 group by branch_name;