Lessons
GROUP BY and HAVING in depth
Grouping on multiple columns, HAVING to filter groups, and the correct query execution order.
Prerequisite: Foundations track complete
What to look for:
- GROUP BY multiple columns
- HAVING with aggregate conditions — cannot use WHERE here
- The query execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
- Combining WHERE (pre-group filter) and HAVING (post-group filter) in the same query
Multiple joins and self-joins
Joining 3+ tables, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, and self-joins for hierarchy queries.
Prerequisite: Lesson 1 of Practitioner
What to look for:
- Multi-table joins: FROM employees e JOIN departments d ... JOIN projects p...
- Self-join: employees e JOIN employees m ON e.manager_id = m.id
- USING shorthand when column names match
- Non-equi join: ON e.salary BETWEEN s.min AND s.max
Subqueries and EXISTS
Scalar subqueries, list subqueries with IN, correlated subqueries, derived tables in FROM, and EXISTS.
Prerequisite: Lesson 2 of Practitioner
What to look for:
- Scalar subquery: single value returned, used in WHERE
- IN with subquery returning a list of values
- Correlated subquery: references outer table — runs per row
- EXISTS: often faster than IN for large datasets
CASE, COALESCE, and string/date functions
CASE expressions for conditional logic, COALESCE for NULL defaults, and key string and date functions.
Prerequisite: Lesson 3 of Practitioner
What to look for:
- CASE in SELECT, WHERE, and ORDER BY
- COALESCE returns first non-NULL
- String: UPPER, LOWER, TRIM, SUBSTRING, CONCAT, ||
- Date: EXTRACT(YEAR FROM date), DATE_TRUNC, AGE, INTERVAL arithmetic
DDL: CREATE TABLE, ALTER, and views
Creating tables with constraints (NOT NULL, UNIQUE, CHECK, FOREIGN KEY), ALTER TABLE, and CREATE VIEW.
Prerequisite: Lesson 4 of Practitioner
What to look for:
- NOT NULL, UNIQUE, DEFAULT, CHECK constraints
- REFERENCES + ON DELETE CASCADE for foreign keys
- ALTER TABLE: add column, change type, add constraint
- CREATE VIEW as a saved SELECT query
How to use this track: Each lesson links to the Codex SQL reference page. Use the reading level tabs. Use the "What to look for" bullets. Practice each query in a PostgreSQL session — try db-fiddle.com for a free browser SQL environment.
Track quiz
4 questions to check your understanding.
What is the correct order of execution for a query with GROUP BY and HAVING?
When is EXISTS preferred over IN?
What does COALESCE(a, b, c) return?
What does ON DELETE CASCADE on a FOREIGN KEY constraint do?