Oefeningen Stored Functions

Maak een stored function die aangeeft om welk uur en vanuit welke luchthaven de eerstvolgende vlucht vandaag vertrekt.
TIP: maak gebruik van de functies Now(), Time(), DayOfWeek() en Concat()

MSSQL

MySQL

CREATE FUNCTION FirstFlight()
RETURNS VARCHAR(255)
BEGIN
    DECLARE @resultaat varchar(255);     
    SELECT TOP 1
        @resultaat = CONCAT((flightdep.DepTime),' ', 
            airport.AirportName)  
    FROM
        flightdep
            INNER JOIN
        flight ON flightdep.FlightID = flight.FlightID
            INNER JOIN
        route ON flight.RouteID = route.RouteID
            INNER JOIN
        airport ON route.[From] = airport.AirportID       
    WHERE
        flightdep.DepDay = DATEPART(WEEKDAY, GETDATE())
            AND flightdep.DepTime > 
                CAST(GETDATE() AS TIME);
RETURN @resultaat;
END

Command(s) completed successfully.

DELIMITER //
CREATE FUNCTION FirstFlight()
RETURNS VARCHAR(255)
BEGIN
    DECLARE resultaat varchar(255);
    SELECT 
        CONCAT((flightdep.DepTime),' ', 
          airport.AirportName) INTO resultaat
    FROM
        flightdep
            INNER JOIN
        flight ON flightdep.FlightID = flight.FlightID
            INNER JOIN
        route ON flight.RouteID = route.RouteID
            INNER JOIN
        airport ON route.`From` = airport.AirportID       
    WHERE
        flightdep.DepDay = DAYOFWEEK(NOW())
            AND flightdep.DepTime > NOW()
    LIMIT 1;
RETURN resultaat;
END //

(0 row(s) affected)

SELECT [dbo].[FirstFlight]();

(1 row(s) affected)

SELECT FirstFlight();

(1 row(s) returned)

	(No column name)
1	09:10:00.0000000 Orly Airport

	FirstFlight()
1	09:10:00 Orly Airport

Maak een stored function die voor een op te geven luchthavencode (input-parameter) aangeeft om welk uur de eerstvolgende vlucht vandaag vertrekt uit die luchthaven.

MSSQL

MySQL

CREATE FUNCTION [dbo].[FirstFlightFromAirportCode]
    (
    @airportNr INT
    )
RETURNS VARCHAR(255)
BEGIN
    DECLARE @resultaat varchar(255);
    SELECT TOP 1 
        @resultaat = CONCAT
        (
        flightdep.DepTime,' ', airport.AirportName
        )
    FROM
        flightdep
            INNER JOIN
        flight ON flightdep.FlightID = 
                      flight.FlightID
            INNER JOIN
        route ON flight.RouteID = route.RouteID
            INNER JOIN
        airport ON route.[From] = airport.AirportID
    WHERE
        airport.AirportID = @airportNr
            AND flightdep.DepDay = 
                DATEPART(WEEKDAY, GETDATE())
            AND flightdep.DepTime > 
                CAST(GETDATE() AS TIME);
RETURN @resultaat;
END

Command(s) completed successfully.

DELIMITER //
CREATE FUNCTION FirstFlightFromAirportCode
    (
    airportNr INT
    )
RETURNS VARCHAR(255)
BEGIN
    DECLARE resultaat varchar(255);
    SELECT 
        CONCAT(flightdep.DepTime,' ', 
            airport.AirportName) INTO resultaat
    FROM
        flightdep
            INNER JOIN
        flight ON flightdep.FlightID = 
                      flight.FlightID
            INNER JOIN
        route ON flight.RouteID = route.RouteID
            INNER JOIN
        airport ON route.`From` = airport.AirportID
    WHERE
        airport.AirportID = airportNr
            AND flightdep.DepDay = DAYOFWEEK(NOW())
            AND flightdep.DepTime > NOW()
    LIMIT 1;
RETURN resultaat;
END //

(0 row(s) affected)

SELECT [dbo].[FirstFlightFromAirportCode](126);

(1 row(s) returned)

SELECT FirstFlightFromAirportCode(126);

(1 row(s) returned)

	(No column name)
1	21:30:00.0000000 Chhatrapati Shivaji International Airport

	FirstFlightFromAirportCode(126)
1	21:30:00 Chhatrapati Shivaji International Airport

Maak een stored function die voor een bepaald vlucht-id, datum en klasse (inputparameters) uit tabel stats de prijs (CurrPrice) aangeeft.

MSSQL

MySQL

CREATE FUNCTION PriceFlight
    (
    @flightnr INT, @datum DATE, @klasse INT
    )
RETURNS INT
BEGIN
    DECLARE @resultaat INT;
    SELECT TOP 1
        @resultaat = CurrPrice 
    FROM
        [stats]
    WHERE [stats].FlightID = @flightnr
        AND [stats].FlightDate = @datum
        AND stats.ClassID = @klasse
RETURN @resultaat;
END

Command(s) completed successfully.

DELIMITER //
CREATE FUNCTION PriceFlight
    (
    flightnr INT, datum DATE, klasse INT
    )
RETURNS INT
BEGIN
    DECLARE resultaat INT;
    SELECT 
        CurrPrice INTO resultaat
    FROM
        stats
    WHERE stats.FlightID = flightnr
        AND stats.FlightDate = DATE(datum)
        AND stats.ClassID = klasse
    LIMIT 1;
RETURN resultaat;
END //

(0 row(s) affected)

SELECT [dbo].[PriceFlight](652, '2009/1/20', 2);

(1 row(s) returned)

SELECT PriceFlight(652, '2009/1/20', 2); -- 200

(1 row(s) returned)

	(No column name)
1	200

	PriceFlight(652, ‘2009/1/20’, 2)
1	200

SELECT [dbo].[PriceFlight](652, '2009/1/20', 3);

(1 row(s) returned)

SELECT PriceFlight(652, '2009/1/20', 3); -- 50

(1 row(s) returned)

	(No column name)
1	50

	PriceFlight(652, ‘2009/1/20’, 3)
1	50