Relational Databases & SQL

Tables, queries and data modeling in relational databases.

Backend Engineer track

Junior

Write correct code, ship small changes safely, ask good questions.

Core: start here

Database Connection and Connection StringHow an app connects to a database: host, port, credentials and options.
Foreign KeyA column referencing another table's primary key.
JOINCombining rows from several tables.
ORMMapping database tables to objects in code.
Primary KeyA column that uniquely identifies each row.
SELECT, WHERE, ORDER BYReading, filtering and sorting rows.
SQLThe language for querying relational databases.

21 more junior concepts

Audit Columns (created_at, updated_at)Recording when, and by whom, rows changed.
CASE ExpressionConditional logic inside a SQL query.
COALESCE and NULLIFHandling NULLs inside SQL expressions.
ConstraintsDatabase rules like NOT NULL, UNIQUE and CHECK.
CRUDCreate, Read, Update, Delete: the four basic data operations.
DatabaseOrganized, persistent storage for data.
Database Client / GUITools like psql, DBeaver or TablePlus for exploring databases.
Database SchemaThe structure of tables, columns and relationships.
DDL, DML, DCL and DQLSQL's categories: defining structure, changing data, granting access, querying.
GROUP BY and AggregatesSummarizing rows with COUNT, SUM and AVG.
HAVINGFiltering groups after aggregation.
INNER, LEFT, RIGHT and FULL JOINWhich rows each kind of join keeps.
Junction TableA table linking two others in a many-to-many relationship.
NULL in SQLThree-valued logic, and why NULL = NULL isn't true.
One-to-Many and Many-to-ManyThe basic kinds of relationship and how to model them.
PostgreSQL, MySQL and SQLiteThe common relational databases and where each one fits.
Relational DatabaseData stored in tables with rows, columns and relationships.
SQL Data TypesChoosing integer, numeric, text, timestamp and other column types.
SQL String, Date and Math FunctionsBuilt-in functions for transforming values in queries.
Table, Row, ColumnThe basic structure of relational data.
UNION, INTERSECT, EXCEPTCombining the results of several queries.

Mid-level

Own a feature end to end without hand-holding.

Core: start here

Data ModelingDesigning how your data is structured and related.
EXPLAINShowing how the database plans to run a query.
NormalizationOrganizing tables to reduce duplication: 1NF, 2NF, 3NF.
Offset vs Cursor PaginationSimple page numbers vs stable, scalable cursors.

27 more mid-level concepts

Active RecordObjects that know how to save themselves to the database.
Common Table Expression (WITH)Named subqueries that make complex SQL readable.
Correlated SubqueryA subquery that runs once per row of the outer query.
Cross JoinEvery row of one table paired with every row of another.
Database Connection PoolReusing database connections, with tools like PgBouncer at scale.
Database CursorFetching a large result set in batches instead of all at once.
Dynamic SQLBuilding SQL strings at runtime, and doing it without injection.
Enums in the DatabaseStoring fixed sets of values safely.
ER DiagramA diagram of entities and their relationships.
Full-Text Search in SQLSearching words in text columns with built-in indexes.
JSON ColumnsStoring semi-structured data inside a relational database.
Natural vs Surrogate KeyUsing real-world data as the key vs a generated ID.
Prepared StatementA query parsed once and executed many times with different values.
Recursive CTEQuerying hierarchies and graphs, like org charts or category trees.
Relational ModelThe theory behind SQL: relations, tuples, attributes and keys.
Repository PatternA collection-like interface over data storage.
Self JoinJoining a table to itself, e.g. employees and their managers.
SequenceA database object that generates increasing numbers, used for auto-increment IDs.
Soft DeleteMarking rows as deleted instead of removing them.
Stored ProcedureLogic that runs inside the database.
SubqueryA query nested inside another query.
Timestamp With vs Without Time ZoneStoring moments in time correctly in the database.
TriggerCode the database runs automatically on insert, update or delete.
UpsertInsert, or update if the row exists, in one statement.
UUID vs Auto-Increment IDsSequential integers vs globally unique IDs, and their trade-offs.
ViewA saved query that acts like a table.
Window FunctionsCalculations across related rows, like running totals and rankings.

Senior

Own a system, its failure modes, and its trade-offs.

DenormalizationDeliberately duplicating data for read performance.
LATERAL JoinA join where the right side can reference columns from the left.
Materialized ViewA view whose results are stored and refreshed.
Pivot / UnpivotTurning rows into columns and back.
Query PlanThe database's chosen strategy of scans, joins and sorts.
Relational AlgebraThe operations (select, project, join) that SQL queries compile to.
Sequential Scan vs Index ScanReading the whole table vs jumping in through an index.

Staff

Shape how many teams build, across systems.

Nothing here yet.

Principal

Set technical direction for the organization.

Nothing here yet.

Data Analyst track

Junior

Write correct SQL, build trusted dashboards, ask good questions.

Core: start here

Common Table Expression (WITH)Named subqueries that make complex SQL readable.
GROUP BY and AggregatesSummarizing rows with COUNT, SUM and AVG.
INNER, LEFT, RIGHT and FULL JOINWhich rows each kind of join keeps.
SELECT, WHERE, ORDER BYReading, filtering and sorting rows.
SubqueryA query nested inside another query.

18 more junior concepts

CASE ExpressionConditional logic inside a SQL query.
COALESCE and NULLIFHandling NULLs inside SQL expressions.
DatabaseOrganized, persistent storage for data.
Database Client / GUITools like psql, DBeaver or TablePlus for exploring databases.
Database SchemaThe structure of tables, columns and relationships.
DDL, DML, DCL and DQLSQL's categories: defining structure, changing data, granting access, querying.
Foreign KeyA column referencing another table's primary key.
HAVINGFiltering groups after aggregation.
JOINCombining rows from several tables.
NULL in SQLThree-valued logic, and why NULL = NULL isn't true.
PostgreSQL, MySQL and SQLiteThe common relational databases and where each one fits.
Primary KeyA column that uniquely identifies each row.
SQLThe language for querying relational databases.
SQL Data TypesChoosing integer, numeric, text, timestamp and other column types.
SQL String, Date and Math FunctionsBuilt-in functions for transforming values in queries.
Table, Row, ColumnThe basic structure of relational data.
UNION, INTERSECT, EXCEPTCombining the results of several queries.
ViewA saved query that acts like a table.

Mid-level

Own an analysis end to end, from vague question to recommendation.

Core: start here

Window FunctionsCalculations across related rows, like running totals and rankings.

7 more mid-level concepts

Audit Columns (created_at, updated_at)Recording when, and by whom, rows changed.
DenormalizationDeliberately duplicating data for read performance.
JSON ColumnsStoring semi-structured data inside a relational database.
Pivot / UnpivotTurning rows into columns and back.
Self JoinJoining a table to itself, e.g. employees and their managers.
Soft DeleteMarking rows as deleted instead of removing them.
Timestamp With vs Without Time ZoneStoring moments in time correctly in the database.

Senior

Own experimentation and metrics design; call out bad numbers.

Correlated SubqueryA subquery that runs once per row of the outer query.
EXPLAINShowing how the database plans to run a query.
Materialized ViewA view whose results are stored and refreshed.

Staff

Shape how the organization measures and decides.

Nothing here yet.

Principal

Set measurement strategy across the company.

Nothing here yet.

Data Engineer track

Junior

Build and fix pipelines from clear specs; write correct SQL.

Core: start here

CASE ExpressionConditional logic inside a SQL query.
Common Table Expression (WITH)Named subqueries that make complex SQL readable.
Foreign KeyA column referencing another table's primary key.
GROUP BY and AggregatesSummarizing rows with COUNT, SUM and AVG.
INNER, LEFT, RIGHT and FULL JOINWhich rows each kind of join keeps.
JOINCombining rows from several tables.
NULL in SQLThree-valued logic, and why NULL = NULL isn't true.
Primary KeyA column that uniquely identifies each row.
SELECT, WHERE, ORDER BYReading, filtering and sorting rows.
SQLThe language for querying relational databases.
SubqueryA query nested inside another query.
UpsertInsert, or update if the row exists, in one statement.
Window FunctionsCalculations across related rows, like running totals and rankings.

20 more junior concepts

Audit Columns (created_at, updated_at)Recording when, and by whom, rows changed.
COALESCE and NULLIFHandling NULLs inside SQL expressions.
ConstraintsDatabase rules like NOT NULL, UNIQUE and CHECK.
CRUDCreate, Read, Update, Delete: the four basic data operations.
DatabaseOrganized, persistent storage for data.
Database Client / GUITools like psql, DBeaver or TablePlus for exploring databases.
Database Connection and Connection StringHow an app connects to a database: host, port, credentials and options.
Database SchemaThe structure of tables, columns and relationships.
DDL, DML, DCL and DQLSQL's categories: defining structure, changing data, granting access, querying.
HAVINGFiltering groups after aggregation.
Junction TableA table linking two others in a many-to-many relationship.
One-to-Many and Many-to-ManyThe basic kinds of relationship and how to model them.
PostgreSQL, MySQL and SQLiteThe common relational databases and where each one fits.
Relational DatabaseData stored in tables with rows, columns and relationships.
Self JoinJoining a table to itself, e.g. employees and their managers.
SQL Data TypesChoosing integer, numeric, text, timestamp and other column types.
SQL String, Date and Math FunctionsBuilt-in functions for transforming values in queries.
Table, Row, ColumnThe basic structure of relational data.
UNION, INTERSECT, EXCEPTCombining the results of several queries.
ViewA saved query that acts like a table.

Mid-level

Own pipelines and models end to end, including their quality.

Core: start here

DenormalizationDeliberately duplicating data for read performance.
Materialized ViewA view whose results are stored and refreshed.

22 more mid-level concepts

Correlated SubqueryA subquery that runs once per row of the outer query.
Cross JoinEvery row of one table paired with every row of another.
Data ModelingDesigning how your data is structured and related.
Database CursorFetching a large result set in batches instead of all at once.
Dynamic SQLBuilding SQL strings at runtime, and doing it without injection.
Enums in the DatabaseStoring fixed sets of values safely.
ER DiagramA diagram of entities and their relationships.
EXPLAINShowing how the database plans to run a query.
Full-Text Search in SQLSearching words in text columns with built-in indexes.
JSON ColumnsStoring semi-structured data inside a relational database.
Natural vs Surrogate KeyUsing real-world data as the key vs a generated ID.
NormalizationOrganizing tables to reduce duplication: 1NF, 2NF, 3NF.
Offset vs Cursor PaginationSimple page numbers vs stable, scalable cursors.
Prepared StatementA query parsed once and executed many times with different values.
Recursive CTEQuerying hierarchies and graphs, like org charts or category trees.
Relational ModelThe theory behind SQL: relations, tuples, attributes and keys.
SequenceA database object that generates increasing numbers, used for auto-increment IDs.
Soft DeleteMarking rows as deleted instead of removing them.
Stored ProcedureLogic that runs inside the database.
Timestamp With vs Without Time ZoneStoring moments in time correctly in the database.
TriggerCode the database runs automatically on insert, update or delete.
UUID vs Auto-Increment IDsSequential integers vs globally unique IDs, and their trade-offs.

Senior

Design the platform's storage, processing and modeling choices.

LATERAL JoinA join where the right side can reference columns from the left.
Pivot / UnpivotTurning rows into columns and back.
Query PlanThe database's chosen strategy of scans, joins and sorts.
Relational AlgebraThe operations (select, project, join) that SQL queries compile to.
Sequential Scan vs Index ScanReading the whole table vs jumping in through an index.

Staff

Shape how the whole organization produces and uses data.

Nothing here yet.

Principal

Set data strategy and architecture across the company.

Nothing here yet.

Frontend Engineer track

Junior

Build UI that works, ship small changes safely, ask good questions.

CRUDCreate, Read, Update, Delete: the four basic data operations.
DatabaseOrganized, persistent storage for data.
Database Client / GUITools like psql, DBeaver or TablePlus for exploring databases.
Database SchemaThe structure of tables, columns and relationships.
Foreign KeyA column referencing another table's primary key.
JOINCombining rows from several tables.
NULL in SQLThree-valued logic, and why NULL = NULL isn't true.
One-to-Many and Many-to-ManyThe basic kinds of relationship and how to model them.
PostgreSQL, MySQL and SQLiteThe common relational databases and where each one fits.
Primary KeyA column that uniquely identifies each row.
Relational DatabaseData stored in tables with rows, columns and relationships.
SELECT, WHERE, ORDER BYReading, filtering and sorting rows.
SQLThe language for querying relational databases.
Table, Row, ColumnThe basic structure of relational data.

Mid-level

Own a feature end to end without hand-holding.

Data ModelingDesigning how your data is structured and related.
GROUP BY and AggregatesSummarizing rows with COUNT, SUM and AVG.
HAVINGFiltering groups after aggregation.
INNER, LEFT, RIGHT and FULL JOINWhich rows each kind of join keeps.
Offset vs Cursor PaginationSimple page numbers vs stable, scalable cursors.
SubqueryA query nested inside another query.
UUID vs Auto-Increment IDsSequential integers vs globally unique IDs, and their trade-offs.

Senior

Own an app's architecture, performance, and failure modes.

Nothing here yet.

Staff

Shape how many teams build, across apps.

Nothing here yet.

Principal

Set technical direction for the organization.

Nothing here yet.