DatabaseCreation: FlightDetails.sql

create view FlightDetails (FlightID, FlightNumber, AirplaneId, AirplaneRegNumber, DepartureTime, ArrivalTime,
    DepartureGate, DepartureTerminal, DepartureAirport, DepartureCity, DepartureCountry, DepartureTimezone,
    ArrivalGate, ArrivalTerminal, ArrivalAirport, ArrivalCity, ArrivalCountry, ArrivalTimezone,
    FlightStatus) as
    select flight.id, flight.flightnumber, airplane.id, airplane.registrationnumber, flight.departure, flight.arrival,
           departureGate.number, departureTerminal.name, departureAirport.name,
           departureCity.name, departureCountry.name, departureTimezone.name,
           arrivalGate.number, arrivalTerminal.name, arrivalAirport.name,
           arrivalCity.name, arrivalCountry.name, arrivalTimezone.name, flightstatus.name
        from flight
        join airplane on flight.airplaneid = airplane.id
        join gate as departureGate on departureGate.id = flight.actualgatedepartureid
        join gate as arrivalGate on arrivalGate.id = flight.actualgatearrivalid
        join terminal as departureTerminal on departureTerminal.id = departureGate.terminalid
        join terminal as arrivalTerminal on arrivalTerminal.id = arrivalGate.terminalid
        join airport as departureAirport on departureAirport.id = departureTerminal.airportid
        join airport as arrivalAirport on arrivalAirport.id = arrivalTerminal.airportid
        join city as departureCity on departureAirport.cityid = departureCity.id
        join city as arrivalCity on arrivalAirport.cityid = arrivalCity.id
        join country as departureCountry on departureCountry.id = departureCity.countryid
        join country as arrivalCountry on arrivalCity.countryid = arrivalCountry.id
        join timezone as departureTimezone on departureTimezone.id = departureAirport.timezoneid
        join timezone as arrivalTimezone on arrivalTimezone.id = arrivalAirport.timezoneid
        join flightstatus on flight.statusid = flightstatus.id;