SQL guides you can run.
Short, practical explanations of the SQL you'll use every week. Every example runs on a real PostgreSQL or SQLite database right on the page, so you can change it and see what happens.
Basics
- Basics
The SQL SELECT statement
How SELECT works in SQL: choosing columns, renaming them, calculating values and removing duplicates, with runnable PostgreSQL and SQLite examples.
4 runnable examples
- Basics
SQL WHERE: filtering rows
Filter rows with WHERE: comparisons, AND/OR precedence, IN, BETWEEN, LIKE patterns and NULL handling.
5 runnable examples
- Basics
NULL in SQL
Why NULL = NULL isn't true, how NULL behaves in WHERE, COUNT, joins and text, and how COALESCE and NULLIF fix it.
4 runnable examples
- Basics
PostgreSQL vs SQLite: SQL differences
Where PostgreSQL and SQLite SQL differ: dates, text, types, booleans and GROUP BY strictness.
4 runnable examples
- Basics
SQL ORDER BY and LIMIT
Sort query results with ORDER BY (ASC, DESC, several columns, NULLS LAST), keep the top rows with LIMIT, and page with OFFSET.
5 runnable examples
- Basics
SQL CASE WHEN
Use CASE WHEN for labels, buckets, conditional counts and custom sort orders, plus the FILTER clause.
4 runnable examples
- Basics
SQL string functions
Clean and reshape text in SQL: UPPER, LOWER, LENGTH, SUBSTR, REPLACE, TRIM, concatenation, splitting and case-insensitive search, in PostgreSQL and SQLite.
5 runnable examples
- Basics
SQL date functions
Filter by date, extract years and months, group by month, add days and measure gaps, with PostgreSQL and SQLite versions of every example.
5 runnable examples
Combining data
- Combining data
SQL JOINs explained
INNER JOIN vs LEFT JOIN vs FULL OUTER JOIN, how ON works, finding rows with no match, and joining three or more tables, with runnable SQL.
4 runnable examples
- Combining data
SQL CTEs: the WITH clause
Common table expressions make complex SQL readable: WITH syntax, chaining steps, CTE vs subquery, and when to use them.
2 runnable examples
- Combining data
SQL subqueries
Use one query's result inside another: scalar subqueries, IN, EXISTS and NOT EXISTS, derived tables and correlated subqueries.
5 runnable examples
- Combining data
SQL UNION, INTERSECT and EXCEPT
Combine query results with UNION and UNION ALL, find overlaps with INTERSECT and differences with EXCEPT.
4 runnable examples
- Combining data
SQL self joins and CROSS JOIN
Join a table to itself for managers, hierarchies and pairs, and use CROSS JOIN to build every combination.
3 runnable examples
- Combining data
SQL recursive CTEs
WITH RECURSIVE step by step: number series, date calendars, org charts up and down, and avoiding infinite loops, in PostgreSQL and SQLite.
4 runnable examples
Analysis
- Analysis
SQL GROUP BY and HAVING
Summarise data with GROUP BY: COUNT, SUM, AVG per group, HAVING vs WHERE, and the 'must appear in GROUP BY' error, with runnable examples.
4 runnable examples
- Analysis
SQL window functions
Learn window functions with OVER and PARTITION BY: ROW_NUMBER vs RANK, top-N per group, running totals and LAG, in runnable PostgreSQL and SQLite.
4 runnable examples
- Analysis
Cleaning data with SQL
Clean messy imports in SQL: empty strings vs NULL, defaults, trimming and normalising text, converting text to numbers, and removing duplicates.
4 runnable examples
- Analysis
Advanced window functions
Go beyond ROW_NUMBER: window frames and moving averages, share of total, FIRST_VALUE, NTILE, PERCENT_RANK and subtotals with ROLLUP (or UNION ALL in SQLite).
5 runnable examples
- Analysis
Product analytics in SQL
Write the SQL product teams use every week: daily active users, month-over-month growth, rolling averages, funnels, conversion and cohort retention.
4 runnable examples
Building
- Building
SQL INSERT, UPDATE and DELETE
Change data safely: INSERT single and multiple rows, UPDATE and DELETE with WHERE, RETURNING, and upserts with ON CONFLICT.
4 runnable examples
- Building
SQL CREATE TABLE, constraints and views
Design tables in SQL: column types, PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, foreign keys, ALTER TABLE and views, with runnable PostgreSQL and SQLite examples.
5 runnable examples