DBMS-Slip14

Slip14
Consider the following Entities and Relationships                                   
Branch (bname , bcity ,assets)
Loan (loan_no, amount)
Relation between Branch and Loan is One to Many.
Constraint: Primary key, amount and assets should be > 0.

Solution:-
 
Create a Database in 3NF & write queries for following.
•Display total loan amount given by DYP branch.
SQL>Select sum(lamount) from branch,loans where branch.bno=loans.bno and  bname=’DYP’;

•Find total number of loans given by each branch.
SQL>select count(lno),bname from branch,loans where branch.bno=loans.bno group by bname;

•Find the name of  branch that have maximum assets located in Mumbai.
SQL>select max(asssets),bname from branch,loans where branch.bno=loans.bno and bcity=’mumbai’  group by bname;

•Display loan details in descending order of their amount.
SQL>select * from loans order by lamount desc;

•Display all branches located in Mumbai, Pune and Nasik.
SQL>select * from branch where bcity in(‘mumbai’,’pune’,’nasik’);