---
title: "Lab 10.1 - Creating Databases using SQL"
author: "Author Goes Here"
date: "Semester Goes Here"
output:
  html_document:
    code_download: true
---

```{r include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
```

The goal of this exercise is to learn about how to work with databases directly in SQL, in particular PostgreSQL. This document will present SQL code that was created on [ElephantSQL](https://www.elephantsql.com/) and copy-pasted into this document as non-executed code (i.e., as a code chunk with `eval = FALSE`). 

## Defining the Games Database

We define a database that stores information on games, players, and scores. 

- <strong>Player</strong>(<u>ID</u>, emailAddress, name)
- <strong>PlayerGame</strong>(gameID, playerID, score)
- <strong>Game </strong>(<u>ID</u>, time)

This model specifies the tables required to represent games over time, who played in them, and who won. It assumes that all games are finished games (because it doesn't represent the state of the game or the players) and that the score is a single value computed as required by the game.
    
Here's a graphical version of the schema.

![games schema](https://cs.calvin.edu/courses/info/601/10databases/lab/images/games-schema.png)

Here is the definition of the games table 

```{sql eval = FALSE}
CREATE TABLE Game (
	ID integer PRIMARY KEY, 
	time timestamp
	);
```

🚧
Try running this code in the "Browser" of your ElephantSQL database instance. 

🚧
Add table-creation commands to the previous code chunk for the Player and PlayerGame tables. Notes:

- Define the email address and name to be `varchar(50)`.
- Define the score to be an integer.
- Define the `ID` fields as primary keys; the PlayerGame table does not have a primary key.
- Define `gameID` and `playerID` as foreign keys referring to `Game(ID)` and `Player(ID)` respectively.

## Loading the Games Database

We load the database with sample records that represent a simple database with three games and three players. Here are commands to add three games to the game table.

```{sql eval = FALSE}
INSERT INTO Game VALUES (1, '2006-06-27 08:00:00');
INSERT INTO Game VALUES (2, '2006-06-28 13:20:00');
INSERT INTO Game VALUES (3, '2006-06-29 18:41:00');
```

🚧
Add data-insertion commands to the previous code chunk that insert records into the Player and PlayerGame tables that represent the following information:

- Player
  1. name: `me@calvin.edu` name: none (leave this blank)
  2. name: `king@gmail.edu` name: `The King`
  3. name: `dog@gmail.edu` name: `Dogbreath`
- PlayerGame
  - in game 1, player 3 wins with 2350 points; 0 points for players 1 &amp; 2
  - in game 2, player 1 wins with 1000 points; 500 points for player 3; 0 points for player 2
  - in game 3, player 3 wins with 5500 points; 0 points for player 2; player 1 doesn't play in this game.

## Querying the Games Database

We write some sample SQL commands that query information from this database. This command retrieves all the records in the Player table.

```{sql eval = FALSE}
SELECT *
  FROM Player
```

🚧
Write, test, and include queries that do the following:

1. Retrieve the highest score ever recorded. Note that you can order the records by descending score and add `LIMIT 1` to get only the highest score.

2. Retrieve the email addresses of the players who played in game 3.

3. Retrieve name, score, and time for all games, order chronologically by game time and, in case of ties, by player name. Note that you can order by to columns by saying `ORDER BY col1, col2`.
