Markdown CheatSheet¶

Shown and practiced separately

Markdown Cheatsheet

Load the database¶

In [1]:
%LOAD nycflights13.sqlite

Select all¶

SELECt * from FLIGHTS

In [6]:
SELECT * 
FROM flights
LIMIT 10;
Out[6]:
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
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
2013 1 1 533 529 4.0 850 830 20.0 UA 1714 N24211 LGA IAH 227.0 1416.0 5.0 29.0 1357034400.0
2013 1 1 542 540 2.0 923 850 33.0 AA 1141 N619AA JFK MIA 160.0 1089.0 5.0 40.0 1357034400.0
2013 1 1 544 545 -1.0 1004 1022 -18.0 B6 725 N804JB JFK BQN 183.0 1576.0 5.0 45.0 1357034400.0
2013 1 1 554 600 -6.0 812 837 -25.0 DL 461 N668DN LGA ATL 116.0 762.0 6.0 0.0 1357038000.0
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
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
2013 1 1 557 600 -3.0 709 723 -14.0 EV 5708 N829AS LGA IAD 53.0 229.0 6.0 0.0 1357038000.0
2013 1 1 557 600 -3.0 838 846 -8.0 B6 79 N593JB JFK MCO 140.0 944.0 6.0 0.0 1357038000.0
2013 1 1 558 600 -2.0 753 745 8.0 AA 301 N3ALAA LGA ORD 138.0 733.0 6.0 0.0 1357038000.0

Limited select¶

In [22]:
SELECT origin, dest, flight
FROM flights
LIMIT 10;
Out[22]:
origin dest flight
EWR IAH 1545
LGA IAH 1714
JFK MIA 1141
JFK BQN 725
LGA ATL 461
EWR ORD 1696
EWR FLL 507
LGA IAD 5708
JFK MCO 79
LGA ORD 301
In [5]:
SELECT dep_time, sched_dep_time, sched_arr_time, arr_time
FROM flights
LIMIT 10;
Out[5]:
dep_time sched_dep_time sched_arr_time arr_time
517 515 819 830
533 529 830 850
542 540 850 923
544 545 1022 1004
554 600 837 812
554 558 728 740
555 600 854 913
557 600 723 709
557 600 846 838
558 600 745 753

Limited select with rename¶

Please select date (required format), origin, dest, flight

Required format for date: year--month--day

Example format for date : 2024--03--11

In [6]:
SELECT year || '--' || month || '--' || day as date,
origin, dest, carrier, flight
FROM flights
LIMIT 10;
Out[6]:
date origin dest carrier flight
2013--1--1 EWR IAH UA 1545
2013--1--1 LGA IAH UA 1714
2013--1--1 JFK MIA AA 1141
2013--1--1 JFK BQN B6 725
2013--1--1 LGA ATL DL 461
2013--1--1 EWR ORD UA 1696
2013--1--1 EWR FLL B6 507
2013--1--1 LGA IAD EV 5708
2013--1--1 JFK MCO B6 79
2013--1--1 LGA ORD AA 301

Conditional select¶

Show all flight details from John F. Kennedy Airport

In [9]:
SELECT * 
from flights
WHERE origin='JFK'
LIMIT 10;
Out[9]:
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
2013 1 1 542 540 2.0 923 850 33.0 AA 1141 N619AA JFK MIA 160.0 1089.0 5.0 40.0 1357034400.0
2013 1 1 544 545 -1.0 1004 1022 -18.0 B6 725 N804JB JFK BQN 183.0 1576.0 5.0 45.0 1357034400.0
2013 1 1 557 600 -3.0 838 846 -8.0 B6 79 N593JB JFK MCO 140.0 944.0 6.0 0.0 1357038000.0
2013 1 1 558 600 -2.0 849 851 -2.0 B6 49 N793JB JFK PBI 149.0 1028.0 6.0 0.0 1357038000.0
2013 1 1 558 600 -2.0 853 856 -3.0 B6 71 N657JB JFK TPA 158.0 1005.0 6.0 0.0 1357038000.0
2013 1 1 558 600 -2.0 924 917 7.0 UA 194 N29129 JFK LAX 345.0 2475.0 6.0 0.0 1357038000.0
2013 1 1 559 559 0.0 702 706 -4.0 B6 1806 N708JB JFK BOS 44.0 187.0 5.0 59.0 1357034400.0
2013 1 1 606 610 -4.0 837 845 -8.0 DL 1743 N3739P JFK ATL 128.0 760.0 6.0 10.0 1357038000.0
2013 1 1 611 600 11.0 945 931 14.0 UA 303 N532UA JFK SFO 366.0 2586.0 6.0 0.0 1357038000.0
2013 1 1 613 610 3.0 925 921 4.0 B6 135 N635JB JFK RSW 175.0 1074.0 6.0 10.0 1357038000.0

Show all tail numbers from John F. Kennedy Airport

In [10]:
SELECT tailnum
from flights
WHERE origin='JFK'
LIMIT 10;
Out[10]:
tailnum
N619AA
N804JB
N593JB
N793JB
N657JB
N29129
N708JB
N3739P
N532UA
N635JB

Question (Class Activity)¶

Find all arrival delays which are from John F. Kennedy Airport to Mississippi Airport.

In [7]:
SELECT arr_delay
FROM flights
WHERE origin = "JFK" AND dest="MSP"
LIMIT 10;
Out[7]:
arr_delay
11.0
39.0
-19.0
15.0
0.0
-26.0
-10.0
0.0
11.0
-2.0

Select all Miami destinated flights including date (in the following format), origin, dest, carrier and flight

  • Required format for date: !year--month--day!

  • Example format for date : !2024--03--11!

In [8]:
SELECT '!' || year || '--' || month || '--' || day || '!' AS date, 
origin, dest, carrier, flight
FROM flights
WHERE dest="MIA"
LIMIT 10;
Out[8]:
date origin dest carrier flight
!2013--1--1! JFK MIA AA 1141
!2013--1--1! EWR MIA AA 1895
!2013--1--1! EWR MIA UA 1077
!2013--1--1! LGA MIA AA 1837
!2013--1--1! LGA MIA DL 2003
!2013--1--1! LGA MIA AA 2279
!2013--1--1! LGA MIA AA 2267
!2013--1--1! JFK MIA DL 1843
!2013--1--1! JFK MIA AA 443
!2013--1--1! LGA MIA DL 2143

Conditional Select with limits¶

Same request as above with only first three lines

In [15]:
SELECT '!' || year || '--' || month || '--' || day || '!' as date,
origin, dest, carrier, flight
FROM flights
WHERE dest="MIA"
LIMIT 3;
Out[15]:
date origin dest carrier flight
!2013--1--1! JFK MIA AA 1141
!2013--1--1! EWR MIA AA 1895
!2013--1--1! EWR MIA UA 1077

Ordered Select¶

Descending direction

In [9]:
SELECT year || '-' || month || '-' || day as date,
origin, carrier, flight
FROM flights
ORDER BY year desc, month desc, day desc
LIMIT 10;
Out[9]:
date origin carrier flight
2013-12-31 JFK UA 443
2013-12-31 EWR UA 219
2013-12-31 LGA MQ 3301
2013-12-31 JFK US 1831
2013-12-31 EWR UA 1729
2013-12-31 EWR UA 1506
2013-12-31 EWR UA 1493
2013-12-31 EWR UA 1483
2013-12-31 EWR UA 1482
2013-12-31 EWR UA 1455

Ordered Select¶

Ascending direction

In [10]:
SELECT year || '--' || month || '--' || day AS date, 
origin, dest, carrier, flight
FROM flights
WHERE dest="MIA"
ORDER BY year ASC, month ASC, day ASC
LIMIT 10;
Out[10]:
date origin dest carrier flight
2013--1--1 JFK MIA AA 1141
2013--1--1 EWR MIA AA 1895
2013--1--1 EWR MIA UA 1077
2013--1--1 LGA MIA AA 1837
2013--1--1 LGA MIA DL 2003
2013--1--1 LGA MIA AA 2279
2013--1--1 LGA MIA AA 2267
2013--1--1 JFK MIA DL 1843
2013--1--1 JFK MIA AA 443
2013--1--1 LGA MIA DL 2143

Ordered Select with Condition Same as previous example, only filter origin is from Kennedy Airport

In [11]:
SELECT year || '-' || month || '-' || day as date,
origin, carrier, flight
FROM flights
WHERE origin = 'JFK'
ORDER BY year desc, month desc, day desc
LIMIT 10;
Out[11]:
date origin carrier flight
2013-12-31 JFK UA 443
2013-12-31 JFK US 1831
2013-12-31 JFK B6 161
2013-12-31 JFK B6 745
2013-12-31 JFK B6 1503
2013-12-31 JFK B6 486
2013-12-31 JFK B6 2002
2013-12-31 JFK B6 718
2013-12-31 JFK B6 1816
2013-12-31 JFK B6 234

Why does not work ?¶

In [12]:
SELECT year || '-' || month || '-' || day as date,
origin, carrier, flight
FROM flights
WHERE origin = 'JFK'
ORDER BY year desc
LIMIT 10;
Out[12]:
date origin carrier flight
2013-12-31 JFK UA 443
2013-12-31 JFK US 1831
2013-12-31 JFK B6 161
2013-12-31 JFK B6 745
2013-12-31 JFK B6 1503
2013-12-31 JFK B6 486
2013-12-31 JFK B6 2002
2013-12-31 JFK B6 718
2013-12-31 JFK B6 1816
2013-12-31 JFK B6 234

Quiz 1¶

ch3 - 3.21 - Self Join¶

Question StackOverflow

person supervisor
Bob Alice
Mary Susan
Alice David
David Mary

Relation emp_super

  • Find the supervisor of “Bob”
    • SELECT supervisor FROM emp_super WHERE person="Bob";
  • Find the supervisor of the supervisor of “Bob”
    • SELECT supervisor FROM emp_super WHERE person in ( SELECT supervisor from empsuper WHERE person="Bob");
  • Can you find ALL the supervisors (direct and indirect) of “Bob”?
    • SELECT distinct s.super_name, t.super_name FROM emp_super s * emp_super t WHERE s.super_name = t.person_name;
In [ ]:

Quiz 2¶

Filter all carriers if their arrival time longer than 800 and there is no departure delay !¶

  • carrier
  • arr_time
  • dep_delay

Morning class - Till here - 11/03/2024

Aggregate Select¶

Order the total number of flights for each carrier

In [14]:
SELECT carrier, count(*) as numOfFlights 
FROM flights
GROUP BY carrier
ORDER BY numOfFlights DESC
LIMIT 10;
Out[14]:
carrier numOfFlights
UA 58665
B6 54635
EV 54173
DL 48110
AA 32729
MQ 26397
US 20536
9E 18460
WN 12275
VX 5162

Order it by timezones for carriers over flights

In [15]:
SELECT tzone, count(*) AS numOfTimeZone
FROM airports 
GROUP BY tzone
ORDER BY tzone ASC
LIMIT 10;
Out[15]:
tzone numOfTimeZone
America/Anchorage 239
America/Chicago 342
America/Denver 119
America/Los_Angeles 176
America/New_York 519
America/Phoenix 38
America/Vancouver 2
Asia/Chongqing 2
Pacific/Honolulu 18
\N 3

Order it by the number of time zones for carriers over flights

In [16]:
SELECT tzone, count(*) AS numOfTimeZone
FROM airports 
GROUP BY tzone
ORDER BY numOfTimeZone DESC;
Out[16]:
tzone numOfTimeZone
America/New_York 519
America/Chicago 342
America/Anchorage 239
America/Los_Angeles 176
America/Denver 119
America/Phoenix 38
Pacific/Honolulu 18
\N 3
Asia/Chongqing 2
America/Vancouver 2

Natural Join¶

  • combine these tables based on the common column carrier, which exists in both tables.
  • automatically join the flights and airlines tables based on the common column carrier. It retrieves all columns from both tables for rows where the carrier values match.
  • A natural join automatically matches columns with the same name in both tables. In this case, it matches carrier from both tables. However, it's essential to review the structure of both tables before using a natural join to ensure that the columns being matched make sense and produce the desired results.
In [ ]:
SELECT *
FROM flights
NATURAL JOIN airlines
LIMIT 20;

LEFT OUTER JOIN¶

  • A left outer join to retrieve all flights along with the corresponding plane manufacturer information, if available.
  • This query performs a left outer join on the flights and planes tables based on the common column tailnum. It retrieves all columns from the flights table and the manufacturer column from the planes table. If there's a match between the tailnum columns, it includes the manufacturer information; otherwise, it includes NULL values for the manufacturer.
  • A left outer join retrieves all rows from the left table (flights in this case) and the matching rows from the right table (planes), if any. It's useful for situations where you want to retrieve all records from one table and matching records from another table, with missing matches represented as NULL values.
In [12]:
SELECT planes.manufacturer, flights.*
FROM flights
LEFT OUTER JOIN planes ON flights.tailnum = planes.tailnum
LIMIT 10;
Out[12]:
manufacturer 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
BOEING 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
BOEING 2013 1 1 533 529 4.0 850 830 20.0 UA 1714 N24211 LGA IAH 227.0 1416.0 5.0 29.0 1357034400.0
BOEING 2013 1 1 542 540 2.0 923 850 33.0 AA 1141 N619AA JFK MIA 160.0 1089.0 5.0 40.0 1357034400.0
AIRBUS 2013 1 1 544 545 -1.0 1004 1022 -18.0 B6 725 N804JB JFK BQN 183.0 1576.0 5.0 45.0 1357034400.0
BOEING 2013 1 1 554 600 -6.0 812 837 -25.0 DL 461 N668DN LGA ATL 116.0 762.0 6.0 0.0 1357038000.0
BOEING 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
AIRBUS INDUSTRIE 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
CANADAIR 2013 1 1 557 600 -3.0 709 723 -14.0 EV 5708 N829AS LGA IAD 53.0 229.0 6.0 0.0 1357038000.0
AIRBUS 2013 1 1 557 600 -3.0 838 846 -8.0 B6 79 N593JB JFK MCO 140.0 944.0 6.0 0.0 1357038000.0
2013 1 1 558 600 -2.0 753 745 8.0 AA 301 N3ALAA LGA ORD 138.0 733.0 6.0 0.0 1357038000.0
In [2]:
SELECT planes.manufacturer, flights.tailnum, planes.tailnum
FROM flights
LEFT OUTER JOIN planes ON flights.tailnum = planes.tailnum
LIMIT 50;
Out[2]:
manufacturer tailnum tailnum

RIGHT OUTER JOIN¶

  • A right outer join to retrieve all airports along with the corresponding flights departing from each airport, if available
  • This query performs a right outer join on the airports and flights tables based on the common column faa in airports and origin in flights. It retrieves all columns from the airports table and the flights table. If there's a match between the faa columns in airports and origin columns in flights, it includes the corresponding flight information; otherwise, it includes NULL values for flight-related columns.
  • A right outer join retrieves all rows from the right table (flights in this case) and the matching rows from the left table (airports), if any. It's useful for situations where you want to retrieve all records from one table and matching records from another table, with missing matches represented as NULL values.
In [4]:
SELECT airports.*, flights.*
FROM airports
RIGHT OUTER JOIN flights ON airports.faa = flights.origin
LIMIT 10
Out[4]:
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
In [5]:
SELECT airports.*, flights.*
FROM flights
LEFT OUTER JOIN airports ON airports.faa = flights.origin
LIMIT 10
Out[5]:
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
LGA La Guardia 40.777245 -73.872608 22 -5.0 A America/New_York 2013 1 1 533 529 4.0 850 830 20.0 UA 1714 N24211 LGA IAH 227.0 1416.0 5.0 29.0 1357034400.0
JFK John F Kennedy Intl 40.639751 -73.778925 13 -5.0 A America/New_York 2013 1 1 542 540 2.0 923 850 33.0 AA 1141 N619AA JFK MIA 160.0 1089.0 5.0 40.0 1357034400.0
JFK John F Kennedy Intl 40.639751 -73.778925 13 -5.0 A America/New_York 2013 1 1 544 545 -1.0 1004 1022 -18.0 B6 725 N804JB JFK BQN 183.0 1576.0 5.0 45.0 1357034400.0
LGA La Guardia 40.777245 -73.872608 22 -5.0 A America/New_York 2013 1 1 554 600 -6.0 812 837 -25.0 DL 461 N668DN LGA ATL 116.0 762.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 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
LGA La Guardia 40.777245 -73.872608 22 -5.0 A America/New_York 2013 1 1 557 600 -3.0 709 723 -14.0 EV 5708 N829AS LGA IAD 53.0 229.0 6.0 0.0 1357038000.0
JFK John F Kennedy Intl 40.639751 -73.778925 13 -5.0 A America/New_York 2013 1 1 557 600 -3.0 838 846 -8.0 B6 79 N593JB JFK MCO 140.0 944.0 6.0 0.0 1357038000.0
LGA La Guardia 40.777245 -73.872608 22 -5.0 A America/New_York 2013 1 1 558 600 -2.0 753 745 8.0 AA 301 N3ALAA LGA ORD 138.0 733.0 6.0 0.0 1357038000.0

FULL OUTER JOIN¶

  • A full outer join to combine these tables based on the common column date, which exists in both tables.
  • This query performs a full outer join on the flights and weather tables based on the common column date. It retrieves all columns from both tables. If there's a match between the date values in both tables, it includes the corresponding flight and weather information; otherwise, it includes NULL values for the missing side.
  • A full outer join retrieves all rows from both tables, matching rows where available and including NULL values for unmatched rows on either side. It's useful for situations where you want to retrieve all records from both tables, combining them into a single result set.
In [4]:
SELECT *
FROM flights
FULL OUTER JOIN weather USING (time_hour)
LIMIT 20;
Out[4]:
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 origin year month day hour temp dewp humid wind_dir wind_speed wind_gust precip pressure visib
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 2013.0 1.0 1 5 39.02 28.04 64.43 260.0 12.65858 0.0 1011.9 10.0
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 JFK 2013.0 1.0 1 5 39.02 26.96 61.63 260.0 14.96014 0.0 1012.1 10.0
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 LGA 2013.0 1.0 1 5 39.92 24.98 54.81 250.0 14.96014 21.86482 0.0 1011.4 10.0
2013 1 1 533 529 4.0 850 830 20.0 UA 1714 N24211 LGA IAH 227.0 1416.0 5.0 29.0 1357034400.0 EWR 2013.0 1.0 1 5 39.02 28.04 64.43 260.0 12.65858 0.0 1011.9 10.0
2013 1 1 533 529 4.0 850 830 20.0 UA 1714 N24211 LGA IAH 227.0 1416.0 5.0 29.0 1357034400.0 JFK 2013.0 1.0 1 5 39.02 26.96 61.63 260.0 14.96014 0.0 1012.1 10.0
2013 1 1 533 529 4.0 850 830 20.0 UA 1714 N24211 LGA IAH 227.0 1416.0 5.0 29.0 1357034400.0 LGA 2013.0 1.0 1 5 39.92 24.98 54.81 250.0 14.96014 21.86482 0.0 1011.4 10.0
2013 1 1 542 540 2.0 923 850 33.0 AA 1141 N619AA JFK MIA 160.0 1089.0 5.0 40.0 1357034400.0 EWR 2013.0 1.0 1 5 39.02 28.04 64.43 260.0 12.65858 0.0 1011.9 10.0
2013 1 1 542 540 2.0 923 850 33.0 AA 1141 N619AA JFK MIA 160.0 1089.0 5.0 40.0 1357034400.0 JFK 2013.0 1.0 1 5 39.02 26.96 61.63 260.0 14.96014 0.0 1012.1 10.0
2013 1 1 542 540 2.0 923 850 33.0 AA 1141 N619AA JFK MIA 160.0 1089.0 5.0 40.0 1357034400.0 LGA 2013.0 1.0 1 5 39.92 24.98 54.81 250.0 14.96014 21.86482 0.0 1011.4 10.0
2013 1 1 544 545 -1.0 1004 1022 -18.0 B6 725 N804JB JFK BQN 183.0 1576.0 5.0 45.0 1357034400.0 EWR 2013.0 1.0 1 5 39.02 28.04 64.43 260.0 12.65858 0.0 1011.9 10.0

Asagidaki ornek secim: Daha mantikli (daha az ornegin dondugu) senaryoyou canlandir ki program query islemede donmasin!

❌ Database error - RIGHT and FULL OUTER JOINs are not currently supported

In [8]:
SELECT *
FROM flights
LEFT OUTER JOIN weather on flights.time_hour = weather.time_hour
union
SELECT *
FROM weather
LEFT OUTER JOIN flights on flights.time_hour = weather.time_hour
LIMIT 10;
Out[8]:
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 origin year month day hour temp dewp humid wind_dir wind_speed wind_gust precip pressure visib time_hour
2013 1 1 600 901 B6 125 N618JB JFK FLL 1069.0 6.0 0.0 1357038000.0 EWR 2013.0 1.0 1 6 37.94 28.04 67.21 240.0 11.5078 0.0 1012.4 10.0 1357038000.0
2013 1 1 600 901 B6 125 N618JB JFK FLL 1069.0 6.0 0.0 1357038000.0 JFK 2013.0 1.0 1 6 37.94 26.96 64.29 260.0 13.80936 0.0 1012.6 10.0 1357038000.0
2013 1 1 600 901 B6 125 N618JB JFK FLL 1069.0 6.0 0.0 1357038000.0 LGA 2013.0 1.0 1 6 39.92 24.98 54.81 260.0 16.11092 23.0156 0.0 1011.7 10.0 1357038000.0
2013 1 1 1500 1825 AA 1925 N3EVAA LGA MIA 1096.0 15.0 0.0 1357070400.0 EWR 2013.0 1.0 1 15 37.94 24.08 57.04 290.0 9.20624 0.0 1011.9 10.0 1357070400.0
2013 1 1 1500 1825 AA 1925 N3EVAA LGA MIA 1096.0 15.0 0.0 1357070400.0 JFK 2013.0 1.0 1 15 39.02 23.0 52.26 290.0 12.65858 0.0 1011.7 10.0 1357070400.0
2013 1 1 1500 1825 AA 1925 N3EVAA LGA MIA 1096.0 15.0 0.0 1357070400.0 LGA 2013.0 1.0 1 15 39.02 21.92 49.93 310.0 12.65858 21.86482 0.0 1011.4 10.0 1357070400.0
2013 1 1 1630 1815 EV 4308 N18120 EWR RDU 416.0 16.0 30.0 1357074000.0 EWR 2013.0 1.0 1 16 37.04 19.94 49.62 300.0 13.80936 20.71404 0.0 1012.1 10.0 1357074000.0
2013 1 1 1630 1815 EV 4308 N18120 EWR RDU 416.0 16.0 30.0 1357074000.0 JFK 2013.0 1.0 1 16 37.94 17.96 44.0 320.0 17.2617 24.16638 0.0 1012.1 10.0 1357074000.0
2013 1 1 1630 1815 EV 4308 N18120 EWR RDU 416.0 16.0 30.0 1357074000.0 LGA 2013.0 1.0 1 16 37.94 21.02 50.14 330.0 11.5078 0.0 1011.7 10.0 1357074000.0
2013 1 1 1935 2240 AA 791 N3EHAA LGA DFW 1389.0 19.0 35.0 1357084800.0 EWR 2013.0 1.0 1 19 33.08 12.92 42.84 320.0 10.35702 0.0 1014.4 10.0 1357084800.0