210-sql-query-steps.Rmd

# SQL queries broken down {#chapter_sql-queries-breakdown}

> This chapter has two separate topics: SQL execution steps and passing values to SQL statements.  Do they belong together?  Does the chapter have the right title?

> This chapter explains:
> 
> * Some details about how SQL queries work behind the scenes
> * SQL queries are executed behind the scenes
> * You can pass values to SQL queries

These packages are called in almost every chapter of the book:
```{r setup, echo=TRUE, message=FALSE, warning=FALSE}

library(tidyverse)
library(DBI)
library(RPostgres)
library(glue)
require(knitr)
library(dbplyr)
library(sqlpetr)
```

Start up the `docker-pet` container
```{r}
sp_docker_start("sql-pet")

```

Connect to the database with R:
```{r}

con <- sp_get_postgres_connection(user = Sys.getenv("DEFAULT_POSTGRES_USER_NAME"),
                         password = Sys.getenv("DEFAULT_POSTGRES_PASSWORD"),
                         dbname = "dvdrental",
                         seconds_to_test = 30, connection_tab = TRUE
                         )
```

## SQL Execution Steps

*  Parse the incoming SQL query
*  Compile the SQL query
*  Plan/optimize the data acquisition path
*  Execute the optimized query / acquire and return data

> how do those steps map to the following code?

```{r}
dbWriteTable(con, "mtcars", mtcars, overwrite = TRUE)

rs <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
dbFetch(rs)
dbClearResult(rs)
```

## Passing values to SQL statements
```{r}
#Pass one set of values with the param argument:
rs <- dbSendQuery(con,"SELECT * FROM mtcars WHERE cyl = 4")
dbFetch(rs)
dbClearResult(rs)
```
## Pass multiple sets of values with dbBind():
```{r}
rs <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = $1")
dbBind(rs, list(6L)) # cyl = 6
dbFetch(rs)
dbBind(rs, list(8L)) # cyl = 8
dbFetch(rs)
dbClearResult(rs)
```

## Clean up
```{r}
# dbRemoveTable(con, "cars")
dbRemoveTable(con, "mtcars")
# dbRemoveTable(con, "cust_movies")
```


```{r}
## Disconnect from the database and stop Docker

dbDisconnect(con)

sp_docker_stop("sql-pet")
```