Top 65 SQL Interview Questions and Answers (2026)

SQL (Structured Query Language) is the declarative language relational databases use for defining, querying, and changing data. You state what result you want, and the database's query planner decides how to fetch it. Every major system, PostgreSQL, MySQL, SQL Server, Oracle, SQLite, implements the same core with its own dialect.

Interviewers test it because nearly every engineering, data, and analytics role touches a relational database, and SQL skill is hard to fake in person. Ten minutes of joins and aggregation reveals more about practical data fluency than most resume lines.

A SELECT statement reads data: FROM names the tables, WHERE filters rows, GROUP BY collapses rows into groups, HAVING filters those groups, SELECT picks and computes the output columns, ORDER BY sorts the result, and LIMIT caps how many rows come back. Only SELECT and FROM are required.

Being able to recite what each clause does, in order, is the foundation for half the questions in a SQL round, including the classic ones about WHERE versus HAVING and why aliases fail in some clauses.

SELECT * fetches every column whether the application needs it or not, which wastes I/O and network transfer, blocks index-only scans that could have answered the query from an index alone, and silently changes the result shape whenever someone adds a column to the table.

It's fine for interactive exploration. In application code, listing columns explicitly documents intent, keeps results stable under schema changes, and lets the database do less work. If a table later gains a wide JSON or text column, every SELECT * on it silently starts dragging that payload across the network.

WHERE evaluates a condition for every candidate row and keeps only the rows where it's true. It supports comparisons (=, <>, <, >=), boolean combinators (AND, OR, NOT), range checks with BETWEEN, list membership with IN, pattern matching with LIKE, and NULL tests with IS NULL.

Conditions can combine freely with parentheses. The one operator that never works the way beginners expect is = NULL, which is its own classic question. For LIKE, % matches any run of characters and _ matches exactly one; ILIKE is PostgreSQL's case-insensitive variant.

DISTINCT removes duplicate rows from the result set, comparing the full set of selected columns. COUNT(DISTINCT column) counts unique values of one column. Deduplication isn't free: the database has to sort or hash the whole result to find the duplicates.

It becomes a warning sign when it's papering over a join that multiplied rows. If you find yourself adding DISTINCT to fix an inflated result, the real bug is usually in the join condition. Related and worth naming: DISTINCT ON (column) is a PostgreSQL extension that keeps one full row per value.

ORDER BY sorts the result by one or more columns, each ascending or descending; LIMIT then caps how many rows are returned, and OFFSET skips rows before counting. Without ORDER BY, row order is undefined, so LIMIT without a sort returns an arbitrary slice.

Two details worth adding: sort columns can be expressions or aliases, and NULL ordering is controllable in PostgreSQL with NULLS FIRST or NULLS LAST. OFFSET also gets expensive at depth, since skipped rows are still generated; the keyset alternative appears in the experienced tier.

Logically: FROM and JOINs first, then WHERE, then GROUP BY, then HAVING, then SELECT (including window functions), then DISTINCT, then ORDER BY, then LIMIT. That's different from the written order, where SELECT comes first, and the gap explains several classic errors.

It's why a column alias defined in SELECT can't be used in WHERE (WHERE runs earlier), why HAVING can see aggregates but WHERE can't, and why ORDER BY is the one clause that can use aliases everywhere. Databases are free to physically execute in any order that preserves these semantics; the logical order is the contract, not the plan.

Aggregates collapse many rows into one value: COUNT counts rows, SUM adds, AVG averages, MIN and MAX find extremes. COUNT(*) counts all rows in the group; COUNT(column) counts only rows where that column is not NULL; COUNT(DISTINCT column) counts unique non-NULL values.

Every aggregate except COUNT(*) ignores NULLs, which quietly changes results: AVG(commission) averages only the rows that have a commission, not all rows. One myth worth preempting: COUNT(1) is not faster than COUNT(*); they compile to the same operation in every major database.

GROUP BY partitions rows into groups that share the same values in the listed columns, then aggregates run once per group instead of once over the whole table. Every column in SELECT must either appear in GROUP BY or sit inside an aggregate function.

That rule exists because a group of many rows has no single value for an ungrouped column. PostgreSQL raises an error; MySQL's legacy modes used to pick an arbitrary value, which is worse. PostgreSQL also accepts the select-list alias or a column position in GROUP BY, though explicit names read better in reviews.

WHERE filters individual rows before grouping happens, so it can't reference aggregates. HAVING filters groups after aggregation, so it exists precisely to express conditions like 'departments with more than five people'. Using both in one query is normal and often optimal.

Filtering early is cheaper: push every condition that doesn't need an aggregate into WHERE, so fewer rows reach the grouping step at all. HAVING without GROUP BY is legal too: it treats the entire result as one group, a corner that occasionally appears as a trick question.

An INNER JOIN combines rows from two tables where the join condition matches, and drops rows from either side that have no partner. It's the default JOIN: writing JOIN alone means INNER JOIN. The result contains only customer-order pairs that actually exist, in this example.

Rows multiply when matches aren't one-to-one: a customer with three orders appears three times. That multiplication is the root cause behind many double-counted aggregates. The expected cardinality out loud (one customer to many orders) before writing the join; it's how you catch fan-out before it corrupts a SUM.

A LEFT JOIN keeps every row from the left table and fills the right side with NULLs where no match exists. A RIGHT JOIN is the mirror image: it keeps every row from the right table. They're interchangeable by swapping table order, so most teams standardize on LEFT JOIN.

The unmatched-rows-become-NULL behavior enables a classic pattern: LEFT JOIN then filter WHERE right_table.id IS NULL to find rows with no partner, like customers who never ordered. One trap: filtering the right table in WHERE quietly turns a LEFT JOIN back into an INNER one; those conditions belong in the ON clause.

A FULL OUTER JOIN keeps every row from both tables: matched pairs join normally, and unmatched rows from either side appear once with NULLs filling the missing half. It's the union of a LEFT and a RIGHT JOIN over the same condition.

The everyday use is reconciliation: comparing two systems that should agree, like billing accounts against CRM accounts, where rows missing on either side are exactly the discrepancies you're hunting. MySQL doesn't implement FULL OUTER JOIN at all; the workaround there is a LEFT JOIN unioned with the unmatched half of a RIGHT JOIN.

A CROSS JOIN pairs every row of one table with every row of another: the Cartesian product, with no join condition at all. A 1,000-row table crossed with a 10,000-row table produces ten million rows, which is why it's usually an accident (a missing ON clause).

Deliberate uses exist: generating a date-times-product grid so reports show zero-sales days, or building test combinations. If you write one on purpose, comment it. The same product also appears by accident when someone lists two tables in FROM with a comma and forgets the WHERE condition.

A self join joins a table to itself, using two aliases so the database treats one physical table as two logical ones. It answers questions about relationships between rows of the same table, and the canonical example is an employees table where manager_id points at another employee's id.

The LEFT variant matters here: an INNER self join would silently drop the CEO, whose manager_id is NULL.

NULL means unknown, and SQL uses three-valued logic: comparisons involving NULL evaluate to UNKNOWN rather than TRUE or FALSE, and WHERE only keeps rows where the condition is TRUE. So column = NULL, and even NULL = NULL, match nothing. The correct tests are IS NULL and IS NOT NULL.

The same logic hides a nastier trap: NOT IN against a subquery that returns any NULL matches zero rows, because every comparison collapses to UNKNOWN. NOT EXISTS is the safe form.

COALESCE returns the first non-NULL argument in its list, which makes it the standard way to substitute defaults for missing data: COALESCE(commission, 0). NULLIF returns NULL when its two arguments are equal, and its main job is turning a zero into a NULL to dodge division-by-zero errors.

Both are standard SQL and work across every major database, which makes them safer interview answers than dialect-specific functions like ISNULL or IFNULL.

Both enforce uniqueness and both get an index automatically. A table has exactly one primary key, its columns can never be NULL, and it's the default target for foreign keys. Unique constraints can appear many times per table and allow NULLs, which the standard treats as distinct from each other.

The mental model: the primary key is the row's identity; unique constraints guard alternate keys like email or national ID that must not repeat but aren't the identity.

A foreign key declares that values in one table must exist in another, and the database enforces it: you can't insert an order for a customer who doesn't exist, or delete a customer who still has orders. This is referential integrity, enforced at the data layer where no application bug can bypass it.

ON DELETE decides what happens to child rows when the parent goes: RESTRICT blocks the delete, CASCADE deletes the children too, SET NULL orphans them explicitly. CASCADE is convenient and dangerous; most production schemas default to RESTRICT.

Six do most of the work: NOT NULL requires a value, DEFAULT supplies one, UNIQUE forbids repeats, CHECK enforces an arbitrary condition like salary > 0, PRIMARY KEY identifies rows, and FOREIGN KEY ties tables together. All of them reject bad writes at the moment they happen.

The 'why' matters: applications get rewritten, scripts bypass them, and bugs ship. Constraints are the one validation layer every write path shares.

Both stack the results of two queries with compatible column lists. UNION removes duplicate rows, which forces a sort or hash over the combined result; UNION ALL just concatenates, keeping duplicates and skipping that work entirely. When duplicates are impossible or acceptable, UNION ALL is strictly faster.

The same family includes INTERSECT (rows in both results) and EXCEPT (rows in the first but not the second), which round out set operations.

DELETE removes rows one at a time, supports a WHERE clause, fires row-level triggers, and is fully transactional. TRUNCATE empties the entire table by deallocating its storage, which is far faster but all-or-nothing. DROP removes the table itself: data, structure, indexes, and constraints, gone.

A PostgreSQL nuance worth knowing: TRUNCATE there is transactional and can be rolled back inside a transaction, which isn't true in several other databases.

They're the four command families. DDL (data definition) shapes structure: CREATE, ALTER, DROP, TRUNCATE. DML (data manipulation) changes rows: INSERT, UPDATE, DELETE, with SELECT often filed here or under its own DQL label. DCL (data control) manages permissions: GRANT and REVOKE. TCL (transaction control) manages units of work: BEGIN, COMMIT, ROLLBACK, SAVEPOINT.

The categories matter practically because they behave differently: DDL is often auto-committing in databases like MySQL and Oracle, which surprises people mid-transaction.

A subquery is a query nested inside another. A scalar subquery returns one value and can sit anywhere an expression can, like a SELECT column or a comparison. Table subqueries return row sets and feed IN, EXISTS, or the FROM clause, where the result behaves like a temporary table.

The variant that changes performance behavior is the correlated subquery, which references the outer row; that gets its own question in the intermediate tier.

CASE is SQL's inline conditional: it walks WHEN conditions top to bottom, returns the THEN value of the first match, and falls back to ELSE (or NULL without one). It's an expression, so it works anywhere a value does: SELECT lists, ORDER BY, GROUP BY, and inside aggregates.

That last placement, CASE inside an aggregate, is the building block for pivoting rows into columns, which is a classic exercise later in this bank.

A CTE (common table expression) is a named result set declared with WITH at the top of a query. It does what a derived-table subquery does, but reads top to bottom instead of inside out, can be referenced multiple times by name, and is the only form that supports recursion for hierarchies.

One depth point: before PostgreSQL 12, CTEs were always materialized, acting as an optimization fence; modern versions inline them unless you write MATERIALIZED. Knowing that history explains a lot of older advice online.

A correlated subquery references columns from the outer query, so conceptually it re-executes once per outer row instead of once total. The classic example: employees earning above their own department's average, where the inner AVG depends on the current outer row's department.

Cost-wise, naive execution is O(rows times subquery), though planners often rewrite correlated forms into joins. When they can't, rewriting by hand into a join against a grouped subquery or a window function is the standard fix.

EXISTS asks 'does at least one matching row exist' and can stop at the first hit; IN builds the subquery's value list and tests membership. Modern planners usually execute both the same way, so the real difference is semantics: NOT IN silently matches nothing if the subquery yields a single NULL, while NOT EXISTS is immune.

That makes the practical rule simple: EXISTS and NOT EXISTS for subqueries against nullable columns, IN for short literal lists.

A window function computes a value across a set of related rows (the window) while keeping every input row in the output. GROUP BY collapses each group to one row; a window function annotates rows with group-level context instead, like showing each employee next to their department's average.

The OVER clause defines the window: PARTITION BY splits rows into groups, ORDER BY sequences them for running and ranking calculations, and an optional frame clause narrows which neighbors count.

All three number rows within a window ordered by some key; they differ only on ties. ROW_NUMBER always gives unique sequential numbers, breaking ties arbitrarily. RANK gives tied rows the same rank and then skips (1, 1, 3). DENSE_RANK gives tied rows the same rank without gaps (1, 1, 2).

The choice is driven by the problem: deduplication and top-N-per-group want ROW_NUMBER; Nth-highest-value problems want DENSE_RANK so ties count as one value.

LAG reaches back to a previous row in the window's order and returns one of its values; LEAD reaches forward. They turn row-to-row comparisons, previously a painful self join, into one readable expression, with month-over-month change as the canonical example.

Both take an offset (default 1) and a default value for the edges, where LAG on the first row would otherwise return NULL.

With a window aggregate: SUM(amount) OVER (ORDER BY date) accumulates from the first row to the current one. Adding PARTITION BY resets the total per group, like a running balance per account. This replaces the old correlated-subquery approach, which recomputed the sum for every row.

One subtlety: with ORDER BY, the default frame is RANGE, which includes all peer rows that tie on the sort key. Spell out ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for strict row-by-row accumulation.

Because of logical processing order: WHERE runs before the SELECT phase where window functions are computed, so the value you want to filter on doesn't exist yet. The database rejects it outright. The workaround is structural: compute the window function in a CTE or subquery, then filter in the outer query.

Some engines added QUALIFY as a shortcut for exactly this, but the CTE pattern is the portable answer.

The classic exercise, and the key point is multiple approaches with their edge cases. DENSE_RANK is the strongest: it handles ties correctly and generalizes to any Nth value. The MAX-below-MAX subquery is the traditional answer. LIMIT with OFFSET works too but silently depends on DISTINCT and returns an empty set rather than NULL when there's no second value.

Whatever you write, say the edge cases out loud: duplicate top salaries, a table with one row, and NULL salaries.

Number the rows within each group with ROW_NUMBER, partitioned by the group and ordered by the ranking key, then keep row numbers up to N in an outer query. This shape solves a whole family of interview problems: top three earners per department, latest order per customer, first event per session.

Swap ROW_NUMBER for DENSE_RANK when ties should all survive the cut; that one-word change is a favorite follow-up.

Group by the columns that define 'duplicate' and keep groups with a count above one. GROUP BY plus HAVING COUNT(*) > 1 returns each duplicated value once, with its frequency; joining that result back to the table, or using a COUNT window function, retrieves the full offending rows.

The definitional step matters more than the syntax: duplicated on email alone, or on the whole row? Interviewers deliberately leave that ambiguous to see if you ask.

Rank the rows within each duplicate set with ROW_NUMBER, choosing the ordering that defines which copy survives (lowest id, newest timestamp), then delete every row whose number exceeds one. The CTE-plus-DELETE form is the cleanest way to express it in PostgreSQL.

Before running it in production, mention wrapping it in a transaction, checking the count first, and adding the unique constraint afterward so the duplicates can't return.

Compare each row to its predecessor with LAG: wherever the difference between the current and previous value exceeds one, a gap sits between them, and simple arithmetic gives its exact bounds. The same LAG-and-compare move powers the wider gaps-and-islands family of problems, like finding streaks of consecutive activity.

An alternative when you know the full expected range is generating it (generate_series in PostgreSQL) and anti-joining actual values against it.

With conditional aggregation: one aggregate per output column, each counting or summing only the rows that match that column's condition. PostgreSQL's FILTER clause states it directly; SUM or COUNT over a CASE expression is the fully portable spelling. Some databases also ship a dedicated PIVOT keyword.

The limitation to name: the output columns are fixed at write time. Dynamic pivoting, where categories become columns at runtime, needs generated SQL or is better left to the reporting layer.

A view is a saved query that runs fresh every time you select from it: always current, no storage, and a clean way to hide complexity or restrict which columns a user sees. A materialized view stores its result physically, so reads are fast and cheap but the data goes stale until you refresh it.

The trade is recomputation versus staleness. Dashboards and heavy aggregations suit materialized views with scheduled refreshes; anything needing live data stays a plain view.

An index is a separate, ordered structure, a B-tree by default, that maps column values to row locations. Instead of scanning every row, the database descends the tree in a handful of page reads, turning O(n) lookups into O(log n). Primary keys and unique constraints get indexes automatically; everything else is your call.

Indexes serve equality lookups, range scans, and can satisfy ORDER BY without sorting. The cost side, every write maintaining every index, is its own question in the experienced tier.

Normalization organizes tables so each fact lives in exactly one place, which prevents update, insert, and delete anomalies. 1NF requires atomic values: no comma-separated lists in a column. 2NF removes attributes that depend on only part of a composite key. 3NF removes attributes that depend on other non-key attributes.

The memorable summary: every non-key column depends on the key, the whole key, and nothing but the key. Most transactional schemas aim for 3NF and stop there.

When read performance or query simplicity is worth paying for with redundancy: duplicating a customer's name onto orders to avoid a hot join, keeping a running order_count on accounts, or maintaining wide pre-joined reporting tables. Analytics schemas like star schemas denormalize by design.

The cost is that every duplicated fact can now disagree with its source, so each one needs an owner: a trigger, a job, or application code that keeps it in sync, plus monitoring for drift. Normalize by default; denormalize with a measured reason.

A transaction bundles several statements into one all-or-nothing unit. BEGIN opens it, COMMIT makes every change permanent at once, and ROLLBACK discards them all. The textbook case is a money transfer: the debit and the credit must both happen or neither, no matter what fails in between.

SAVEPOINT adds partial rollback inside a transaction. And most databases run each standalone statement as its own implicit transaction, which is why single statements never half-apply.

Atomicity: a transaction applies fully or not at all. Consistency: each transaction moves the database from one valid state to another, with constraints holding. Isolation: concurrent transactions don't see each other's half-finished work. Durability: once committed, changes survive crashes, because they're persisted to a write-ahead log before the commit returns.

The property with configurable strength is isolation: databases offer levels that trade correctness guarantees for concurrency, which is the follow-up question waiting behind this one.

Store timestamps in a timestamp type, in PostgreSQL preferably timestamptz, which normalizes to UTC. Group by periods with date_trunc('month', created_at), do arithmetic with intervals like now() - interval '30 days', and filter ranges with half-open bounds: >= the start and < the day after the end, which no rounding can break.

The classic mistake is wrapping the column in a function inside WHERE, like date(created_at) = '2026-07-01', which blocks index use; the range form is equivalent and index-friendly.

With a string aggregation function: string_agg(name, ', ') in PostgreSQL, GROUP_CONCAT in MySQL, LISTAGG in Oracle and SQL Server 2017's STRING_AGG. It behaves like any aggregate, so it works with GROUP BY, and PostgreSQL lets you order the elements inside the call.

It comes up constantly in reporting: one row per parent with children listed inline. The caution is treating it as presentation, keeping the underlying data normalized rather than storing the concatenated string.

A B-tree is a balanced tree of pages: a root, a shallow layer of internal pages holding ordered key ranges, and leaf pages holding keys with pointers to rows. High fanout keeps it shallow, so millions of rows resolve in three or four page reads, and the tree rebalances itself on writes.

Two properties follow from the sorted structure: range scans are cheap, since leaves are ordered and linked, and the index can hand back rows already sorted, letting the planner skip an explicit sort for matching ORDER BY clauses.

A composite index sorts by the first column, then the second within it, like a phone book sorted by last name then first. Queries can use any leftmost prefix: an index on (customer_id, created_at) serves filters on customer_id alone or both columns, but a filter on created_at alone can't seek into it.

The ordering heuristic: equality-filtered columns first, range-filtered columns after, because a range on an earlier column stops the index from narrowing on later ones.

A covering index contains every column a query touches, so the database answers from the index alone without visiting the table: an index-only scan. That removes the random I/O of heap lookups, which is often the dominant cost of an index scan that returns many rows.

PostgreSQL's INCLUDE clause adds payload columns to the leaves without making them part of the key. One PostgreSQL nuance: index-only scans still consult the visibility map, so recently modified tables may fall back to heap checks until vacuum catches up.

Every INSERT, UPDATE, and DELETE must maintain every index on the table, so each added index taxes all writes; heavily indexed tables can spend more time updating trees than storing data. Indexes also occupy disk and cache, and unused ones pay that cost for nothing.

They also mislead: on low-selectivity columns (a status flag with three values), or when a query matches a large fraction of the table, a sequential scan is genuinely cheaper, and the planner will rightly ignore the index someone insisted on adding.

Sargable means the predicate's shape lets the planner seek into an index: comparisons against a bare column. Wrapping the column in a function or expression, date(created_at), lower(email), price * 1.1, forces evaluation per row, so the index on the raw column becomes useless for that filter. Leading-wildcard LIKE patterns and implicit type casts do the same damage.

Fixes: move the arithmetic to the constant side, rewrite date functions as ranges, or create an expression index matching the exact expression you query.

EXPLAIN shows the planner's chosen strategy with cost estimates; EXPLAIN ANALYZE executes the query and adds actual times and row counts. Read it inside out: leaf nodes are scans (sequential or index), parents are joins (nested loop, hash, merge) and aggregations, and each node reports estimated versus actual rows.

The highest-value signal is a large gap between estimated and actual rows, which means stale statistics steering the planner wrong; ANALYZE the table. After that: sequential scans on big filtered tables, and nested loops fed by huge outer inputs.

Establish what changed: data volume, a deploy, new query patterns, or plan flips. Capture the real query with its parameters from logs or pg_stat_statements, run EXPLAIN ANALYZE, and compare estimated to actual rows. Stale statistics, an index the planner stopped choosing, growth past a memory threshold, or parameter values with skewed distributions cover most cases.

Then fix at the right layer, ANALYZE, an index, a rewrite, and verify with the same plan-reading measurement before and after. The technical sequence matters more than the specific culprit: observe, hypothesize, verify, fix, confirm.

It's the pattern where code fetches N parent rows with one query, then loops and issues one more query per row for related data: 1 + N round trips. Each query is fast, so the database looks innocent while the page takes seconds. ORMs generate it silently through lazy-loaded relations.

Fixes: fetch the relation in the same query with a JOIN, batch it with one IN query for all parent ids, or turn on the ORM's eager loading. Detection is the senior half: query counts per request in APM, or spotting hundreds of near-identical statements in the log.

The standard defines four levels by which anomalies they permit. A dirty read sees uncommitted data. A non-repeatable read gets different values re-reading the same row. A phantom read finds new rows matching a repeated query. Read uncommitted allows all three, read committed blocks dirty reads, repeatable read also blocks non-repeatable reads, and serializable blocks everything.

PostgreSQL specifics worth volunteering: it never allows dirty reads at any level, its repeatable read blocks phantoms too through snapshots, and serializable uses serializable snapshot isolation, so applications must retry serialization failures.

Multi-version concurrency control keeps multiple versions of each row: an update writes a new version instead of overwriting, and every transaction reads the snapshot of versions that were committed when it started. Readers never take locks that writers wait on, and writers never wait for readers, so read-heavy workloads scale without lock queues.

The costs come as follow-ups: dead row versions accumulate until vacuum reclaims them, which is why long-running transactions are dangerous in PostgreSQL, they pin old versions and cause table bloat.

A deadlock is two transactions each holding a lock the other needs: A locked row 1 and wants row 2, B locked row 2 and wants row 1. Neither can proceed, so the database's deadlock detector picks a victim, rolls it back, and returns an error the application should catch and retry.

Prevention is mostly ordering discipline: every code path acquires locks in the same order (say, ascending id), transactions stay short, and lock acquisition happens as late as possible. You can't fully eliminate them under concurrency, so retry logic is part of the answer.

Pessimistic locking takes the lock up front: SELECT FOR UPDATE holds the row so no one else can change it until you commit. It's right when conflicts are common or the update must not fail. Optimistic locking holds nothing: you read a version, and your update asserts the version is unchanged; zero rows updated means someone got there first, so you retry.

Optimistic wins for web apps with rare conflicts and no desire to hold locks across user think time; pessimistic wins for short, contended, must-succeed sections like inventory decrements.

OFFSET must generate and discard every skipped row, so page 1,000 scans 25,000 rows to return 25; cost grows linearly with page depth, and rows shifting underneath cause skips and repeats. Keyset pagination instead remembers the last row's sort values and asks for rows after them, which an index answers directly at any depth.

Requirements: a deterministic ORDER BY with a unique tiebreaker like id, and accepting the trade-off that you can't jump to an arbitrary page number, only forward and back.

In PostgreSQL: INSERT ... ON CONFLICT (key) DO UPDATE, with EXCLUDED carrying the values the insert attempted. MySQL spells it ON DUPLICATE KEY UPDATE, and the standard's MERGE covers the general case. One statement either inserts or updates, atomically, backed by a unique constraint.

The SELECT-then-INSERT alternative has a race: two requests both see 'not there' and both insert, one dying on the constraint, or worse, duplicating without one. The upsert closes that window inside the database.

A natural key is a real-world attribute (email, ISBN, national ID); a surrogate is a meaningless generated value (identity column, UUID). Surrogates win by default in practice: natural keys change (people change emails), get typed wrong, recycle, and cascade painfully through every referencing table when corrected.

The balanced answer keeps both: a surrogate primary key for joins and foreign keys, plus a unique constraint on the natural key so the business rule still holds. The UUID-versus-bigint trade too: UUIDs generate anywhere but index worse than sequential integers matters.

Clarify scope first, then the core is four tables: customers, products, orders, and order_items as the join table carrying quantity and unit_price. The judgment call interviewers watch for: order_items copies the price at purchase time rather than referencing the product's current price, because catalog prices change and historical orders must not.

The same snapshot logic applies to shipping addresses. Add a constrained status column for order state, and money as NUMERIC. Close by walking through a query the design must serve, like a customer's order history with totals.

Soft delete marks rows with a deleted_at timestamp instead of removing them, preserving history, enabling undo, and keeping foreign keys intact. Hard delete keeps the schema honest and is sometimes legally required, as with GDPR erasure requests.

The costs of soft delete are the senior material: every query must remember to filter deleted rows, unique constraints break (a deleted user still holds the email) until you use partial unique indexes, and tables grow without bound. Views that pre-filter, and archive tables for truly cold data, are the standard mitigations.

NUMERIC (or DECIMAL), never FLOAT: floating point stores binary fractions, so 0.1 has no exact representation and cent errors accumulate across arithmetic. NUMERIC(12, 2) stores exact decimal values, and financial code either uses it or stores integer cents.

The wider principle: types are the cheapest correctness tool you have. timestamptz over naive timestamps kills a whole class of time zone bugs, proper date types keep comparisons honest where strings lie, and smaller types keep indexes denser. Type choices outlive the code that made them.