Load the database¶
In [14]:
%LOAD nycflights13.sqlite
airports.name ❌ airlines.name (No logical relation!
In [4]:
SELECT *
FROM airlines, flights
WHERE airlines.carrier = flights.carrier
LIMIT 5;
Out[4]:
| carrier | name | year | month | day | dep_time | sched_dep_time | dep_delay | arr_time | sched_arr_time | arr_delay | carrier | flight | tailnum | origin | dest | air_time | distance | hour | minute | time_hour |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 9E | Endeavor Air Inc. | 2013 | 1 | 1 | 810 | 810 | 0.0 | 1048 | 1037 | 11.0 | 9E | 3538 | N915XJ | JFK | MSP | 189.0 | 1029.0 | 8.0 | 10.0 | 1357045200.0 |
| 9E | Endeavor Air Inc. | 2013 | 1 | 1 | 1451 | 1500 | -9.0 | 1634 | 1636 | -2.0 | 9E | 4105 | N8444F | JFK | IAD | 57.0 | 228.0 | 15.0 | 0.0 | 1357070400.0 |
| 9E | Endeavor Air Inc. | 2013 | 1 | 1 | 1452 | 1455 | -3.0 | 1637 | 1639 | -2.0 | 9E | 3295 | N920XJ | JFK | BUF | 68.0 | 301.0 | 14.0 | 55.0 | 1357066800.0 |
| 9E | Endeavor Air Inc. | 2013 | 1 | 1 | 1454 | 1500 | -6.0 | 1635 | 1636 | -1.0 | 9E | 3843 | N8409N | JFK | SYR | 57.0 | 209.0 | 15.0 | 0.0 | 1357070400.0 |
| 9E | Endeavor Air Inc. | 2013 | 1 | 1 | 1507 | 1515 | -8.0 | 1651 | 1656 | -5.0 | 9E | 3792 | N8631E | JFK | ROC | 66.0 | 264.0 | 15.0 | 15.0 | 1357070400.0 |
In [9]:
SELECT origin, dest, name, airlines.carrier, flights.carrier
FROM airlines, flights
WHERE airlines.carrier = flights.carrier
LIMIT 10;
Out[9]:
| origin | dest | name | carrier | carrier |
|---|---|---|---|---|
| JFK | MSP | Endeavor Air Inc. | 9E | 9E |
| JFK | IAD | Endeavor Air Inc. | 9E | 9E |
| JFK | BUF | Endeavor Air Inc. | 9E | 9E |
| JFK | SYR | Endeavor Air Inc. | 9E | 9E |
| JFK | ROC | Endeavor Air Inc. | 9E | 9E |
| JFK | BWI | Endeavor Air Inc. | 9E | 9E |
| JFK | ORD | Endeavor Air Inc. | 9E | 9E |
| JFK | IND | Endeavor Air Inc. | 9E | 9E |
| JFK | BNA | Endeavor Air Inc. | 9E | 9E |
| JFK | BOS | Endeavor Air Inc. | 9E | 9E |
In [10]:
SELECT origin, dest, name
FROM airlines, flights
WHERE airlines.carrier = flights.carrier
LIMIT 10;
Out[10]:
| origin | dest | name |
|---|---|---|
| JFK | MSP | Endeavor Air Inc. |
| JFK | IAD | Endeavor Air Inc. |
| JFK | BUF | Endeavor Air Inc. |
| JFK | SYR | Endeavor Air Inc. |
| JFK | ROC | Endeavor Air Inc. |
| JFK | BWI | Endeavor Air Inc. |
| JFK | ORD | Endeavor Air Inc. |
| JFK | IND | Endeavor Air Inc. |
| JFK | BNA | Endeavor Air Inc. |
| JFK | BOS | Endeavor Air Inc. |
In [11]:
SELECT origin, dest, name
FROM airlines NATURAL JOIN flights
LIMIT 10;
Out[11]:
| origin | dest | name |
|---|---|---|
| JFK | MSP | Endeavor Air Inc. |
| JFK | IAD | Endeavor Air Inc. |
| JFK | BUF | Endeavor Air Inc. |
| JFK | SYR | Endeavor Air Inc. |
| JFK | ROC | Endeavor Air Inc. |
| JFK | BWI | Endeavor Air Inc. |
| JFK | ORD | Endeavor Air Inc. |
| JFK | IND | Endeavor Air Inc. |
| JFK | BNA | Endeavor Air Inc. |
| JFK | BOS | Endeavor Air Inc. |
In [15]:
SELECT origin, dest, name
FROM airlines NATURAL JOIN flights
WHERE dest = "MSP"
LIMIT 10;
Out[15]:
| origin | dest | name |
|---|---|---|
| LGA | MSP | Delta Air Lines Inc. |
| EWR | MSP | ExpressJet Airlines Inc. |
| LGA | MSP | Envoy Air |
| LGA | MSP | Delta Air Lines Inc. |
| JFK | MSP | Endeavor Air Inc. |
| LGA | MSP | Delta Air Lines Inc. |
| LGA | MSP | Delta Air Lines Inc. |
| LGA | MSP | Envoy Air |
| LGA | MSP | Delta Air Lines Inc. |
| LGA | MSP | Envoy Air |
In [5]:
SELECT *
FROM airlines NATURAL JOIN flights
LIMIT 10;
Out[5]:
| carrier | name | year | month | day | dep_time | sched_dep_time | dep_delay | arr_time | sched_arr_time | arr_delay | flight | tailnum | origin | dest | air_time | distance | hour | minute | time_hour |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 9E | Endeavor Air Inc. | 2013 | 1 | 1 | 810 | 810 | 0.0 | 1048 | 1037 | 11.0 | 3538 | N915XJ | JFK | MSP | 189.0 | 1029.0 | 8.0 | 10.0 | 1357045200.0 |
| 9E | Endeavor Air Inc. | 2013 | 1 | 1 | 1451 | 1500 | -9.0 | 1634 | 1636 | -2.0 | 4105 | N8444F | JFK | IAD | 57.0 | 228.0 | 15.0 | 0.0 | 1357070400.0 |
| 9E | Endeavor Air Inc. | 2013 | 1 | 1 | 1452 | 1455 | -3.0 | 1637 | 1639 | -2.0 | 3295 | N920XJ | JFK | BUF | 68.0 | 301.0 | 14.0 | 55.0 | 1357066800.0 |
| 9E | Endeavor Air Inc. | 2013 | 1 | 1 | 1454 | 1500 | -6.0 | 1635 | 1636 | -1.0 | 3843 | N8409N | JFK | SYR | 57.0 | 209.0 | 15.0 | 0.0 | 1357070400.0 |
| 9E | Endeavor Air Inc. | 2013 | 1 | 1 | 1507 | 1515 | -8.0 | 1651 | 1656 | -5.0 | 3792 | N8631E | JFK | ROC | 66.0 | 264.0 | 15.0 | 15.0 | 1357070400.0 |
| 9E | Endeavor Air Inc. | 2013 | 1 | 1 | 1530 | 1530 | 0.0 | 1650 | 1655 | -5.0 | 3369 | N913XJ | JFK | BWI | 40.0 | 184.0 | 15.0 | 30.0 | 1357070400.0 |
| 9E | Endeavor Air Inc. | 2013 | 1 | 1 | 1546 | 1540 | 6.0 | 1753 | 1748 | 5.0 | 3338 | N904XJ | JFK | ORD | 146.0 | 740.0 | 15.0 | 40.0 | 1357070400.0 |
| 9E | Endeavor Air Inc. | 2013 | 1 | 1 | 1550 | 1550 | 0.0 | 1844 | 1831 | 13.0 | 3372 | N934XJ | JFK | IND | 139.0 | 665.0 | 15.0 | 50.0 | 1357070400.0 |
| 9E | Endeavor Air Inc. | 2013 | 1 | 1 | 1552 | 1600 | -8.0 | 1749 | 1757 | -8.0 | 3459 | N910XJ | JFK | BNA | 150.0 | 765.0 | 16.0 | 0.0 | 1357074000.0 |
| 9E | Endeavor Air Inc. | 2013 | 1 | 1 | 1554 | 1600 | -6.0 | 1701 | 1734 | -33.0 | 3331 | N931XJ | JFK | BOS | 41.0 | 187.0 | 16.0 | 0.0 | 1357074000.0 |
- SELECT * FROM airlines; ✅ 16 rows
- SELECT * FROM weather; ✅ 26115 rows
- SELECT * FROM airlines NATURAL JOIN weather; ✅ 417840 ( = 26115 x 16)
Confusion Matrix¶
Joins¶
In [18]:
SELECT airports.*, flights.*
FROM airports
RIGHT OUTER JOIN flights ON airports.faa = flights.origin
LIMIT 10;
Out[18]:
| faa | name | lat | lon | alt | tz | dst | tzone | year | month | day | dep_time | sched_dep_time | dep_delay | arr_time | sched_arr_time | arr_delay | carrier | flight | tailnum | origin | dest | air_time | distance | hour | minute | time_hour |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| EWR | Newark Liberty Intl | 40.6925 | -74.168667 | 18 | -5.0 | A | America/New_York | 2013 | 1 | 1 | 517 | 515 | 2.0 | 830 | 819 | 11.0 | UA | 1545 | N14228 | EWR | IAH | 227.0 | 1400.0 | 5.0 | 15.0 | 1357034400.0 |
| EWR | Newark Liberty Intl | 40.6925 | -74.168667 | 18 | -5.0 | A | America/New_York | 2013 | 1 | 1 | 554 | 558 | -4.0 | 740 | 728 | 12.0 | UA | 1696 | N39463 | EWR | ORD | 150.0 | 719.0 | 5.0 | 58.0 | 1357034400.0 |
| EWR | Newark Liberty Intl | 40.6925 | -74.168667 | 18 | -5.0 | A | America/New_York | 2013 | 1 | 1 | 555 | 600 | -5.0 | 913 | 854 | 19.0 | B6 | 507 | N516JB | EWR | FLL | 158.0 | 1065.0 | 6.0 | 0.0 | 1357038000.0 |
| EWR | Newark Liberty Intl | 40.6925 | -74.168667 | 18 | -5.0 | A | America/New_York | 2013 | 1 | 1 | 558 | 600 | -2.0 | 923 | 937 | -14.0 | UA | 1124 | N53441 | EWR | SFO | 361.0 | 2565.0 | 6.0 | 0.0 | 1357038000.0 |
| EWR | Newark Liberty Intl | 40.6925 | -74.168667 | 18 | -5.0 | A | America/New_York | 2013 | 1 | 1 | 559 | 600 | -1.0 | 854 | 902 | -8.0 | UA | 1187 | N76515 | EWR | LAS | 337.0 | 2227.0 | 6.0 | 0.0 | 1357038000.0 |
| EWR | Newark Liberty Intl | 40.6925 | -74.168667 | 18 | -5.0 | A | America/New_York | 2013 | 1 | 1 | 601 | 600 | 1.0 | 844 | 850 | -6.0 | B6 | 343 | N644JB | EWR | PBI | 147.0 | 1023.0 | 6.0 | 0.0 | 1357038000.0 |
| EWR | Newark Liberty Intl | 40.6925 | -74.168667 | 18 | -5.0 | A | America/New_York | 2013 | 1 | 1 | 606 | 610 | -4.0 | 858 | 910 | -12.0 | AA | 1895 | N633AA | EWR | MIA | 152.0 | 1085.0 | 6.0 | 10.0 | 1357038000.0 |
| EWR | Newark Liberty Intl | 40.6925 | -74.168667 | 18 | -5.0 | A | America/New_York | 2013 | 1 | 1 | 607 | 607 | 0.0 | 858 | 915 | -17.0 | UA | 1077 | N53442 | EWR | MIA | 157.0 | 1085.0 | 6.0 | 7.0 | 1357038000.0 |
| EWR | Newark Liberty Intl | 40.6925 | -74.168667 | 18 | -5.0 | A | America/New_York | 2013 | 1 | 1 | 608 | 600 | 8.0 | 807 | 735 | 32.0 | MQ | 3768 | N9EAMQ | EWR | ORD | 139.0 | 719.0 | 6.0 | 0.0 | 1357038000.0 |
| EWR | Newark Liberty Intl | 40.6925 | -74.168667 | 18 | -5.0 | A | America/New_York | 2013 | 1 | 1 | 615 | 615 | 0.0 | 833 | 842 | -9.0 | DL | 575 | N326NB | EWR | ATL | 120.0 | 746.0 | 6.0 | 15.0 | 1357038000.0 |