Week9 - Stored Functions / Assess

The questions can come in any order, so make sure you are selecting right option for all questions.

1. Create a function named 'findRoute' which have the metroTrainId as the input parameter with integer as its datatype and it should return the route name of the train.

Design Rules:
If there is route name for given metro train id then it should return the corresponding route name.

create or replace function findRoute(metroTrainId int)
return varchar is
route varchar(255);
begin
select route_name into route
from route
where id in (
  select route_id
  from metro_train
  where id = metroTrainId);
return route;
end;
/

2. Create a function named 'findTheScheduledTime' which have the metroTrainId as the input parameter with integer as its datatype and it should return the scheduled time of the train.

Design Rules:
If there is metro train id then it should return the scheduled time of that train.

create or replace function findTheScheduledTime(metroTrainId in int)
return varchar is
schedule varchar(255);
begin
select scheduled_time into schedule
from train_schedule
where metro_train_id = metroTrainId;
return schedule;
end;
/