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 bank customers having a loan, an account, or both at the bank.

View Answer

Answer: select customer_name from depositor union all select customer_name from borrower;

2. Find all the bank customers having both a loan and an account at the bank

View Answer

Answer: select customer_name from depositor intersect all select customer_name from borrower;

3. Find all customers who have an account but no loan at the bank.

View Answer

Answer: select customer_name from depositor except select customer_name from borrower;

Aggregate Functions (avg,min,max,sum,count) / Group By
4. Find the average account balance at the Perryridge branch.

View Answer

Answer: select avg(balance) from account where branch_name=’Perryridge’;

5. Find the average account balance at each branch.

View Answer

Answer: select avg(balance),branch_name from account natural join depositor group by branch_name;

6. Find the number of depositors for each branch (Use distinct).

View Answer

Answer: select count(Distinct customer_name),branch_name from account natural join depositor group by branch_name;

7. Find those branches where the average accounts balance is more than Rs. 1200.

View Answer

Answer: select branch_name from account group by branch_name having avg(balance)>1200

8. Find the number of branches of the bank.

View Answer

Answer: select count(distinct branch_name) from branch;

9. Find the average balance for each customer who lives in Harrison and has at least three accounts.

View Answer

Answer: /*select avg(balance) from account where account_number in ( select account_number from depositor where customer_name = (select customer_name from depositor group by customer_name having count(account_number) >= 2 intersect select customer_name from customer where customer_city = ‘Harrison’));*/

select avg(balance), customer_name from depositor natural join account natural join customer where customer_city = ‘Harrison’ group by customer_name having count(customer_name)>=2;

Null values
10. Find all loan numbers that appear in the loan relation with null values for amount.

View Answer

Answer: select loan_number from loan where amount is null.