SQL Coding Assignment
10/10 points earned (100%)
Quiz passed!
Back to Week 3
1.
In this quiz, you'll be writing queries based on the following database. Follow this link to
access a larger picture of the databas
...
SQL Coding Assignment
10/10 points earned (100%)
Quiz passed!
Back to Week 3
1.
In this quiz, you'll be writing queries based on the following database. Follow this link to
access a larger picture of the database.
Note: your SQL code entries will not be saved between quiz attempts! Please copy paste them
somewhere so you don't have to retype the entire code when you take the quiz again.
How many aircrafts are there in the PLANES table?
Refer to the following video if you need a refresher: video 1.
Correct
Correct! Here's the code for your reference:
SELECT COUNT(*)
p1oi/n1ts
25
1 SELECT
Run
FROM PLANES
50
562
10961
2.
Write a query that provides a list of all planes that have a seat count of 100 or more, ordered
from lowest to highest number of seats.
What is the TAIL_NUMBER of the plane with the second lowest number of seats in that list?
Refer to the following video if you need a refresher: video 2.
Correct
Correct! Here's the code for your reference:
SELECT *
FROM PLANES
WHERE SEAT_COUNT >= 100
ORDER BY SEAT_COUNT
p1oi/n1ts
N114DD
N132AA
N120EE
N126AA
1 SELECT
Run
3.
Write a query that provides the number of Ƞights Ƞown by each aircraft.
Which aircraft Ƞew the most Ƞights?
Refer to the following video if you need a refresher: video 2.
Correct
Correct! Here's the code for your reference:
SELECT TAIL_NUMBER, COUNT(*) AS NUM_FLIGHTS
FROM FLIGHTS
GROUP BY TAIL_NUMBER
ORDER BY NUM_FLIGHTS DESC
p1oi/n1ts
N125EE
N135DD
N120EE
N111AA
4.
p1oi/n1ts
1 SELECT
Run
Write a query that provides a list of planes that Ƞew more than 600 passengers across all
Ƞights.
How many planes are in that list?
Refer to the following video if you need a refresher: video 2.
Correct
Correct! Here's the code for your reference:
SELECT TAIL_NUMBER, SUM(PASSENGER_COUNT) AS TOT_PASSENGERS
FROM FLIGHTS
GROUP BY TAIL_NUMBER
HAVING TOT_PASSENGERS > 600
5 2
2
3 7
5.
Write a query that provides the total number of Ƞights by country.
How many Ƞights originated in the United States (Country US)?
Refer to the following video if you need a refresher: video 3.
p1oi/n1ts
SELECT
SELECT
1 1
Run
Run
Correct
Correct! Here's the code for your reference:
SELECT COUNTRY, COUNT(*) AS NUM_FLIGHTS
FROM FLIGHTS a
LEFT JOIN AIRPORTS b
ON a.DEPARTURE_AIRPORT = b.AIRPORT
34
23
12
2
6.
Write a query that provides the total number of Ƞights by regionality.
Which regionality has the second highest number of Ƞights?
Refer to the following video if you need a refresher: video 3.
Correct
Correct! Here's the code for your reference:
SELECT REGIONALITY, COUNT(*) AS NUM_FLIGHTS
p1oi/n1ts
US-US
US-ROW
ROW-NA
ROW-ROW
1 SELECT
Run
FROM FLIGHTS a
LEFT JOIN CITY_PAIRS b
ON a.DEPARTURE_AIRPORT = b.DEPARTURE_AIRPORT AND
a.ARRIVAL_AIRPORT = b.ARRIVAL_AIRPORT
GROUP BY REGIONALITY
7.
How many CITY_PAIRS are there which depart from one of the following airports?
KLAX, KDEN, KORD, KDET, KLGA
Hint: use the IN operator!
Refer to the following video if you need a refresher: video 5.
Correct
Correct! Here's the code for your reference:
SELECT COUNT(*)
FROM CITY_PAIRS
WHERE DEPARTURE_AIRPORT IN ('KLAX','KDEN','KORD','KDET','KLGA')
p1oi/n1ts
722
803
245
104
1 / 1
1 SELECT
8.
How many airports are missing elevation values?
Refer to the following video if you need a refresher: video 5.
Correct
Correct! Here's the code for your reference:
SELECT COUNT(*)
FROM AIRPORTS
WHERE ELEVATION IS NULL
1 / 1
points
10
8 6 4
9.
What Ƞight number had the lowest passenger count (try using a subquery if you can!)?
Refer to the following video if you need a refresher: video 6.
Correct
Correct! Here's the code for your reference:
p1oi/n1ts
ALN626
SELECT
SELECT
1 1
Run
Run
SELECT FLIGHT_NUMBER
FROM FLIGHTS
WHERE PASSENGER_COUNT IN
(SELECT MIN(PASSENGER_COUNT) FROM FLIGHTS)
ALN745
GLB805
SKY996
10.
What is the average distance Ƞown by SKY Airline Ƞights (nearest mile)?
Refer to the following video if you need a refresher: video 6.
Correct
Correct! Here's the code for your reference:
SELECT AVG (DISTANCE) AS AVG_DISTANCE
FROM FLIGHTS a
LEFT JOIN PLANES b
ON a.TAIL_NUMBER = b.TAIL_NUMBER
p1oi/n1ts
1474
1570
1577
1768
1 SELECT
Run
[Show More]
