Database tricks that make SQL stick

ACID (A=Atomicity, C=Consistency, I=Isolation, D=Durability)

AAtomicity — all or nothing

CConsistency — valid states only

IIsolation — concurrent transactions safe

DDurability — survives crashes

SQL clause order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

SQL Execution Order — Written: SELECT...FROM...WHERE...GROUP BY...ORDER BY. Executed: FROM first, WHERE filters rows, GROUP BY aggregates, HAVING filters groups, SELECT outputs columns, ORDER BY sorts.

1NF (First Normal Form): atomic values. 2NF (Second Normal Form): full key dependency. 3NF (Third Normal Form): no transitive dependency.

1NFAtomic values, no repeating groups

2NFFull functional dependency on PK

3NFNo transitive dependencies

Index = fast lookup at cost of slower writes

Database Indexes — Like a book's index — precomputed pointers to data. B-tree indexes: good for range queries. Hash indexes: good for exact matches. Add indexes on columns you frequently search, sort, or join on.

CAP theorem (C=Consistency, A=Availability, P=Partition tolerance) — distributed systems can only guarantee 2 of 3

CAP Theorem — Consistency: all nodes see same data simultaneously. Availability: every request gets a response. Partition tolerance: system works despite network failures. In a distributed system, partition tolerance is usually required — choose C or A.

NoSQL types: document (MongoDB), key-value (Redis), column-family (Cassandra), graph (Neo4j)

DocumentJSON-like, flexible — MongoDB

Key-valueFast lookups — Redis

Column-familyTime-series — Cassandra

GraphRelationships — Neo4j

SQL vs NoSQL: SQL = structured, ACID, relational. NoSQL = flexible schema, horizontal scaling, eventual consistency.

SQL vs NoSQL — SQL: fixed schema, powerful querying (JOINs), ACID transactions, vertical scaling. Best for: financial systems, complex relationships, strong consistency. NoSQL: flexible/no schema, horizontal scaling (sharding), eventual consistency, optimized for specific access patterns. Best for: big data, high write volume, unstructured data.

Transactions: BEGIN → statements → COMMIT (or ROLLBACK). All or nothing.

Database Transactions — Transaction: group of SQL statements treated as a single unit. BEGIN marks start. COMMIT saves all changes permanently. ROLLBACK undoes all changes if anything fails. Example: bank transfer — debit one account AND credit another must both succeed or both fail. Isolation levels control how concurrent transactions interact.

Stored procedure: precompiled SQL code stored in database. Trigger: automatically executes on data changes.

Stored Procedures and Triggers — Stored procedure: named, precompiled SQL code — called by applications. Faster than sending raw SQL (precompiled, less network traffic). Triggers: automatically execute when specified database event occurs (INSERT, UPDATE, DELETE). Use cases: audit logging, enforcing business rules, derived value calculation.

Query optimization: use EXPLAIN to see execution plan. Index on WHERE and JOIN columns.

Query Optimization — EXPLAIN (MySQL) or EXPLAIN ANALYZE (PostgreSQL): shows query execution plan. Look for: full table scans (bad), index seeks (good). Optimization: add indexes on columns in WHERE, JOIN, and ORDER BY clauses. Avoid SELECT * — specify columns. Avoid functions on indexed columns in WHERE (prevents index use). Batch operations.

Data warehouse vs OLTP: warehouse for analytics (read-heavy). OLTP (Online Transaction Processing) for transactions (write-heavy).

Data Warehouses — OLTP (Online Transaction Processing): optimized for many small, fast transactions — normalized, row-oriented storage. Data warehouse (OLAP): optimized for complex analytical queries across large datasets — denormalized (star schema), column-oriented storage. ETL: Extract, Transform, Load data from OLTP to warehouse. Examples: Snowflake, Redshift.

Sharding: split database horizontally across multiple servers. Each shard holds a subset of rows.

Database Sharding — Vertical scaling (scale up): more powerful server — has limits. Horizontal scaling (scale out): multiple servers. Sharding: partition data across servers by shard key (user ID range, geography). Challenges: joins across shards are expensive, resharding is painful, transactions across shards are complex. Used by Facebook, Twitter, Uber.