All flight routes from Vienna
Previously, you looked at how the flight data is structured. You have already identified the necessary fields from the flightPlan
table. These will be used in this exercise for the anchor and the recursive query.
The task of this exercise is to combine this knowledge to create a recursive query that:
- gets all possible flights from
Vienna
- has a
travel cost
under500
Euro - has fewer than
5
stops
You should output only the destinations and the corresponding costs!
This exercise is part of the course
Hierarchical and Recursive Queries in SQL Server
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
WITH flight_route (Departure, Arrival, stops) AS(
SELECT
f.Departure, f.Arrival,
-- Initialize the number of stops
___
FROM flightPlan f
WHERE Departure = 'Vienna'
UNION ALL
SELECT
p.Departure, f.Arrival,
-- Increment the number of stops
p.stops + ___
FROM flightPlan f, flight_route p
-- Limit the number of stops
WHERE p.Arrival = f.Departure AND
p.stops < ___)
SELECT
DISTINCT Arrival,
Departure,
stops
FROM flight_route;