SQL Queries from inside R

Intermediate Importing Data in R

Filip Schouwenaars

Instructor, DataCamp

ch_2_1_slides.003.png

ch_2_1_slides.004.png

ch_2_1_slides.005.png

ch_2_1_slides.006.png

ch_2_1_slides.007.png

Selective importing

SQL Queries
DBI -> RMySQL, RPostgreSQL, ...
Just the basics of SQL

company

ch_2_1_slides.012.png

company

ch_2_1_slides.013.png

Load package and connect

library(DBI)con <- dbConnect(RMySQL::MySQL(),
         dbname = "company",
         host = "courses.csrrinzqubik.us-
                          east-1.rds.amazonaws.com",
         port = 3306,
         user = "student",
         password = "datacamp")

Example 1

employees <- dbReadTable(con, "employees")
subset(employees, 
         subset = started_at > "2012-09-01", 
         select = name)

     name
3   Julie
4 Heather
5    John

dbGetQuery(con, "SELECT name FROM employees 
                      WHERE started_at > '2012-09-01'")

     name
1   Julie
2 Heather
3    John

Example 1

ch_2_1_slides.027.png

Example 1

ch_2_1_slides.028.png

Example 1

ch_2_1_slides.029.png

Example 1

ch_2_1_slides.030.png

Example 1

ch_2_1_slides.031.png

Example 1

ch_2_1_slides.032.png

company

ch_2_1_slides.034.png

company

ch_2_1_slides.035.png

Example 2

products <- dbReadTable(con, "products")

subset(products, subset = contract == 1)

  id          name contract
2  2     Call Plus        1
4  9 Biz Unlimited        1

dbGetQuery(con, "SELECT * FROM products 
                                   WHERE contract = 1")

  id          name contract
1  2     Call Plus        1
2  9 Biz Unlimited        1

Let's practice!

Intermediate Importing Data in R