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.

Practice

1. What is the main purpose of dbConnect() in R when working with databases?

easy

A. To run a SQL query and get results

B. To close an open database connection

C. To open a connection to a database

D. To create a new table in the database

Solution

1. Step 1: Understand the role of dbConnect()

   dbConnect() is used to start a connection between R and a database so they can communicate.

2. Step 2: Differentiate from other functions

   dbDisconnect() closes connections, dbGetQuery() runs queries, and dbExecute() changes data. Only dbConnect() opens the connection.

3. Final Answer:

   To open a connection to a database -> Option C

4. Quick Check:

   dbConnect() = open connection [OK]

Hint: Remember: Connect opens, Disconnect closes [OK]

Common Mistakes:

• Confusing dbConnect() with dbDisconnect()
• Thinking dbConnect() runs queries
• Assuming dbConnect() creates tables

2. Which of the following is the correct syntax to connect to an SQLite database file named mydata.sqlite using DBI in R?

easy

A. dbConnect(SQLite::RSQLite(), file = "mydata.sqlite")

B. dbConnect(SQLite, "mydata.sqlite")

C. dbConnect(RSQLite, dbname = "mydata.sqlite")

D. dbConnect(RSQLite::SQLite(), dbname = "mydata.sqlite")

Solution

1. Step 1: Identify correct function usage

   The correct way is to call dbConnect() with the driver RSQLite::SQLite() and specify the database file with dbname.

2. Step 2: Check each option

   dbConnect(RSQLite::SQLite(), dbname = "mydata.sqlite") uses the correct driver and argument name. Options B, C, and D misuse the driver or argument names.

3. Final Answer:

   dbConnect(RSQLite::SQLite(), dbname = "mydata.sqlite") -> Option D

4. Quick Check:

   Correct driver and dbname argument = dbConnect(RSQLite::SQLite(), dbname = "mydata.sqlite") [OK]

Hint: Use RSQLite::SQLite() and dbname for file [OK]

Common Mistakes:

• Using wrong driver syntax
• Using 'file' instead of 'dbname'
• Omitting the namespace RSQLite::

3. What will be the output of the following R code?

library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbExecute(con, "CREATE TABLE test (id INTEGER, name TEXT)")
dbExecute(con, "INSERT INTO test VALUES (1, 'Alice'), (2, 'Bob')")
res <- dbGetQuery(con, "SELECT name FROM test WHERE id = 2")
print(res)
dbDisconnect(con)

medium

A. name
1 Bob

B. id name
1 2 Bob

C. Error: no such table: test

D. NULL

Solution

1. Step 1: Understand the code flow

   The code creates an in-memory SQLite database, makes a table 'test', inserts two rows, then queries the name where id=2.

2. Step 2: Predict the query result

   The query selects the 'name' column for id=2, which is 'Bob'. The printed result is a data frame with one row and one column 'name' containing 'Bob'.

3. Final Answer:

   name 1 Bob -> Option A

4. Quick Check:

   Query returns name 'Bob' for id=2 [OK]

Hint: SELECT returns matching rows as data frame [OK]

Common Mistakes:

• Expecting full row instead of selected column
• Confusing dbExecute with dbGetQuery output
• Forgetting to disconnect connection

4. Identify the error in the following R code snippet that tries to connect to an SQLite database and run a query:

library(DBI)
con <- dbConnect(RSQLite::SQLite(), "mydb.sqlite")
result <- dbGetQuery(con, "SELECT * FROM users")
dbDisconnect(con)

medium

A. dbDisconnect() should be called before dbGetQuery()

B. The database file name should be passed as dbname argument, not directly

C. dbGetQuery() cannot be used with SQLite

D. The library DBI is not needed for SQLite connections

Solution

1. Step 1: Check dbConnect() usage

   The SQLite file name must be passed as dbname = "mydb.sqlite", not as a positional argument.

2. Step 2: Verify other function calls

   Calling dbGetQuery() before dbDisconnect() is correct. dbGetQuery() works with SQLite. The DBI library is required.

3. Final Answer:

   The database file name should be passed as dbname argument, not directly -> Option B

4. Quick Check:

   Use dbname= for file in dbConnect() [OK]

Hint: Always name dbname argument in dbConnect() [OK]

Common Mistakes:

• Passing file name without dbname=
• Disconnecting before query
• Thinking DBI is unnecessary

5. You want to create a new SQLite database in R, add a table named employees with columns id (integer) and name (text), insert three employees, and then retrieve all names. Which sequence of commands correctly achieves this?

hard

A. con <- dbConnect(RSQLite::SQLite(), dbname = "company.sqlite")
dbExecute(con, "CREATE TABLE employees (id INTEGER, name TEXT)")
dbExecute(con, "INSERT INTO employees VALUES (1, 'Anna'), (2, 'Ben'), (3, 'Cara')")
names <- dbGetQuery(con, "SELECT name FROM employees")
dbDisconnect(con)
print(names)

B. con <- dbConnect(RSQLite::SQLite(), "company.sqlite")
dbExecute(con, "CREATE employees (id INTEGER, name TEXT)")
dbExecute(con, "INSERT INTO employees VALUES (1, 'Anna'), (2, 'Ben'), (3, 'Cara')")
names <- dbGetQuery(con, "SELECT name FROM employees")
dbDisconnect(con)
print(names)

C. con <- dbConnect(RSQLite::SQLite(), dbname = "company.sqlite")
dbExecute(con, "CREATE TABLE employees (id TEXT, name INTEGER)")
dbExecute(con, "INSERT INTO employees VALUES (1, 'Anna'), (2, 'Ben'), (3, 'Cara')")
names <- dbGetQuery(con, "SELECT id FROM employees")
dbDisconnect(con)
print(names)

D. con <- dbConnect(RSQLite::SQLite(), dbname = "company.sqlite")
dbExecute(con, "CREATE TABLE employees (id INTEGER, name TEXT)")
dbExecute(con, "INSERT INTO employees VALUES (1, 'Anna')")
dbExecute(con, "INSERT INTO employees VALUES (2, 'Ben')")
dbExecute(con, "INSERT INTO employees VALUES (3, 'Cara')")
names <- dbGetQuery(con, "SELECT id FROM employees")
dbDisconnect(con)
print(names)

Solution

1. Step 1: Identify correct sequence

   Use dbConnect(RSQLite::SQLite(), dbname = "company.sqlite"), CREATE TABLE employees (id INTEGER, name TEXT), multi-row INSERT INTO employees VALUES (1, 'Anna'), (2, 'Ben'), (3, 'Cara'), SELECT name FROM employees.

2. Step 2: Eliminate distractors

   B: No dbname=, missing TABLE. C: Wrong types (name INTEGER fails insert), selects id. D: Selects id instead of name.

3. Final Answer:

   dbConnect(RSQLite::SQLite(), dbname = "company.sqlite"), correct CREATE TABLE, multi-row INSERT, SELECT name -> Option A

4. Quick Check:

   dbname=, id INTEGER name TEXT, INSERT 3 rows, SELECT name [OK]

Hint: Insert multiple rows in one dbExecute for efficiency [OK]

Common Mistakes:

• Passing file name without dbname=
• Wrong SQL syntax for CREATE TABLE
• Mixing column types (id TEXT instead of INTEGER)
• Selecting id instead of name
• Forgetting to disconnect