Database connections (DBI, RSQLite) in R Programming - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the output of this R code connecting to an SQLite database?

Consider the following R code that creates an in-memory SQLite database, creates a table, inserts data, and queries it. What will be the output printed?

R Programming

library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbExecute(con, "CREATE TABLE fruits (name TEXT, quantity INTEGER)")
dbExecute(con, "INSERT INTO fruits VALUES ('apple', 5), ('banana', 3), ('orange', 7)")
res <- dbGetQuery(con, "SELECT name FROM fruits WHERE quantity > 4 ORDER BY quantity DESC")
print(res)
dbDisconnect(con)

A

    name
1   apple
2  orange

B

    name
1  banana
2   apple

C

    name
1  orange
2   apple

D

    name
1  banana
2  orange

💡 Hint

Look at the WHERE clause filtering quantity > 4 and the ORDER BY quantity DESC.

The query selects fruits with quantity greater than 4, which are 'apple' (5) and 'orange' (7). Ordering by quantity descending puts 'orange' first, then 'apple'.

❓ Predict Output

intermediate

What error does this R code produce when connecting to a non-existent SQLite file?

What error message will this R code produce when trying to connect to a SQLite database file that does not exist?

R Programming

library(DBI)
con <- dbConnect(RSQLite::SQLite(), "nonexistent_file.sqlite")
dbDisconnect(con)

A Error: unable to open database file

B Warning: database file missing

C Error: file not found

D No error, connection is created and file is created automatically

💡 Hint

RSQLite creates the file if it does not exist when connecting.

RSQLite automatically creates the SQLite file if it does not exist when connecting, so no error occurs.

🔧 Debug

advanced

Why does this R code fail to insert data into the SQLite table?

This R code tries to insert data into a SQLite table but fails with an error. Identify the cause.

R Programming

library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbExecute(con, "CREATE TABLE cars (model TEXT, year INTEGER)")
data <- data.frame(model = c('Ford', 'Toyota'), year = c(2010, 2015))
dbWriteTable(con, "cars", data, append = TRUE)
dbDisconnect(con)

A Error because data frame column names do not match table column names

B No error, data is inserted successfully

C Error because dbWriteTable cannot append to an existing table without overwrite=FALSE

D Error because dbWriteTable requires row names argument

💡 Hint

Check the usage of dbWriteTable with append=TRUE on an existing table.

dbWriteTable with append=TRUE correctly inserts data into an existing table if column names match. Here, columns match and no error occurs.

❓ Predict Output

advanced

What is the output of this R code that lists tables in an SQLite database?

Given this R code that creates two tables in an SQLite database, what will dbListTables(con) return?

R Programming

library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbExecute(con, "CREATE TABLE users (id INTEGER, name TEXT)")
dbExecute(con, "CREATE TABLE orders (order_id INTEGER, user_id INTEGER)")
tables <- dbListTables(con)
print(sort(tables))
dbDisconnect(con)

A [1] "orders" "users"

B [1] "users" "orders"

C [1] "sqlite_sequence" "users" "orders"

D [1] "orders"

💡 Hint

dbListTables returns all user tables; sorting alphabetically helps.

The database has two tables: 'users' and 'orders'. Sorting alphabetically returns 'orders' then 'users'.

🧠 Conceptual

expert

Which option correctly explains the behavior of dbDisconnect() in RSQLite?

When using DBI with RSQLite, what happens if you do not call dbDisconnect(con) after finishing database operations?

A The connection remains open, potentially locking the database file and causing resource leaks

B The connection is automatically closed when the R session ends, so no issues occur

C The connection is closed immediately after the last query, so dbDisconnect() is optional

D The database file is deleted automatically if dbDisconnect() is not called

💡 Hint

Think about resource management and file locks in database connections.

Not calling dbDisconnect() leaves the connection open, which can lock the database file and waste resources until R session ends or garbage collection occurs.