Assignment 3: SQL Investigator #

This assignment will have you apply the basic SQL query techniques you’ve learned to crack an investigative puzzle.

There will be 2 parts to this, each is a distinct mystery you solve by querying the database to unmask the culprit.

This assignment was derived from the very cool project: https://www.sqlnoir.com, I think these challenges are a fun way to practice the SQL skills you have learned up to this point.

§1 1. Start the Database Instances #

1. Extract the project archive to a new directory.
2. Open a terminal window and navigate into that directory.
3. Confirm you are in the correct place by running ls:

$ ls
docker-compose.yaml   init  PART1.md   PART2.md  README.md

4. Launch the database container:

docker-compose up

This command will start the PostgreSQL instances. Logs will stream to your terminal; leave this window open while you work.

$ docker-compose up
Attaching to postgres-a3-part1, postgres-a3-part2
postgres-a3-part1  | The files belonging to this database system will be owned by user "postgres".
postgres-a3-part1  | This user must also own the server process.
postgres-a3-part1  |
postgres-a3-part1  | The database cluster will be initialized with locale "en_US.utf8".
postgres-a3-part1  | The default database encoding has accordingly been set to "UTF8".
postgres-a3-part1  | The default text search configuration will be set to "english".
postgres-a3-part1  |
postgres-a3-part1  | Data page checksums are disabled.
postgres-a3-part1  |
postgres-a3-part1  | fixing permissions on existing directory /var/lib/postgresql/data ... ok
postgres-a3-part1  | creating subdirectories ... ok
postgres-a3-part1  | selecting dynamic shared memory implementation ... posix
postgres-a3-part2  | The files belonging to this database system will be owned by user "postgres".
postgres-a3-part2  | This user must also own the server process.
...

To shut down the databases after completing the assignment, press Ctrl + C (⌘ + C on MacOS) in this same terminal window.

If you accidentally close the window, simply reopen a terminal and navigate bask to the assignment directory, then run docker-compose up again to re-attach.

§2 2. Connect to the Database #

There are 2 parts to this assignment, each part is through a separate database instance, you will need to separately connect to a different database for each part.

§2.1 Part 1: #

• To connect to the part 1 database, run: docker exec -it postgres-a3-part1 psql
• This will immediately launch the psql CLI against the database.
• Read the introduction in PART1.md, The objectives are just guidelines on how to solve this.
• Use SQL commands to solve the case, remember that you can run \d & \d table_name to inspect table schemas.
• Write down the SQL commands (including \d commands) you use to find the culprit, and the culprit name for submission.
• Type exit to leave the database session when you are done.

§2.2 Part 2: #

To connect to the part 2 database, run: docker exec -it postgres-a3-part2 psql

• Read the introduction in PART2.md, The objectives are just guidelines on how to solve this.
• Use SQL commands to solve the case, remember that you can run \d & \d table_name to inspect table schemas.
• Write down the SQL commands (including \d commands) you use to find the culprit, and the culprit name for submission.
• Type exit to leave the database session when you are done.

§3 3. Stop the Database Instance #

Do not shut down the container until you have finished the assignment.
If you accidentally stop the database, repeat the commands from section 1.

When ready:

1. Return to the terminal running docker-compose up, you will see the output logs from the database instance.
2. Press Ctrl + C to gracefully stop both containers.
3. Verify nothing is running:

$ docker ps
CONTAINER ID   IMAGE     COMMAND   CREATED   STATUS    PORTS     NAMES
# empty output

§4 4. Submission #

Create a plain-text submission on canvas with 6 numbered sections:

Part 1:

1. Commands used to find the culprit
2. The culprit name and id
3. Short explanation of how you figured out the answer.

Part 2:

4. Commands used to find the culprit
5. The culprit name and id
6. Short explanation of how you figured out the answer.

If you have any questions or issues, please message or email the professor.