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 under 500 Euro
has fewer than 5 stops

You should output only the destinations and the corresponding costs!

Deze oefening maakt deel uit van de cursus

Hierarchical and Recursive Queries in SQL Server

Cursus bekijken

Praktische interactieve oefening

Probeer deze oefening eens door deze voorbeeldcode in te vullen.

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;

Code bewerken en uitvoeren