Rdbms 4 Slips
Q.1) Consider the following entities and their relationship. [Marks 40]
Bus(bus_no, capacity, source, destination)
Driver(driver_no, driver_name, license_no, addr, age, salary)
Relationship between Bus and Driver is many-to-many with descriptive attribute
date_of_duty_allotted and shift.
Constraints: primary key, foreign key,
primary key for third table (bus_no, driver_no,date_of_duty_allotted),
driver_name should not be null,
shift can be morning or evening.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a function which will return name of driver having maximum salary.
2) Write a cursor which will display date wise bus and their driver details.
3) Write a procedure which will display bus_no and name of allotted driver on given
date and shift.
4) Write a trigger that restricts insertion or updation of driver table if driver age is less
than 18 or greater than 50. (Raise user defined exception and give appropriate
message).
Solution
create table bus
(
bno number(10) primary key,
capacity number(20),
sour varchar2(20),
desti varchar2(20)
);
create table driv
(
dno number(10) primary key,
dname varchar2(20) NOT NULL,
lno number(10),
address varchar2(20),
age number(10),
sal number(10)
);
create table bus_dri
(
bno number(10) references bus(bno),
dno number(10) references driv(dno),
dt_of_duty_alloc date,
shift varchar2(10) check(shift in('morning','evening'))
);
insert into bus values(123,20,'katraj','nigadi');
insert into bus values(111,25,'marketyard','shivaji nagar');
insert into bus values(113,22,'katraj','hadapasar');
insert into bus values(133,21,'katraj','swargate');
insert into bus values(143,25,'kondhwa','sargate');
insert into driv values(11,'firoj',1234,'pune',32,15000);
insert into driv values(12,'pradip',3456,'pune',36,18000);
insert into driv values(13,'dipak',2345,'nasik',38,20000);
insert into driv values(14,'raj',145,'pune',35,17000);
insert into driv values(15,'sumit',1634,'nagar',39,18000);
insert into bus_dri values(123,11,'22 jul 2018','morning');
insert into bus_dri values(111,12,'26 jul 2018','morning');
insert into bus_dri values(113,13,'27 jul 2018','evening');
insert into bus_dri values(133,14,'29 jul 2018','morning');
insert into bus_dri values(143,15,'21 jul 2018','evening');
Q.1) Write a function which will return name of driver having maximum salary.
FUNCTION
create or replace function f4
(vsal in number)
return varchar2
as
vname driv.dname%type;
begin
select driv.dname into vname from bus,driv,bus_dri
where bus.bno=bus_dri.bno and
driv.dno=bus_dri.dno and driv.sal=(select max(sal) from driv);
if SQL%found then
return vname;
else
return 0;
end if;
end f4;
/
OUTPUT-
Function created.SQL> select f4(20000)from dual;
F4(20000)
------------------------------------
dipak
//Calling Programm//
declare
a number;
b varchar2(50);
begin
a:=&a;
b:= f4(a);
dbms_output.put_line('the name of driver is'||b);
end;
/
OUTPUT-
Enter value for a: 20000
old 5: a:=&a;
new 5: a:=20000;
the name of driver isdipak
PL/SQL procedure successfully completed.
Q.2) Write a cursor which will display date wise bus and their driver details.
CURSOR
declare
cursor c4 (vdt in date) is select bus.*,driv.* from bus,driv,bus_dri
where bus.bno=bus_dri.bno and
driv.dno=bus_dri.dno ;
x c4%rowtype;
cdt bus_dri.dt_of_duty_alloc%type;
begin
cdt:=&cdt;
for x in c4(cdt)
loop
dbms_output.put_line(x.bno||' '||x.capacity||' '||x.sour||' '||x.desti||' '||x.dno||' '||x.dname||' '||x.lno||' '||x.address||
' '||x.age||' '||x.sal);
end loop;
end;
/
OUTPUT-
Enter value for cdt: '29 jul 2018'
old 8: cdt:=&cdt;
new 8: cdt:='29 jul 2018';
123 20 katraj nigadi 11 firoj 1234 pune 32 15000
111 25 marketyard shivaji nagar 12 pradip 3456 pune
36 18000
113 22 katraj hadapasar 13 dipak 2345 nasik 38
20000
133 21 katraj swargate 14 raj 145 pune 35 17000
143 25 kondhwa sargate 15 sumit 1634 nagar 39
18000
PL/SQL procedure successfully completed.
Q.3) Write a procedure which will display bus_no and name of allotted driver on given
date and shift.
PROCEDURE
create or replace procedure p3
(pdt in date,pshif in varchar2)
as
cursor c4 is select bus.bno,driv.dname from bus,driv,bus_dri
where bus.bno=bus_dri.bno and
driv.dno=bus_dri.dno and
bus_dri.dt_of_duty_alloc='29 jul 2018' and shift='morning';
vno bus.bno%type;
vname driv.dname%type;
begin
open c4;
loop
fetch c4 into vno,vname;
exit when c4%notfound;
dbms_output.put_line(vno||' '||vname);
end loop;
close c4;
end p3;
/
Procedure created.
SQL> execute p3('29 jul 2018','morning');
133 raj
PL/SQL procedure successfully completed.
Q.4) Write a trigger that restricts insertion or updation of driver table if driver age is less
than 18 or greater than 50. (Raise user defined exception and give appropriate
message).
TRIGGER
create or replace trigger t7
before insert or update on driv
for each row
begin
if inserting then
if:new.age<18 or:new.age>50 then
raise_application_error('-20010','driver age is less than 18 or greater than 50');
end if;
end if;
if updating then
if:new.age<18 or:new.age>50then
raise_application_error('-20010','driver age is less than 18 or greater than 50');
end if;
end if;
end t7;
/
OUTPUT-
insert into driv values(15,'sumit',1634,'nagar',16,18000);
*
ERROR at line 1:
ORA-20010: driver age is less than 18 or greater than 50
ORA-06512: at "HR.T7", line 4
ORA-04088: error during execution of trigger 'HR.T7'
update driv set age=60
where dno=15;
*
ERROR at line 1:
ORA-20010: driver age is less than 18 or greater than 50
ORA-06512: at "HR.T7", line 9
ORA-04088: error during execution of trigger 'HR.T7'
Tags:
RDBMS