Rdbms Practical Silps

RDBMS 11 Slips

Q.1) Consider the following entities and their relationship. [Marks 40]
Researcher (r_no, r_name, r_city)
Research_Paper (rp_no, rp_title, rp_subject, rp_level)

Relationship between Researcher and Research_Paper is many-to-many with
descriptive attribute year.
Constraints: primary key, foreign key,
r_name and rp_title should not be null,
rp_subject can be computer, electronics or finance.
rp_level can be state, national or international.

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

1) Write a procedure which will display details of research paper of a given subject for a
specified year.

2) Write a trigger before insert or update of each row of research_paper published after
2010 be entered into table. (Raise user defined exception and give appropriate
message)

3) Write a function which will return name of subject for which maximum numbers of
national level papers were presented by researcher in year 2013.

4) Write a cursor which will display rp_level wise and researcher wise details of
research paper presented by them.


create table res
(
rno number(10) primary key,
rname varchar2(20) NOT NULL,
city varchar2(20)
);

create table re_paper
(
rpno number(10)primary key,
rpname varchar2(20) NOT NULL,
sub varchar2(20)check(sub in('computer','electronics','finance')),
rpl varchar2(30)check(rpl in('state','national','international'))
);

create table r_p
(
rno number(10)references res(rno),
rpno number(10)references re_paper(rpno),
year number(10)
);

insert into res values(1,'rahul','pune');
insert into res values(2,'reshma','mumbai');
insert into res values(3,'karan','nasik');
insert into res values(4,'raj','pune');
insert into res values(5,'rubina','thane');

insert into re_paper values(21,'hardware','computer','state');
insert into re_paper values(22,'led','electronics','national');
insert into re_paper values(23,'mobile','electronics','international');
insert into re_paper values(24,'marketing','finance','state');
insert into re_paper values(25,'software','computer','national');

insert into r_p values(1,21,2010);
insert into r_p values(2,22,2016);
insert into r_p values(3,23,2015);
insert into r_p values(4,24,2012);
insert into r_p values(5,25,2009);


Q.1) Write a procedure which will display details of research paper of a given subject for a
specified year.

PROCEDURE
create or replace procedure p11
(psub in varchar2)
as
cursor c4 is select re_paper.* from res,re_paper,r_p
where res.rno=r_p.rno and
re_paper.rpno=r_p.rpno and
year=2015;
x c4%rowtype;
begin
for x in c4
loop
dbms_output.put_line(x.rpno||'  '||x.rpname||'   '||x.sub||'  '||x.rpl);
end loop;
end p11;
/
OUTPUT-

Procedure created.

SQL> execute p11('mobile');
23  mobile   electronics  international

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

Q.2) Write a trigger before insert or update of each row of research_paper published after
2010 be entered into table. (Raise user defined exception and give appropriate
message)

TRIGGER
create or replace trigger t17
before insert or update on r_p
for each row
begin
if inserting then
if:new.year>2010 then
raise_application_error('-20010','reserch paper published after 2010');
end if;
end if;
if updating then
if:new.year>2010 then
raise_application_error('-20010','reserch paper published after 2010');
end if;
end if;
end t17;
/

OUTPUT-

insert into r_p values(1,21,2018);
SQL>
SQL> insert into r_p values(1,21,2018);
insert into r_p values(1,21,2018)
            *
ERROR at line 1:
ORA-20010: reserch paper published after 2010
ORA-06512: at "SYSTEM.T17", line 4
ORA-04088: error during execution of trigger 'SYSTEM.T17'

update r_p set year=2018
where rno=1;


SQL> update r_p set year=2018
  2  where rno=1;
update r_p set year=2018
       *
ERROR at line 1:
ORA-20010: reserch paper published after 2010
ORA-06512: at "SYSTEM.T17", line 9
ORA-04088: error during execution of trigger 'SYSTEM.T17'
..............................................................

Q.3) Write a function which will return name of subject for which maximum numbers of
national level papers were presented by researcher in year 2013.

FUNCTION
create or replace function f11
(vl in varchar2)
return varchar2
as
vsub re_paper.sub%type;
begin
select max(re_paper.sub)into vsub from res,re_paper,r_p
where res.rno=r_p.rno and
re_paper.rpno=r_p.rpno and
re_paper.rpl='national'and
year=2009;
if SQL%found then
return vsub;
else
return 0;
end if;
end f11;
/
OUTPUT-


Function created.

SQL> select f11('national') from dual;

F11('NATIONAL')
--------------------------------------------

computer

//CALLING//

declare
a varchar2(10);
b varchar2(10);
begin
a:=&a;
b:=f11(a);
dbms_output.put_line('name of subject having maximum national level paper in 2009 is'||b);
end;
/
OUTPUT-

Enter value for a: 'national'
old   5: a:=&a;
new   5: a:='national';
name of subject having maximum national level paper in 2009 iscomputer

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

Q.4) Write a cursor which will display rp_level wise and researcher wise details of
research paper presented by them.

CURSOR
declare
cursor c6(vl in varchar2) is select re_paper.rpname,sub,res.rname from res,re_paper,r_p
where res.rno=r_p.rno and
re_paper.rpno=r_p.rpno order by rpl;
cl re_paper.rpl%type;
x c6%rowtype;
begin
cl:=&cl;
for x in c6(cl)
loop
dbms_output.put_line(x.rpname||'   '||x.sub||'  '||x.rname);
end loop;
end;
/

OUTPUT-

Enter value for cl: 'state'
old   8: cl:=&cl;
new   8: cl:='state';
mobile   electronics  karan
led   electronics  reshma
software   computer  rubina
marketing   finance  raj
hardware   computer  rahul

PL/SQL procedure successfully completed.