Install/Run Packages

#install.packages("odbc", "DBI")
library(odbc)
## Warning: package 'odbc' was built under R version 4.2.3
library(DBI)
## Warning: package 'DBI' was built under R version 4.2.3
library(tidyverse)
## Warning: package 'ggplot2' was built under R version 4.2.3
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr     1.1.0     ✔ readr     2.1.4
## ✔ forcats   1.0.0     ✔ stringr   1.5.0
## ✔ ggplot2   3.4.2     ✔ tibble    3.1.8
## ✔ lubridate 1.9.2     ✔ tidyr     1.3.0
## ✔ purrr     1.0.1     
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()
## ℹ Use the ]8;;http://conflicted.r-lib.org/conflicted package]8;; to force all conflicts to become errors

Create Sample Database

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

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

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

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

#Now, we can only access this data through the database (via global environment)

### View Table

tbl(src = con, #source
    "who") #table name)

Establish SQL Connection


SELECT 
  who.country, who.year, who.new_sp_m3544, population.population 
FROM
  who
LEFT JOIN
  population ON population.country = who.country AND population.year = who.year
WHERE
  who.country IN ('Brazil', 'Germany') AND
  who.year >= 2000 AND
  who.year <= 2010

View db Using R Syntax

# This might be intuituve to you, but to explain, we SELECT columns FROM the who table, JOIN synched columns (where coutnry lines up), and filter to WHERE we see German and Brazilian data from 2000 to 2010. 

#To prove this is now a legitamate df, we'll call the headers with r syntax.

head(M1_results)