Why we revisit SQL fundamentals at all
Most 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 we built this module as a deliberate, from-scratch pass through the mechanics for every engineer on the team — no ORM, no framework, just .sql files organized by topic, written and run against both PostgreSQL and MySQL side by side. The goal isn't to learn syntax an engineer already knows; it's to rebuild the muscle memory for reasoning about schemas, constraints, and query plans without a library doing the thinking for them.
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 on a client's production database.
Dual-dialect by design, not by accident
A lot of the value here comes from having engineers write the same schema twice — once for MySQL, once for PostgreSQL — and hit 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 an engineer is asked to review a schema a client wrote in a dialect our team 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 engineers 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 a 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 our engineers internalize 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 on a client's production system.
What this program demonstrates
| Approach | What it signals |
|---|---|
| Writing schemas twice (MySQL + Postgres) | Comfort working across engines when a client's stack isn't the one our team would have chosen |
| Constraint-first schema design | A preference for correctness enforced by the database, not just the app layer |
| Explicit transaction/rollback exercises | Understanding of what a transaction boundary actually buys you, not just the keyword |
| Real-world table names (orders, bookings, employers) | Practicing on shapes that resemble production data, not toy examples |
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 for our engineers to debug.