Lab 10.1 - Creating Databases using SQL

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 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.

• Player(ID, emailAddress, name)
• PlayerGame(gameID, playerID, score)
• Game (ID, 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 is the definition of the games table

CREATE TABLE Game (
    ID integer PRIMARY KEY, 
    time timestamp
    );

UPDATE
Try running this code in the “Browser” of your ElephantSQL database instance.

UPDATE
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.

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');

UPDATE
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 & 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.

SELECT *
  FROM Player

UPDATE
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.