SQL Basics

In one line

Tables, primary/foreign keys, indexes, constraints — the vocabulary of relational modeling.

0/7 · 0%

Think of it like this

An Excel workbook with linked sheets. Each sheet (table) has rows with a unique row number (primary key), and one sheet can point to a row in another sheet (foreign key) instead of copying its data.

Key ideas

01
Table = typed rows; PRIMARY KEY = unique identity (usually auto-increment id or UUID).
02
FOREIGN KEY = the link that keeps referential integrity (order.user_id → user.id).
03
Constraints: NOT NULL, UNIQUE, CHECK, DEFAULT — cheap correctness at ingestion time.
04
Indexes make lookups fast but slow writes — the fundamental trade.
05
Every query you write should be read as 'what index would make this fast?'.
06
SQL review habits: EXPLAIN ANALYZE, index usage, scan vs seek.

Java / Spring map

→
JPA entities map 1:1 to tables; @Id, @Column(unique=true), @ManyToOne → FK.

Code & diagrams

schema.sqlsql

The canonical e-commerce-ish schema to know cold.

CREATE TABLE users (
  id         BIGSERIAL PRIMARY KEY,
  email      TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id         BIGSERIAL PRIMARY KEY,
  user_id    BIGINT NOT NULL REFERENCES users(id),   -- FK keeps integrity
  status     TEXT NOT NULL DEFAULT 'PENDING'
             CHECK (status IN ('PENDING','PAID','SHIPPED','CANCELLED')),
  total_cents BIGINT NOT NULL,                       -- money in cents, not float!
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_user ON orders(user_id);      -- every FK deserves an index
-- most queries: by user → B-tree on user_id keeps it a seek, not a scan

Explain without notes

Why does every foreign key deserve an index — what happens to a join without one?

Practice

Model Splitwise tables (users, groups, expenses, splits) with keys + constraints + indexes.

Trade-offs

↔
Indexes are write amplification; on hot write path, fewer indexes win. Measure with real access patterns.

Run it in production

You've designed it. Now build, operate, and break the same idea hands-on in the DevOps courses:

Stateful · PostgreSQL essentials

MVCC, VACUUM, roles, and the SQL vs NoSQL families, from the operator's side.

Completion checklist

I can model any LLD entity set as tables with PK/FK/index reasoning.

Back to phase

Command Palette