DBMS Practical Slips

4.Client_Policy

create table cli201
(
cno number(5) primary key,
cname varchar2(10) NOT NULL,
address varchar2(20),
bidt date
);

create table polic202
(
pno number(5) primary key,
description varchar2(10),
m_amt number(10)check(m_amt>0),
p_amt number(10)check(p_amt>0),
dt date
);

create table cli_polic203
(
cno number(5)references cli201(cno),
pno number(5)references polic202(pno)
);

insert into cli201 values(11,'ankit','pune','21 jan 2000');
insert into cli201 values(12,'rohit','pune','5 feb 1999');
insert into cli201 values(13,'hitesh','nasik','1 mar 2000');
insert into cli201 values(14,'laxman','mumbai','6 apr 2001');
insert into cli201 values(15,'jay','pune','11 jun 1998');

insert into polic202 values(10,'jeevanbima',500000,2000,'12 jan 2013');
insert into polic202 values(20,'home',600000,2500,'13 feb 2010');
insert into polic202 values(30,'education',200000,1500,'15 jan 2013');
insert into polic202 values(40,'jeevanbima',600000,2000,'2 apr 2014');
insert into polic202 values(50,'car',600000,2000,'5 jun 2016');

insert into cli_polic203 values(11,10);
insert into cli_polic203 values(12,20);
insert into cli_polic203 values(13,30);
insert into cli_polic203 values(14,40);
insert into cli_polic203 values(15,50);

1. select * from polic202 where m_amt>500000;

       PNO DESCRIPTIO      M_AMT      P_AMT DT
---------- ---------- ---------- ---------- ---------
        20 home           600000       2500 13-FEB-10
        40 jeevanbima     600000       2000 02-APR-14
        50 car            600000       2000 05-JUN-16


2. select count(polic202.pno)from cli201,polic202,cli_polic203
where cli201.cno=cli_polic203.cno and
polic202.pno=cli_polic203.pno and
polic202.dt='12 jan 2013';


COUNT(POLIC202.PNO)
-------------------
                  1


3. select cname from cli201,polic202,cli_polic203
where cli201.cno=cli_polic203.cno and
polic202.pno=cli_polic203.pno and
polic202.pno>3;

 CNAME
----------
ankit
rohit
hitesh
laxman
jay


4. select polic202.description from cli201,polic202,cli_polic203
where cli201.cno=cli_polic203.cno and
polic202.pno=cli_polic203.pno and
polic202.description='jeevanbima';


DESCRIPTIO
----------
jeevanbima
jeevanbima

5. select description,cname from cli201,polic202,cli_polic203
where cli201.cno=cli_polic203.cno;

DESCRIPTIO CNAME
---------- ----------
jeevanbima ankit
jeevanbima rohit
jeevanbima hitesh
jeevanbima laxman
jeevanbima jay
home       ankit
home       rohit
home       hitesh
home       laxman
home       jay
education  ankit

DESCRIPTIO CNAME
---------- ----------
education  rohit
education  hitesh
education  laxman
education  jay
jeevanbima ankit
jeevanbima rohit
jeevanbima hitesh
jeevanbima laxman
jeevanbima jay
car        ankit
car        rohit

DESCRIPTIO CNAME
---------- ----------
car        hitesh
car        laxman
car        jay

25 rows selected.