Rdbms Practical Silps

RDBMS 6 Slips

Q.1) Consider the following entities and their relationship. [Marks 40]
Train(t_no, t_name)
Passenger (p_no, p_name, addr, age)

Relationship between Train and Passenger is many-to-many with descriptive
attribute date, seat_no and amt.
Constraints : primary key, foreign key,
primary key for third table (t_no, p_no, date),
t_name and p_name should not be null,
amt 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 display train details having maximum passenger for a
given date.

2) Write a cursor which will display date wise train and their passenger details.

3) Write a procedure which will display passenger details of given train on ‘1 July
2014’.

4) Write a trigger that restricts insertion of train_passenger detail having date less than
current date. (Raise user defined exception and give appropriate message)

create table tra02
(
tno number(10) primary key,
tname varchar2(20)NOT NULL
);

create table pass05
(
pno number(10) primary key,
pname varchar2(20)NOT NULL,
address varchar2(10),
age number(10)
);

create table tr_pass08
(
tno number(10) references tra02(tno),
pno number(10) references pass05(pno),
dt date,
sno number(10),
amt number(10)check(amt>0)
);


insert into tra02 values(11,'kokan');
insert into tra02 values(12,'mumbai express');
insert into tra02 values(13,'kamyani');
insert into tra02 values(14,'shatabdi');
insert into tra02 values(15,'holiday');


insert into pass05 values(111,'rahul','pune',34);
insert into pass05 values(112,'sagar','mumbai',30);
insert into pass05 values(113,'veena','pune',31);
insert into pass05 values(114,'sahid','nasik',25);
insert into pass05 values(115,'sneha','mumbai',35);


insert into tr_pass08 values(11,111,'28 aug 2018',23,1000);
insert into tr_pass08 values(12,112,'25 aug 2018',24,7500);
insert into tr_pass08 values(13,113,'22 jul 2018',30,2000);
insert into tr_pass08 values(14,114,'28 jul 2018',12,1500);
insert into tr_pass08 values(15,115,'18 aug 2018',22,700);


Q.1) Write a procedure which will display passenger details of given train on ‘1 July
2014’.

PROCEDURE

create or replace procedure p4
(pdt in date)
as
cursor c2 is select pass05.* from tra02,pass05,tr_pass08
where tra02.tno=tr_pass08.tno and
pass05.pno=tr_pass08.pno and
tr_pass08.dt='28 jul 2018';
x c2%rowtype;
begin
for x in c2
loop
dbms_output.put_line(x.pno||'   '||x.pname||'    '||x.address||'   '||x.age);
end loop;
end p4;
/

output-
Procedure created.

SQL> execute p4('28 jul 2018');
114   sahid    nasik   25

PL/SQL procedure successfully completed.

Q.2) Write a trigger that restricts insertion of train_passenger detail having date less than
current date. (Raise user defined exception and give appropriate message)

TRIGGER

create or replace trigger t111
before insert on tr_pass08
for each row
declare
a varchar2(10);
b varchar2(10);
begin
a:=to_char(:new.dt,'dd-mm-yyyy');
b:=to_char(sysdate,'dd-mm-yyyy');
if(a<b) then
raise_application_error('-20010','date  is less than current date');
end if;
end t111;
/
OUTPUT-
insert into tr_pass08 values(18,220,'21 sep 2016',25,1100);


Trigger created.

SQL> insert into tr_pass08 values(18,220,'21 sep 2016',25,1100);
insert into tr_pass08 values(18,220,'21 sep 2016',25,1100)
            *
ERROR at line 1:
ORA-20010: date  is less than current date
ORA-06512: at "SYSTEM.T111", line 8
ORA-04088: error during execution of trigger 'SYSTEM.T111'



Q.3) Write a cursor which will display date wise train and their passenger details.

CURSOR

declare
cursor c4 (vdt in date) is select tra02.*,pass05.* from tra02,pass05,tr_pass08
where tra02.tno=tr_pass08.tno and
pass05.pno=tr_pass08.pno and
tr_pass08.dt='28 jul 2018';
cdt tr_pass08.dt%type;
x c4%rowtype;
begin
cdt:=&cdt;
for x in c4 (cdt)
loop
dbms_output.put_line(x.tno||'  '||x.tname||'   '||x.pno||'   '||x.pname||'   '||x.address||'    '||x.age);
end loop;
end;
/

OUTPUT-
Enter value for cdt: '28 jul 2018'
old   9: cdt:=&cdt;
new   9: cdt:='28 jul 2018';
14  shatabdi   114   sahid   nasik    25

PL/SQL procedure successfully completed.

Q.4) Write a function which will display train details having maximum passenger for a
given date.

FUNCTION

create or replace function f5
(vdt in date)
return varchar2
as
vname tra02.tname%type;
begin
select tname into vname from tra02,pass05,tr_pass08
where tra02.tno=tr_pass08.tno and
pass05.pno=tr_pass08.pno and
tr_pass08.pno=(select max(pno) from tr_pass08) and
tr_pass08.dt='28 jul 2018';
if sql%found then
return 1;
else
return 0;
end if;
end f5;
/

OUTPUT-
Function created.

SQL> select f5('28 jul 2018') from dual;

F5('28JUL2018')
---------------------------------------------

shatabdi