02-sql-fundamentals

02 — SQL Fundamentals

Exercises for the SQL Fundamentals course.

This is where SQL actually starts to feel like SQL. You'll insert and query data, filter and sort it, group and aggregate it, apply math functions, modify rows, build relationships between tables, and master joins. By the end you'll be able to read a question in English and translate it straight into a working query.

Sections

Section	What you'll practice
1 — Insert and Query Data	INSERT INTO, SELECT, column aliases, ORDER BY, DISTINCT
2 — Filtering Data	WHERE, comparison and logical operators, IN, BETWEEN, LIKE / ILIKE, IS NULL, LIMIT / OFFSET / FETCH
3 — Grouping & Aggregate Functions	GROUP BY, COUNT, SUM, MIN, MAX, AVG, HAVING
4 — Mathematical Operators & Functions	Arithmetic operators, ROUND, CEIL, FLOOR, ABS, POWER, derived columns
5 — Modifying Data	UPDATE, DELETE, safe modification patterns, CRUD in practice
6 — Relationships	PRIMARY KEY, FOREIGN KEY, REFERENCES, ON DELETE, 1-to-1 / 1-to-many modeling
7 — Joins	INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, self-joins, USING
8 — Order of SQL	The logical order of execution (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT) applied to real problems

The upstream course's Important Stuff section (SQL commands, casing, semicolons, the query buffer, naming conventions) is tooling and conventions — nothing meaningful to practice with SQL, so it isn't included here. The Next Steps section is a wrap-up.

Prerequisites

• You've completed ../SETUP.md and have a sql_exercise database
• You're comfortable with the material in ../01-up-and-running-with-sql/ — specifically CREATE TABLE, data types, and NOT NULL
• PostgreSQL 13+ (any modern version works)

How each section is structured

Every section has its own PostgreSQL schema (like a namespace) so nothing you do in one section can clobber another. Each section folder contains:

• schema.sql — drops and recreates the schema and tables. You load this.
• seed.sql — inserts realistic sample data. You load this after the schema.
• exercises.md — the exercises themselves, with hints and verifications.

And the matching solutions live in ../solutions/02-sql-fundamentals/<section>/solutions.sql.

Suggested order

The sections build on each other — do them in order.

1. 1 gets you inserting and querying.
2. 2 layers WHERE filters on top of SELECT.
3. 3 introduces grouping and aggregates.
4. 4 adds math and derived columns.
5. 5 teaches you to safely modify (and delete) rows.
6. 6 shifts from single-table to multi-table modeling.
7. 7 is where joins click.
8. 8 ties everything together by forcing you to think about SQL's logical order of execution.

If you only have time for the highlights, do 2, 3, and 7 — those are the three sections you'll lean on every day as a working developer.

Resetting a section

Every schema.sql starts with DROP SCHEMA IF EXISTS ... CASCADE;. If you break something, just re-run schema.sql and then seed.sql and you're back to a clean state.