Working with Databases in R
Intermediate
8 hrs
2 Concepts
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)
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. What is the primary purpose of connecting and querying?
Q2. Which R package is most relevant for this topic?
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. What is the primary purpose of dbplyr — dplyr on databases?
Q2. Which R package is most relevant for this topic?