SQL Tutorial with Examples.
SQL tutorial from fundamentals to advanced: SELECT, JOINs, window functions, CTEs and query optimisation, every query shown with its result.
Beginner
Start here — no prior experience needed
Introduction to SQL
Learn what SQL is and how relational databases work by running real PostgreSQL queries: tables, keys, a declarative SELECT, and your first JOIN.
Setting Up PostgreSQL
Run PostgreSQL 18 in Docker or install it natively, connect with psql, create a database, load a seed file, and read the connection string apps use.
SQL Data Types
Choose PostgreSQL data types from real output: NUMERIC vs float, integer overflow, VARCHAR limits, TIMESTAMPTZ time zones, JSONB, arrays, casts.
DDL: Creating and Changing Tables
Learn SQL DDL on real PostgreSQL output: CREATE TABLE constraints, ALTER TABLE pitfalls, DROP with dependencies, TRUNCATE, schemas, generated columns.
INSERT, UPDATE, and DELETE
Change data safely in PostgreSQL with real output: multi-row INSERT, RETURNING, UPDATE ... FROM, DELETE, ON CONFLICT upserts, and ROLLBACK.
SELECT Queries
Master SQL SELECT on real PostgreSQL output: columns, aliases, DISTINCT, WHERE, the NULL comparison trap, ORDER BY, LIMIT/OFFSET, and computed columns.
Filtering Data
Filter SQL rows with WHERE on real PostgreSQL output: comparisons, AND/OR precedence, BETWEEN date traps, IN vs NOT IN with NULL, LIKE, and COALESCE.
SQL JOINs
Learn SQL JOINs on real PostgreSQL output: INNER, LEFT, RIGHT, FULL, CROSS and self-joins, anti-joins, and the WHERE that breaks a LEFT JOIN.
Intermediate
For developers with core concepts down
Aggregations and GROUP BY
Learn SQL aggregation on real PostgreSQL output: COUNT, SUM, AVG and NULL, GROUP BY, HAVING vs WHERE, FILTER, ROLLUP subtotals, and the join fan-out trap.
Subqueries
Learn SQL subqueries on real PostgreSQL output: scalar subqueries, IN, EXISTS and NOT EXISTS, derived tables, correlated queries, LATERAL joins.
Common Table Expressions (CTEs)
Learn SQL CTEs on real PostgreSQL output: WITH clauses, chained CTEs, MATERIALIZED plans, recursive org-chart queries, and stopping a cycle with CYCLE.
Window Functions
Learn SQL window functions on real PostgreSQL output: OVER, PARTITION BY, ROW_NUMBER vs RANK, LAG/LEAD, running totals, and the LAST_VALUE frame trap.
Indexes
Measure PostgreSQL indexes with real EXPLAIN ANALYZE: B-tree vs seq scans, expression, partial, covering and GIN indexes, column order, write cost.
Transactions
See PostgreSQL transactions on real output: BEGIN/COMMIT, aborted transactions, autocommit, savepoints, READ COMMITTED vs REPEATABLE READ, lock timeouts.
Views and Materialized Views
Use PostgreSQL views with real output: updatable views, WITH CHECK OPTION, stale materialized views, REFRESH CONCURRENTLY, and view privileges.
JSON in SQL
Query JSONB in PostgreSQL with real output: -> vs ->>, the text comparison trap, paths, jsonb_set, containment with @>, and GIN indexes.
Schema Design
Design relational schemas with real PostgreSQL output: an update anomaly and its fix, junction tables, soft deletes, and UUID vs BIGINT keys.
SQL Interview Questions
20 classic SQL interview questions solved on real PostgreSQL output: Nth salary, NOT IN with NULL, top-N per group, gaps and islands, retention, and more.
UNION, INTERSECT, and EXCEPT: Combining Query Results
SQL set operations on real PostgreSQL output: UNION vs UNION ALL, INTERSECT, EXCEPT and EXCEPT ALL, NULL matching, and why NOT IN returns nothing.
MERGE: Syncing One Table Into Another
SQL MERGE on real PostgreSQL 17 output: insert, update and delete in one statement, merge_action(), NOT MATCHED BY SOURCE, and the race ON CONFLICT avoids.
One Script, Four Engines: Where SQL Dialects Disagree
One SQL script run on PostgreSQL, MySQL, SQL Server and SQLite: division, case, NULL order, ||, LIMIT vs TOP vs FETCH FIRST, and GROUP BY rules.
Advanced
Production-grade patterns for experienced engineers
Functions, Procedures, and Triggers
Write PostgreSQL functions, procedures and triggers with real output: PL/pgSQL, RAISE and exceptions, RETURN QUERY, CALL, and an audit trigger.
Query Optimization
Read real PostgreSQL EXPLAIN ANALYZE plans, compare scan and join types, spot index-killing queries, and find slow ones with pg_stat_statements.
Database Security
Secure PostgreSQL with real output: roles, GRANT and REVOKE, column privileges, the locked public schema, row-level security, and SQL injection.
Replication and High Availability
Build a real PostgreSQL primary, pg_basebackup standby and logical subscriber in Docker, then monitor lag, slots, PgBouncer pooling and failover.
Also part of SQL
Taught in other topics. Each link opens the lesson in its home topic.
ACID vs BASE: the Lost Update Neither Engine Prevents by Default
A naive read-then-write race loses an update in both PostgreSQL and Redis when neither uses locking — the real difference is that Postgres ships a built-in fix (SELECT ... FOR UPDATE) and a raw key-value GET/SET pair has no equivalent.
A Real Deadlock, and Why Lock Order Fixed It
One error inside a transaction poisons every statement after it until ROLLBACK — and two transactions locking the same two rows in opposite order produces a real 'deadlock detected' error, not a hang.
Data Modeling Basics: Star Schemas and Denormalization
Load the same order data into a normalized schema and a star schema, then run the same query to see why analytics tables trade storage for join-free reads.
Incremental Loads and Change Data Capture
Four ways to extract only what changed, why deletes are the hard part, and building SCD type 2 history from a CDC stream that arrives out of order.
Scaling Patterns: Real Sharding vs a Table That Only Looks Sharded
MongoDB's sharding puts data on genuinely separate machines; MySQL's PARTITION BY divides a table by the same kind of range key but keeps every partition on one server. Both engines' own distribution-reporting tools also turn out to report the wrong numbers.