Aggregating Techniques
📚 Schema reference · PNW flights 5 tables
airlines
- carrier
- name
airports
- faa
- name
- lat
- lon
- alt
- tz
- dst
- tzone
flights
- year
- month
- day
- dep_time
- sched_dep_time
- dep_delay
- arr_time
- sched_arr_time
- arr_delay
- carrier
- flight
- tailnum
- origin
- dest
- air_time
- distance
- hour
- minute
- time_hour
planes
- tailnum
- year
- type
- manufacturer
- model
- engines
- seats
- speed
- engine
weather
- origin
- year
- month
- day
- hour
- temp
- dewp
- humid
- wind_dir
- wind_speed
- wind_gust
- precip
- pressure
- visib
- time_hour
Numerical summaries
- Calculate the average temperature from the weather table as
avg_temperature
Hint 1
-- HINT: Use AVG() function to calculate the average of a column. Example syntax:
SELECT AVG(column_name) AS alias
FROM table_name;Hint 2
-- HINT: Complete this query to calculate the average temperature. Remember to fill in the blanks:
SELECT AVG(___ ) AS ___
FROM weather;- Find the maximum arrival time for flights destined to ORD as
max_arr_time
Hint 1
-- HINT: Use MAX() function to find the maximum value of a column. Syntax example:
SELECT MAX(column_name) AS alias
FROM table_name
WHERE condition;Hint 2
-- HINT: Fill in the blanks to complete the query for finding the maximum arrival time to ORD:
SELECT MAX(___) AS ___
FROM flights
WHERE dest = 'ORD';Categorical summaries
- Find the first destination alphabetically for flights from SEA as
first_sea_dest
Hint 1
-- HINT: Use MIN() function to find the minimum (first alphabetically) value of a column. Syntax example:
SELECT MIN(column_name) AS alias
FROM table_name
WHERE condition;Hint 2
-- HINT: Complete this query to find the first destination alphabetically for flights departing from SEA. Fill in the blanks:
SELECT MIN(___) AS first_sea_dest
FROM flights
WHERE origin = 'SEA';Rounding summaries
- Round wind gust values in the weather table to 0 decimal places as
rounded_wind_gust
Hint 1
-- HINT: Use the ROUND() function to round numerical values to a specified number of decimal places. Syntax example:
SELECT ROUND(column_name, number_of_decimal_places) AS alias
FROM table_name;Hint 2
-- HINT: Complete this query to round wind gust values in the weather table to 0 decimal places. Fill in the blanks:
SELECT ROUND(___, 0) AS rounded_wind_gust
FROM weather;