Intro to
Databases

Day 24

Prof Amanda Luby

Carleton College
Stat 220 - Winter 2026

Warm Up: on your own

Using nycflights23::flights, answer the following using {dplyr} code

How many flights flew to MSP from JFK in 2023?
What was the average flight delay for MSP flights in Sept 2023?
What percentage of MSP flights in 2023 were delayed by 15 minutes or more?

03:00

`nycflights23::flights` is a subset of all flights data

nycflights23::flights %>%
  select(year) %>%
  distinct()

# A tibble: 1 × 1
   year
  <int>
1  2023

nycflights23::flights %>% 
  summarize(n = n())

# A tibble: 1 × 1
       n
   <int>
1 435352

nycflights23::flights %>%
  select(origin) %>%
  distinct()

# A tibble: 3 × 1
  origin
  <chr> 
1 EWR   
2 JFK   
3 LGA

We can also connect to a database containing more flights data:

library(mdsr)
library(DBI)
db <- dbConnect_scidb("airlines")

flights <- tbl(db, "flights")
carriers <- tbl(db, "carriers")
airports <- tbl(db, "airports")

class(flights)

[1] "tbl_MariaDBConnection" "tbl_dbi"               "tbl_sql"              
[4] "tbl_lazy"              "tbl"

We can access information about `flights` with {dplyr} commands:

flights %>%
  select(year) %>%
  distinct()

# Source:   SQL [?? x 1]
# Database: mysql  [mdsr_public@mdsr.crcbo51tmesf.us-east-2.rds.amazonaws.com:3306/airlines]
   year
  <int>
1  2013
2  2014
3  2015

flights %>% summarize(n = n())

# Source:   SQL [?? x 1]
# Database: mysql  [mdsr_public@mdsr.crcbo51tmesf.us-east-2.rds.amazonaws.com:3306/airlines]
         n
   <int64>
1 18008372

We can access information about `flights` with {dplyr} commands:

flights %>%
  select(origin) %>%
  distinct()

# Source:   SQL [?? x 1]
# Database: mysql  [mdsr_public@mdsr.crcbo51tmesf.us-east-2.rds.amazonaws.com:3306/airlines]
   origin
   <chr> 
 1 ABE   
 2 ABI   
 3 ABQ   
 4 ABR   
 5 ABY   
 6 ACK   
 7 ACT   
 8 ACV   
 9 ACY   
10 ADK   
# ℹ more rows
# ℹ Use `print(n = ...)` to see more rows

What is happening?

If we want to load results into R’s memory, use `collect()`

origins <- flights %>%
  select(origin) %>%
  distinct()

class(origins)

[1] "tbl_MariaDBConnection" "tbl_dbi"               "tbl_sql"              
[4] "tbl_lazy"              "tbl"

origins_tbl <- collect(origins)
class(origins_tbl)

[1] "tbl_df"     "tbl"        "data.frame"

Under the hood:

flights %>%
  select(origin) %>%
  distinct()

is generating the following SQL code:

<SQL>
SELECT DISTINCT `origin`
FROM `flights`

SQL

SQL stands for Structured Query Language and is a language for database management systems developed in the 1970’s

Up to this point, we’ve been working with small data
- Can be saved on our personal computers
- Can be loaded directly into R’s memory
Moving into medium data:
- Can be saved on our personal computers
- Can’t be loaded or worked with in R’s memory

SQL

There are many “dialects” of SQL, but they’re all very similar

Oracle/MySQL
Microsoft SQL Server
SQLite
MariaDB is a community version of MySQL

The “dialect” we’ll use is MySQL/MariaDB

SQL

Point of the next few days is to show you the basics and build a little bit of familiarity with the commands
The good news: “thinking” in SQL is similar to “thinking” in {dplyr} (even though the syntax is different)

SQL in R

To run SQL in R, use

dbGetQuery(db, "<INSERT SQL QUERY HERE>")

dbGetQuery(db, 
"SELECT DISTINCT `origin`
FROM `flights`")

    origin
1      ABE
2      ABI
3      ABQ
4      ABR
5      ABY
6      ACK
7      ACT
8      ACV
9      ACY
10     ADK
11     ADQ
12     AEX
13     AGS
14     AKN
15     ALB
16     ALO
17     AMA
18     ANC
19     APN
20     ART
21     ASE
22     ATL
23     ATW
24     AUS
25     AVL
26     AVP
27     AZA
28     AZO
29     BDL
30     BET
31     BFL
32     BGM
33     BGR
34     BHM
35     BIL
36     BIS
37     BJI
38     BKG
39     BLI
40     BMI
41     BNA
42     BOI
43     BOS
44     BPT
45     BQK
46     BQN
47     BRD
48     BRO
49     BRW
50     BTM
51     BTR
52     BTV
53     BUF
54     BUR
55     BWI
56     BZN
57     CAE
58     CAK
59     CDC
60     CDV
61     CEC
62     CHA
63     CHO
64     CHS
65     CIC
66     CID
67     CIU
68     CLD
69     CLE
70     CLL
71     CLT
72     CMH
73     CMI
74     CMX
75     CNY
76     COD
77     COS
78     COU
79     CPR
80     CRP
81     CRW
82     CSG
83     CVG
84     CWA
85     CYS
86     DAB
87     DAL
88     DAY
89     DBQ
90     DCA
91     DEN
92     DFW
93     DHN
94     DIK
95     DLG
96     DLH
97     DRO
98     DRT
99     DSM
100    DTW
101    DVL
102    EAU
103    ECP
104    EGE
105    EKO
106    ELM
107    ELP
108    ERI
109    ESC
110    EUG
111    EVV
112    EWN
113    EWR
114    EYW
115    FAI
116    FAR
117    FAT
118    FAY
119    FCA
120    FLG
121    FLL
122    FNT
123    FOE
124    FSD
125    FSM
126    FWA
127    GCC
128    GCK
129    GEG
130    GFK
131    GGG
132    GJT
133    GNV
134    GPT
135    GRB
136    GRI
137    GRK
138    GRR
139    GSO
140    GSP
141    GST
142    GTF
143    GTR
144    GUC
145    GUM
146    HDN
147    HIB
148    HLN
149    HNL
150    HOB
151    HOU
152    HPN
153    HRL
154    HSV
155    HYA
156    HYS
157    IAD
158    IAG
159    IAH
160    ICT
161    IDA
162    ILG
163    ILM
164    IMT
165    IND
166    INL
167    IPL
168    ISN
169    ISP
170    ITH
171    ITO
172    IYK
173    JAC
174    JAN
175    JAX
176    JFK
177    JLN
178    JMS
179    JNU
180    KOA
181    KTN
182    LAN
183    LAR
184    LAS
185    LAW
186    LAX
187    LBB
188    LBE
189    LCH
190    LEX
191    LFT
192    LGA
193    LGB
194    LIH
195    LIT
196    LMT
197    LNK
198    LRD
199    LSE
200    LWS
201    MAF
202    MBS
203    MCI
204    MCN
205    MCO
206    MDT
207    MDW
208    MEI
209    MEM
210    MFE
211    MFR
212    MGM
213    MHK
214    MHT
215    MIA
216    MKE
217    MKG
218    MLB
219    MLI
220    MLU
221    MMH
222    MOB
223    MOD
224    MOT
225    MQT
226    MRY
227    MSN
228    MSO
229    MSP
230    MSY
231    MTJ
232    MVY
233    MYR
234    OAJ
235    OAK
236    OGG
237    OKC
238    OMA
239    OME
240    ONT
241    ORD
242    ORF
243    ORH
244    OTH
245    OTZ
246    PAH
247    PBG
248    PBI
249    PDX
250    PHF
251    PHL
252    PHX
253    PIA
254    PIB
255    PIH
256    PIT
257    PLN
258    PNS
259    PPG
260    PSC
261    PSE
262    PSG
263    PSP
264    PUB
265    PVD
266    PWM
267    RAP
268    RDD
269    RDM
270    RDU
271    RFD
272    RHI
273    RIC
274    RKS
275    RNO
276    ROA
277    ROC
278    ROW
279    RST
280    RSW
281    SAF
282    SAN
283    SAT
284    SAV
285    SBA
286    SBN
287    SBP
288    SCC
289    SCE
290    SDF
291    SEA
292    SFO
293    SGF
294    SGU
295    SHD
296    SHV
297    SIT
298    SJC
299    SJT
300    SJU
301    SLC
302    SMF
303    SMX
304    SNA
305    SPI
306    SPN
307    SPS
308    SRQ
309    STC
310    STL
311    STT
312    STX
313    SUN
314    SUX
315    SWF
316    SYR
317    TLH
318    TOL
319    TPA
320    TRI
321    TTN
322    TUL
323    TUS
324    TVC
325    TWF
326    TXK
327    TYR
328    TYS
329    UST
330    VEL
331    VLD
332    VPS
333    WRG
334    WYS
335    XNA
336    YAK
337    YUM

A quick overview of SQL commands

`SELECT` and `FROM`

are required for every query. The simplest query we can write is:

SELECT * FROM flights;

which means “select everything from the flights dataset”.

DO NOT EXECUTE THIS QUERY! This will cause all 169 million records to be dumped! This will not only crash your machine, but also tie up the server for everyone else.

A safe version is:

SELECT * FROM flights LIMIT 0,10;

`LIMIT`

is similar to head() or slice()

dbGetQuery(db, 
"SELECT DISTINCT `origin`
FROM `flights`
LIMIT 0,5")

  origin
1    ABE
2    ABI
3    ABQ
4    ABR
5    ABY

Create new variables with `SELECT` and `AS`

SELECT SUM(1) AS numFlights
FROM flights

  numFlights
1   18008372

Grouped operations with `GROUP BY`

SELECT 
  origin, SUM(1) AS numFlights,
  AVG(arr_delay) AS avg_arr_delay
FROM flights
GROUP BY origin
LIMIT 0, 6;

  origin numFlights avg_arr_delay
1    ABE       7251        5.5627
2    ABI       8116        6.3329
3    ABQ      74195        5.8051
4    ABR       2239        1.3198
5    ABY       3002        5.2652
6    ACK       1295        7.6131

Order output with `ORDER BY`

Similar to arrange() in {dplyr}

SELECT 
  origin, SUM(1) AS numFlights,
  AVG(arr_delay) AS avg_arr_delay
FROM flights
GROUP BY origin
ORDER BY avg_arr_delay DESC
LIMIT 0, 6;

  origin numFlights avg_arr_delay
1    PPG        334       33.6946
2    STC        550       22.8691
3    OTH       1262       20.5713
4    FOE        449       19.7127
5    CEC       2143       18.4951
6    ILG       1155       16.5394

Filter output with `WHERE`

SELECT 
  origin, SUM(1) AS numFlights,
  AVG(arr_delay) AS avg_arr_delay
FROM flights
WHERE dest = 'MSP'
  AND numFlights > 365 * 2
GROUP BY origin
ORDER BY avg_arr_delay DESC
LIMIT 0, 6;

  origin numFlights avg_arr_delay
1    ASE         43       28.0930
2    HRL        131       27.0687
3    ORF        483       17.8240
4    EGE         31       17.4516
5    TTN        290       17.1552
6    ORD      18382       12.7469

Filter output on multiple conditions

SELECT 
  origin, SUM(1) AS numFlights,
  AVG(arr_delay) AS avg_arr_delay
FROM flights
WHERE dest = 'MSP'
  AND numFlights > 365 * 2
GROUP BY origin
ORDER BY avg_arr_delay DESC
LIMIT 0, 6;

Error: Unknown column 'numFlights' in 'WHERE' [1054]

Doesn’t work, since numFlights is a column we created in SELECT

Filter output with `HAVING`

If we want to filter based on a column we created, put it in the HAVING clause instead

SELECT 
  origin, SUM(1) AS numFlights,
  AVG(arr_delay) AS avg_arr_delay
FROM flights
WHERE dest = 'MSP'
GROUP BY origin
HAVING numFlights > 365 * 2
ORDER BY avg_arr_delay DESC
LIMIT 0, 6;

  origin numFlights avg_arr_delay
1    ORD      18382       12.7469
2    MDW       9510        9.6110
3    DEN      17054        9.0643
4    CID       2106        8.2042
5    RIC       1009        7.8840
6    DFW       8404        7.2574

SQL commands must be written in a specific order:

SELECT
FROM
JOIN (next class)
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

Your turn

Try the exercises in the 25-databases activity. We’ll start Friday off by talking about them

Intro to Databases

Warm Up: on your own

nycflights23::flights is a subset of all flights data

We can also connect to a database containing more flights data:

We can access information about flights with {dplyr} commands:

We can access information about flights with {dplyr} commands:

What is happening?

What is happening?

What is happening?

If we want to load results into R’s memory, use collect()

Under the hood:

SQL

SQL

SQL

SQL in R

A quick overview of SQL commands

SELECT and FROM

LIMIT

Create new variables with SELECT and AS

Grouped operations with GROUP BY

Order output with ORDER BY

Filter output with WHERE

Filter output on multiple conditions

Filter output with HAVING

SQL commands must be written in a specific order:

Your turn

Intro to
Databases

`nycflights23::flights` is a subset of all flights data

We can access information about `flights` with {dplyr} commands:

We can access information about `flights` with {dplyr} commands:

If we want to load results into R’s memory, use `collect()`

`SELECT` and `FROM`

`LIMIT`

Create new variables with `SELECT` and `AS`

Grouped operations with `GROUP BY`

Order output with `ORDER BY`

Filter output with `WHERE`

Filter output with `HAVING`