# Level - intro to databases - joins
# 10/10/2018

# load packages 
library(tidyverse)
library(RSQLite)
library(DBI)
library(knitr)

# create database on data science programs
source("create_db.R")

# let's take a look at our institutions table
tbl(ds_programs, "institutions")

## Exercise 1: Review how to select variables from a table ----

# use sql to build a query to select name and zipcode from the institutions table
dbGetQuery(conn = ds_programs, 
           "SELECT name, zipcode
             FROM institutions")

# use dplyr to build a query to select name and zipcode from the institutions table
ds_programs %>% 
  tbl("institutions") %>% 
    select(name, zipcode) 

## Exercise 2: Learn how to join two tables using a query ----

# use sql to select name and zipcode from the institutions table and to left_join all data from the weather table
dbGetQuery(conn = ds_programs, 
            "SELECT institutions.name, institutions.zipcode, weather.snow
             FROM institutions
             LEFT JOIN weather ON institutions.zipcode = weather.zip")

# use dplyr to select name and zipcode from the institutions table and to left_join all data from the weather table
ds_programs %>%
  tbl("institutions") %>%
    select(name, zipcode) %>%
    left_join(
      ds_programs %>% 
        tbl("weather") %>% 
        select(zip, snow),
      by = c("zipcode" = "zip")
  )

# close database connection to ds_programs database
dbDisconnect(ds_programs)