What a Deliberate Pass Through SQL Fundamentals Actually Looks Like

Why revisit SQL fundamentals at all

Most of my client work sits a few layers above raw SQL — an ORM here, a query builder there, a migration tool doing the heavy lifting. That's fine until something breaks in a way the abstraction can't explain: a query that's slow because of a missing composite index, a LEFT JOIN that silently duplicates rows because of a one-to-many relationship nobody modeled correctly, or a race condition that only a real transaction boundary would have prevented.

So I built this workspace as a deliberate, from-scratch pass through the mechanics — no ORM, no framework, just .sql files organized by topic, written and run against both PostgreSQL and MySQL side by side. The goal wasn't to learn syntax I already knew; it was to rebuild the muscle memory for reasoning about schemas, constraints, and query plans without a library doing the thinking for me.

Structure: topic-first, not tutorial-first

The repo is organized into numbered modules that mirror how a schema actually gets built up in a real project, not how a course table-of-contents lists topics:

04 - Data Definition: tables, data types, constraints, generated columns
05 - Data Manipulation & Querying: CRUD, filtering, subqueries
06 - Normalization & Relationships: splitting data, JOIN variants, UNION
07 - Primary & Foreign Keys: self-referencing FKs, many-to-many junction tables
08 - Grouping & Aggregates: GROUP BY, HAVING, window functions
09 - Built-in Functions & Operators: string/date/math functions, EXISTS/IN
10 - Transactions: START TRANSACTION, ROLLBACK, COMMIT — MySQL and Postgres

Each module builds real schemas — users, employers, conversations, customers, orders, bookings — rather than throwaway foo/bar tables, so the constraints and relationships have to hold up the way they would in production.

Dual-dialect by design, not by accident

A lot of the value here came from writing the same schema twice — once for MySQL, once for PostgreSQL — and hitting the actual API differences instead of reading about them:

-- MySQL: ENUM as a column type, AUTO_INCREMENT for identity
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(401) GENERATED ALWAYS AS (CONCAT(first_name,' ',last_name)),
    current_status ENUM('self-employed', 'employed', 'unemployed')
);

-- PostgreSQL: ENUM as a first-class type, SERIAL for identity
CREATE TYPE employment_status AS ENUM('self-employed', 'employed', 'unemployed');

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    yearly_salary INT CHECK (yearly_salary > 0),
    current_status employment_status
);

That distinction — Postgres treats enums as reusable types you CREATE once and reference everywhere, MySQL inlines them per-column — is exactly the kind of thing that's invisible when a migration tool generates the DDL for you, and exactly the kind of thing that matters when you're asked to review a schema someone else wrote in a dialect you didn't pick.

The parts worth calling out

Self-referencing and many-to-many relationships. Modeling a friends-list or org-chart style relationship inside a single table is a small problem with a lot of ways to get subtly wrong. The junction table below uses a CHECK constraint to enforce a canonical ordering, which kills duplicate (A, B) / (B, A) pairs at the schema level instead of relying on application code to catch it:

CREATE TABLE users_friends (
    user_id INT REFERENCES users ON DELETE CASCADE,
    friend_id INT REFERENCES users ON DELETE CASCADE,
    CHECK (user_id < friend_id),
    PRIMARY KEY(user_id, friend_id)
);

Transactions as an explicit exercise, not an afterthought. Module 10 walks through START TRANSACTION → insert → ROLLBACK/COMMIT in both dialects, deliberately including a failure case — inserting an order with no customer_id — to observe how each engine handles a constraint violation mid-transaction. That's the difference between knowing transactions exist and knowing what your rollback actually undoes.

Window functions for reporting-style queries. The aggregate module goes past GROUP BY/HAVING into SUM(...) OVER (PARTITION BY ...) and RANK() OVER (...), which is the pattern almost every "top N per category" or running-total dashboard query in client work eventually needs:

SELECT
    booking_date,
    amount_tipped,
    RANK() OVER (PARTITION BY booking_date ORDER BY amount_tipped DESC)
FROM bookings;

Constraint-driven data integrity over application-layer validation. Across the modules, the pattern is consistent: push what you can into CHECK, REFERENCES ... ON DELETE CASCADE, and generated columns, so invalid states are unrepresentable in the database rather than merely rejected by a form. It's a smaller list of things that can go wrong in production.

What this demonstrates

None of this is exotic. That's the point — this is a deliberate refresh of the fundamentals that everything else in a stack ultimately sits on top of, done thoroughly enough that when a client's slow query or inconsistent data turns out to be a schema problem rather than an application problem, it's a familiar kind of problem to debug.