SQL
All examples in this section use PostgreSQL 18 syntax. Other databases (MySQL, SQL Server, SQLite) differ in data types, upserts, identity columns and the behavior of isolation levels; the pages point out the differences that come up in interviews.
SQL Commands
DML, DDL, DCL and TCL, SELECT and the logical order of clauses, JOIN types, GROUP BY and HAVING, subqueries and CTEs, upsert and RETURNING
Data Types in SQL
PostgreSQL data types, integers, numeric vs floating point, text, timestamp vs timestamptz, boolean, uuid, json vs jsonb, identity columns
SQL Constraints
NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY with ON DELETE actions, CHECK and DEFAULT, and how NULL interacts with them
Relationships Between Tables
One-to-one, one-to-many and many-to-many relationships, how each is built with foreign keys, junction tables, querying with JOIN
Indexes
What an index is, B-tree and other index types, composite, partial and expression indexes, when an index is not used, EXPLAIN
Transactions in SQL
ACID, BEGIN / COMMIT / ROLLBACK and savepoints, isolation levels and read anomalies, how PostgreSQL actually implements them, lost updates and locking
NoSQL Databases
NoSQL data models (key-value, document, wide-column, graph), SQL vs NoSQL, transactions and consistency, CAP theorem, when to choose which