Rdbms Practical Silps

RDBMS 7 Slips

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

Route(route_no, source, destination, no_of_station)

Bus (bus_no, capacity, depot_name)

Relationship between Route and Bus is one-to-many

Constraints: primary key, foreign key,

depot_name should not be null,

bus capacity should be greater than 40.

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

1) Write a procedure which will display all bus details for a given route.

2) Write a trigger that restricts insertion of route having number of station less than zero.

    (Raise user defined exception and give appropriate message)

3) Write a function which will return total number of routes having number of stations

    greater than 10.

4) Write a cursor which will display route wise bus details. (Use parameterized cursor)

create table rou1

(

rno number(10) primary key,

sour varchar2(20),

desti varchar2(20),

no_stn number(10)

);

create table bu

(

bno number(10) primary key,

capacity number(20)check(capacity>40),

dept_name varchar2(20) NOT NULL,

rno number(10) references rou1(rno)

);

insert into rou1 values(1,'chinchwad','katraj',8);

insert into rou1 values(3,'swargate','hadapsar',9);

insert into rou1 values(5,'nigadi','kothrud',12);

insert into rou1 values(7,'chinchwad','swargate',7);

insert into rou1 values(12,'swargate','katraj',10);

insert into bu values(11,45,'katraj',1);

insert into bu values(12,44,'swargate',3);

insert into bu values(13,48,'kothrud',5);

insert into bu values(14,45,'swargate',7);

insert into bu values(15,50,'katraj',12);

Q.1) Write a procedure which will display all bus details for a given route.

PROCEDURE

create or replace procedure p5

(vno in number)

as

cursor c5 is select bu.* from rou1,bu

where rou1.rno=bu.rno and 

rou1.rno=7;

x c5%rowtype;

begin

for x in c5

loop

dbms_output.put_line(x.bno||'   '||x.capacity||'   '||x.dept_name||'   '||x.rno);

end loop;

end p5;

/

OUTPUT-

Procedure created.

SQL>

SQL> execute p5(7);

14   45   swargate   7

PL/SQL procedure successfully completed.

Q.2) Write a trigger that restricts insertion of route having number of station less than zero.

(Raise user defined exception and give appropriate message)

 

TRIGGER

create or replace trigger t12

before insert on rou1

for each row

begin

if:new.no_stn<0 then

raise_application_error('-20010','no of station is 0');

end if;

end t12;

/

insert into rou1 values(12,'swargate','katraj',-10);

OUTPUT-

Trigger created.

SQL> insert into rou1 values(12,'swargate','katraj',-10);

insert into rou1 values(12,'swargate','katraj',-10)

            *

ERROR at line 1:

ORA-20010: no of station is 0

ORA-06512: at "HR.T12", line 3

ORA-04088: error during execution of trigger 'HR.T12'

Q.3) Write a function which will return total number of routes having number of stations

 greater than 10.

 

FUNCTION

create or replace function f6

(vno in number)

return number

as 

cnt number;

begin

select count(rou1.rno) into cnt from rou1,bu

where rou1.rno=bu.rno and 

rou1.no_stn>10;

if SQL%found then

return cnt;

else

return 0;

end if;

end f6;

/

OUTPUT-

Function created.

SQL> select f6(5)from dual;

     F6(5)

----------

         1

//CALLING//

declare

a number;

b number;

begin

a:=&a;

b:=f6(a);

if(b>0) then

dbms_output.put_line('total no of routes having number of station greater than 10'||b);

else

dbms_output.put_line('no data found');

end if;

end ;

/

 OUTPUT-

Enter value for a: 7

old   5: a:=&a;

new   5: a:=7;

total no of routes having number of station greater than 101

PL/SQL procedure successfully completed.

Q.4) Write a cursor which will display route wise bus details. (Use parameterized cursor)

 

CURSOR

declare

cursor c6(vno  rou1.rno%type) is select bu.* from  rou1,bu

where rou1.rno=bu.rno and rou1.rno=7;

x c6%rowtype;

cno  rou1.rno%type;

begin

cno:=&cno;

for x in c6(cno)

loop

dbms_output.put_line(x.bno||'   '||x.capacity||'   '||x.dept_name||'    '||x.rno);

end loop;

end;

/

OUTPUT-

Enter value for cno: 7

old   7: cno:=&cno;

new   7: cno:=7;

14   45   swargate    7

PL/SQL procedure successfully completed.