Lesson 2/7 origin in


#1

Hi !

In Lesson 2/7, we are looking from information in the table 1 : flights, depending from conditions in table 2 : airports,

Specifically, we are looking for flights informations, under condition that the airport is more elevated that 2000.

Here is the code :

SELECT *
FROM flights
WHERE origin in (
SELECT code
FROM airports
WHERE elevation > 2000);

How is the relation between this table is completed, since they have no common columns/columns name ?

Find below the database scheme

flights
id BIGINT
carrier text
origin text
destination text
flight_num text
flight_time int
tail_num text
dep_time text
arr_time text
dep_delay int
arr_delay int
taxi_out int
taxi_in int
distance int
cancelled text
diverted text
dep_day_of_week varchar (255)
dep_month varchar (255)
dep_date text

airports
id bigint
code text
site_number text
fac_type text
fac_use text
faa_region text
faa_dist text
city text
county text
state text
full_name text
own_type text
longitude double
latitude double
elevation integer
aero_cht text
cbd_dist integer
cbd_dir text
act_date text
cert text
fed_agree text
cust_intl text
c_ldg_rts text
joint_use text
mil_rts text
cntl_twr text
major text


#2

In the flights Table
the fields
origin text
and
destination text
use the SAME Airport-code
as
the code field in the Airports-Table

With the query

SELECT code 
 FROM airports 
 WHERE elevation > 2000

you create a list of Airport-codes.

and with

SELECT * 
FROM flights 
WHERE origin in (
the list of Airport-codes );

you select only those flights
which have a =start= origin
which is a member of the list of Airport-codes


#3

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.