Slip 1 - C) Consider the following entities and their relationships. Client (client_no, client_name, address, birthdate) Policy_info (policy_no, desc, maturity_amt, prem_amt, date) Relation between Client and Policy_info is Many to Many Constraint: Primary key, prem_amt and maturity_amt should be > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1) Write a function which will return total maturity amount of policies of a particular client. 2) Write a cursor which will display policy date wise client details.

Solution:

SQL> create table client1(Cno int primary key, Cname varchar(15), Address varchar(20),birthdate varchar(10));

SQL> create table poly(pno int primary key,decb varchar(10),mamt int check(mamt>0),pamt int check(pamt>0),pdate varchar(15));

SQL> create table cp(Cno int,pno int);

SQL> insert into client1  values(1,'shree','pune','23jan');

SQL> insert into poly values(1,'L-term',1000,500,'23jan');

SQL> insert into cp values(1,1);

SQL> set serveroutput on

create or replace function f1(abc in varchar) return number as xyz number;

begin

select sum(poly.mamt)into xyz from client1 ,poly,cp where client1.Cno=cp.Cno and poly.pno=cp.pno   and cname='shree';

if sql% found then

return(xyz);

else

return null;

end if;

end f1;

 /

Function created.

SQL> begin

dbms_output.put_line('amt-'||f1('shree'));

end;

/

amt-1000

2)Write a cursor which will display policy date wise client details.

SQL>  set serveroutput on

SQL>  declare

cursor c1 is select  cname, address,pdate from client,poly,cp where client.cno=cp.cno and   poly.pno=cp.pno order by pdate;

c c1%rowtype;

begin

open c1;

loop

fetch c1 into c;

exit when c1%notfound;

dbms_output.put_line(c.cname||''||c.address||''||c.pdate);

end loop;

close c1;

end;

/

shreepune23jan

ommumbai22feb

sairaichur21march

PL/SQL procedure successfully completed.