RDBMS Multiple Choice Questions on “SQL Queries”.
1. List all branch names and their assests
Answer: select branch_name, assets from branch;
2. List all accounts of Brooklyn branch
Answer: select account_number from account natural join branch where branch_city=’Brooklyn’;
3. List all loans with amount > 1000.
Answer: select * from loan where amount>1000;
4. List all accounts of Perryridge branch with balance < 1000.
Answer: select account_number from account where branch_name=’Perryridge’ and balance < 1000;
5. List Numbers of accounts with balances between 700 and 900
Answer: select count(account_number) from account where balance > 700 and balance < 900;
6. Change the assests of Perryridge branch to 340000000.
Answer: update branch set assets = 340000000 where branch_name=’Perryridge’;
7. Transfer the accounts and loans of Perryridge branch to Downtown branch.
Answer: update account set branch_name=’Downtown’ where branch_name=’Perryridge’;
update loan set branch_name=’Downtown’ where branch_name = ‘Perryridge’;
8. Transfer Rs. 100 from account A-101 to A-215.
Answer: update account
set balance = case
when account_number=’A-101′ then balance-100
when account_number=’A-215′ then balance+100
else balance
end;
9. Delete the branch Perryridge.
Answer: delete from account where branch_name=’Perryridge’;
delete from branch where branch_name=’Perryridge’;
delete from loan where branch_name=’Perryridge’;
10. Waive off all the loans with amount < 1000.
Answer: delete from borrower where loan_number in
(select loan_number from loan where amount<1000);
delete from loan where amount<1000;