Welcome to RStudio! (Meet the 4 Panes)

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

  1. 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.
  2. Bottom-Left (Console): Where R executes commands behind the scenes.
  3. Top-Right (Environment): Shows all the datasets and variables currently loaded in memory.
  4. 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"      "Genre"        
##  [6] "Invoice"       "InvoiceLine"   "MediaType"     "Playlist"      "PlaylistTrack"
## [11] "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

Follow these steps to publish your finished lab report to RPubs.com and get your submission link for Canvas:

  1. Add Your Name: At the very top of this file (Line 3), replace [Type Your Name Here] with your actual name and save (Ctrl+S / Cmd+S).
  2. Click the Green ▶ Play Button on the Code Chunk Below:
    • It will automatically knit your report to HTML, upload it to RPubs.com, and print a http://rpubs.com/publish/claim/... link right below the box!
  3. Click That Link:
    • It will open RPubs.com in a new tab (sign in or create a free account if this is your first lab, then click Continue).
    • Copy your final https://rpubs.com/your_username/... URL from your browser’s address bar and paste it into Canvas!
# 👉 CLICK THE GREEN '▶' PLAY BUTTON ON THE RIGHT OF THIS BOX TO PUBLISH!

# Step 1: Automatically knit your latest work into an HTML report
rmarkdown::render("Week01_SQL_to_R_Chinook.Rmd", quiet = TRUE)

# Step 2: Upload the HTML report directly to RPubs.com
upload_result <- rsconnect::rpubsUpload(
  title = "BAN 3083: Week 1 SQL to R (Chinook)",
  contentFile = "Week01_SQL_to_R_Chinook.html",
  originalDoc = "Week01_SQL_to_R_Chinook.Rmd"
)

cat("\n============================================================\n")
cat("🚀 SUCCESS! CLICK THE LINK BELOW TO CLAIM YOUR RPUBS PAGE:\n")
cat(upload_result$continueUrl, "\n")
cat("============================================================\n")