RDBMS Do As Directed

Do as directed (any two) 

Train(Train-no,Train-name)

Station(Station-code,Station-name)

Halt(Halt-code,Seq-no,Train-no,Station-no,Time-in,Time-out)

(Halt-code,Seq-no are composite key)

1. Create a procedure which return Time-in and Time-out for given

Train-no and Station-name.

Create or replace procedure proc_gettime(tno number,stnane varchar)As

vtimein  varchar(6);

vtimeout varchar(6);

begin

Select time-in, time-out into vtimein, vtimeout 

from halt h, station s

where h.train-no = tno and s.station-name = stname

and s.station-code = h.station-no;

dbms_output.put_line(vtimein,vtimeout);

end;

/

2. Create a function which return name of train which stops at maximum

stations.

Create or Replace Function Get_Train() Return Varchar AS

vtrainname varchar(30);

Begin

Select Train-name into vtrainname

From Train 

Where Train-no in( Select Train-no

From Halt

Group By Train-no

Having Max(count(Train-no)));

Return vtrain-name;

End;

/

3. Create a trigger which delete all related records from other tables

when any record is deleted from train table.

Create or Replace Trigger Trg_Train

After Delete 

On Train

For Each ROW

BEGIN

Delete From Halt Where Train-no = :new.Train-no;

End;

/