RDBMS SOLUTION


ASSIGNMENT 4 

SET A Q.1 SOLUTION

Write a function which will return minimum maturity amount of all policies.




Source code:

    

    /* assignment 4 set a q 1 solution

    here first we will create 3 tables that is 3nf database and then solve the 3 queries

    creating tables*/

create table cclient

(

    client_no number not null primary key,

    client_name varchar2(40),

    address varchar2(40),

    birthdate date

)

insert into cclient values(1,'c1','a1',to_date('12/12/2000','dd/mm/yyyy'))

insert into cclient values(2,'c2','a2',to_date('10/02/1999','dd/mm/yyyy'))

insert into cclient values(3,'c3','a3',to_date('06/01/1974','dd/mm/yyyy'))

insert into cclient values(4,'c4','a4',to_date('02/11/2003','dd/mm/yyyy'))

create table policy_info

(

    policy_no number not null primary key,

    descr varchar2(40),

    maturity_amt number check(maturity_amt>0),

    prem_amt number check(prem_amt>0),

    pdate date

)

insert into policy_info values(1,'d1',10000,20000,to_date('01,01,2020'))

insert into policy_info values(2,'d2',15000,30000,to_date('01,01,2020'))

insert into policy_info values(3,'d3',20000,40000,to_date('01,02,2020'))

insert into policy_info values(4,'d4',25000,450000,to_date('01,03,2020'))

create table cli_pol

(

    client_no number references cclient(client_no),

    policy_no number references policy_info(policy_no)

)

insert into cli_pol values(1,1)

insert into cli_pol values(1,2)

insert into cli_pol values(1,3)

insert into cli_pol values(1,4)

insert into cli_pol values(2,2)

insert into cli_pol values(2,1)

insert into cli_pol values(3,3)

/*inserting the records will be done already*/

/*function declaration*/

create or replace function tot_mat(cno in number)

return number is totamt number;

begin

select sum(maturity_amt) into totamt from cclient cc,policy_info pi,cli_pol cp

where cp.client_no=cno and pi.policy_no=cp.policy_no and cc.client_no=cp.client_no;

return totamt;

end;

/*execution*/

declare

cno number:=:client_no;

mat_amt number;

begin

mat_amt:=tot_mat(cno);

dbms_output.put_line(mat_amt||'is the total maturity amount');

end;
 Download code         next