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: statisticsglob...
Create & Connect to Databases in R: statisticsglob...
Profile of Kirby White: statisticsglob...
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