Week 1: From SQL to R with the Chinook Database

Welcome to RStudio! (Meet the 4 Panes)

Take a quick look around your browser window—you are looking at the 4-pane RStudio IDE:

Top-Left (Source Editor — where you are reading this!): Your notebook and scripts. Click the green ▶ (Run Current Chunk) button on the top-right of any gray code box below to run code.
Bottom-Left (Console): Where R executes commands behind the scenes.
Top-Right (Environment): Shows all the datasets and variables currently loaded in memory.
Bottom-Right (Files / Plots / Help): Where your visualizations (Plots) and files (Files) appear.

Part 1: Connecting R to the Chinook SQLite Database

In our SQL module, you queried the Chinook digital music store database (Artist, Album, Track, Genre, Customer, Invoice).

R doesn’t replace SQL—in fact, R works hand-in-hand with SQL! Let’s load our packages and connect to chinook.sqlite.

👉 Click the green ▶ play button on the right side of the gray box below:

library(DBI)
library(RSQLite)
library(tidyverse)
library(dbplyr)

# Connect to the Chinook SQLite file
con <- dbConnect(RSQLite::SQLite(), "data/chinook.sqlite")

# List all tables in the database (these should look familiar!)
dbListTables(con)

##  [1] "Album"         "Artist"        "Customer"      "Employee"     
##  [5] "Genre"         "Invoice"       "InvoiceLine"   "MediaType"    
##  [9] "Playlist"      "PlaylistTrack" "Track"

Part 2: Writing Pure SQL Directly Inside RMarkdown

Did you know you can write 100% pure SQL inside an RMarkdown document without learning a single line of R syntax first?

Look at the header of the code chunk below: {sql connection=con, output.var="genre_summary"}. - connection=con tells RStudio to run this SQL query against our Chinook database. - output.var="genre_summary" tells RStudio to save the resulting table as a new R dataset named genre_summary!

👉 Run the SQL chunk below, then look at your Top-Right (Environment) pane:

SELECT 
    g.Name AS Genre,
    COUNT(t.TrackId) AS TrackCount,
    ROUND(AVG(t.Milliseconds) / 60000.0, 2) AS AvgMinutes,
    ROUND(AVG(t.UnitPrice), 2) AS AvgPrice
FROM Track AS t
INNER JOIN Genre AS g ON t.GenreId = g.GenreId
GROUP BY g.Name
HAVING COUNT(t.TrackId) >= 30
ORDER BY TrackCount DESC;

Let’s inspect the genre_summary table that SQL just handed over to R:

genre_summary

##                 Genre TrackCount AvgMinutes AvgPrice
## 1                Rock       1297       4.73     0.99
## 2               Latin        579       3.88     0.99
## 3               Metal        374       5.16     0.99
## 4  Alternative & Punk        332       3.91     0.99
## 5                Jazz        130       4.86     0.99
## 6            TV Shows         93      35.75     1.99
## 7               Blues         81       4.51     0.99
## 8           Classical         74       4.90     0.99
## 9               Drama         64      42.92     1.99
## 10           R&B/Soul         61       3.67     0.99
## 11             Reggae         58       4.12     0.99
## 12                Pop         48       3.82     0.99
## 13         Soundtrack         43       4.07     0.99
## 14        Alternative         40       4.40     0.99
## 15        Hip Hop/Rap         35       2.97     0.99
## 16  Electronica/Dance         30       5.05     0.99

Part 3: Running SQL Inside R Code with `dbGetQuery()`

Another common way data scientists run SQL in R is using the dbGetQuery() function. You pass it your database connection (con) and a SQL query string, and it returns an R data frame.

Let’s find the Top 10 Countries by Total Invoice Sales in Chinook:

country_sales <- dbGetQuery(con, "
  SELECT 
      BillingCountry AS Country,
      COUNT(InvoiceId) AS NumInvoices,
      ROUND(SUM(Total), 2) AS TotalRevenue
  FROM Invoice
  GROUP BY BillingCountry
  ORDER BY TotalRevenue DESC
  LIMIT 10;
")

country_sales

##           Country NumInvoices TotalRevenue
## 1             USA          91       523.06
## 2          Canada          56       303.96
## 3          France          35       195.10
## 4          Brazil          35       190.10
## 5         Germany          28       156.48
## 6  United Kingdom          21       112.86
## 7  Czech Republic          14        90.24
## 8        Portugal          14        77.24
## 9           India          13        75.26
## 10          Chile           7        46.62

Part 4: Why Combine SQL and R? Visualization (`ggplot2`)!

SQL is fantastic at joining and aggregating tables—but SQL cannot create graphics.

Because our SQL query results (genre_summary and country_sales) are now R data frames, we can pipe them directly into ggplot2, R’s industry-standard visualization library!

👉 Run the chunk below to visualize the SQL query we just ran:

ggplot(genre_summary, aes(x = reorder(Genre, TrackCount), y = TrackCount, fill = AvgMinutes)) +
  geom_col() +
  coord_flip() +
  scale_fill_viridis_c(option = "plasma", name = "Avg Track\nLength (min)") +
  labs(
    title = "Chinook Catalog: Most Popular Genres & Track Lengths",
    subtitle = "Data extracted via SQL JOIN on Track and Genre tables",
    x = "Genre",
    y = "Number of Tracks (n >= 30)"
  ) +
  theme_minimal(base_size = 12)

Part 5: Sneak Peek at Next Week — How `dplyr` Speaks SQL!

Starting next week, we will learn dplyr, R’s grammar of data wrangling.

Here is a secret that makes learning dplyr much easier: dplyr verbs are really just SQL clauses in disguise!

What you want to do	SQL Clause	R `dplyr` Verb
Pick columns	`SELECT`	`select()`
Filter rows	`WHERE` / `HAVING`	`filter()`
Create new columns	`SELECT ... AS`	`mutate()`
Group rows	`GROUP BY`	`group_by()`
Aggregate metrics	`COUNT()`, `AVG()`, `SUM()`	`summarize()`
Sort rows	`ORDER BY`	`arrange()`

In fact, R can connect directly to a SQL table using tbl(con, "TableName") and automatically translate your R code into SQL!

👉 Run the chunk below to see R write the SQL query for you using show_query():

# Connect to the Track and Genre tables lazily
tracks_db <- tbl(con, "Track")
genres_db <- tbl(con, "Genre")

# Write R (dplyr) code using the pipe operator |>
r_pipeline <- tracks_db |>
  inner_join(genres_db, by = "GenreId") |>
  group_by(Genre = Name.y) |>
  summarize(
    TrackCount = count(),
    AvgMinutes = mean(Milliseconds, na.rm = TRUE) / 60000.0
  ) |>
  filter(TrackCount >= 30) |>
  arrange(desc(TrackCount))

# Ask R: "What SQL query did you generate behind the scenes?"
show_query(r_pipeline)

## <SQL>
## SELECT
##   `Genre`,
##   count() AS `TrackCount`,
##   AVG(`Milliseconds`) / 60000.0 AS `AvgMinutes`
## FROM (
##   SELECT
##     `TrackId`,
##     `Track`.`Name` AS `Name.x`,
##     `AlbumId`,
##     `MediaTypeId`,
##     `Track`.`GenreId` AS `GenreId`,
##     `Composer`,
##     `Milliseconds`,
##     `Bytes`,
##     `UnitPrice`,
##     `Genre`.`Name` AS `Name.y`,
##     `Genre`.`Name` AS `Genre`
##   FROM `Track`
##   INNER JOIN `Genre`
##     ON (`Track`.`GenreId` = `Genre`.`GenreId`)
## ) AS `q01`
## GROUP BY `Genre`
## HAVING (count() >= 30.0)
## ORDER BY `TrackCount` DESC

And if we execute collect(r_pipeline), R runs that exact query and brings back the results:

collect(r_pipeline)

## # A tibble: 16 × 3
##    Genre              TrackCount AvgMinutes
##    <chr>                   <int>      <dbl>
##  1 Rock                     1297       4.73
##  2 Latin                     579       3.88
##  3 Metal                     374       5.16
##  4 Alternative & Punk        332       3.91
##  5 Jazz                      130       4.86
##  6 TV Shows                   93      35.8 
##  7 Blues                      81       4.51
##  8 Classical                  74       4.90
##  9 Drama                      64      42.9 
## 10 R&B/Soul                   61       3.67
## 11 Reggae                     58       4.12
## 12 Pop                        48       3.82
## 13 Soundtrack                 43       4.07
## 14 Alternative                40       4.40
## 15 Hip Hop/Rap                35       2.97
## 16 Electronica/Dance          30       5.05

Part 6: Your Turn! (Lab Exercises)

Exercise 1: Write Your Own SQL Query in R

Modify the {sql} chunk below to find the Top 10 Artists with the most Albums in the Chinook database (joining Artist and Album). The result will automatically be saved to R as top_artists.

SELECT 
    ar.Name AS ArtistName,
    COUNT(al.AlbumId) AS AlbumCount
FROM Artist AS ar
INNER JOIN Album AS al ON ar.ArtistId = al.ArtistId
GROUP BY ar.ArtistId, ar.Name
ORDER BY AlbumCount DESC
LIMIT 10;

Exercise 2: Visualize Your SQL Output with `ggplot2`

Run the chunk below to plot your top_artists SQL result. Try changing fill = "steelblue" to another color (like "darkorange", "seagreen", or "firebrick") and updating the chart title!

ggplot(top_artists, aes(x = reorder(ArtistName, AlbumCount), y = AlbumCount)) +
  geom_col(fill = "steelblue") +
  coord_flip() +
  labs(
    title = "Top 10 Artists by Number of Albums in Chinook",
    x = "Artist",
    y = "Number of Albums"
  ) +
  theme_minimal()

🌐 How to Publish to RPubs & Submit Your URL on Canvas

Instead of downloading files, you will publish your finished report to RPubs.com and submit your public web link on Canvas!

Step 1: Knit & Publish

At the top of this file (Line 3), replace [Type Your Name Here] with your name.
Click the 🧶 Knit button at the top of this pane. Your formatted HTML report will open in a preview window.
In the top-right corner of that preview window (or the top-right of this editor pane), click the blue Publish button (the blue eye/circle icon) \(\rightarrow\) choose RPubs.
A browser tab will open on RPubs.com:
- Sign in (or create a free RPubs account if this is your first lab).
- Give your report a title (e.g., Stats 101 - Week 1 Chinook Lab) and click Continue.
Copy the URL from your browser address bar (it will look like https://rpubs.com/your_username/1234567) and paste that URL into the Canvas assignment submission box!

💡 Popup Blocker Backup: If your browser blocks the Publish popup window, just run the code chunk below after clicking Knit—it will print a direct clickable link in the Console to finish publishing your report to RPubs!

# Only run this if the blue "Publish -> RPubs" button was blocked by your browser:
upload_result <- rsconnect::rpubsUpload(
  title = "Stats 101: Week 1 SQL to R (Chinook)",
  contentFile = "Week01_SQL_to_R_Chinook.html",
  originalDoc = "Week01_SQL_to_R_Chinook.Rmd"
)
upload_result$continueUrl

Week 1: From SQL to R with the Chinook Database

Student Name: [Type Your Name Here]

2026-09-22

Welcome to RStudio! (Meet the 4 Panes)

Part 1: Connecting R to the Chinook SQLite Database

Part 2: Writing Pure SQL Directly Inside RMarkdown

Part 3: Running SQL Inside R Code with `dbGetQuery()`

Part 4: Why Combine SQL and R? Visualization (`ggplot2`)!

Part 5: Sneak Peek at Next Week — How `dplyr` Speaks SQL!

Part 6: Your Turn! (Lab Exercises)

Exercise 1: Write Your Own SQL Query in R

Exercise 2: Visualize Your SQL Output with `ggplot2`

🌐 How to Publish to RPubs & Submit Your URL on Canvas

Step 1: Knit & Publish

Week 1: From SQL to R with the Chinook Database

Student Name: [Type Your Name Here]

2026-09-22

Welcome to RStudio! (Meet the 4 Panes)

Part 1: Connecting R to the Chinook SQLite Database

Part 2: Writing Pure SQL Directly Inside RMarkdown

Part 3: Running SQL Inside R Code with dbGetQuery()

Part 4: Why Combine SQL and R? Visualization (ggplot2)!

Part 5: Sneak Peek at Next Week — How dplyr Speaks SQL!

Part 6: Your Turn! (Lab Exercises)

Exercise 1: Write Your Own SQL Query in R

Exercise 2: Visualize Your SQL Output with ggplot2

🌐 How to Publish to RPubs & Submit Your URL on Canvas

Step 1: Knit & Publish

Part 3: Running SQL Inside R Code with `dbGetQuery()`

Part 4: Why Combine SQL and R? Visualization (`ggplot2`)!

Part 5: Sneak Peek at Next Week — How `dplyr` Speaks SQL!

Exercise 2: Visualize Your SQL Output with `ggplot2`