Database connections (DBI, RSQLite) in R Programming - Mini Project: Build & Apply

Database connections (DBI, RSQLite)

📖 Scenario: You are working on a small project where you need to store and retrieve data using a database. You will use R with the DBI and RSQLite packages to connect to a database, create a table, insert data, and then read it back.

🎯 Goal: Build a simple R script that connects to an SQLite database, creates a table, inserts some data, and then reads the data back to display it.

📋 What You'll Learn

Use the DBI package to connect to an SQLite database

Create a table called students with columns id (integer) and name (text)

Insert exactly three students with ids 1, 2, 3 and names 'Alice', 'Bob', 'Charlie'

Read the data back from the students table

Print the retrieved data

💡 Why This Matters

🌍 Real World

Databases are used everywhere to store data safely and efficiently. Knowing how to connect and interact with databases is essential for data analysis and application development.

💼 Career

Many data jobs require working with databases to extract, manipulate, and analyze data. Skills with DBI and SQLite in R are useful for data scientists, analysts, and developers.

1

Set up the database connection

Load the DBI and RSQLite packages, then create a connection called conn to an in-memory SQLite database using dbConnect().

R Programming

# Load DBI and RSQLite packages
# Create a connection called conn to an in-memory SQLite database
# Your code here

Need a hint?

Use dbConnect() with RSQLite::SQLite() and the special string ":memory:" to create a temporary database.

2

Create the students table

Use dbExecute() with the connection conn to create a table called students with columns id as INTEGER and name as TEXT.

R Programming

library(DBI)
library(RSQLite)
conn <- dbConnect(RSQLite::SQLite(), ":memory:")
# Create a table called students with columns id (INTEGER) and name (TEXT)
# Your code here

Need a hint?

Write a SQL command inside dbExecute() to create the table with the specified columns.

3

Insert data into the students table

Use dbExecute() with conn to insert three rows into the students table with these exact values: (1, 'Alice'), (2, 'Bob'), and (3, 'Charlie').

R Programming

library(DBI)
library(RSQLite)
conn <- dbConnect(RSQLite::SQLite(), ":memory:")
dbExecute(conn, "CREATE TABLE students (id INTEGER, name TEXT)")
# Insert three students with ids 1, 2, 3 and names 'Alice', 'Bob', 'Charlie'
# Your code here

Need a hint?

Use a single INSERT INTO statement with multiple value tuples.

4

Read and print the data

Use dbGetQuery() with conn to select all rows from the students table into a variable called result. Then print result.

R Programming

library(DBI)
library(RSQLite)
conn <- dbConnect(RSQLite::SQLite(), ":memory:")
dbExecute(conn, "CREATE TABLE students (id INTEGER, name TEXT)")
dbExecute(conn, "INSERT INTO students (id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')")
# Select all rows from students into result and print it
# Your code here

Need a hint?

Use dbGetQuery() to run a SELECT statement and assign it to result. Then use print() to show the data.