Intro to SQL

Day 25

Prof Amanda Luby

Carleton College
Stat 220 - Winter 2026

Warm up: more time for shiny app??

Warm Up: on your own

Load flights23 <- nycflights23::flights and write {dplyr} code to do the following

1. Compute the number of flights in each year in the dataset
2. Compute the average flight delay by carrier for flights to Minneapolis

03:00

Warm Up: in groups of ~3

????

04:00

If you run the following code in R….

year_summary <- nycflights23::flights %>%
  group_by(year) %>%
  summarize(
    n = n()
  )

class(year_summary)

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

db <- dbConnect_scidb("airlines")
flights <- tbl(db, "flights")
carriers <- tbl(db, "carriers")

year_summary <- flights %>% 
  group_by(year) %>%
  summarize(
    n = n()
  )

class(year_summary)

year_summary |>
  collect() |>
  class()

Under the hood…

year_summary |>
  show_query()

SQL

Five main clauses:

• SELECT
• FROM
• WHERE
• ORDER BY
• GROUP BY

Every SQL query must have SELECT and FROM

Example:

flights |>
  show_query()

SELECT (everything) FROM (the flights database)

Example

year_summary |>
  show_query()

• SELECT (the year column) …. FROM (the flights database)
• COUNT (all rows) and save it AS a new variable called n
• Also, results should be GROUPed BY year

Your turn

Example

flights |>
  filter(dest == "MSP") |>
  arrange(dep_delay)