Consider the database with single relation FLIGHTFARES that records the fares of
ID: 3736414 • Letter: C
Question
Consider the database with single 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: FLIGHTFARES: ToCity Memphis Memphis FareAirline FromCity San Francisco San Francisco Memphis Memphis Memphis Chicago Memphis Memphis San Francisco San Francisco New York San Francisco New York Americarn United United American Jet Blue Delta Delta American United United United Jet Blue United 300 350 San Francisco 250 San Francisco 250 250 Chicago New York New York New York New York Chicago Chicago Chicago San Francisco 500 400 0 650 0 Implement the database using a DBMS of your choice, including: [a] 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 data above. [b] 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 data above. Identify the DBMS and show the SQL code used.Explanation / Answer
Solution:
The first question is done as per Chegg guidelines, please repost others.
a)
SELECT MIN(Fare) FROM Flightfares WHERE flightType= 'nonstop' OR GROUP BY FromCity, ToCity
b)
SELECT MIN(Fare) FROM Flightfares WHERE flightType= 'nonstop' OR GROUP BY FromCity, ToCity IN BETWEEN FromCity, ToCity
I think more info is required to answer the b part since there maybe some more tables in this schema.
I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)