---
title: "SQL: Queries"
author: "Statistical Computing, 36-350"
date: "Monday November 14, 2016"
---

What is a database?
===

It helps define things "from the ground up":

- A **field** is a variable/quantity of interest 
- 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 tables in database lingo

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

Why do we need database software?
===

- **Size**
    - R keeps its data frames in memory
    - Industrial databases can be much bigger
    - Must work with selected subsets
    
- **Speed**
    - Smart people have worked very hard on getting just what you want, quickly (like 2014 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, SQL
===

- 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** (**structured query language**) is the standard for database software
- Mostly about **queries**, which are like doing row/column selections on a data frame in R

Connecting R to SQL
===

SQL is its own language, independent of R (similar to when you learned regexes). But we're goign to learn how to run SQL queries through R

First, we need to install the packages `DBI`, `RSQLite`, then we load them into our R session with `library()`

Also, we need a database file: to run the following examples, download the file up at http://www.stat.cmu.edu/~ryantibs/statcomp-F16/data/baseball.db, and save it in your R working directory

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

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

Listing what's available
===

```{r}
dbListTables(con) # List tables in our database 
dbListFields(con, "Batting") # List fields in Batting table
dbListFields(con, "Pitching") # List fields in Pitching table
```

Importing a table as a data frame
===

```{r}
batting = dbReadTable(con, "Batting") 
class(batting) 
dim(batting)
```

Now we could go on and perform R operations on `batting`, since it's a data frame

This week, we'll use this route primarily to check our work in SQL; in general, should try to do as much in SQL as possible, since it's more efficient and can be simpler

`SELECT`
===

Main tool in the SQL language: `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;
```

`WHERE`, `GROUP BY`, `HAVING`, `ORDER BY`, `LIMIT` are all optional 

Examples
===

To pick out five columns from the table "Batting", and only 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 successful SQL query (congrats!)

We can replicate this simple command on the imported data frame:

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

To reiterate: 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)

`ORDER BY`
===

We can use the `ORDER BY` option in `SELECT` to specify an ordering for the rows

Default is ascending order; add `DESC` for descending

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