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. 👇
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!
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
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:
- mmtable2: ggplot2 for tables
- ggdist: Make a Raincloud Plot to Visualize Distribution in ggplot2
- ggside: Plot linear regression with marginal distributions
- DataEditR: Interactive Data Editing in R
- openxlsx: How to Automate Excel in R
- officer: How to Automate PowerPoint in R
- DataExplorer: Fast EDA in R
- esquisse: Interactive ggplot2 builder
- gghalves: Half-plots with ggplot2
- rmarkdown: How to Automate PDF Reporting
- patchwork: How to combine multiple ggplots
- Geospatial Map Visualizations in R
Want these tips every week? Join R-Tips Weekly.