Programming > QUESTIONS & ANSWERS > Guidelines > University of Iowa - 06K 230hw3-solution.sql: hw3-queries.sql -- homework 3 sample solu (All)

Guidelines > University of Iowa - 06K 230hw3-solution.sql: hw3-queries.sql -- homework 3 sample solution (queries and indexes)

Document Content and Description Below

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 t ... hem -- 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 More]

Last updated: 3 years ago

Preview 1 out of 3 pages

Buy Now

Instant download

We Accept: