Rdbms Practical Slips

                                              RDBMS 2 SLIPS

Q.1) Consider the following entities and their relationship. [Marks 40]
Student (s_reg_no, s_name, s_class)
Competition (comp_no, comp_name, comp_type)

Relationship between Student and Competition is many-to-many with descriptive
attribute rank and year.
Constraints: primary key, foreign key,
primary key for third table(s_reg_no, comp_no, year),
s_name and comp_name should not be null,
comp_type can be sports or academic.

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

1) Write a function which will accept s_reg_no of student and returns total number of
competition in which student has participated in a given year.

2) Write a cursor which will display year wise details of competitions held. (Use
parameterized cursor)

3) Write a procedure which will accept year and type of competition as an input and
display details of competition accordingly.

4) Write a trigger that restricts insertion of rank value greater than 3. (Raise user defined
exception and give appropriate

SOLUTION

create table stu
(
sno number(10) primary key,
sname varchar(20),
cl number(10)
);

create table co
(
cno number(10) primary key,
cname varchar2(20),
ctype varchar2(10) check(ctype in('sports','academic'))
);

create table stu_co
(
sno number(10) references stu(sno),
cno number(10) references co(cno),
year number(10),
rank number(10)
);

insert into stu values(1,'sagar',9);
insert into stu values(2,'kavita',8);
insert into stu values(3,'aaditi',10);
insert into stu values(4,'tabassum',11);
insert into stu values(5,'sanky',12);

insert into co values (10,'E-rangoli','academic');
insert into co values (11,'carrom','sports');
insert into co values (12,'marathon','sports');
insert into co values (13,'programming','academic');
insert into co values (14,'chess','sports');

insert into stu_co values(1,10,2014,1);
insert into stu_co values(2,11,2014,3);
insert into stu_co values(3,12,2016,2);
insert into stu_co values(4,13,2015,2);
insert into stu_co values(5,14,2015,1);


Q.1) Write a function which will accept s_reg_no of student and returns total number of
competition in which student has participated in a given year.

FUNCTION
create or replace function f2
(v_no in number)
return number
as
cnt number;
begin
select count(co.cno) into cnt from stu,co,stu_co
where stu.sno=stu_co.sno and
co.cno=stu_co.cno and year='2015';
if SQL%found then
return 1;
else
return 0;
end if;
end;
/
OUTPUT-
SQL>
SQL>
SQL> select f2(2015) from dual;

  F2(2015)
----------
         1

//Calling Programm//

declare
a number;
b number;
begin
a:=&a;
b:= f2(b);
if(b>0) then
dbms_output.put_line('total no of compitition in given year is'||b);
else
dbms_output.put_line('no data found');
end if;
end;
/

OUTPUT-
Enter value for a: 5
old   5: a:=&a;
new   5: a:=5;
total no of compitition in given year is1

PL/SQL procedure successfully completed.

Q.2) Write a cursor which will display year wise details of competitions held. (Use
parameterized cursor)

CURSOR
declare
cursor c2 is select co.*,stu_co.year from stu,co,stu_co
where stu.sno=stu_co.sno and
co.cno=stu_co.cno order by year;
x c2%rowtype;
begin
for x in c2
loop
dbms_output.put_line(x.cno||'    '||x.cname||'   '||x.ctype||'   '||x.year);
end loop;
end;
/

OUTPUT

10    E-rangoli   academic   2014
11    carrom   sports   2014
14    chess   sports   2015
13    programming   academic   2015
12    marathon   sports   2016

PL/SQL procedure successfully completed.


Q.3) Write a procedure which will accept year and type of competition as an input and
display details of competition accordingly.

PROCEDURE
create or replace procedure p2
(p_year in number,p_type in varchar2)
as
cursor c1  is select co.* from stu,co,stu_co
where stu.sno=stu_co.sno and
co.cno=stu_co.cno and stu_co.year=2014 and co.ctype='sports';
vno co.cno%type;
vname  co.cname%type;
vtype  co.ctype%type;
begin
open c1;
loop
fetch c1 into vno,vname,vtype;
exit when c1%notfound;
dbms_output.put_line(vno||'    '||vname||'   '||vtype);
end loop;
close c1;
end p2;
/

output
Procedure created.

SQL> execute p2(2014,'sports');
11    carrom   sports

PL/SQL procedure successfully completed.

Q.4) Write a trigger that restricts insertion of rank value greater than 3. (Raise user defined
exception and give appropriate

TRIGGER

create or replace trigger t4
before insert on stu_co
for each row
begin
if:new.rank>3 then
raise_application_error('-20010','rank should be greater than 3');
end if;
end;
/

OUTPUT
Trigger created.
 insert into stu_co values(5,14,2015,5);

            *
ERROR at line 1:
ORA-20010: rank should be greater than 3
ORA-06512: at "HR.T4", line 3
ORA-04088: error during execution of trigger 'HR.T4'