create table airlines ( airline_id integer, airline_name text, alias text, iata text, airline_icao text, callsign text, airline_country text, active text ); create table airports ( airport_id integer, name text, city text, country text, iata_faa text, icao text, latitude text, longitude text, altitude text, utc_offset text, dst text, tz text ); create table routes ( airline_code text, airline_id integer, origin_code text, origin_id integer, dest_code text, dest_id integer, codeshare text, stops integer, equipment text );
Find out how many flights went from NYC to Paris
- Do this so that just the number appears as the result of only one SQL statement
- Which airlines travel from NYC to Paris?
- Find all the flights that leave NYC. Give a list of how many go to each destination city.
The routes table has a column called
origin_idand another called
destination_id. These map to the
idcolumn in the airport table.
You're going to have to use the airports table twice in the same SQL statement. In order to tell which airport is the
destinationand which is the
origin, you're going to have to temporarily rename the airports table like so:
/* note that once you rename a table, you MUST refer to it by its new name */ SELECT * FROM airports AS origin WHERE origin.city = 'New York'; /* later on in the SQL statement, when dealing with the destination, you should do the same for airports AS destination */