Programming > QUESTIONS & ANSWERS > Questions and Answers > University of Washington - CSE 414hw3-queries.sql (All)

Questions and Answers > University of Washington - CSE 414hw3-queries.sql

Document Content and Description Below

Question 1 For each origin city, find the destination city (or cities) with the longest direct flight. By direct flight, we mean a flight with no intermediate stops. Judge the longest flight in t... ime, not distance. Show the name of the origin city, destination city, and the flight time between them. Do not include duplicates of the same origin/destination city pair. Order the result by origin_city name then destination city name. [333 rows] Question 2 Find all origin cities that only serve flights shorter than 3 hours. You can assume that flights with NULL actual_time are not 3 hours or more. Return only the names of the cities sorted by name. List each city only once in the result. [147 rows] Question 3 For each origin city, find the percentage of departing flights shorter than 3 hours. For this question, treat flights with null actual_time values as longer than 3 hours. Return the name of the city and the percentage value. Order by percentage value. Be careful to handle cities without any flights shorter than 3 hours. We will accept both 0 and NULL as the result for those cities. [327 rows] Question 4 List all cities that cannot be reached from Seattle though a direct flight but can be reached with one stop (i.e., with two flights). Do not include Seattle as one of these destinations (even though you could get back with two flights). Order results alphabetically. [256 rows] Question 5 List all cities that cannot be reached from Seattle though a direct flight nor with one stop (i.e., with two flights). Do not forget to consider all cities that appear in a flight as an origin_city. Order results alphabetically. [3 or 4 rows] uestion 2 */ DROP INDEX IF EXISTS idx_actual_time ON Flights; CREATE INDEX idx_actual_time ON Flights (actual_time); Question 3 Yes the index I created on the actual_time attribute, is used by sql server Question 4 Runtimes: Query 1: - With indexes created: 15 seconds Without indexes created: 16 seconds Query 2: - With indexes created: 15 seconds Without indexes created: 15 seconds Query 3: - With indexes created: 23 seconds Without indexes created: 20 seconds Query 4: - With indexes created: 30 seconds Without indexes created: 26 seconds Query 5: - With indexes created: 282 seconds Without indexes created: 109 seconds */ [Show More]

Last updated: 2 years ago

Preview 1 out of 7 pages

Buy Now

Instant download

We Accept: