🗄️

Databases & SQL

Schema design, queries, indexing, optimization

SQL vs NoSQL

FeatureSQLNoSQL

Data Model Tables with rows/columns Document, Key-Value, Column, Graph

Schema Fixed schema (DDL) Schema-less / flexible

Scaling Vertical (read replicas) Horizontal (sharding built-in)

ACID Full ACID support Eventual consistency (mostly)

Joins Native JOINs No joins (denormalize)

Best For Relational data, transactions High scale, flexible schemas

Examples PostgreSQL, MySQL MongoDB, DynamoDB, Redis, Cassandra

Index Types

B-Tree

Default. Range queries, sorting, equality

Most common

Hash

Equality lookups only

O(1) but no range

GIN

Full-text search, JSONB, arrays

PostgreSQL

GiST

Geometric, range types

PostgreSQL

BRIN

Large sequential tables

Block Range Index

Covering

Index-only scans (INCLUDE)

Avoids heap lookup

Normalization

1NF

Atomic values, no repeating groups

2NF

1NF + no partial dependencies (all non-key cols depend on full PK)

3NF

2NF + no transitive dependencies (non-key → non-key)

BCNF

Every determinant is a candidate key

JOIN Types

∩

INNER JOIN

Only matching rows from both tables

⊃

LEFT JOIN

All from left + matching from right (NULL if no match)

⊂

RIGHT JOIN

All from right + matching from left

∪

FULL OUTER

All rows from both, NULL where no match

CROSS JOIN

Cartesian product (every combination)

↻

SELF JOIN

Table joined with itself

Window Functions

ROW_NUMBER() Unique sequential number per partition

RANK() Rank with gaps for ties

DENSE_RANK() Rank without gaps

LAG() / LEAD() Access previous/next row value

SUM() OVER Running total

NTILE(n) Divide into n equal groups

Common Query Patterns

Second Highest

              SELECT DISTINCT salary FROM emp ORDER BY salary DESC LIMIT 1 OFFSET 1;

Duplicate Rows

              SELECT col, COUNT(*) FROM t GROUP BY col HAVING COUNT(*) > 1;

Running Total

              SELECT *, SUM(amount) OVER (ORDER BY date) AS running FROM orders;

Top N per Group

              WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn
  FROM emp
) SELECT * FROM ranked WHERE rn <= 3;

PostgreSQL Power Features

✦ JSONB (binary JSON, indexed with GIN)

✦ CTEs (WITH clause, recursive CTEs)

✦ LATERAL joins (correlated subqueries)

✦ UPSERT (ON CONFLICT DO UPDATE)

✦ Partitioning (range, list, hash)

✦ EXPLAIN ANALYZE (query plan + actual times)

✦ pg_stat_statements (query performance)

✦ Advisory Locks (application-level locking)