Filtering 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
Filtering rows/records
- Select Boeing Planes’ Tail Numbers and Models
Hint 1
-- HINT: Use a WHERE clause to filter for specific conditions. Syntax example:
SELECT column1, column2
FROM table_name
WHERE condition;Hint 2
-- HINT: Fill in the blanks to select tail numbers and models from the planes table for Boeing planes.
SELECT ___, ___
FROM planes
WHERE manufacturer = '___';- Select airports (faa, name, lat) above 40 latitude and in the America/New_York time zone.
Hint 1
-- HINT: Use WHERE to filter records based on conditions. Remember to use AND for multiple conditions.
SELECT column1, column2, column3
FROM table_name
WHERE condition1 AND condition2;Hint 2
-- HINT: Complete the query to select certain airports. Fill in the blanks appropriately.
SELECT ___, ___, ___
FROM airports
WHERE ___ > ___
AND ___ = '___';- Select airports (faa code, name, and lat) within 40 to 42 degrees latitude.
Hint 1
-- HINT: Use BETWEEN to filter records within a range. Example syntax:
SELECT column1, column2
FROM table_name
WHERE column3 BETWEEN value1 AND value2;Hint 2
-- HINT: Complete this query to select airports based on their latitude. Remember to fill in the blanks:
SELECT ___, ___, ___
FROM airports
WHERE lat BETWEEN ___ AND ___;Filtering text
- Select airlines (carrier code and name) with name starting with an “A”
Hint 1
-- HINT: Use LIKE to filter records based on a pattern. Example syntax:
SELECT column1
FROM table_name
WHERE column1 LIKE 'pattern%';Hint 2
-- HINT: Complete this query to select airlines based on the starting letter of their names. Remember to fill in the blanks:
SELECT ___, ___
FROM airlines
WHERE name LIKE '___%';- Select the tail numbers (
tailnum) of flights having 101 as the second, third, and fourth characters of the tail number.
Hint 1
-- HINT: Use LIKE with underscores (_) to represent single characters in a pattern. For example:
SELECT column
FROM table_name
WHERE column LIKE '_pattern%';Hint 2
-- HINT: Fill in the blanks to complete the query for selecting flights by their tail number pattern.
SELECT ___
FROM flights
WHERE tailnum LIKE '_pattern%';- Select flights (all columns) where the carrier is either “AS” or “HA”
Hint 1
-- HINT: Use the IN operator to specify multiple possible values for a column. Example syntax:
SELECT *
FROM table_name
WHERE column_name IN ('value1', 'value2');Hint 2
-- HINT: Complete this query to select flights from specific carriers. Remember to fill in the blanks:
SELECT ___
FROM flights
WHERE carrier IN ('___', '___');- Select weather records (all columns) where wind direction is missing
Hint 1
-- HINT: Use IS NULL to check for missing values in a column. Example syntax:
SELECT *
FROM table_name
WHERE column_name IS NULL;Hint 2
-- HINT: Complete this query to select records with missing information. Remember to fill in the blanks:
SELECT ___
FROM weather
WHERE ___ IS NULL;- Select weather records (all columns) where temperature is not missing
Hint 1
-- HINT: Use IS NOT NULL to check for non-missing values in a column. Example syntax:
SELECT *
FROM table_name
WHERE column_name IS NOT NULL;Hint 2
-- HINT: Complete this query to select records with available temperature information. Remember to fill in the blanks:
SELECT ___
FROM weather
WHERE ___ IS NOT NULL;