Learn How to Write SQL From R

Written by Matt Dancho on January 26, 2021

---

This article is part of a R-Tips Weekly, a weekly video tutorial that shows you step-by-step how to do common R coding tasks.

SQL queries getting you down 😰? Let R write SQL queries for you!

Here are the links to get set up. 👇

• Get the Code
• YouTube Tutorial

Writing SQL from R

We’re going to generate SQL from R using 3 key packages:

• DBI - For connecting to & working with databases
• RSQLite - For working with the SQLite-variety of database
• tidyverse - Gives us dbplyr - the database-backened to dplyr - FOR FREE!! OMG!

Step 1: Connect to database
Use DBI to make a connection

DBI has the dbConnect() function. Let’s use this to establish a connection with our SQL database.

Examine the connection. You should see a connection object has been created and the SQL database has one table named “MPG”.

Step 2: Write Dplyr, Generate SQL
Use the dbplyr backend to auto-magically create SQL for us.

DBI has the show_query() function we can use to generate SQL…

…and here’s the result!

Friendly Reminders:
1. Full code in the Github Repository.
2. Watch the YouTube Video for detailed instructions.
3. Share with your friends. (Don't be a hog).

But if you really want to improve your productivity…

Here's how to master R. 👇 What happens after you learn R for Business.

When your CEO gets word of your Shiny Apps saving the company tons of $$$ (cha-ching!). 👇

This is career acceleration.

SETUP R-TIPS WEEKLY PROJECT

1. Get the Code

2. Check out the R-Tips Setup Video.

Once you take these actions, you’ll be set up to receive R-Tips with Code every week. =)