Database connections (DBI, RSQLite) in R Programming - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of the DBI package in R?

DBI provides a common interface to connect and interact with databases in R, allowing you to send queries and retrieve data in a consistent way.

beginner

How do you create a connection to an SQLite database using RSQLite?

Use dbConnect(RSQLite::SQLite(), dbname = "path_to_db.sqlite") to open a connection to an SQLite database file.

beginner

What function do you use to run a SQL query and get results as a data frame in R?

Use dbGetQuery(connection, "SQL query") to send a query and get the results directly as a data frame.

beginner

Why is it important to close a database connection in R?

Closing a connection with dbDisconnect(connection) frees resources and avoids locking the database, which helps keep your system stable.

beginner

What does the function dbListTables() do?

It lists all the tables available in the connected database, helping you see what data you can access.

Which function opens a connection to an SQLite database in R?

A dbOpen(RSQLite::SQLite(), "mydb.sqlite")

B connectSQLite("mydb.sqlite")

C dbConnect(RSQLite::SQLite(), dbname = "mydb.sqlite")

D openDB("mydb.sqlite")

The correct function is dbConnect with RSQLite::SQLite() specifying the database file.

How do you retrieve query results as a data frame in R?

A dbSendQuery(connection, "SELECT * FROM table")

B dbGetQuery(connection, "SELECT * FROM table")

C dbFetch(connection, "SELECT * FROM table")

D dbReadTable(connection, "table")

dbGetQuery sends the query and returns the results as a data frame directly.

What is the purpose of dbDisconnect()?

A To close the database connection

B To open a new database connection

C To list tables in the database

D To execute a SQL query

dbDisconnect closes the connection and frees resources.

Which package provides the interface to connect to databases in R?

A DBI

B dplyr

C ggplot2

D tidyr

DBI is the package that defines the database interface in R.

What does dbListTables(connection) return?

A The connection status

B The number of rows in a table

C The structure of a table

D A list of table names in the database

dbListTables returns all table names available in the connected database.

Explain how to connect to an SQLite database in R and retrieve data from a table.

Think about opening a door (connection) and then reading a book (query data).

Describe why it is important to close a database connection after use in R.

Imagine leaving a room open or closed after you finish using it.