Databases Reference

Database operations from the command line and application code. SQL fundamentals, engine-specific patterns, and NoSQL essentials.

SQL Fundamentals

Topic	Description
SQL Core	SELECT, INSERT, UPDATE, DELETE, CTEs, window functions, views
Joins	INNER, LEFT, RIGHT, FULL, self-join, anti-join, multi-table
Query Patterns	Filtering, set operations, conditional aggregation, deduplication
Indexes	B-tree, composite, partial, GIN, covering indexes, anti-patterns
Transactions	ACID, isolation levels, savepoints, locking, deadlock prevention
Migrations	Schema versioning, Alembic, safe ALTER TABLE, compatibility matrix

Topic

Description

SELECT, INSERT, UPDATE, DELETE, CTEs, window functions, views

INNER, LEFT, RIGHT, FULL, self-join, anti-join, multi-table

Filtering, set operations, conditional aggregation, deduplication

B-tree, composite, partial, GIN, covering indexes, anti-patterns

ACID, isolation levels, savepoints, locking, deadlock prevention

Schema versioning, Alembic, safe ALTER TABLE, compatibility matrix

Engine	Description
SQLite	File-based, zero-config, PRAGMAs, JSON, FTS5, Python integration
PostgreSQL	psql, JSONB, arrays, roles, pg_dump, performance analysis
MySQL	mysql CLI, InnoDB, mysqldump, MySQL vs PostgreSQL differences

Engine

Description

File-based, zero-config, PRAGMAs, JSON, FTS5, Python integration

psql, JSONB, arrays, roles, pg_dump, performance analysis

mysql CLI, InnoDB, mysqldump, MySQL vs PostgreSQL differences