Write & Run SQL Query in R (Example) | RMarkdown & dplyr Package | File Access & Server Management

How to execute a SQL query in the R programming language. More details: https://statisticsglobe.com/run-sql-q...
Create & Connect to Databases in R: https://statisticsglobe.com/create-co...
Profile of Kirby White: https://statisticsglobe.com/kirby-white

R code of this video:

install.packages("odbc")
install.packages("DBI")
install.packages("tidyverse")

library(odbc)
library(DBI)
library(tidyverse)

#Load the sample data
data("population")
data("who")

#Create database
con ＜- dbConnect(drv = RSQLite::SQLite(),
dbname = ":memory:")

#store sample data in database
dbWriteTable(conn = con,
name = "population",
value = population)
dbWriteTable(conn = con,
name = "who",
value = who)

#remove the local data from the environment
rm(who, population)

tbl(src = con, #the source if the database connection profile
"who") #the name of the table to preview

select ＜- "SELECT who.country, who.year, who.new_sp_m2534, population.population"
from ＜- "FROM who"
ljoin ＜- "LEFT JOIN population ON population.country = who.country AND population.year = who.year"
where ＜- "WHERE who.country IN ('Brazil', 'Germany') AND who.year ＞= 2000 AND who.year ＜= 2010"

query ＜- paste(select, from, ljoin, where)

M2_results ＜- DBI::dbGetQuery(conn = con,
statement = query)

head(M2_results)

M3_results ＜-
tbl(src = con, "who") %＞%
filter(country %in% c("Brazil", "Germany"),
year ＞= 2000,
year ＜= 2010) %＞%
dplyr::select(country, year, new_sp_m014) %＞%
left_join(y = tbl(src = con, "population"),
by = c("country", "year")) %＞%
collect() #this tells dplyr to execute and store the query

Follow me on Social Media:
Facebook – Statistics Globe Page:   / statisticsglobecom  
Facebook – Group for Discussions & Questions:   / statisticsglobe  
LinkedIn – Statistics Globe Page:   / statisticsglobe  
LinkedIn – Group for Discussions & Questions:   / 12555223  
Twitter:   / joachimschork  

Music by bensound.com