---
title: 'Lecture 18: More Databases'
author: "Statistical Computing, 36-350"
date: "Monday November 23, 2015"
---

Outline
===

- Recap of databases, and SQL 
- Recap of interfacing R and SQL
- Operations across multiple tables

Databases versus data frames
===

- Data frames in R are actually tables in database lingo

R jargon | Database jargon
--------|----------------
column  | field
row     | record
dataframe | table
types of the columns | table schema
bunch of dataframes       | database
selections, `subset` | `SELECT ... FROM ... WHERE ... HAVING`
`aggregate`, `d*ply` | `GROUP BY`
`merge`              | `JOIN`
`order`              | `ORDER BY`

---

Databases are very useful for performing manipulations across different 
tables

They don't require the tables to be stored in memory, which allows for better
speed and efficiency, also handles concurrency issues

The use a **client-server** model, and the standard language for database 
programming is called **SQL**

Connecting R to SQL
===

Before running the following, install the packages: `DBI`, `RSQLite`.
Also, download the database file 
http://www.stat.cmu.edu/~ryantibs/statcomp-F15/lectures/baseball.db, and save it in your
working directory.

```{r}
library(DBI)
library(RSQLite)
drv = dbDriver("SQLite")
con = dbConnect(drv, dbname="baseball.db")
```

`con` is now a persistent connection to the database `baseball.db`

---

```{r}
dbListTables(con)                     # Get tables in the database 
dbListFields(con, "Batting")          # List fields in table Batting
batting = dbReadTable(con, "Batting") # Import a table as a data frame
dim(batting)
```

SELECT
===

The main tool in SQL is `SELECT`, which allows you to perform
queries on a particular table in a database. It has the form:

```
SELECT columns or computations
  FROM table
  WHERE condition
  GROUP BY columns
  HAVING condition
  ORDER BY column [ASC|DESC]
  LIMIT offset,count;
```

Example: picking out columns
===

Pick out columns `playerID`, `yearID`, `AB`, `H`, `HR` from `Batting`, 
order the rows by `yearID` (increasing order by default), limit display 
to 10 rows:

```{r}
dbGetQuery(con, paste("SELECT playerID, yearID, AB, H, HR",
                      "FROM Batting",
                      "ORDER BY yearID",
                      "LIMIT 10"))
```

Note that the R equivalent is:

```{r}
head(batting[order(batting$yearID), c("playerID", "yearID", "AB", "H", "HR")],10)
```

Example: computing homerun averages
===

In R, to compute the average the homeruns `HR` per year, and sort in descending order:

```{r}
library(plyr)
sort(daply(batting, .(yearID), function(df) { mean(df$HR, na.rm=TRUE)} ), dec=TRUE)[1:10]
```

How we would do this in SQL:

```{r}
dbGetQuery(con, paste("SELECT yearID, AVG(HR) as AvgHR",
                      "FROM Batting",
                      "GROUP BY yearID",
                      "ORDER BY AvgHR DESC",
                      "LIMIT 10"))
```

Few notes: 

- here we dynamically renamed the computed column `AVG(HR)` to be `AvgHR`
- the order of commands matters; try switching the order of `GROUP BY` and 
  `ORDER BY`, and you'll get an error

Example: limiting records we average
===

Only compute homerun averages past 1990:

```{r}
dbGetQuery(con, paste("SELECT yearID, AVG(HR) as AvgHR",
                      "FROM Batting",
                      "WHERE yearID >= 1990",
                      "GROUP BY yearID",
                      "ORDER BY AvgHR DESC",
                      "LIMIT 10"))

# Exercise: what is the R equivalent?
```

Example: limiting records we display
===

Only show homerun averages bigger than 4:

```{r}
dbGetQuery(con, paste("SELECT yearID, AVG(HR) as AvgHR",
                      "FROM Batting",
                      "WHERE yearID >= 1990",
                      "GROUP BY yearID",
                      "HAVING AvgHR >= 4",
                      "ORDER BY AvgHR DESC"))

# Exercise: what is the R equivalent?
```

`JOIN`
===

So far `FROM` has just been one table. Sometimes we need to combine information from 
many tables. SQL can do this without needing to read them each into memory

```{r}
dbListFields(con, "Salaries")
dbGetQuery(con, "SELECT * FROM Salaries LIMIT 5")
dbGetQuery(con, "SELECT yearID, teamID, lgID, playerID, HR FROM Batting LIMIT 5")
```

Suppose we want to figure out the average salaries of the players with the top 10
highest homerun averages. We'd have to combine these two data frames

- In R, we'd use `merge()` to link the tables up by `playerID` (or do it manually)
- SQL doesn't have `merge()`, but it has `JOIN` as a modifier to `FROM`

Practice problems
===

**Enter your unique ID here:**

Work through the following problems (go ahead and fill in code where needed)

```{r}
# 1. Our first attempt using JOIN
dbGetQuery(con, paste("SELECT playerID, salary, HR",
                      "FROM Batting JOIN Salaries USING(playerID)",
                      "LIMIT 10"))

# You can see that we've attempted to join the two tables using playerID

# What happened? We can see that there are (at least) 10 records of the player
# aardsda01 ... 
# Write SQL code below to confirm that there are only 6 records of this player
# in Salaries, and 5 of this player in the Salaries table. Hence there are far 
# too many in the joined table ...
# (Hint: use WHERE, and note SQL requires ' ' to define a string)
```

---

```{r}
# 2. Our second attempt using JOIN
dbGetQuery(con, paste("SELECT yearID, playerID, salary, HR",
                      "FROM Batting JOIN Salaries USING(yearID, playerID)",
                      "LIMIT 10"))

# You can see that we've attempted to join the two tables using yearID and 
# playerID, both. This makes sense because each player has a different record
# (row) for each year of their career

# What happened now? There are only 5 records for the player aardsda01. Write
# SQL code to confirm that these are the properly merged records for this player.
# That is, look at the records in the Batting and Salaries data tables for this 
# player, and look at the yearID field 
```

---

```{r}
# 3. Modify the code below which derives the merged data table (same as above),
# to list the names of the players with the top 10 average homeruns, along with 
# their average salaries
# (Hint: building on top of this code, you can just pretend like you have one big
# table with fields from both Batting and Salaries; use AVG, GROUP BY, ORDER BY)

dbGetQuery(con, paste("SELECT playerID, salary, HR",
                      "FROM Batting JOIN Salaries USING(yearID, playerID)",
                      "LIMIT 10"))
```

---

```{r}
# 4. Modify the code below which derives the merged data table (same as above),
# to list the names of the players with the top 10 average salaries, along with 
# their average homeruns

dbGetQuery(con, paste("SELECT playerID, salary, HR",
                      "FROM Batting JOIN Salaries USING(yearID, playerID)",
                      "LIMIT 10"))
```

--- 

```{r}
# 5. Write SQL code to list the 10 worst seasons in terms of errors committed E
# by an individual player. You should display the year and player name, along with 
# their error count E. Also, restrict your search to years 1990 and later
# (Hint: the error column E is available as part of the Fielding table; use WHERE
# to restrict your search)

dbListFields(con,"Fielding")
```

---

```{r}
# 6. Write SQL code to appropriately JOIN the Fielding and Salaries data tables,
# and list the salaries for the player / year combinations you extracted in the 
# previous question 
```

---

```{r}
# 7. Write SQL code to answer the following question: what was the highest salary
# paid to a player who made more than 30 errors in a season, after 1990?
```
