Working with Databases in R
Intermediate
8 hrs
2 Concepts
Your Learning Map
📌 You already know
You can wrangle data frames with dplyr.
🎯 You'll learn here
Connecting R to databases, running SQL, and using
dbplyr to write dplyr that runs in the database.🌍 Where it's used
Real datasets live in databases too big for memory; you query them where they sit.
🔗 Unlocks next
Same SQL ideas as the Class 12 DBMS unit — here driven from R.
M1
DBI and SQL
Concept 1
Connecting and Querying
DBI provides a consistent interface. RSQLite (for SQLite), RMySQL/RMariaDB (for MySQL), RPostgres (for PostgreSQL).
R
library(DBI); library(RSQLite)
con <- dbConnect(RSQLite::SQLite(), 'vidaara.db')
dbWriteTable(con, 'students', df, overwrite=TRUE)
dbGetQuery(con, 'SELECT subject, AVG(score) as avg FROM students GROUP BY subject')
dbDisconnect(con)
R — Filter rows like a SQL query
LIVE READY
Output (verified)
mpg cyl wt Fiat 128 32.4 4 2.200 Honda Civic 30.4 4 1.615 Toyota Corolla 33.9 4 1.835 Lotus Europa 30.4 4 1.513
Solved Examples
Example 1
Apply the concept of Connecting and Querying to a sample dataset. Show at least two approaches.
# See the code example above and adapt it to your data. # Always check your output with str() and head().
Self-Assessment (2 questions)
Q1. Which package provides a common interface for connecting R to databases?
DBI defines a standard connect/query interface; backends like RSQLite implement it.
Q2. After dbConnect(), you run an SQL query with:
DBI::dbGetQuery(con, "SELECT ...") runs SQL and returns a data frame.
Concept 2
dbplyr — dplyr on Databases
dbplyr translates dplyr code to SQL automatically. Use tbl() to create a reference, collect() to fetch results.
R
library(dplyr)
tbl(con, 'students') |>
filter(score >= 90) |>
group_by(subject) |>
summarise(n=n(), mean_score=mean(score, na.rm=TRUE)) |>
collect() # execute SQL and return data frame
Solved Examples
Example 1
Apply the concept of dbplyr — dplyr on Databases to a sample dataset. Show at least two approaches.
# See the code example above and adapt it to your data. # Always check your output with str() and head().
Self-Assessment (2 questions)
Q1. dbplyr lets you:
dbplyr translates dplyr verbs into SQL so work happens in the database, not in R memory.
Q2. A benefit of dbplyr over pulling all rows into R is that it:
Computation runs in the database; only the small result is collected into R.