SQL Course

The foundations: reading data from a table and filtering it.

1. 1.1Introduction to SQLWhat SQL is, why it is used, and what a database looks like.~5 min
2. 1.2SELECT … FROMThe simplest query: reading rows from a table.~10 min
3. 1.3Filtering with WHERESelecting only the rows that satisfy a condition.~10 min
4. 1.4Sorting with ORDER BYPutting results in order, ascending or descending.~8 min

Limit, deduplicate, rename and count: the tools you use every day.

1. 2.1Limiting with LIMITReturning only the first N rows, for example to build leaderboards.~6 min
2. 2.2Unique values with DISTINCTRemoving duplicates from the result of a SELECT.~6 min
3. 2.3Renaming with ASGiving more readable names to columns and expressions in the result.~6 min
4. 2.4Counting and summarizing: COUNT, AVG, MAXAggregate functions: getting a single number from many rows.~8 min

Combining multiple tables and producing reports by group: the heart of every real query.

1. 3.1Joining two tables: INNER JOINCombining rows from different tables following their relationships.~12 min
2. 3.2Keeping everything on the left: LEFT JOINIncluding rows that have no match in the other table.~10 min
3. 3.3Grouping with GROUP BYComputing an aggregate per group, for example per department.~10 min
4. 3.4Filtering groups: HAVINGKeeping only the groups that satisfy a condition on the aggregate.~8 min

The tools that make a difference in real work: lists, ranges, NULLs and conditional logic.

1. 4.1Lists and ranges: IN and BETWEENCompact filters for "one of these" and "between A and B".~8 min
2. 4.2Handling NULLs: IS NULL and COALESCERecognizing and dealing with missing values without surprises.~10 min
3. 4.3Conditional logic: CASE WHENTransforming values into categories directly in the SELECT.~10 min
4. 4.4A query inside another: subqueriesUsing the result of a SELECT inside another SELECT.~12 min

Multi-table JOINs, self-joins, OUTER, CROSS and set operations: combining data like a pro.

1. 5.1JOINs on three or more tablesTraversing a chain of relationships: orders → customers → products, in a single query.~12 min
2. 5.2Self-JOIN: a table with itselfHierarchies and internal relationships: reading a table with two roles (child and parent).~10 min
3. 5.3OUTER JOIN: finding the "gaps"Using LEFT/RIGHT/FULL OUTER JOIN to find rows without a match.~10 min
4. 5.4UNION, INTERSECT, EXCEPT (and CROSS JOIN)Combining results as sets: union, intersection, difference and cartesian product.~12 min

Transform and manipulate data with PostgreSQL native functions: strings, dates, numbers, type conversions.

1. 6.1String functionsLOWER, UPPER, LENGTH, concatenation, SUBSTRING, TRIM, REPLACE, LIKE and ILIKE.~12 min
2. 6.2Date and timestamp functionsEXTRACT, DATE_TRUNC, intervals and date differences.~12 min
3. 6.3Numeric functionsROUND, CEIL, FLOOR, ABS, MOD and integer vs decimal division.~10 min
4. 6.4CAST and type conversionsConverting between types with `::type` and `CAST(... AS ...)`.~10 min

Multi-step queries (CTE), rankings and running totals (window functions), unbounded hierarchies (WITH RECURSIVE).

1. 7.1CTE: multi-step queries with WITHBreaking a complex query into named steps for readability and reuse.~12 min
2. 7.2Window functions: rankings and per-group calculationsROW_NUMBER, RANK, DENSE_RANK with OVER(PARTITION BY …) for in-group rankings.~14 min
3. 7.3LAG, LEAD and running totalsComparing each row with the previous/next one and computing cumulative sums.~12 min
4. 7.4Recursive CTEs: arbitrary-depth hierarchiesWITH RECURSIVE for traversing hierarchies like categories.~14 min

INSERT, UPDATE, DELETE, CREATE TABLE with constraints, transactions: closing the loop with writes.

1. 8.1Inserting data: INSERTSingle and multi-row INSERT, RETURNING clause, explicitly choosing columns.~10 min
2. 8.2Updating and deleting: UPDATE and DELETESelective changes with WHERE, and why a forgotten WHERE is a disaster.~10 min
3. 8.3Creating tables and constraints: DDLCREATE TABLE with PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, CHECK and DEFAULT.~12 min
4. 8.4Transactions: all or nothingBEGIN, COMMIT, ROLLBACK: making a group of operations atomic.~10 min

Creating and altering tables, advanced constraints, complex types and views.

1. 9.1Advanced data types and ENUMsNumeric types, TEXT vs VARCHAR, BOOLEAN, UUID and custom ENUM types.~10 min
2. 9.2Multiple and table-level constraintsComposite UNIQUE and PRIMARY KEY constraints, advanced CHECK and foreign keys (ON DELETE CASCADE).~12 min
3. 9.3Changing the structure: ALTER TABLEAdding, modifying and removing columns or constraints from existing tables.~10 min
4. 9.4Views and data abstractionCreating VIEWs to simplify complex queries and MATERIALIZED VIEWs for performance.~12 min

The art of making queries fast: indexes, execution plans and database optimization.

1. 10.1Indexes and B-TreesCreate indexes to dramatically speed up lookup queries on large tables.~15 min
2. 10.2EXPLAIN and ANALYZELearn to read the database execution plans to diagnose bottlenecks.~15 min
3. 10.3Composite and partial indexesUse indexes on multiple columns at once or only on a specific slice of the data.~12 min
4. 10.4The N+1 problemThe infamous ORM performance problem and the native SQL methods to overcome it in a single query.~12 min

Safely manage concurrent data modifications: ACID transactions, locks and isolation levels.

1. 11.1ACID transactions (BEGIN/COMMIT)Keep data consistent with BEGIN, COMMIT and ROLLBACK, grouping queries into a single block invisible to others.~15 min
2. 11.2Race conditionsEdge scenarios: what happens when two parallel database employees act on the same user cart.~10 min
3. 11.3Locking and SELECT FOR UPDATEActively lock rows during reads, forcing other mutating threads to wait their turn.~15 min
4. 11.4Isolation levelsPush to the limits by implementing Serializable, or drop to Read Committed for unsafe but fast performance.~12 min

Automate business logic inside the database: write reusable functions and react to events with triggers.

1. 12.1UDFs and base SQLCreate your own User Defined Functions to encapsulate complex calculations and reuse them like macros in every query.~15 min
2. 12.2Advanced logic with PL/pgSQLGive your functions superpowers: variables, if-else branches and loops in pure procedural style inside the database.~15 min
3. 12.3Introduction to triggersDefine special functions that can instantly inspect the OUTGOING (OLD) or INCOMING (NEW) record.~15 min
4. 12.4Practical triggers and auditAutomate safety: auto-update the "updated_at" date and magically archive deleted rows for compliance.~12 min

Advanced data analysis without collapsing rows: discover OVER(), PARTITION BY and analytical functions.

1. 13.1Introduction and the OVER clauseRun aggregate computations (like SUM or AVG) while preserving the original individual rows.~15 min
2. 13.2Grouping with PARTITION BYCreate logical groupings (windows) inside the data on which to apply functions, without using GROUP BY.~15 min
3. 13.3Rankings: ROW_NUMBER, RANK and DENSE_RANKAssign sequential row numbers and ranks to results, correctly handling ties.~15 min
4. 13.4Shifts: LAG and LEADAccess values from the previous or next row directly from the current row to compute deltas and variations.~12 min

Simplify huge queries with Common Table Expressions and navigate unbounded data trees with recursive CTEs.

1. 14.1Introduction to CTEs (WITH)Replace unreadable nested subqueries with a clean procedural format by declaring temporary "virtual tables".~12 min
2. 14.2Multiple and chained CTEsTame complexity by breaking processing into multiple sequential steps inside a single WITH statement.~15 min
3. 14.3Recursive CTEs: tree-shaped dataNavigate without limits manager hierarchies and parent-child categories by calling the CTE from itself.~18 min

Leverage relational power mixed with flexible document support via JSON and JSONB without using external databases.

1. 15.1JSON and JSONB integrationStore complex documents inside a column and query their fields with arrow operators.~15 min
2. 15.2JSON aggregation functionsGenerate JSON files or object arrays on the fly natively in SQL using json_build_object() and json_agg().~15 min