RDBMS Assessment Questions and Answers on “Sql Queries – IV”.
1. Find all the customers who have both a loan and an account at the Perryridge branch
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.
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
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.
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.
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
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.
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
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
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.
Answer: select avg(balance),branch_name from account
where balance > 1200 group by branch_name;