Working with Single-Table Data

Project Checkpoint

In Part 2, the same project from Part 1 stays in use, but now you work with its data more directly. This milestone has two steps:

  1. practice SQL directly against the existing project database
  2. add one small read-only feature that uses the same database from application code

Recommended timing: complete the chapter-level exercises in this part before treating this project milestone as complete.

Step 1: Direct SQL Practice

By this point, the project should already start, expose /decks, and contain the seeded decks data from the previous part. The goal of Step 1 is to get comfortable inspecting and safely changing that same data directly in SQL.

Complete the following:

  1. Connect to the project database with the course-approved tool, such as psql inside the running environment.
  2. Run at least three single-table SELECT queries against project tables.
  3. Insert one temporary row into decks.
  4. Update that same row.
  5. Delete that same row.
  6. Verify each change with follow-up SELECT queries and, when useful, by refreshing /decks.

If the project is running through Docker Compose, one typical psql workflow is:

docker compose exec db psql -U postgres -d studytracker

Inside psql, the following commands are useful:

  • \dt to list tables
  • \d table_name to describe a table’s columns and keys
  • SELECT * FROM table_name to read all rows from a table
  • SELECT * FROM table_name WHERE condition to read only some rows
  • INSERT INTO table_name (columns) VALUES (values) to add a new row
  • UPDATE table_name SET column = value WHERE condition to change existing rows
  • DELETE FROM table_name WHERE condition to remove rows

Try at least the following:

\dt

SELECT * FROM decks ORDER BY name;

INSERT INTO decks (name, description)
VALUES ('Temporary SQL Practice', 'Created in Part 2');

SELECT * FROM decks WHERE name = 'Temporary SQL Practice';

UPDATE decks
SET description = 'Updated in Part 2'
WHERE name = 'Temporary SQL Practice';

SELECT * FROM decks WHERE name = 'Temporary SQL Practice';

DELETE FROM decks
WHERE name = 'Temporary SQL Practice';

SELECT * FROM decks WHERE name = 'Temporary SQL Practice';

This is intentionally repetitive. The point is to build the habit of checking the database state after every write.

Submit this small check after you have completed Step 1:

Loading Exercise...

Step 2: Add a Small Read-Only Feature

Step 2 moves the same SQL ideas into the application. The task is deliberately small: one table, one query, one page. The route and template structure are given below; the main missing piece is the SQL query.

Add this near the existing routes in app/main.py:

@app.get("/decks/newest")
def newest_deck(request: Request):
    with get_connection() as conn:
        deck = conn.execute(
            """
            -- TODO: return id, name, description for the newest deck
            """
        ).fetchone()

    return render(request, "newest_deck.html", deck=deck)

Add this template as app/templates/newest_deck.html:

<!doctype html>
<html lang="en">
  <body>
    <h1>Newest Deck</h1>
    {% if deck %}
      <p><strong>{{ deck.name }}</strong></p>
      {% if deck.description %}
        <p>{{ deck.description }}</p>
      {% endif %}
    {% else %}
      <p>No decks found.</p>
    {% endif %}
  </body>
</html>

Fill in the missing SQL so that it should:

  • read from the existing decks table,
  • return id, name, and description for one deck,
  • order the rows so that the newest deck comes first,
  • and limit the result to one row

Once ready, check that the new route works and shows the expected deck.

  1. Start the project with docker compose up --build. If the project was already running, stop it and restart with the --build flag to ensure that the new code is included.
  2. Confirm that /decks still works.
  3. Open /decks/newest and check which deck appears.
  4. Login to the database and insert a new deck (allow PostgreSQL to generate the id, i.e., do not explicitly specify it).
  5. Refresh /decks/newest and confirm that it now shows the new deck.

Step 2 is complete when:

  • /decks/newest loads successfully,
  • the page reads from the existing decks table,
  • the page shows the newest deck’s name and description,
  • and the query uses ordering plus limiting to select that one row

Return the Project

When both steps are complete, return a zip of the project checkpoint. The root of the zip should contain:

  • requirements.txt
  • the folder app
  • the folder migrations

You may also include compose.yaml, Dockerfile, and start.sh. These files are useful for local development, but the automated grader focuses on the Python application and the SQL migrations.

Loading Exercise...
← Debugging SQL Queries
Recap and Feedback →