Homework 2

In this assignment you will practice writing SQL queries against a database of indie video games. The database has four related tables — developers, genres, games, and reviews — and covers the real-world catalog of indie titles you may already know.

There are 30 questions worth 150 points total (5 points each). All questions use only the concepts covered in Weeks 0–2: SELECT, WHERE, ORDER BY, DISTINCT, calculated columns, aliases, NULL handling, string and date filtering, logical operators, IN, and LIKE.

The Database¶

The database is named indiegames and contains four tables.

developers¶

Studios and individuals who make games.

genres¶

A lookup table for game genres.

games¶

The main table. Each game belongs to one developer and one genre.

reviews¶

Player reviews for individual games.

Entity-Relationship Diagram¶

The Questions¶

There are 30 questions, each in its own .sql file (q01.sql through q30.sql). Open each file and write your query below the comment block.

Step 1 — Accept the Assignment¶

1. Click the GitHub Classroom invitation link posted on Moodle

2. Click Accept this assignment

3. Wait a few seconds, then refresh the page

4. Click the link to your personal repository

Step 2 — Clone and Open¶

Open a terminal in your Coder workspace (or locally) and run:

git clone https://github.com/[YOUR-REPO-URL-HERE]
cd hw02-steam-sale-[YOUR-USERNAME]
code .

Step 3 — Set Up Python¶

python3 -m venv venv
source venv/bin/activate
pip install -r requirements.txt

Remember: activate the virtual environment (source venv/bin/activate) every time you open a new terminal before running grade.py.

Step 4 — Connect to the Database¶

Follow the same steps as hw01 to connect VSCode SQLTools to the database. Use these settings:

Connection name:  CS354
Server:           localhost
Port:             5432
Database:         indiegames
Username:         admin
Password:         admin

The database is pre-loaded with 50 games, 15 developers, 8 genres, and 77 reviews. No setup needed — start writing queries right away.

To explore the tables, run:

-- Preview a table
SELECT * FROM games LIMIT 5;
SELECT * FROM developers LIMIT 5;
SELECT * FROM reviews LIMIT 5;

Step 5 — Write Your Queries¶

Each question has its own .sql file. Open the file and write your query below the comment block. Example:

-- ============================================================
-- Question 1 (5 points)
-- List all columns and all rows from the games table.
-- There is no required ordering for this question.
--
-- Expected columns: all columns
-- ============================================================

-- Write your query below:
SELECT * FROM games;

Step 6 — Check Your Score Locally¶

Run the grader before submitting:

python3 grade.py

The first time you run it, the grader creates and loads the database automatically. Output looks like this:

✅ q01: correct    (5 pts)
✅ q02: correct    (5 pts)
❌ q03: incorrect  (0 pts)
💥 q04: error — syntax error at or near "FORM"  (0 pts)
⬜ q05: empty file  (0 pts)

Score: 10/150

Fix any issues and run grade.py again until you’re happy with your score.

Step 7 — Submit Your Work¶

When you’re ready to submit:

git add .
git commit -m "completed hw02"
git push

GitHub Actions will automatically run the grader on your submission. To see your results:

Go to your repo on GitHub
→ Click the Actions tab
→ Click the most recent workflow run
→ See ✅ or ❌ per question and your total score

You can push as many times as you want before the deadline — each push triggers a new grading run.

Grading¶

Each of the 30 questions is worth 5 points. Partial credit is not awarded — a question is either correct or not.