dbplyr / SQL Exercises in R: 15 Practice Problems

Fifteen practice problems on dbplyr: connect, translate dplyr to SQL, lazy ops, joins, collect. Hidden solutions.

By Selva Prabhakaran · Published May 11, 2026 · Last updated May 11, 2026

library(dplyr)
library(dbplyr)
library(DBI)
library(RSQLite)

Exercise 1: Connect to SQLite (in-memory)

Difficulty: Beginner.

Show solution

con <- dbConnect(SQLite(), ":memory:")
dbWriteTable(con, "mtcars", mtcars)
dbListTables(con)

Exercise 2: tbl reference

Difficulty: Beginner.

Show solution

con <- dbConnect(SQLite(), ":memory:")
dbWriteTable(con, "mtcars", mtcars)
mt <- tbl(con, "mtcars")
mt

Exercise 3: filter

Difficulty: Beginner.

Show solution

con <- dbConnect(SQLite(), ":memory:"); dbWriteTable(con, "mtcars", mtcars)
tbl(con, "mtcars") |> filter(mpg > 25)

Exercise 4: Show SQL

Difficulty: Intermediate.

Show solution

con <- dbConnect(SQLite(), ":memory:"); dbWriteTable(con, "mtcars", mtcars)
tbl(con, "mtcars") |> filter(mpg > 25) |> show_query()

Exercise 5: select

Difficulty: Beginner.

Show solution

con <- dbConnect(SQLite(), ":memory:"); dbWriteTable(con, "mtcars", mtcars)
tbl(con, "mtcars") |> select(mpg, cyl)

Exercise 6: group_by + summarise

Difficulty: Intermediate.

Show solution

con <- dbConnect(SQLite(), ":memory:"); dbWriteTable(con, "mtcars", mtcars)
tbl(con, "mtcars") |> group_by(cyl) |> summarise(m = mean(mpg))

Exercise 7: arrange

Difficulty: Beginner.

Show solution

con <- dbConnect(SQLite(), ":memory:"); dbWriteTable(con, "mtcars", mtcars)
tbl(con, "mtcars") |> arrange(desc(mpg))

Exercise 8: collect to local

Difficulty: Intermediate.

Show solution

con <- dbConnect(SQLite(), ":memory:"); dbWriteTable(con, "mtcars", mtcars)
local_df <- tbl(con, "mtcars") |> filter(mpg > 25) |> collect()
nrow(local_df)

Exercise 9: Inner join

Difficulty: Intermediate.

Show solution

con <- dbConnect(SQLite(), ":memory:")
dbWriteTable(con, "a", data.frame(id = 1:3, x = 10:12))
dbWriteTable(con, "b", data.frame(id = 2:4, y = 20:22))
inner_join(tbl(con, "a"), tbl(con, "b"), by = "id")

Exercise 10: Send raw SQL

Difficulty: Intermediate.

Show solution

con <- dbConnect(SQLite(), ":memory:"); dbWriteTable(con, "mtcars", mtcars)
dbGetQuery(con, "SELECT cyl, AVG(mpg) AS m FROM mtcars GROUP BY cyl")

Exercise 11: List tables

Difficulty: Beginner.

Show solution

con <- dbConnect(SQLite(), ":memory:"); dbWriteTable(con, "mtcars", mtcars)
dbListTables(con)

Exercise 12: Disconnect

Difficulty: Beginner.

Show solution

con <- dbConnect(SQLite(), ":memory:")
dbDisconnect(con)

Exercise 13: window function via SQL

Difficulty: Advanced.

Show solution

con <- dbConnect(SQLite(), ":memory:"); dbWriteTable(con, "mtcars", mtcars)
tbl(con, "mtcars") |> group_by(cyl) |>
  mutate(rk = row_number(desc(mpg))) |>
  show_query()

Exercise 14: SQL escape protected string

Difficulty: Advanced.

Show solution

con <- dbConnect(SQLite(), ":memory:")
dbQuoteIdentifier(con, "my table")

Exercise 15: Write large data

Difficulty: Intermediate.

Show solution

con <- dbConnect(SQLite(), "demo.sqlite")
dbWriteTable(con, "mtcars", mtcars, overwrite = TRUE)
dbDisconnect(con)

What to do next

dplyr-Exercises (shipped), local dplyr practice.
Data-Wrangling-Exercises (shipped), broader wrangling.