Qa. For each origin city, find the destination city (or cities) thatis farthest away (i.e., longest direct flight). Show the name of the origincity, destination city, and the flight time between them -- Qb. Find all origin cities that only serve flights shorter than 3 hours.List each city only once in the result. -- Qb. Find all origin cities that only serve flights shorter than 3 hours.Lis
...[Show More]
Qa. For each origin city, find the destination city (or cities) that
is farthest away (i.e., longest direct flight). Show the name of the origin
city, destination city, and the flight time between them
-- Qb. Find all origin cities that only serve flights shorter than 3 hours.
List each city only once in the result.
-- Qb. Find all origin cities that only serve flights shorter than 3 hours.
List each city only once in the result.
-- Qd. List all cities that cannot be reached from MN though a direct flight
but can be reached with one stop.
- 2.
-- For the question on indexes, we will accept any answer that is soundly
analyzed
-- a) origin_city
-- all of the queries ask for a specific value of origin_city, so an index
will make that lookup faster (there are many cities, so a small percentage
of records will be accessed)
-- Without the index, we have to scan the entire flights table, looking
for tuples with origin_city='Cedar Rapids/Iowa City IA'
-- With the index, the optimizer decided on a different, less costly plan.
The plan is to do an Index Scan on flights_origin_city_idx using 'Cedar
Rapids/Iowa City IA' as the key
-- d) index on dest_city: PostgreSQL picked a different join algorithm that
utilizes the index on dest_city. The estimated cost (EXPLAIN) was lower,
but the running time (EXPLAIN ANALYZE) was similar.
[Show Less]