Try all SQL command in Impala.

Question 1.

The flights dataset includes the departure delay (in minutes) and the scheduled time of departure (as an integer, for example 3:14 in the afternoon is 1514).type and run a query to find the average delay of only those flights that were scheduled to depart after 1:00 in the afternoon. Do not include those scheduled for exactly 1:00. Report to the nearest minute. Note: There are two columns related to departure timebe sure you're using the scheduled departure time.

Question 2.

In the fly.flights table, the air time of each flight is given in minutes by the air_time column. type and run a query to find the average air_time of the flights, in hours, to the nearest tenth of an hour.

Question 3.

Type and run a query on the fly.planes table that would answer the question, "How many different manufacturer values are there for each type of aircraft?" Then use the results to enter the number of different values for balloon manufacturers are included in the table.

Question 4

Type and run a query in the VM to find all the airports with average departure delays of more than 30 minutes. (Note that you want the origin airports, not the destinations. Also, the dep_delay column is given in minutes.) How many airports have more than 30 minutes for their average departure delay?

Question 5

Suppose you want to find the longest-distance flights in the fly.flights table for a particular carrier, and then find the flights with the shortest air time.

Type a query to return the data in fly.flights for American Airlines (carrier is AA) so that they are sorted by distance with the longest distance first, and for those that tie distances, by air_time with the shortest air time first. Execute the query in Hue using Impala. What's the shortest air time for the longest distance?

Question 6

type and run a SQL query to determine which airport in the fly.airports table is closest to the geographical (not magnetic) North Pole, using the following calculation for the distance in kilometers, using the latitude (lat) column: distance = 6371 * 2 * asin(least(1, sin(radians(90 - lat) / 2)))

(Note: The least function chooses the minimum value among two or more scalar valuessimilar to the MIN function, but MIN works on values in a column.)

Which airport is closest to the geographical North Pole?

Question 7.

type a query to run in Impala to return the distinct union of the columns zip_plus_4 (type STRING, has values like '94306-0001') in the california_emp table and zip (type INT, has values like 94105) in the california_offices table.