SQL

Overview

SQL databases (relational databases) store data in tables of rows and columns with a fixed schema, relate tables through keys, and let you query with SQL, a declarative language where you describe what you want and the optimizer decides how. They provide ACID transactions, so multi-step changes like transferring money either fully happen or not at all.

Relational databases are the default choice for most business data because of strong consistency, flexible queries (joins, aggregations), constraints that protect data integrity, and decades of tooling. Their scaling limit is writes on a single primary, handled with vertical scaling, replicas for reads, and eventually sharding.

When to use it

• Structured data with relationships: users, orders, payments, inventory.
• You need transactions and strong consistency.
• Ad hoc queries, reporting, joins, and aggregations.
• Data integrity rules: unique constraints, foreign keys.

Where it shows up in interviews

Where it is used in real software

Key terms

Schema

Table definitions: columns, types, constraints.

Primary key

Unique identifier of a row.

Foreign key

A column referencing another table's primary key.

ACID

Atomicity, Consistency, Isolation, Durability.

Query optimizer

Chooses indexes, join order, and algorithms for a query.

How it works, step by step

1. 1
   Model entities as tables

   users, orders, order_items, products.

2. 2
   Define keys and constraints

   Primary keys, foreign keys, NOT NULL, UNIQUE, CHECK.

3. 3
   Normalize, then denormalize deliberately

   Avoid duplicate data by default; copy data only for measured read needs.

4. 4
   Add indexes for query patterns

   Based on WHERE, JOIN, and ORDER BY columns.

5. 5
   Scale reads, then writes

   Caching and read replicas first; partitioning and sharding when writes outgrow one primary.

A simple e-commerce schema

Core tables and relationships

order_items stores price_cents at purchase time on purpose: historical orders must not change when product prices change.

Implementation

1CREATE TABLE users (2  id          BIGSERIAL PRIMARY KEY,3  email       TEXT NOT NULL UNIQUE,4  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()5);6 7CREATE TABLE orders (8  id          BIGSERIAL PRIMARY KEY,9  user_id     BIGINT NOT NULL REFERENCES users(id),10  status      TEXT NOT NULL CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),11  total_cents BIGINT NOT NULL CHECK (total_cents >= 0),12  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()13);14CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);15 16-- Revenue per day for the last week17SELECT date_trunc('day', created_at) AS day, sum(total_cents) / 100.0 AS revenue18FROM orders19WHERE status = 'paid' AND created_at > now() - interval '7 days'20GROUP BY 121ORDER BY 1;

Complexity and performance

Trade-offs

Variants and related techniques

Common mistakes

• Choosing NoSQL by default for 'scale'.

  Fix: Most products fit comfortably in a well-indexed relational database for a long time.

• Floating point for money.

  Fix: Store integer cents or DECIMAL.

• Missing indexes on foreign keys.

  Fix: Index columns used in joins and filters.

Interview questions

Practice problems