Consider
the following entities and their relationship.
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.
SQL>
set serveroutput on
SQL>
create or replace function fun1(pn in varchar) return varchar as maxpass
varchar(15);
2
begin
3
select max(noofpassenger) into maxpass from train;
4 if
sql %found then
5
return(maxpass);
6 else
7
return null;
8 end
if;
9 end
;
10 /
Function
created.
SQL>
begin
2
dbms_output.put_line('no of competition-'||fun1('12/5/15'));
3 end;
4 /
no
of competition-2
PL/SQL
procedure successfully completed.
2)Write
a cursor which will display date wise train and their passenger details.
SQL>
set serveroutput on
SQL>
declare
2
cursor c2 is select tname,pname,addr,age,trainpassenger.tpdate from
train, passenger, trainpassenger where train.tno=trainpassenger.tno and
passenger.pno=trainpassenger.pno order by trainpassenger.tpdate;
3 c c2%rowtype;
4
begin
5 open
c2;
6 loop
7
fetch c2 into c;
8 exit
when c2%notfound;
9
dbms_output.put_line(c.tpdate||' '||c.tname||' '||c.pname||'
'||c.addr||' '||c.age);
10 end
loop;
11
close c2;
12 end;
13 /
10/9/15
deccan roshan hadapsar 19
12/5/15
garib rath lokesh daund 18
12/5/15
garib rath harsh delhi 18
3/5/14
duranto akash daund 19
8/12/11
shatabdhi vishal daund 20
9/4/13
konark shashikant pune 19
PL/SQL
procedure successfully completed.
