Learn How to Write SQL From R

Written by Matt Dancho

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.


  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:

  1. mmtable2: ggplot2 for tables
  2. ggdist: Make a Raincloud Plot to Visualize Distribution in ggplot2
  3. ggside: Plot linear regression with marginal distributions
  4. DataEditR: Interactive Data Editing in R
  5. openxlsx: How to Automate Excel in R
  6. officer: How to Automate PowerPoint in R
  7. DataExplorer: Fast EDA in R
  8. esquisse: Interactive ggplot2 builder
  9. gghalves: Half-plots with ggplot2
  10. rmarkdown: How to Automate PDF Reporting
  11. patchwork: How to combine multiple ggplots
  12. Geospatial Map Visualizations in R

Want these tips every week? Join R-Tips Weekly.