Selection 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
Selecting columns/fields
- Select all airline names from the
airlinestable.
Hint 1
-- HINT: The syntax for selecting all columns from a table is:
SELECT ___
FROM ___;Hint 2
-- HINT: The syntax for selecting specific columns from airlines is:
SELECT ___
FROM airlines;- Select all columns from the
weathertable.
Hint 1
-- HINT: Use SELECT * to select all columns from a table.
SELECT *
___ ___;Hint 2
-- HINT: Use FROM to specify which table to pull all columns from.
SELECT *
FROM ___;- Select the FAA code and name from the
airportstable.
Hint 1
-- HINT: The syntax for selecting specific columns from a table is:
SELECT ___, ___
FROM ___;Hint 2
-- HINT: You need to specify the columns and table you're selecting from:
SELECT faa, ___
FROM airports;Aliasing
- Select and alias specific columns from the airports table
Hint 1
-- HINT: Start by selecting columns and use AS to give them aliases. For example:
SELECT column_name AS alias_name
FROM table_name;Hint 2
-- HINT: Your query should look something like this, fill in the blanks:
SELECT faa AS code, name AS ___, alt AS ___
FROM airports;- Choose the destination airport code and air time from the flights table with an alias
f
Hint 1
-- HINT: Use the SELECT statement to choose columns and AS to alias the table. For example:
SELECT column_name
FROM table_name AS table_alias;Hint 2
-- HINT: Your query should include the table alias and select specific columns like this:
SELECT f.dest, f.air_time
FROM flights AS f;Unique entries
- Select all distinct time zones (
tz) from theairportstable.
Hint 1
-- HINT: Use SELECT DISTINCT to get unique values from a column. For example:
SELECT DISTINCT column_name
FROM table_name;Hint 2
-- HINT: Fill in the blank with the appropriate column name:
SELECT DISTINCT ___
FROM airports;Counting
- Count the records in the flights table (aliased as
total_flights)
Hint 1
-- HINT: Use the COUNT() function to count the number of records. For example:
SELECT COUNT(*) AS alias
FROM table_name;Hint 2
-- HINT: Apply the COUNT() function to the flights table to count all its records:
SELECT ___ AS ___
FROM ___;- Count all distinct carriers in the flights table as
total_distinct_carriers
Hint 1
-- HINT: To count unique values from a column, use
SELECT COUNT(DISTINCT column) AS alias
FROM table_name;Hint 2
-- HINT: Fill in the blank to complete the query for counting distinct carriers.
SELECT COUNT(DISTINCT ___) AS ___
FROM flights;