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

Capitalization matters when you match text with =. The flights database stores manufacturers in capitals ('BOEING'), airport codes in capitals ('SEA'), and time zones as written ('America/New_York'); manufacturer = 'Boeing' returns no rows. Type the value exactly as the data has it. LIKE is the exception in SQLite: it ignores capitalization for plain letters.

Select the tail numbers (tailnum) and models (model) of Boeing planes from the planes table. The manufacturer is stored in capitals as ‘BOEING’.

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;