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;
    /