---
title: 'Lecture 17: Databases'
author: "Statistical Computing, 36-350"
date: "Wednesday November 18, 2015"
---

Outline
===

- What databases are, and why
- SQL
- Interfacing R and SQL

Databases
===

- A **record** is a collection of **fields**
- A **table** is a collection of records which all have the same 
  fields (with different values)
- A **database** is a collection of tables


Databases versus data frames
===

- Data frames in R are actually tables

R jargon | Database jargon
--------|----------------
column  | field
row     | record
data frame | table
types of the columns | table **schema**
bunch of related data frames | database

So, why do we need database software?
===

- **Size**
    + R keeps its data frames in memory
    + Industrial databases can be much bigger
    + Work with selected subsets
- **Speed**
    + Clever people have worked very hard on getting just what you want fast
    (Turing award winner Michael Stonebraker!)
- **Concurrency**
    + Many users accessing the same database simultaneously
    + Lots of potential for trouble (two users want to change the same record 
    at once)
    
The client-server model
===

- Databases live on a **server**, which manages them
- Users interact with the server through a **client** program
- Lets multiple users access the same database simultaneously

SQL
===

- **SQL** (**structured query language**) is the standard for database software
- Mostly about **queries**, which are like doing a selection in R

```
debt[debt$Country=="France",c("growth","ratio")]
with(debt,debt[Country=="France",c("growth","ratio")])
subset(debt,subset=(Country=="France"),select=c("growth","ratio"))
```
- We will see shortly how SQL does stuff like this

Connecting R to SQL
===

First, though let's see how to connect SQL to R. Note:

- SQL is a language; database management systems (DMBS) actually implement 
  it and do the work
    + MySQL, SQLite, etc.,
- They all have somewhat different conventions
- The R package `DBI` is a unified interface to them
- Need a separate "driver" for each DBMS

---

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 the SQL language 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
===

Suppose we want to pick out five columns from the table `Batting`, 
and we only want to look at the first 10 rows.

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

This is our very first SQL query (congrats!). It was very efficient

---

Note that we can replicate this command on the data frame `batting`:

```{r}
batting[1:10, c("playerID", "yearID", "AB", "H", "HR")]
```

This was simply to check our work, and we wouldn't actually want to do this on 
a large database, since it'd be much more inefficient to first read into an R data frame,
and then call R commands)

Likewise, throughout this lecture, we'll be writing R code to check our SQL code, but
keep in mind this is just for the sake of learning (not that you would do this in practice)

Practice problems
===

**Enter your unique ID here:**

Work through the following problems (go ahead and fill in the code below). 
In particular, for each of the following, explain the SQL commands, and replicate
the results using R commands that you write

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

# What's going on here? R equivalent on batting data frame?
```

---

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

# What's going on here? R equivalent on batting data frame?
```

---

```{r}
dbGetQuery(con, paste("SELECT playerID, yearID, AB, H, HR",
                      "FROM Batting",
                      "WHERE HR > 55",
                      "ORDER BY HR DESC"))

# What's going on here? R equivalent on batting data frame?
```

---

```{r}
dbGetQuery(con, paste("SELECT playerID, yearID, AB, H, HR",
                      "FROM Batting",
                      "WHERE yearID >= 1990 AND yearID <= 2000",
                      "ORDER BY HR DESC",
                      "LIMIT 10"))

# What's going on here? R equivalent on batting data frame?
```

---

```{r}
dbGetQuery(con, paste("SELECT AVG(HR)",
                      "FROM Batting"))

# What's going on here? R equivalent on batting data frame?
```

---

```{r}
dbGetQuery(con, paste("SELECT SUM(HR)",
                      "FROM Batting"))

# What's going on here? R equivalent on batting data frame?
```

---

```{r}
dbGetQuery(con, paste("SELECT teamID, yearID, playerID, MAX(HR)",
                      "FROM Batting"))

# What's going on here? R equivalent on batting data frame?
```

---

```{r}
dbGetQuery(con, paste("SELECT AVG(HR)",
                      "FROM Batting",
                      "WHERE yearID >= 1990"))

# What's going on here? R equivalent on batting data frame?
```

---

```{r}
dbGetQuery(con, paste("SELECT teamID, AVG(HR)",
                      "FROM Batting",
                      "WHERE yearID >= 1990",
                      "GROUP BY teamID",
                      "LIMIT 5"))

# What's going on here? R equivalent on batting data frame?
```

---

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

# What's going on here? R equivalent on batting data frame?
```

---

```{r}
dbGetQuery(con, paste("SELECT teamID, yearID, AVG(HR)",
                      "FROM Batting",
                      "WHERE yearID == 1991 OR yearID == 1992",
                      "GROUP BY teamID, yearID",
                      "ORDER BY AVG(HR) DESC",
                      "LIMIT 15"))

# What's going on here? R equivalent on batting data frame?
```

Summary
===

- A database is basically a way of dealing efficiently with lots of
  potentially huge data frames
- SQL is the standard language for telling databases what to do, especially 
  what queries to run
- Pretty much everything in an SQL query is something we've practiced 
  already in R 
    + subsetting/selection, aggregation, merging, ordering
- Connect R to the database, send it an SQL query, analyse the returned 
  data frame