Consider a relation DirectFlight(StartCity, DestinationCity) that lists all direct ?ights among cities. Use the recursion facility of SQL:1999 to write a query that ?nds all pairs city 1 , city 2 such that there is an indirect ?ight from city 1 to city 2 with at least two stops in between.

What will be an ideal response?

The simplest way to do this is to compute IndirectFlight similarly to IndirectPrereqView:



CREATE RECURSIVE VIEW IndirectFlight(From,To) AS

         SELECT * FROM DirectFlight

         UNION

         SELECT D.StartCity, I.To

         FROM DirectFlight D, IndirectFlight I

         WHERE D.DestinationCity = I.From

Then we can compute all ?ights with just one stop — Flight1 — and then subtract DirectFlight and Flight1 from IndirectFlight.
One might be tempted to ?rst create a recursive view IndirectFlight2(From,TO,NumOfStops) and then select the ?ights with NumOfStops > 1.



CREATE RECURSIVE VIEW IndirectFlight2(From,To,Stops) AS

         SELECT D.StartCity, D.DestinationCity, 0

         FROM DirectFlight D

         UNION

         SELECT D.StartCity, I.To, I.Stops+1

         FROM DirectFlight D, IndirectFlight2 I

         WHERE D.DestinationCity = I.From

However, this recursive de?nition has a problem: because we keep incrementing the number of stops with each iteration, the evaluation process will not terminate. (Check that the termination condition will never be true!)