No description, website, or topics provided.
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
README.md
air-traffic.db
airlines.sql
airports.sql
routes.sql

README.md

SQL Homework

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
);

Goal

Find out how many flights went from NYC to Paris

Stretch Goals

  • 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.

Hints

  • The routes table has a column called origin_id and another called destination_id. These map to the id column 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 destination and 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 */