Here we show SQL constructs using the Lahman baseball dataset (downloaded from https://github.com/jknecht/baseball-archive-sqlite). We also show how to use a SQL database inside Rmarkdown.

First, we create a connection to the database. In this case, we are using a `SQLite` database. A good system to prototype database designs. To make the most of a database system, one would use some of the more powerful products: Oracle, Microsoft SQLServer, MySQL (MariaDB), PostgreSQL or other. In all cases, the way to create a connection to the server from Rmarkdown is the same.

```{r setup}
library(tidyverse)
```

```{r setup_db}
library(DBI)
db <- dbConnect(RSQLite::SQLite(), dbname="../lecture-notes/data/lahman2016.sqlite")
```

### Select-From-Where

First, we write a query to get batting statistics for Washington Nationals in 2010: note the *rename*. Note that the chunk in the markdown file is declared as <code>{sql, connection=db}</code> in the Rmarkdown file:

```{sql, connection=db}
SELECT b.playerId, b.yearId, b.H, b.AB
FROM BATTING AS b
WHERE teamID = 'WAS' AND yearID = 2010
```

### Expressions

The **select** clause can contain expressions (this is paralleled by the `mutate` operation we saw previously)

```{sql, connection=db}
SELECT b.playerId, b.yearId, b.AB, 1.0 * b.H / b.AB AS BP
FROM BATTING AS b
```


### WHERE predicates

The **where** clause support a large number of different predicates and combinations thereof (this is parallel to the `filter` operation)

```{sql, connection=db}
SELECT b.playerId, b.yearID, b. teamId, b.AB, 1.0 * b.H / b.AB AS BP
FROM BATTING AS b
WHERE b.AB > 0 AND
  b.yearID > 2000 AND
  b.yearID < 2010 AND 
  b.teamID LIKE 'NY%'
```

### ORDERING

We can include ordering (parallel to `arrange`)

```{sql, connection=db}
SELECT b.playerId, b.yearID, b. teamId, b.AB, 1.0 * b.H / b.AB AS BP
FROM BATTING AS b
WHERE b.AB > 0 AND
  b.yearID > 2000 AND
  b.yearID < 2010 AND 
  b.teamID LIKE 'NY%'
ORDER BY b.AB DESC, BP DESC;
```

### Group_by and Summarize

- What it does: Partition the tuples by the group attributes (*teamId* and *yearId* in this case), and do something (*compute avg* in this case) for each group
- Number of resulting tuples == Number of groups

```{sql, connection=db}
SELECT b.teamId, b.yearId, avg(1.0 * b.H / b.AB) AS AVE_BP
FROM BATTING AS b
WHERE b.AB > 0 AND
  b.yearID > 2000 AND
  b.yearID < 2010
GROUP BY b.teamId, b.yearId
ORDER BY AVE_BP DESC
```

For reference, this is how we would do this using 
the `dplyr` operations we learned about previously.

```{r, eval=FALSE}
Batting %>%
  filter(AB > 0, ...) %>%
  group_by(teamId, yearId) %>%
  summarize(ave_bp = mean(1.0 * H / AB))
```

### Subqueries

Sometimes it's easier to nest queries like the one above into query and subquery

```{sql, connection=db}
SELECT teamID, yearID, avg(BP) AS AVG_BP
FROM (SELECT b.teamId, b.yearId, 1.0 * b.H / b.AB AS BP
      FROM BATTING AS b
      WHERE b.AB > 0 AND
        b.yearID > 2000 AND
        b.yearID < 2010)
GROUP BY teamID, yearID
ORDER BY AVG_BP DESC;
```

## Joins

List all players from California, playing in 2015



```{sql, connection=db}
select b.playerId, b.teamId, m.birthState
from Batting as b join master as m on b.playerId == m.playerId
where yearId = "2015" and m.birthState = "CA"
```



Finally, we close the connection to the database:

```{r}
dbDisconnect(db)
```