RDBMS Questions and Answers – SQL Queries – III

«
»

This set of RDBMS Questions and Answers for Aptitude test focuses on “Sql Queries – III”.

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;
advertisement

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;
advertisement

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’));*/

advertisement

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.

Sanfoundry Global Education & Learning Series – RDBMS.

To practice all areas of RDBMS for Aptitude test, here is complete set of 1000+ Multiple Choice Questions and Answers.

advertisement

Participate in the Sanfoundry Certification contest to get free Certificate of Merit. Join our social networks below and stay updated with latest contests, videos, internships and jobs!

advertisement
advertisement
advertisement
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He is Linux Kernel Developer & SAN Architect and is passionate about competency developments in these areas. He lives in Bangalore and delivers focused training sessions to IT professionals in Linux Kernel, Linux Debugging, Linux Device Drivers, Linux Networking, Linux Storage, Advanced C Programming, SAN Storage Technologies, SCSI Internals & Storage Protocols such as iSCSI & Fiber Channel. Stay connected with him @ LinkedIn