RDBMS PL/SQL Queries Person,Investment

Q.1)     Consider the following entities and their relationship.                             

Person (p_no, p_name, p_addr)

            Investment (inv_no, inv_name, inv_date, inv_amt)

          Relationship between Person and Investment is one-to-many.

         Constraints:   primary key, foreign key,

                        p_name and inv_name should not be null,

                        inv_amt should be greater than 10000.

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

Person Table

Create table person(pno int primary key,pname varchar(15) NOT NULL,paddr varchar(20));

  insert into person values(1,’Ram’,’pune’);

  insert into person values(2,’sham’,’Mumbai’);

Investment Table 

 create table Investment(invno int primary key,invname varchar(15) NOT NULL,invdate date,invamt int check(invamt>10000),pno int references person);

Insert 

insert into investment values(101,’property’,’05 -jan-2018’,10000,1);

 insert into investment values(102,’land’,’02 -Aug-2019’,15000,1);

       

Q1.  procedure which will display details of person, made investment on given date?

 create or replace procedure p1(d date)

   as

    p person%rowtype;

    begin

    select pname, paddr into p.pname, p.paddr

   from person, investment

    where invdate=d

    and person.pno=investment.pno;

    dbms_output.put_line(p.pname||' '||p.paddr);

   end;

   /

Procedure created.

execute p5('05-jan-18');

___________________________________________________________________________

Q2. Write a trigger that restricts insertion or updation of investment having inv_date greater than current date.(Raise user defined exception and give appropriate message)

 create or replace trigger t2

    before insert or update

    on  investment

    for each row

   declare

    p exception;

   begin

  9if(:new.invdate>'11-sep-19') then

   raise p;

   end if;

  exception

   when p then

  dbms_output.put_line('Enter valid investment date, because investment date

cannot be greater than current date');

 end;

  /

OUTPUT:-

SQL> insert into investment values(103,'land','10-sep-19',5000,2);

Enter valid investment date, because investment date cannot be greater than current date

1 row created.

___________________________________________________________________________

3)Write a function which will return name of person having maximum total amount of investment.

set serveroutput on;

create or replace function fun1(pn in varchar)
return varchar
as
maxinv varchar(15);
Begin
Select(pname)into maxinv from person,investment where
Person.pno=investment.pno and
 investment.pno=(select max(investment.pno)from investment);
if sql%found then
return(maxinv);
else
return null;
end if;
end;
/
Function call

begin

dbms_output.put_line(‘maximum no. of invested person is’||fun1(‘pname’));

end;

/

___________________________________________________________________________

4)Write a cursor which will display person wise details of investment. (Use parameterized cursor)

set serveroutput on;

declare
Cursor c1(pwise person.pname%type)is select pname,invname,invdate,invamount from person,investment where person.pno=investment.pno;
c c1%rowtype;
begin
open c1(‘&pwise’);
loop
fetch c1 into c;
exit when c1%notfound;
dbms_output.put_line(c.pname||’’||c.invname||’ ‘||c.invdate||’ ‘||c.invamount);
end loop;
close c1;
end;
/