Consider the relation FLIGHTFARES that records the fares of nonstop flights from
ID: 3844715 • Letter: C
Question
Consider the relation FLIGHTFARES that records the fares of nonstop flights from one city A to another city B by various airlines. Note that the flights from city B to city A are independent from the flights from A to B. For example: Write SQL query that returns the cost of the cheapest nonstop flight or flights between each pair of the cities. Show the result for the instance table above. Write SQL query that returns the cost of the cheapest cost and flights for traveling between each pair of cities a customer is willing to stop once en-route. Show the result for the instance table above.Explanation / Answer
SELECT * FROM FLIGHTFARES WHERE FARE=(SELECT MIN(FARE) FROM FLIGHTFARES)
SELECT F1.FROMCITY AS FROM_CITY, F1.TOCITY AS VIA_CITY, F2.TOCITY AS TO_CITY, (F1.FARE+ F2.FARE) AS TOTAL_FARE
FROM FLIGHTFARES AS F1, FLIGHTFARES AS F2
WHERE F1.TOCITY=F2.FROMCITY
AND
TOTAL_FARE IN (SELECT MIN(F1.FARE+ F2.FARE)
FROM FROM FLIGHTFARES AS F1, FLIGHTFARES AS F2
WHERE F1.TOCITY=F2.FROMCITY GROUP BY F1.FROMCITY, F2.TOCITY)