Command Palette

Search for a command to run...

Hectal
Phase 4Beginner5 of 18 in Database Design

SQL from Fundamentals to Advanced

DDL and DML, logical query order, every join type, subqueries with EXISTS and the NOT IN NULL trap, CTEs and recursive queries, window functions, set operations, data types and three-valued NULL logic.

SQL is declarative: you describe the result and the planner decides how to compute it. Fluency means knowing what each clause does, the order it's applied in, and where NULLs and duplicates quietly change answers.

All examples run on PostgreSQL 17 and use a small shop schema: customer, orders, order_item, product.

0/6 · 0%
6 topics ~48 min 7 code blocks & diagrams
Start with the first topic
1
4.1

Core SQL: DDL, DML and Logical Query Order

DDL (CREATE, ALTER, DROP, TRUNCATE) defines structure; DML (INSERT, UPDATE, DELETE, SELECT) works with rows. A SELECT is evaluated logically as FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT, which explains why aliases can't be used in WHERE and why HAVING exists.

8 min 1 diagram 1 code practice

2
4.2

Joins: Inner, Outer, Cross, Self, Semi and Anti

INNER JOIN keeps matching pairs; LEFT/RIGHT/FULL OUTER JOINs keep unmatched rows from one or both sides with NULLs; CROSS JOIN makes every combination; a SELF JOIN relates a table to itself. Semi-joins (EXISTS) and anti-joins (NOT EXISTS) answer "has any" and "has none" without duplicating rows.

8 min 1 code practice

3
4.3

Subqueries: Scalar, Correlated, EXISTS, IN and the NOT IN Trap

Scalar subqueries return one value, correlated subqueries reference the outer row, and EXISTS/IN test membership. NOT IN with a subquery that returns any NULL yields no rows at all, so prefer NOT EXISTS for anti-joins. Modern planners usually turn IN and EXISTS into the same semi-join.

8 min 1 code practice

4
4.4

CTEs, Recursive Queries and Set Operations

Common table expressions (WITH) name intermediate results for readability; since PostgreSQL 12 they're inlined unless referenced twice or marked MATERIALIZED. Recursive CTEs walk hierarchies and graphs. UNION, UNION ALL, INTERSECT and EXCEPT combine result sets, and UNION ALL is the one to reach for unless you need de-duplication.

8 min 1 code practice

5
4.5

Window Functions: Ranking, LAG/LEAD, Running Totals

Window functions compute a value for each row from a set of related rows (the window) without collapsing them like GROUP BY. ROW_NUMBER, RANK and DENSE_RANK rank rows; LAG/LEAD read neighbours; SUM ... OVER (ORDER BY ...) gives running totals and moving averages; conditional aggregation with FILTER pivots data.

8 min 1 code practice

6
4.6

Data Types and NULL

Pick types that make invalid data impossible and storage compact: bigint for IDs, numeric for money (never float), timestamptz for instants, text with CHECKs instead of arbitrary varchar(n), jsonb for flexible documents. NULL means unknown and follows three-valued logic, which changes comparisons, joins, aggregates, uniqueness and indexing.

8 min 1 code practice