Building My First Real Database: What a Weekend SQL Assignment Taught Me About Postgres (and Git)

#sql #postgres #database #github

This weekend I got an assignment that sounded simple on paper: build a small school database for a fictional secondary school called Greenwood Academy, fill it with data, and then write a bunch of queries against it. Three tables. Thirty questions. Easy, right?

It was not "easy," but it was one of those exercises where you can actually feel yourself understanding SQL instead of just copying syntax from a tutorial. Here's what the process looked like, and a few things that tripped me up along the way.

Setting up the database

I'm using PostgreSQL hosted on Aiven, with DBeaver as my SQL client. The first step was creating a schema basically a named container to keep all of Greenwood Academy's tables separate from anything else in the database:

CREATE SCHEMA greenwood_academy;
SET search_path TO greenwood_academy;

That second line tripped me up at first. I kept looking for a USE statement like you'd see in MySQL tutorials, and PostgreSQL just doesn't have one. SET search_path is the equivalent-it tells PostgreSQL, "when I say students, I mean greenwood_academy.students" for the rest of the session.

From there I created three tables:students, subjects, and exam_results, and then immediately had to alter them: adding a phone_number column to students, renaming credits to credit_hours in subjects, and then dropping that same phone_number column a few questions later because the "school" changed its mind. It's a small thing, but it made ALTER TABLE feel less like a random command and more like something you reach for constantly in real database work, schemas are never really "done."

Filling it up and immediately breaking it

Once the tables existed, I inserted 10 students, 10 subjects, and 10 exam results.Then the assignment had me simulate the kind of small corrections that happen in any real system: a student moves cities, a mark gets entered wrong and needs fixing, an exam result gets cancelled and needs deleting.

UPDATE students
SET city = 'Nairobi'
WHERE student_id = 5;

UPDATE exam_results
SET marks = 59
WHERE result_id = 5;

DELETE FROM exam_results
WHERE result_id = 9;

Nothing complicated, but it's a good reminder of why WHERE clauses deserve respect.Forget one on an UPDATE or DELETE and you're not fixing one row, you're rewriting the whole table.

The query section is where it clicked

Sections C through F were the actual querying practice: filtering with WHERE, combining conditions with AND/OR, using BETWEEN, IN, NOT IN, and LIKE, running COUNT(), and finally writing CASE WHEN statements to turn raw numbers into labels.

The CASE WHEN questions were my favorite part of the whole assignment.

SELECT
    result_id,
    marks,
    CASE
        WHEN marks >= 80 THEN 'Distinction'
        WHEN marks >= 60 THEN 'Merit'
        WHEN marks >= 40 THEN 'Pass'
        ELSE 'Fail'
    END AS performance
FROM exam_results;

...felt like the first time in this assignment where SQL stopped being about storing data and started being about presenting it.

The part nobody warns you about: Git

Writing the SQL was honestly the easy part.

Pushing it to GitHub is where I actually got stuck.

I created the repo on GitHub first, then initialized git locally, committed my files, and tried to push — only to get hit with:

! [rejected] main -> main (fetch first)

Turns out my GitHub repo wasn't actually empty — it already had its own README sitting there, and my local repo had a different one for the same file. Classic case of two histories that don't know about each other. The fix was:

git pull origin main --allow-unrelated-histories --no-rebase

which merged the two histories, dumped me into a merge conflict in README.md, and forced me to actually open the file and decide what content to keep between the <<<<<<< and >>>>>>> markers.Once I resolved it, added it, and committed the merge, the push finally went through clean.

It wasn't a big deal in hindsight, but in the moment it was the most stressful five minutes of the whole assignment — way more than any SQL query. If you're newer to git, that's a totally normal thing to run into the first time you create a repo with a README both on GitHub and locally. Now I know: pick one place to create the README, not both.

What I'd tell someone starting this assignment

Don't skip SET search_path.
Run your INSERT statements and immediately check them with SELECT COUNT(*).
Read every WHERE clause twice before running an UPDATE or DELETE.
If your GitHub repo and your local folder both end up with a README.md, expect a merge conflict.