Learn SQL

Relational databases store data as tables of typed columns and rows, linked by matching values rather than pointers.

Read columns from a table with SELECT ... FROM, choosing exactly which columns you need.

Compute new columns with expressions, rename them with AS, and remove duplicate rows with DISTINCT.

Keep only the rows that satisfy a condition, combining comparisons with AND, OR and NOT.

Match against lists, ranges and text patterns with IN, BETWEEN and LIKE.

NULL means unknown; comparisons with it are neither true nor false, so you test it with IS NULL.

Sort results with ORDER BY, and return only the first rows with LIMIT / FETCH FIRST.

Collapse many rows into one value with COUNT, SUM, AVG, MIN and MAX, and know how they treat NULL.

Aggregate per group with GROUP BY, and filter whole groups with HAVING.

Compute rankings, running totals and row-to-row comparisons with OVER, without collapsing rows.

Define tables with CREATE TABLE, and change or remove them with ALTER TABLE and DROP TABLE.

Pick the right type for numbers, money, text, booleans, dates and ids, and avoid the classic mistakes.

Add rows with INSERT, change them with UPDATE, remove them with DELETE, and see the affected rows with RETURNING.

Insert a row or update it if it already exists, atomically, with INSERT ... ON CONFLICT or MERGE.

Identify every row with a primary key, usually a database-generated identity column or UUID.

Use foreign keys so the database guarantees that every reference points at a row that exists.

Encode business rules in the schema with NOT NULL, UNIQUE, CHECK and DEFAULT constraints.

Design tables so each fact is stored once, and know when deliberate denormalization is worth it.

Combine rows from several tables by matching key values with JOIN ... ON.

Keep rows without a match using LEFT, RIGHT and FULL joins, and find unmatched rows.

Nest one query inside another as a value, a list, a derived table, or an EXISTS test.

Save a query under a name with CREATE VIEW, or store its result with a materialized view.

Break a complex query into named steps with WITH, and know when PostgreSQL inlines or materialises them.

Walk trees and graphs such as category hierarchies with WITH RECURSIVE.

Combine the results of whole queries by stacking, intersecting or subtracting their rows.

Write conditional logic inside queries with CASE, and handle NULLs with COALESCE and NULLIF.

Clean, search, split and assemble text with SQL's string functions and pattern matching.

Do date arithmetic, truncate and extract parts of timestamps, and handle time zones correctly.

Store, query and index semi-structured data in PostgreSQL jsonb columns, and know when not to.

Speed up lookups, joins and sorts with B-tree indexes, and understand what they cost.

Use EXPLAIN and EXPLAIN ANALYZE to see how PostgreSQL executes a query and where the time goes.

Use partial, expression, covering and multicolumn indexes, and pick GIN, GiST or BRIN when B-tree is not enough.

How PostgreSQL's cost-based planner estimates row counts from statistics and chooses join methods and order.

Split very large tables into partitions by range, list or hash, and use pruning and detach for speed and data lifecycle.

A method for finding and fixing slow database workloads: measure, read plans, fix queries, then tune the server.

Group statements with BEGIN, COMMIT and ROLLBACK so they succeed or fail as a unit.

How PostgreSQL keeps multiple row versions so readers never block writers, and why VACUUM must clean them up.

What READ COMMITTED, REPEATABLE READ and SERIALIZABLE guarantee in PostgreSQL, and which anomalies each allows.

Row and table locks in PostgreSQL, explicit locking with FOR UPDATE, queue patterns, and how deadlocks arise.

How the write-ahead log makes commits durable, and how it powers crash recovery, point-in-time recovery and replicas.