Rdbms Practical Silps

RDBMS 13 Slips

Q.1) Consider the following entities and their relationship. [Marks 40]
Client (c_no, c_name, c_addr, birth_date)
Policy_info (p_no, p_name, maturity_amt, prem_amt, policy_term)

Relationship between Client and Policy_info is many-to-many with descriptive
attribute date_of_purchase.
Constraints: primary key, foreign key,
c_name and p_name should not be null,
policy_term should be greater than zero.

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1) Write a procedure which will display all policy details of given client for a given
year.

2) Write a trigger which restricts insertion of policy_info having maturity amount less

than premium amount. (Raise user defined exception and give appropriate message)

3) Write a function which will return name of policy taken by minimum number of
clients.

4) Write a cursor which will display client wise policy details. (Use parameterized
cursor)

SLIP-13

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

create table policy
(
pno number(5) primary key,
pname varchar2(10) NOT NULL,
m_amt number(10),
p_amt number(10),
term number(10)check(term>0)
);

create table cli_poli
(
cno number(5)references cli(cno),
pno number(5)references policy(pno),
dt_of_pur date
);

insert into cli values(11,'seema','pune','21 jan 2000');
insert into cli values(12,'samir','pune','5 feb 1999');
insert into cli values(13,'samad','nasik','1 mar 2000');
insert into cli values(14,'deep','mumbai','6 apr 2001');
insert into cli values(15,'puja','pune','11 jun 1998');

insert into policy values(10,'jeevanbima',500000,2000,5);
insert into policy values(20,'home',600000,2500,6);
insert into policy values(30,'education',200000,1500,10);
insert into policy values(40,'jeevanbima',600000,2000,7);
insert into policy values(50,'car',600000,2000,2);


insert into cli_poli values(11,10,'21 jan 2016');
insert into cli_poli values(12,20,'1 feb 2015');
insert into cli_poli values(13,30,'23 mar 2018');
insert into cli_poli values(14,40,'24 jul 2018');
insert into cli_poli values(15,50,'2 apr 2010');

Q.1) Write a procedure which will display all policy details of given client for a given
year.

PROCEDURE
create or replace procedure p13
(pname in varchar2)
as
cursor c4 is select policy.* from cli,policy,cli_poli
where cli.cno=cli_poli.cno and
policy.pno=cli_poli.pno and
dt_of_pur='23 mar 2018';
x c4%rowtype;
begin
for x in c4
loop
dbms_output.put_line(x.pno||'  '||x.pname||'  '||x.m_amt||'  '||x.p_amt||'  '||x.term);
end loop;
end p13;
/
OUTPUT-

Procedure created.

SQL> execute p13('samad');
30  education  200000  1500  10

PL/SQL procedure successfully completed.
........................................................................

Q.2) Write a trigger which restricts insertion of policy_info having maturity amount less

than premium amount. (Raise user defined exception and give appropriate message)

TRIGGER
create or replace trigger t19
before insert on policy
for each row
begin
if:new.m_amt<:new.p_amt then
raise_application_error('-20010','maturity amount is less than premium amount');
end if;
end t19;
/
insert into policy values(50,'car',6000,20000,2);

OUTPUT-

Trigger created.

SQL> insert into policy values(50,'car',6000,20000,2);
insert into policy values(50,'car',6000,20000,2)
     *
ERROR at line 1:
ORA-20010: maturity amount is less than premium amount
ORA-06512: at "SYSTEM.T19", line 3
ORA-04088: error during execution of trigger 'SYSTEM.T19'
.......................................................................

Q.3) Write a function which will return name of policy taken by minimum number of
clients.

FUNCTION
create or replace function f13
(vname in varchar2)
return varchar2
as
cname policy.pname%type;
begin
select pname into cname from cli,policy,cli_poli
where cli.cno=cli_poli.cno and
policy.pno=cli_poli.pno and
cli_poli.cno=(select min(cli_poli.cno)from cli_poli);
if SQL%found then
return cname;
else
return 0;
end if;
end f13;
/
OUTPUT-

Function created.

SQL> select f13('seema')from dual;

F13('seema')
--------------------------------------

jeevanbima

//CALLING//

declare
a varchar2(10);
b varchar2(10);
begin
a:=&a;
b:=f13(a);
dbms_output.put_line('minimum no.of clients policy is'||b);
end;
/

 OUTPUT-

Enter value for a: 'seema'
old   5: a:=&a;
new   5: a:='seema';
minimum no.of clients policy isjeevanbima

PL/SQL procedure successfully completed.

....................................................................

Q.4) Write a cursor which will display client wise policy details. (Use parameterized
cursor)

CURSOR
declare
cursor c7(cwise cli.cname%type)is select cname,pname,m_amt,p_amt,term
from  cli,policy,cli_poli
where cli.cno=cli_poli.cno and
policy.pno=cli_poli.pno;
x c7%rowtype;
begin
open c7('&cwise');
loop
fetch c7 into x;
exit when c7%notfound;
dbms_output.put_line(x.cname||'  '||x.pname||'  '||x.m_amt||'  '||x.p_amt||'  '||x.term);
end loop;
close c7;
end;
/


OUTPUT-

Enter value for cwise: seema
old   8: open c7('&cwise');
new   8: open c7('seema');
seema  jeevanbima  500000  2000  5
samir  home  600000  2500  6
samad  education  200000  1500  10
deep  jeevanbima  600000  2000  7
puja  car  600000  2000  2

PL/SQL procedure successfully completed.