The Relational Model and SQL Fundamentals

Almost every backend interview eventually reaches a database question, and the relational model is where it starts. Edgar Codd's 1970 idea was radical for its time: store data as relations (tables) built from mathematical set theory, and let a declarative language describe what you want instead of how to fetch it. That separation between logical model and physical storage is the reason SQL databases have survived fifty years of hardware change.

The core vocabulary

A relation is a table. Each tuple is a row, each attribute is a column, and the domain is the set of legal values for that column. Two properties define a true relation: rows are unordered (there is no "first" row), and every row is unique. Uniqueness is enforced by a key.

Superkey: any set of attributes that uniquely identifies a row.
Candidate key: a minimal superkey — remove any attribute and it stops being unique.
Primary key: the candidate key you choose to identify rows. It cannot be NULL.
Foreign key: an attribute that references the primary key of another table, enforcing referential integrity.

These integrity rules — entity integrity (no null primary keys) and referential integrity (no dangling foreign keys) — are what interviewers mean when they ask "how does the database keep data consistent."

Relational algebra: the theory under SQL

SQL is a friendly skin over relational algebra. Knowing the operators helps you reason about query plans:

Selection (σ): filter rows — the WHERE clause.
Projection (π): pick columns — the SELECT list.
Join (⋈): combine tables on a predicate.
Union / Intersection / Difference: set operations over union-compatible relations.
Cartesian product (×): every row of A paired with every row of B — what you get if you forget the join condition.

A join is just a Cartesian product followed by a selection, which is exactly why a missing ON clause explodes into millions of rows.

Writing SQL: the logical order

Interviewers love to ask why WHERE cannot reference a SELECT alias. The reason is the logical evaluation order, which differs from the written order:

| Step | Clause | Purpose | |------|--------|---------| | 1 | FROM / JOIN | Assemble the working set | | 2 | WHERE | Filter individual rows | | 3 | GROUP BY | Collapse rows into groups | | 4 | HAVING | Filter groups | | 5 | SELECT | Compute output columns | | 6 | ORDER BY | Sort the result | | 7 | LIMIT | Truncate |

Because SELECT runs after WHERE, the alias does not exist yet when WHERE executes. HAVING, by contrast, runs after GROUP BY, so it can filter on aggregates.

Joins in practice

-- Every customer and their orders (customers with no orders included)
SELECT c.name, o.id AS order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.name;

INNER JOIN keeps only matching rows.
LEFT / RIGHT OUTER JOIN keeps all rows from one side, filling the other with NULL.
FULL OUTER JOIN keeps unmatched rows from both sides.
CROSS JOIN is the deliberate Cartesian product.
SELF JOIN joins a table to itself — the classic "find each employee's manager" question.

A frequent trap: adding a condition on the right table in the WHERE clause of a LEFT JOIN silently turns it into an inner join, because NULL rows fail the predicate. Put the condition in the ON clause instead.

Aggregation and grouping

SELECT c.country, COUNT(*) AS order_count, SUM(o.total) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
GROUP BY c.country
HAVING SUM(o.total) > 10000
ORDER BY revenue DESC;

Every non-aggregated column in the SELECT must appear in GROUP BY — this is the single most common SQL error in interviews. Aggregate functions (COUNT, SUM, AVG, MIN, MAX) collapse a group into one value.

The three-valued logic of NULL

NULL means "unknown," not zero and not empty string. Comparisons with NULL return unknown, not true or false, which is why WHERE salary = NULL never matches — you must write IS NULL. COUNT(*) counts rows, but COUNT(column) skips nulls. NOT IN with a subquery that returns a NULL can silently return zero rows. These edge cases are deliberate interview bait.

Subqueries and CTEs

A correlated subquery references the outer row and runs once per outer row; an uncorrelated one runs once. Common Table Expressions (WITH) make complex queries readable and enable recursion:

WITH high_value AS (
  SELECT customer_id, SUM(total) AS spend
  FROM orders
  GROUP BY customer_id
  HAVING SUM(total) > 5000
)
SELECT c.name, h.spend
FROM high_value h
JOIN customers c ON c.id = h.customer_id;

What interviewers actually probe

DDL vs DML vs DCL vs TCL. CREATE/ALTER/DROP define schema; SELECT/INSERT/UPDATE/DELETE manipulate data; GRANT/REVOKE control access; COMMIT/ROLLBACK manage transactions.
DELETE vs TRUNCATE vs DROP. DELETE is row-by-row and logged (and can be rolled back mid-transaction); TRUNCATE deallocates pages quickly and resets identities; DROP removes the table entirely.
WHERE vs HAVING. Rows vs groups.
UNION vs UNION ALL. UNION removes duplicates (and sorts to do so); UNION ALL does not, so it is faster when you know rows are distinct.

The relational model rewards you for thinking in sets, not loops. When you catch yourself imagining a for-loop over rows, step back and describe the set you want — that mindset is exactly what distinguishes a strong SQL answer from a struggling one.