Rdbms Practical Silps

RDBMS 14 Slips

Q.1) Consider the following entities and their relationship. [Marks 40]

Company (c_no, c_name, c_city, c_share_value)
Person (p_no, p_name, p_city, p_ph_no)

Relationship between Company and Person is many-to-many with descriptive
attribute no_of_shares.
Constraints: primary key, foreign key,
c_name and p_name should not be null,
no_of_shares should be greater than zero.

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

1) Write a function which will return name of person having maximum number of
shares of given company.

2) Write a cursor which will display person wise share details. (Use parameterized
cursor)

3) Write a procedure which will display names of person who are shareholder of the
given company.

4) Write a trigger which get activated when share value of company become less than
Rs. 10. (Raise user defined exception and give appropriate message)

SLIP-14

create table comp
(
cno number(10) primary key,
cname varchar2(20) NOT NULL,
city varchar2(20),
share_value number(20)check(share_value>0)
);

create table per
(
pno number(10) primary key,
pname varchar2(20) NOT NULL,
pcity varchar2(20),
phno number(10)
);

create table co_p
(
cno number(10)references comp(cno),
pno number(10)references per(pno),
no_of_share number(10)
);



insert into comp values(11,'shree','pune',100000);
insert into comp values(12,'idbi','mumbai',200000);
insert into comp values(13,'hdfc','nasik',200000);
insert into comp values(14,'bajaj','pune',400000);
insert into comp values(15,'sai','pune',500000);


insert into per values(21,'radhika','pune',9890339055);
insert into per values(22,'suraj','pune',9896333057);
insert into per values(23,'sneha','mumbai',8890339025);
insert into per values(24,'samir','pune',9490236055);
insert into per values(25,'raj','nasik',9890436085);


insert into co_p values(11,21,4);
insert into co_p values(12,22,3);
insert into co_p values(13,23,3);
insert into co_p values(14,24,2);
insert into co_p values(15,25,6);


Q.1) Write a function which will return name of person having maximum number of
shares of given company.

FUNCTION
create or replace function f14
(vname in varchar2)
return varchar2
as
cname per.pname%type;
begin
select pname into cname from comp,per,co_p
where comp.cno=co_p.cno and
per.pno=co_p.cno and
co_p.no_of_share=(select max(co_p.no_of_share)from co_p);
if SQL%found then
return cname;
else
return 0;
end if;
end f14;
/

select f14('radhika')from dual;


//calling//

declare
a varchar2(10);
b varchar2(10);
begin
a:=&a;
b:f14(a);
dbms_output.put_line('the name of person having maximum no. of share is'||b);
end;
/

Q.2) Write a cursor which will display person wise share details. (Use parameterized
cursor)

CURSOR
declare
cursor c2(vname in varchar2) is select pname,share_value,no_of_share from comp,per,co_p
where comp.cno=co_p.cno and
per.pno=co_p.cno order by pname;
cname per.pname%type;
x c2%rowtype;
begin
cname:=&cname;
for x in c2(cname)
loop
dbms_output.put_line(x.pname||'  '||x.share_value||'   '||x.no_of_share);
end loop;
end;
/
Enter value for cname: 'hdfc'
old   8: cname:=&cname;
new   8: cname:='hdfc';

PL/SQL procedure successfully completed.

Q.3) Write a procedure which will display names of person who are shareholder of the
given company.

PROCEDURE
create or replace procedure p12
(vname in varchar2)
as
cursor c3 is select pname from comp,per,co_p
where comp.cno=co_p.cno and
per.pno=co_p.cno and
comp.cname='shree';
x c3%rowtype;
begin
for x in c3
loop
dbms_output.put_line(x.pname);
end loop;
end p12;
/
SQL> execute p12('shree');

PL/SQL procedure successfully completed.

Q.4) Write a trigger which get activated when share value of company become less than
Rs. 10. (Raise user defined exception and give appropriate message)

TRIGGER
create or replace trigger t019
before insert on comp
for each row
begin
if:new.share_value<10 then
raise_application_error('-20010','share value of company is less than 10');
end if;
end t019;
/

OUTPUT-

insert into comp values(15,'sai','pune',0);


Trigger created.

SQL> insert into comp values(15,'sai','pune',0);
insert into comp values(15,'sai','pune',0)
            *
ERROR at line 1:
ORA-20010: share value of company is less than 10
ORA-06512: at "HR.T19", line 3
ORA-04088: error during execution of trigger 'HR.T19'