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. 👇

(Click image to play 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).



The look on your co-workers face says everything.


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. =)



👇 Top R-Tips Tutorials you might like: