DBMS 19 Slips
Q.19) Branch_Loan
create table lo
(
l_no number(5) primary key ,
amt number(10)
) ;
create table br
(
b_name varchar(20) primary key ,
b_city varchar(20) ,
assets number(15) check (assets>0) ,
l_no number(5) references lo(l_no)
) ;
insert into lo values(1,15000) ;
insert into lo values(2,20000) ;
insert into lo values(3,35000) ;
insert into lo values(4,45000) ;
insert into br values('katraj','pune',1200000,1) ;
insert into br values('ganga','nashik',1300000,2) ;
insert into br values('yamuna','nanded',2200000,3) ;
insert into br values('bandra','mumbai',1500000,4) ;
1.select SUM(lo.amt) from lo,br
where lo.l_no=br.l_no AND
br.b_name='katraj';
SUM(LO.AMT)
-----------
15000
...................................................................................
2.select COUNT(lo.l_no) from lo,br GROUP BY b_name;
COUNT(LO.L_NO)
--------------
4
4
4
4
..................................................................................................
3. select b_name from br
where assets=(select MAX(assets) from br) OR
b_city='mumbai';
B_NAME
--------------------
yamuna
bandra
..........................................................................................
4. select lo.* from lo order by amt DESC;
L_NO AMT
---------- ----------
4 45000
3 35000
2 20000
1 15000
.............................................................................................
5. select b_name from br
where b_city='mumbai' OR
b_city='pune' OR
b_city='nashik';
B_NAME
--------------------
katraj
ganga
bandra
....................................................................................................
Tags:
Dbms