Database Querying with SQL

class: center, middle, inverse, title-slide

# Database Querying with SQL
## Using SQL in R Markdown
### Ben Baumer
### SDS 192</br>April 20th, 2020</br>(<a href="http://beanumber.github.io/sds192/lectures/mdsr_sql_04-rmarkdown.html" class="uri">http://beanumber.github.io/sds192/lectures/mdsr_sql_04-rmarkdown.html</a>)

---

class: center, middle, inverse
# Using SQL in R Markdown
---
## Do we need the MySQL Workbench?

- Short answer: No

- Long answer: I find it helpful for:
    - exploring databases with a GUI
    - fiddling around with queries
    - more informative error messages
    - built-in help
    - adding keys and indexes (more on that later)
    - then copy-and-paste working queries into R Markdown

---
## Ways to interact with MySQL

1. GUI SQL client: MySQL Workbench

2. command line SQL client (next slide)

3. generic SQL access in R: ~~`DBI::dbGetQuery()`~~

4. `RMySQL` through R Markdown chunks (see labs & hw)

5. Lots of other ways...

I prefer:

- (1) for SQL development

- (4) for SQL reporting

---
## R Markdown as a conceirge

- Key idea: R Markdown can work with many languages
    - it doesn't *translate*
    - but it knows people who can
    - it can collect results

- Your R Markdown file can contain:
    - R chunks
    - SQL chunks
    - Python chunks
    - many other languages

.footnote[https://bookdown.org/yihui/rmarkdown/language-engines.html]

- Analogy:
    - R Markdown is like hotel conceirge
    - Guests are like chunks
    - Most of them only speak R (i.e., English)
    - The conceirge doesn't speak all these languages, but **she knows people who do**
    - The conceirge can collect information and report it in one place

---
## Method 4: Using SQL in R Markdown

- An R chunk that defines a connection to a database

```r
library(RMySQL)
db <- dbConnect(
  MySQL(), 
  host = "scidb.smith.edu", 
  user = "sds192", 
  password = "DSismfc@S", 
  dbname = "imdb"
)
knitr::opts_chunk$set(connection = db, max.print = 20)
```

.footnote[https://bookdown.org/yihui/rmarkdown/language-engines.html#sql]

- As many SQL chunks as you want

````
```{sql connection=db, output.var="types"}
SELECT * FROM kind_type;
```
````
--

- **Use "Chunk Output Inline" option for interactive use**