Learn SQL & PostgreSQL.
Work through joins, transactions, query plans, and relational correctness.
SQL & PostgreSQL essentials
Start with the essentials or pick the concept you need.
01SQL & joinsExplore concepts
- WHERE vs HAVING: what is the difference?2 min
- What is the difference between the join types?2 min
- Why does my join return duplicate rows?2 min
- Why does a LEFT JOIN behave like an INNER JOIN?2 min
- How should I compare NULL values?2 min
- Subquery vs EXISTS vs CTE?2 min
- When should I use a recursive CTE or set operation?2 min
02Window functionsExplore concepts
03Data modelingExplore concepts
04Transactions & lockingExplore concepts
05Indexes & optimizationExplore concepts
What you will study in SQL & PostgreSQL
Focused lessons organized by concept. Start with the explanation, trace the examples, and check your recall before moving on.
SQL & joins
WHERE filters individual rows before groups are built, while HAVING filters the groups produced by aggregation. They exist at different logical stages of a query. Use WHERE for conditions on source rows and HAVING for conditions such as “the group's total is greater than 1000.”
Window functions
GROUP BY collapses source rows into one row per group, while window functions calculate across related rows without removing the individual rows. A window is defined by partitions, ordering and sometimes a frame. This makes windows useful when a report needs both detail and comparisons such as department rank.
Data modeling
Keys identify records and relationships, while constraints tell the database which stored states are allowed. A primary key identifies each row, a foreign key validates a relationship, and other constraints enforce required or unique values and conditions. Database enforcement protects data even when different application paths write concurrently.
Transactions & locking
ACID describes four properties used to reason about reliable transactions: atomicity, consistency, isolation and durability. Together they address partial changes, preserved rules, concurrent work and persistence after commit. They do not automatically supply correct business rules or the strongest isolation level.
Indexes & optimization
An index is a separately maintained structure that makes suitable lookups, joins or ordering operations cheaper. B-tree is a common general-purpose choice, while composite, partial and unique indexes describe field arrangement, row selection and data rules. An index helps only when the planner estimates its use is worthwhile for the query and data.
PostgreSQL & ORMs
JSON stores JSON input text, while JSONB stores a processed representation that supports efficient operations and indexing. A view stores a query definition, whereas a materialized view stores the query's result for later reads. These are separate choices about data representation and precomputed results.