Slip16
Consider
the following Entities and Relationships
Branch
(bno, bname, bcity, assets)
Account
(acc_no ,balance)
Relation
between Branch and Account is One to Many.
Constraint:
Primary key, balance and assets should be > 0.
Solution:-
Create
a Database in 3NF & write queries for following.
•Find
the maximum account balance of each branch.
SQL>Select
max(balance),bname from branches,accounts
where branches.bno=accounts.bno group by bname;
•Find
branches where average account balance is more than 30000.
SQL>Select
avg(balance) from branches,accounts
where branches.bno=accounts.bno and balance >30000;
•Find
names of all branches that have assets value greater than that of each branch
in ‘pune’.
SQL>Select
max(assets),bname from branches,accounts
where branches.bno=accounts.bno and bcity=’pune’ group by bname;
•Decrease 3%
balance on account whose balance is greater than 100000.
SQL>
Update accounts set balance=balance-balance*0.03 where balance >3000;
•Display details of branchwhose city starts from
'A'.
SQL>select
* from branches where bcity like 'A%';
