Sorting and Grouping 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

Sorting

Sort flights (all columns) by departure delay

Hint 1

-- HINT: Use the ORDER BY clause to sort results by a specific column. Syntax example:
SELECT column_name
FROM table_name
ORDER BY column_name;

Hint 2

-- HINT: Complete this query to sort flights by departure delay. Fill in the blanks:
SELECT ___
FROM flights
ORDER BY ___;

Sort flights (all columns) by descending arrival delay

Hint 1

-- HINT: Use the ORDER BY clause along with DESC to sort results in descending order. Example:
SELECT column_name
FROM table_name
ORDER BY column_name DESC;

Hint 2

-- HINT: Complete this query to sort flights by descending arrival delay. Fill in the blanks:
SELECT ___
FROM flights
ORDER BY ___ DESC;

Sort weather data (all columns) by descending visibility and then descending wind speed

Hint 1

-- HINT: Use the ORDER BY clause to sort results by multiple columns. You can add DESC to sort in descending order. Example:
SELECT column1, column2
FROM table_name
ORDER BY column1 DESC, column2 DESC;

Hint 2

-- HINT: Complete this query to sort weather data by descending visibility and then by descending wind speed. Fill in the blanks:
SELECT ___
FROM weather
ORDER BY ___ DESC, ___ DESC;

Grouping

Group flights by origin and show each origin along with its average arrival delay as avg_arr_delay

Hint 1

-- HINT: Use the GROUP BY clause to aggregate data by a specific column. AVG() function calculates the average. Example:
SELECT column1, AVG(column2) AS avg_column
FROM table_name
GROUP BY column1;

Hint 2

-- HINT: Fill in the blanks to complete the query for calculating the average arrival delay grouped by origin.
SELECT ___, AVG(___) AS avg_arr_delay
FROM flights
GROUP BY ___;

Group flights by destination and show each dest with its count of flights as total_flights, only for destinations having more than 100 flights

Hint 1

-- HINT: Use GROUP BY to aggregate data by a specific column and HAVING to filter aggregated results. COUNT() function counts the number of records. Example:
SELECT column1, COUNT(*) AS count_column
FROM table_name
GROUP BY column1
HAVING COUNT(*) > condition;

Hint 2

-- HINT: Fill in the blanks to complete the query for counting the number of flights grouped by destination, only including those with more than 100 flights.
SELECT ___, COUNT(*) AS total_flights
FROM flights
GROUP BY ___
HAVING COUNT(*) > ___;