Course Content
Intermediate SQL
2. Nested Subqueries
4. Left, Right, and Inner Joins
Intermediate SQL
Challenge 2
Task
You need to join the three tables: passenger
, departure
and flights
:
- Select
name_passenger
andsurname_passenger
from thepassenger
table; - Select
travel_time
from theflights
table; - Join the
departure
table andpassenger
table by thenumber_of_departure
column. Join thedeparture
table andflights
table byflight_number
column; - Make filtering: flight time (
travel_time
column) must be more than10
hours.
Here's a short example of the departure
table:
number_of_departure | flight_number | departure_date | departure_time |
1 | 3 | 2022-07-16 | 08:00:00 |
2 | 1 | 2022-07-18 | 13:15:00 |
... | ... | ... | ... |
6 | 3 | 2022-07-16 | 08:00:00 |
Here's a short example of the passenger
table:
id | number_of_departure | name_passenger | surname_passenger |
1 | 5 | Liam | Smith |
2 | 5 | Olivia | Johnson |
... | ... | ... | ... |
18 | 5 | Olivia | Johnson |
Here's a short example of the flights
table:
flight_number | start_point_flight | end_point_flight | travel_time |
1 | Paris | New York | 8 |
2 | Chicago | New Delhi | 15 |
3 | New York | Dubai | 12 |
4 | Los Angeles | Honolulu | 5 |
Everything was clear?
Section 3. Chapter 7