SQL: Table Transformation Lesson 20 Sample Solution

Instructions:
Find the percentage of high elevation airports (elevation >= 2000) by state from the airports table.

In the query, alias the percentage column as percentage_high_elevation_airports.

Sample Solution:
SELECT state, 100.0 * sum(CASE WHEN elevation >= 2000 THEN 1 ELSE 0 END) / count(*)  as percentage_high_elevation_airports FROM airports GROUP BY state;

SQL: Table Transformation Lesson 19 Sample Solution

Instructions:
Find the percentage of flights from Delta by origin (carrier = 'DL')

In the query, alias the column as
percentage_flight_distance_from_delta

Sample Solution:
SELECT origin,
    100.0*(sum(CASE WHEN carrier = 'DL' THEN distance ELSE 0 END)/sum(distance)) as percentage_flight_distance_from_delta FROM flights
GROUP BY origin;

SQL: Table Transformation Lesson 18 Sample Solution

Instructions:
Find both the total flight distance as and flight distance by origin for Delta (carrier = 'DL').

Alias the flight distance as total_flight_distance and the and flight distance by origin as total_delta_flight_distance.

Sample Solution:
SELECT origin, sum(distance) as total_flight_distance, sum(CASE WHEN carrier = 'DL' THEN distance ELSE 0 END) as total_delta_flight_distance
FROM flights
GROUP BY origin;

SQL: Table Transformation Lesson 17 Sample Solution

Instructions:
Write a query to count the number of low elevation airports by state where low elevation is defined as less than 1000 ft.

Be sure to alias the counted airports as count_low_elevation_airports.

Sample Solution:
SELECT state,
    COUNT(
      CASE
      WHEN elevation < 1000 THEN 1
        ELSE NULL
      END) as count_low_elevation_aiports
FROM airports
GROUP BY state;

SQL: Table Transformation Lesson 16 Sample Solution

Instructions:
Modify the case statement's such that when the elevation is less than 250, the elevation_tier column returns 'Low', when between 250 and 1749 it returns 'Medium', and when greater than or equal to 1750 it returns 'High'.

Be sure to alias the conditional statement as elevation_tier, in your query.

Sample Solution:
SELECT
    CASE
        WHEN elevation < 250 THEN 'Low'
        WHEN elevation BETWEEN 250 AND 1749 THEN 'Medium'
        WHEN elevation >= 1750 THEN 'High'
        ELSE 'Unknown'
    END AS elevation_tier
    , COUNT(*)
FROM airports
GROUP BY 1;

SQL: Table Transformation Lesson 15 Sample Solution

Instructions:
Count the number of rows from the flights table, where arr_time is not null and the destination is ATL.

Sample Solution:
SELECT COUNT(*) FROM flights WHERE arr_time IS NOT NULL AND destination = 'ATL';

SQL: Table Transformation Lesson 14 Sample Solution

Instructions:
Count the number of rows in the flights table, representing the total number of flights contained in the table.

Sample Solution:
SELECT COUNT(*) FROM flights;

SQL: Table Transformation Lesson 12 Sample Solution

Instructions:
Select the items in the category column that are in the legacy_products table and not in the new_products table.

Sample Solution:
SELECT category FROM legacy_products
EXCEPT
SELECT category FROM new_products;

SQL: Table Transformation Lesson 11 Sample Solution

Instructions:
Select the items in the category column that are both in the newly acquired new_products table and the legacy_products table.

Sample Solution:
SELECT category FROM new_products
INTERSECT
SELECT category FROM legacy_products;

SQL: Table Transformation Lesson 9 Sample Solution

Instructions:
Select a complete list of brand names from the legacy_products and new_products tables.

Sample Solution:
SELECT brand FROM legacy_products
UNION
SELECT brand FROM new_products;

SQL: Table Transformation Lesson 8 Sample Solution

Instructions:
Select ten rows from the new_products table.

Sample Solution:
SELECT * FROM new_products LIMIT 10;

SQL: Table Transformation Lesson 6 Sample Solution

Instructions:
Write a query to view flights by origin, flight id, and sequence number. Alias the sequence number column as flight_sequence_number.

Sample Solution:
SELECT origin, id,
    (SELECT COUNT(*)
FROM flights f
WHERE f.id < flights.id
AND f.origin=flights.origin) + 1
 AS flight_sequence_number
FROM flights;

SQL: Table Transformation Lesson 5 Sample Solution

Instruction:
Find the id of the flights whose distance is below average for their carrier.

Sample Solution:
SELECT id
FROM flights AS f
WHERE distance < (
 SELECT AVG(distance)
 FROM flights
 WHERE carrier = f.carrier);

SQL: Table Transformation Lesson 4 Sample Solution

Instruction:
Find the average total distance flown by day of week and month.

Sample Solution:
SELECT a.dep_month,
       a.dep_day_of_week,
       AVG(a.flight_distance) AS average_distance
  FROM (
        SELECT dep_month,
              dep_day_of_week,
               dep_date,
               sum(distance) AS flight_distance
          FROM flights
         GROUP BY 1,2,3
       ) a
 GROUP BY 1,2
 ORDER BY 1,2;

SQL: Table Transformation Lesson 3 Sample Solution

Instruction:
Find flight information about flights where the Federal Aviation Administration region (faa_region) is the Southern region (ASO).

Sample Solution:
SELECT * FROM flights WHERE origin in
(SELECT code FROM airports WHERE faa_region = 'ASO');

SQL: Table Transformation Lesson 2 Sample Solution

Instruction:
Find flight information about flights where the origin elevation is less than 2000 feet.

Sample Solution:
SELECT * FROM flights WHERE origin in
(SELECT code FROM airports WHERE elevation < 2000);

SQL: Table Transformation Lesson 1 Sample Solution

Instruction:
Select ten rows from the flights table.

Sample Solution:
SELECT * FROM flights LIMIT 10;