42 SQL Interview Questions and Answers

Short answer: SELECT retrieves rows from tables. The logical execution order does NOT match the written order: first FROM, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT.

In depth:

Written (syntactic) order:

SELECT   DISTINCT col1, agg(col2)
FROM     t
WHERE    cond
GROUP BY col1
HAVING   agg_cond
ORDER BY col1
LIMIT    10 OFFSET 20;

Logical execution order:

1. FROM / JOIN      -- which tables, how to join
2. WHERE            -- row filter BEFORE grouping
3. GROUP BY         -- grouping
4. HAVING           -- group filter AFTER aggregation
5. SELECT           -- evaluate expressions, aliases
6. DISTINCT         -- remove duplicates
7. ORDER BY         -- sorting
8. LIMIT / OFFSET   -- slice

⚠️ Gotcha: an alias from SELECT cannot be used in WHERE (since WHERE runs before SELECT), but it can be used in ORDER BY and often in GROUP BY (depends on the DBMS). Example of the error:

SELECT salary * 12 AS annual FROM emp WHERE annual > 100000; -- ERROR
SELECT salary * 12 AS annual FROM emp ORDER BY annual;        -- OK

Short answer: WHERE filters rows, ORDER BY sorts the result, LIMIT n OFFSET m returns n rows, skipping the first m.

In depth:

-- Pagination: page 3 with 20 records each (skip 40, take 20)
SELECT id, name
FROM users
WHERE active = TRUE
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;

⚠️ Gotcha: LIMIT without ORDER BY returns a NON-deterministic set of rows — the order isn't guaranteed. Also, OFFSET is slow at large values (the DBMS still reads and discards the skipped rows) — for deep pagination use keyset pagination:

-- keyset: faster than a large OFFSET
SELECT * FROM users WHERE id > :last_seen_id ORDER BY id LIMIT 20;

Use a unique tie-breaker such as id in the sort; equal timestamps alone do not define a stable page boundary.

Short answer: DISTINCT removes duplicate rows from the result.

In depth:

SELECT DISTINCT department FROM employees;        -- unique departments
SELECT DISTINCT department, city FROM employees;  -- unique PAIRS (department, city)

⚠️ Gotcha: DISTINCT applies to ALL columns in the SELECT, not to a single one. SELECT DISTINCT a, b ≠ "unique a". Also, COUNT(DISTINCT col) counts unique values, ignoring NULL.

The database usually implements DISTINCT with a sort or hash set, which can consume memory and spill to disk. Do not use it as a bandage for an incorrect JOIN: first explain why rows multiplied. If you need one row per group according to a selection rule, use ROW_NUMBER() or PostgreSQL DISTINCT ON (...) with an explicit ORDER BY.

Short answer: INNER — matches only; LEFT — all left rows + matches on the right (otherwise NULL); RIGHT — the mirror of LEFT; FULL — everything from both sides; CROSS — Cartesian product; SELF — a table joined to itself.

In depth:

Suppose we have:

employees           departments
+----+--------+----+   +----+----------+
| id | name   |dept|   | id | title    |
+----+--------+----+   +----+----------+
| 1  | Anna   | 10 |   | 10 | IT       |
| 2  | Boris  | 20 |   | 30 | Finance  |
| 3  | Vera   | NULL|  +----+----------+
+----+--------+----+

INNER JOIN — the intersection (∩). Only rows where there's a match in BOTH tables:

SELECT e.name, d.title
FROM employees e
INNER JOIN departments d ON e.dept = d.id;
-- Anna | IT      (Boris dropped: dept 20 isn't in departments;
--                 Vera dropped: dept = NULL)

employees    departments
   [ ##### ]              <- only the intersection

LEFT JOIN — all rows of the left table + matches on the right (no match -> NULL):

SELECT e.name, d.title
FROM employees e
LEFT JOIN departments d ON e.dept = d.id;
-- Anna  | IT
-- Boris | NULL   <- kept, no department
-- Vera  | NULL   <- kept, dept = NULL

[ left ##### ]   <- all of the left + the intersection

RIGHT JOIN — the mirror of LEFT (all rows of the right):

SELECT e.name, d.title
FROM employees e
RIGHT JOIN departments d ON e.dept = d.id;
-- Anna | IT
-- NULL | Finance  <- department 30 has no employees

FULL OUTER JOIN — all rows of both tables:

SELECT e.name, d.title
FROM employees e
FULL OUTER JOIN departments d ON e.dept = d.id;
-- Anna  | IT
-- Boris | NULL
-- Vera  | NULL
-- NULL  | Finance

CROSS JOIN — the Cartesian product (each with each), with no condition:

SELECT e.name, d.title FROM employees e CROSS JOIN departments d;
-- 3 rows × 2 rows = 6 rows

SELF JOIN — a table to itself (e.g., employee -> manager):

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

💡 How LEFT differs from INNER, by example: if there's no match on the right, INNER DROPS the row, while LEFT KEEPS it, substituting NULL into the right-hand columns. That's why LEFT JOIN is often used to find "orphaned" records:

-- Employees without a department
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept = d.id
WHERE d.id IS NULL;

⚠️ Gotcha: a null-rejecting right-side filter such as d.title = 'IT' in WHERE removes unmatched rows. Put that filter in ON when the intent is to preserve every left row. This is not a ban on right-side WHERE conditions: WHERE d.id IS NULL above intentionally finds unmatched rows.

-- WRONG: the filter in WHERE cuts off rows with NULL on the right -> behaves like INNER
SELECT e.name, d.title FROM employees e
LEFT JOIN departments d ON e.dept = d.id
WHERE d.title = 'IT';

-- RIGHT: if you need to keep all rows on the left
SELECT e.name, d.title FROM employees e
LEFT JOIN departments d ON e.dept = d.id AND d.title = 'IT';

Short answer: if the join key isn't unique on one of the sides, rows multiply (one on the left × N on the right).

In depth:

-- If an order has several items, COUNT(*) counts the items, not the orders
SELECT o.id, COUNT(*) FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.id;

⚠️ Gotcha: summing after a JOIN with duplication gives inflated totals. If you join several "one-to-many" tables, aggregate them in subqueries BEFORE joining.

Short answer: GROUP BY groups rows by column values; aggregates (COUNT, SUM, AVG, MIN, MAX) compute one value per group.

In depth:

SELECT department,
       COUNT(*)        AS headcount,
       SUM(salary)     AS payroll,
       AVG(salary)     AS avg_salary,
       MIN(salary)     AS min_salary,
       MAX(salary)     AS max_salary
FROM employees
GROUP BY department;

⚠️ Gotcha: a selected column must be grouped, aggregated, or functionally dependent on the group. PostgreSQL recognizes the last case when the table's primary key is grouped: SELECT id, name, COUNT(*) FROM employees GROUP BY id is valid if id is its PK. Grouping only by a non-unique department cannot choose one employee name. MySQL rejects ambiguous groups with ONLY_FULL_GROUP_BY enabled; disabling that mode may return an arbitrary value.

Short answer: WHERE filters ROWS before grouping (can't contain aggregates), HAVING filters GROUPS after aggregation (it can).

In depth:

-- Departments where the average salary of active employees > 100000
SELECT department, AVG(salary) AS avg_sal
FROM employees
WHERE active = TRUE          -- row filter BEFORE grouping
GROUP BY department
HAVING AVG(salary) > 100000; -- group filter AFTER aggregation

Rule: a condition on individual rows -> WHERE; a condition on an aggregate result -> HAVING. Performance-wise WHERE is preferable (fewer rows reach the grouping).

⚠️ Gotcha: WHERE AVG(salary) > 100000 cannot aggregate the same query level. Use HAVING for that. A filter on grouped columns can sometimes be pushed down by the optimizer, so HAVING is not inherently slower; use WHERE for row filtering because it states the intent clearly.

Short answer: COUNT(*) — all rows; COUNT(col) — rows where col is NOT NULL; COUNT(DISTINCT col) — unique non-NULL values.

In depth:

-- Table: 5 rows, 2 NULLs in phone, 1 duplicate
SELECT
  COUNT(*)               AS total,        -- 5
  COUNT(phone)           AS with_phone,   -- 3 (NULLs not counted)
  COUNT(DISTINCT phone)  AS unique_phones -- 2
FROM users;

⚠️ Gotcha: COUNT(col) silently ignores NULL — a common cause of discrepancies in reports. If you need to count all rows, always use COUNT(*).

Short answer: a subquery is a SELECT inside another query. They come as scalar (1 value), row/table, and appear in WHERE/FROM/SELECT.

In depth:

-- Scalar (one value) in WHERE
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- In IN (a list of values)
SELECT name FROM employees
WHERE dept IN (SELECT id FROM departments WHERE title = 'IT');

-- In FROM (a derived table)
SELECT dept, avg_sal FROM (
  SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept
) t WHERE avg_sal > 100000;

⚠️ Gotcha: if a scalar NOT IN subquery actually returns a NULL, a non-matching comparison becomes UNKNOWN rather than TRUE. Used as the WHERE condition, it can therefore eliminate every candidate row. If NULL is merely possible but not returned, that outcome is not automatic. Prefer correlated NOT EXISTS when the intent is absence of a matching row; decide separately how an outer NULL should behave.

Short answer: a subquery that references columns of the outer query and is conceptually evaluated for each row of the outer query.

In depth:

-- Employees with a salary above the average FOR THEIR department
SELECT e.name, e.salary, e.dept
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.dept = e.dept   -- reference to the outer e.dept -> correlation
);

-- EXISTS — the classic correlated form
SELECT d.title FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept = d.id);

⚠️ Gotcha: a correlated subquery conceptually runs row by row -> it can be slow on large tables. It's often rewritten via a JOIN or a window function. Optimizers can sometimes "flatten" it, but not always.

Short answer: a CTE (Common Table Expression) is a named temporary query result declared with WITH; it improves readability and lets you reuse a subquery.

In depth:

WITH dept_avg AS (
  SELECT dept, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY dept
)
SELECT e.name, e.salary, da.avg_sal
FROM employees e
JOIN dept_avg da ON da.dept = e.dept
WHERE e.salary > da.avg_sal;

Advantages: readability, you can reference it several times, and you can build chains WITH a AS (...), b AS (...).

⚠️ Gotcha: before PostgreSQL 12, CTEs acted as optimization fences. In PG12+, a non-recursive, side-effect-free SELECT CTE referenced once is normally folded into the parent query. Multiple references normally cause materialization; NOT MATERIALIZED can request folding for eligible CTEs, and MATERIALIZED prevents it. Recursive or volatile CTEs do not follow the simple inlining rule. Read the plan; WITH alone is not a speedup.

Short answer: WITH RECURSIVE lets you traverse hierarchies and graphs (a category tree, an org chart, relationships) iteratively.

In depth: structure: an anchor part UNION ALL a recursive part.

-- The entire branch of subordinates under the manager with id = 1
WITH RECURSIVE subordinates AS (
  -- anchor: the manager themselves
  SELECT id, name, manager_id, 1 AS level, ARRAY[id] AS path
  FROM employees
  WHERE id = 1

  UNION ALL

  -- recursion: those reporting to the already-found people
  SELECT e.id, e.name, e.manager_id, s.level + 1, s.path || e.id
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
  WHERE NOT (e.id = ANY(s.path))
)
SELECT id, name, manager_id, level FROM subordinates ORDER BY level, id;

⚠️ Gotcha: without the path check, UNION ALL would repeat forever on a cycle. This query starts the visited path with the manager, extends it on each step and skips IDs already in that path. PostgreSQL also offers a CYCLE clause. A depth limit is a safety bound, not proof that the full hierarchy was traversed.

Short answer: window functions compute an aggregate/rank "over a window" of rows WITHOUT collapsing rows — each row stays in the output but gets a value for its group.

In depth: func() OVER (PARTITION BY ... ORDER BY ...).

-- Salary + the department average IN THE SAME row (without losing rows)
SELECT name, dept, salary,
       AVG(salary) OVER (PARTITION BY dept) AS dept_avg,
       salary - AVG(salary) OVER (PARTITION BY dept) AS diff
FROM employees;

GROUP BY would return one row per department; a window function keeps all rows.

⚠️ Gotcha: window functions run AFTER WHERE/GROUP BY/HAVING, but BEFORE the query's overall ORDER BY. You can't filter by a window function in WHERE — wrap it in a subquery/CTE.

Short answer: ROW_NUMBER — unique numbers 1,2,3...; RANK — on ties the same rank and a gap (1,1,3); DENSE_RANK — the same rank without a gap (1,1,2).

In depth:

SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
  RANK()       OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense
FROM employees;

salary | rn | rnk | dense
 500   | 1  | 1   | 1
 500   | 2  | 1   | 1
 400   | 3  | 3   | 2     <- RANK skipped 2, DENSE_RANK did not
 300   | 4  | 4   | 3

⚠️ Gotcha: ROW_NUMBER on equal values produces a NON-deterministic order among the ties — add a tiebreaker to ORDER BY (e.g., ORDER BY salary DESC, id).

Short answer: LAG takes a value from the previous row of the window, LEAD — from the next. Handy for computing month-over-month deltas.

In depth:

-- Revenue growth relative to the previous month
SELECT month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS delta
FROM monthly_sales;

LAG(col, n, default) looks n rows back; the default is used only when that row does not exist, not when its column is NULL. Rows must have a deterministic order. A missing calendar month is not a missing row offset: build a calendar series first if the task requires month-to-month rather than previous-record deltas.

⚠️ Gotcha: for the first row LAG returns NULL (there's no previous row) -> the delta becomes NULL. Use the third default argument or COALESCE.

Short answer: UNION combines sets and REMOVES duplicates (does deduplication = slower), UNION ALL simply concatenates, keeping duplicates (faster).

In depth:

SELECT name FROM customers
UNION          -- unique names
SELECT name FROM suppliers;

SELECT name FROM customers
UNION ALL      -- all, including duplicates (faster)
SELECT name FROM suppliers;

Requirements: the same number of columns and compatible types.

⚠️ Gotcha: if duplicates are impossible or don't matter — use UNION ALL: UNION spends resources on sorting/hashing for deduplication. Also, ORDER BY applies to the whole result and is written once at the end.

Short answer: normalization is the process of designing a schema to eliminate redundancy and insert/update/delete anomalies by splitting into related tables.

In depth: anomalies in a denormalized table:

• Update anomaly — repeated data has to be changed in many rows.
• Insert anomaly — you can't add a fact without extra data.
• Delete anomaly — deleting a row loses an unrelated fact.

For example, repeating a department name in every employee row makes a rename a many-row update. Deleting the last employee can also lose the department itself. Store departments separately and reference them with employees.department_id; denormalize measured read bottlenecks only with an explicit plan for keeping copies synchronized.

Short answer: Normal forms describe functional dependencies, not just how many tables you have. 1NF removes repeating groups; 2NF removes partial dependencies of non-prime attributes on any candidate key; 3NF restricts transitive dependencies; BCNF requires every determinant of a non-trivial dependency to be a superkey.

Example: assume one product appears once per order, each order has one customer, and each customer ID determines one customer name:

order_line(order_id, product_id, quantity, customer_id, customer_name)
candidate key = (order_id, product_id)
order_id → customer_id
customer_id → customer_name

1. 1NF: represent products as rows instead of repeating columns product1, product2. Atomicity is relative to the intended domain; a DBMS supporting arrays does not eliminate modeling choices.
2. 2NF: move order-level attributes out because they depend on only order_id, part of the candidate key: order_line(order_id, product_id, quantity) and orders(order_id, customer_id, customer_name).
3. 3NF: split customers(customer_id, customer_name) from orders(order_id, customer_id). Formally, for each non-trivial dependency X → A, X must be a superkey or A must be prime (belong to some candidate key).
4. BCNF: in teaching(student, course, teacher), assume (student, course) → teacher and teacher → course. The candidate keys are (student, course) and (student, teacher): this is 3NF, but not BCNF because teacher is not a superkey. Decompose into teacher_course(teacher, course) and student_teacher(student, teacher); the decomposition is lossless, but enforcing one teacher per student/course now requires extra care.

⚠️ Gotcha: a single-column primary key does not prove 2NF if another candidate key is composite. Check all candidate keys and state the business dependencies first.

Short answer: you denormalize for read performance — duplicating data to avoid expensive JOINs, in analytics/reports/caches.

In depth: examples of justified denormalization:

• Storing order_total in orders so you don't have to sum order_items on every read.
• A star schema in a DWH (facts + denormalized dimensions).
• A cached aggregate column updated by a trigger/application.

⚠️ Gotcha: denormalization shifts responsibility for consistency onto the application — introducing a risk of divergence. Denormalize deliberately, having measured that the normalized schema really is the bottleneck.

Short answer: PRIMARY KEY — the unique identifier of a row (NOT NULL + UNIQUE, one per table); UNIQUE — uniqueness of values (may allow NULL, and there can be several); FOREIGN KEY — a reference to the PK/UNIQUE of another table, enforcing referential integrity.

In depth:

CREATE TABLE departments (
  id    INT PRIMARY KEY,
  code  VARCHAR(10) UNIQUE          -- an alternative unique key
);

CREATE TABLE employees (
  id      INT PRIMARY KEY,
  email   VARCHAR(255) UNIQUE,      -- there can be several UNIQUE
  dept_id INT REFERENCES departments(id)  -- FOREIGN KEY
);

⚠️ Gotcha: PostgreSQL creates an index for a PK/UNIQUE constraint. Its default UNIQUE behavior treats NULLs as distinct, so several NULLs are allowed; UNIQUE NULLS NOT DISTINCT changes that in PG15+. This is a uniqueness rule, not the expression NULL != NULL (which evaluates to UNKNOWN). A PK additionally forbids NULL.

Short answer: a key made of several columns; uniqueness is guaranteed by their COMBINATION.

In detail:

CREATE TABLE enrollments (
  student_id INT,
  course_id  INT,
  grade      INT,
  PRIMARY KEY (student_id, course_id)   -- the pair is unique
);

⚠️ Gotcha: (a, b) and (b, a) enforce the same pair uniqueness, but their B-tree access paths differ. Leading-column filters usually narrow the scan best. A filter on b alone can still use (a, b) via an index scan; PG18 can also choose skip scan when few distinct a values make repeated searches worthwhile. Measure the plan before deciding whether a separate b index is needed.

Short answer: natural — a real business attribute (tax ID, email); surrogate — an artificial one (auto-increment id, UUID). Surrogate is the more common choice.

In detail:

Criterion	Natural	Surrogate
Source	business data	generated by the system
Stability	can change (email changed)	immutable
Size/speed	sometimes large/string-based	compact INT/BIGINT
Leaks meaning	yes (PII in FK)	no

⚠️ Gotcha: a natural key may turn out to be not as unique/immutable as it seems (passports get reissued, emails get reused). A surrogate is safer as a PK, but you should still put a UNIQUE constraint on the natural attribute for integrity.

Short answer: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK constrain permitted data. DEFAULT supplies a value when omitted; it is not a PostgreSQL integrity constraint and does not reject an explicitly supplied value.

In detail:

CREATE TABLE products (
  id     INT PRIMARY KEY,
  name   VARCHAR(255) NOT NULL,
  sku    VARCHAR(50)  UNIQUE,
  price  NUMERIC(10,2) NOT NULL CHECK (price >= 0),
  status VARCHAR(20)  DEFAULT 'active'
                      CHECK (status IN ('active','archived')),
  cat_id INT REFERENCES categories(id)
);

⚠️ Gotcha: a CHECK against NULL passes (NULL doesn't violate a CHECK, because the condition evaluates to UNKNOWN, not FALSE). To forbid NULL you need a separate NOT NULL.

Short answer: when a parent row is deleted, CASCADE automatically deletes the children; there are also RESTRICT/NO ACTION (forbid), SET NULL, and SET DEFAULT.

In detail:

CREATE TABLE order_items (
  id       INT PRIMARY KEY,
  order_id INT REFERENCES orders(id) ON DELETE CASCADE,  -- delete the items with the order
  user_id  INT REFERENCES users(id)  ON DELETE SET NULL  -- null out the reference
);

FK options:

• CASCADE — delete/update the children.
• RESTRICT / NO ACTION — forbid deleting the parent while children exist.
• SET NULL — set NULL in the child column (the column must allow NULL).
• SET DEFAULT — set the default value.

⚠️ Gotcha: CASCADE is dangerous — a bulk delete can silently wipe out huge subtrees of data. Many teams prefer RESTRICT plus explicit deletion in code. Cascades also complicate debugging and can cause deadlocks under concurrent deletes.

In PostgreSQL, NO ACTION may be deferred when the FK is declared DEFERRABLE; RESTRICT cannot. SET NULL/SET DEFAULT still have to satisfy the child column constraints and the FK, so an invalid default does not bypass referential integrity.

Short answer: ACID — the guarantees of reliable transactions: Atomicity (all-or-nothing), Consistency (rules stay satisfied), Isolation (concurrent transactions are isolated), Durability (committed data survives).

In detail:

• Atomicity. A transaction either runs entirely or not at all. If there's an error midway, ROLLBACK undoes all changes. The classic example is a money transfer: the debit and the credit either both happen or neither does.

• Consistency. A transaction moves the database from one valid state to another without violating constraints (FK, CHECK, UNIQUE). This is about data integrity according to the declared rules.

• Isolation. Concurrent transactions don't see each other's intermediate results, behaving as if they ran sequentially (the degree depends on the isolation level).

• Durability. After COMMIT, the data survives a crash/restart (thanks to the WAL/redo log written to disk).

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- atomic: both updates or none

⚠️ Gotcha: don't confuse Consistency in ACID (satisfying constraints within a single database) with Consistency in the CAP theorem (replica agreement in a distributed system) — they're different things.

Short answer: a transaction is an atomic unit of work. BEGIN starts it, COMMIT commits it, ROLLBACK rolls it back, and SAVEPOINT sets a point for partial rollback.

In detail:

BEGIN;
  INSERT INTO orders(id, customer_id) VALUES (1, 42);
  SAVEPOINT after_order;             -- rollback point
  INSERT INTO order_items(order_id, product) VALUES (1, 'X');
  -- error/changed our mind — roll back only to the savepoint
  ROLLBACK TO SAVEPOINT after_order;
  INSERT INTO order_items(order_id, product) VALUES (1, 'Y');
COMMIT;

⚠️ Gotcha: a lingering open transaction holds locks and prevents VACUUM from cleaning up old row versions (in PostgreSQL — bloat). Always close your transactions. A client's autocommit mode commits each statement separately — watch that setting.

Short answer: dirty read (reading uncommitted data), non-repeatable read (re-read yields a different value), phantom read (re-read produces new rows), lost update (an overwritten update).

In detail:

• Dirty read — T1 reads data that T2 changed but hasn't committed yet; T2 rolls back → T1 read "dirt."
• Non-repeatable read — T1 reads a row twice; between the reads T2 changed and committed it → different values.
• Phantom read — T1 runs the same query with a condition twice; between them T2 INSERTED matching rows → "phantoms" appeared.
• Lost update — T1 and T2 both read a value, both add to it; whoever wrote last overwrote the other's change.

⚠️ Gotcha: lost update isn't classified separately in the standard, but in practice it's critical. It's solved with SELECT ... FOR UPDATE, an atomic UPDATE ... SET x = x + 1, or optimistic locking with a version column.

Short answer: Read Uncommitted, Read Committed, Repeatable Read, Serializable — the higher the level, the fewer anomalies, but more locking/rollbacks.

In detail — table (per the SQL standard):

Isolation level	Dirty read	Non-repeatable read	Phantom read
Read Uncommitted	possible	possible	possible
Read Committed	no	possible	possible
Repeatable Read	no	no	possible*
Serializable	no	no	no

* By the standard, phantoms are possible at Repeatable Read, but in PostgreSQL the Repeatable Read level is implemented via snapshot isolation and excludes phantoms too; updating a row changed by another transaction since the snapshot raises a serialization error.

BEGIN ISOLATION LEVEL SERIALIZABLE;
  -- ...
COMMIT;

Defaults: PostgreSQL and Oracle — Read Committed; MySQL/InnoDB — Repeatable Read.

⚠️ Gotcha: implementations differ from the standard. For example, in MySQL Repeatable Read, ordinary snapshot reads exclude later commits by other transactions, while locking reads use current data; gap locks can contribute to deadlocks. Serializable in PostgreSQL (SSI) may roll back transactions with a could not serialize access error — the application must be able to retry.

PostgreSQL treats READ UNCOMMITTED as READ COMMITTED. Repeatable Read still permits serialization anomalies such as write skew: two transactions can update different rows and jointly break a multi-row rule. SERIALIZABLE detects such dangerous combinations; retry the entire transaction from a fresh snapshot on SQLSTATE 40001.

Short answer: ACID describes transaction guarantees; the isolation level specifies which concurrent histories are allowed. Choose a level and write protocol that preserve the operation's invariants, then measure their cost.

PostgreSQL READ COMMITTED gives each statement a fresh snapshot. That can be sufficient for an atomic conditional update, but a multi-statement report may observe different committed states. REPEATABLE READ gives a stable snapshot, yet still permits write skew. SERIALIZABLE makes committed results equivalent to some serial order and may abort a transaction that must be retried.

Money does not become safe just by raising a setting: define constraints, idempotency, multi-row rules and retry behavior. Conversely, SERIALIZABLE does not inevitably destroy throughput; contention and workload determine its cost.

⚠️ Gotcha: retry the entire failed transaction with a fresh snapshot. External effects such as sending a payment must remain safe across that retry.

Short answer: pessimistic — lock the row up front (SELECT ... FOR UPDATE), assuming a conflict is likely; optimistic — hold no lock across the earlier read and user decision, then check for a change on write (version/timestamp), and on conflict roll back/retry.

In detail:

Pessimistic:

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- lock the row
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Optimistic (via a version column):

-- read version = 5
UPDATE accounts
SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = 5;     -- if 0 rows changed → someone beat us → retry

	Pessimistic	Optimistic
When it's better	high contention for the row	rare conflicts
Cost	locks, deadlock risk	retries on conflict
Mechanism	FOR UPDATE, locks	version/timestamp

⚠️ Gotcha: FOR UPDATE holds the lock until the end of the transaction — long transactions block others. Optimistic locking requires the application to be able to retry a failed update.

Optimistic means no lock held between the earlier read and the later user decision; the conditional UPDATE still locks rows while it executes. A version protects against concurrent changes, not business invalidity: re-check balance/stock constraints when retrying.

Short answer: a deadlock is two transactions mutually waiting on each other's locks (T1 holds A, waits for B; T2 holds B, waits for A). The DBMS detects the cycle and rolls back one of the transactions.

In detail:

T1: LOCK A ... LOCK B   (waits for B)
T2: LOCK B ... LOCK A   (waits for A)   → deadlock

Prevention measures:

• Acquire resources in a SINGLE order (for example, always by ascending id).
• Keep transactions short.
• Lower the isolation level where acceptable.
• Be able to retry a transaction rolled back due to a deadlock.

⚠️ Gotcha: deadlocks happen on a single table too — for example, when different queries update several rows in a different order, or due to gap locks at the Repeatable Read level in MySQL.

Short answer: NULL means "unknown," so a comparison with it yields not TRUE/FALSE but UNKNOWN. NULL = NULL → UNKNOWN, and the row doesn't make it into the WHERE result.

In detail: SQL uses TRUE/FALSE/UNKNOWN logic.

SELECT NULL = NULL;     -- NULL (UNKNOWN), NOT TRUE
SELECT NULL <> 5;       -- NULL
SELECT * FROM t WHERE col = NULL;     -- won't find NULLs (always UNKNOWN)
SELECT * FROM t WHERE col IS NULL;    -- the correct way

Table for AND/OR:

TRUE  AND NULL = NULL
FALSE AND NULL = FALSE
TRUE  OR  NULL = TRUE
FALSE OR  NULL = NULL

⚠️ Gotcha: WHERE col != 'x' will NOT return rows where col IS NULL (because NULL != 'x' → UNKNOWN). If you also want the NULLs — write WHERE col != 'x' OR col IS NULL or WHERE col IS DISTINCT FROM 'x'.

Short answer: COALESCE(a, b, ...) returns the first non-NULL argument; NULLIF(a, b) returns NULL if a = b, otherwise a.

In detail:

-- Substitute a default value in place of NULL
SELECT COALESCE(phone, 'not provided') FROM users;

-- Guard against division by zero: NULLIF(x,0) → NULL, division yields NULL instead of an error
SELECT total / NULLIF(count, 0) AS avg_safe FROM stats;

⚠️ Gotcha: COALESCE coerces all arguments to a single type — incompatible types raise an error. Also, when aggregating, remember: SUM/AVG ignore NULL themselves, and AVG divides by the number of NON-NULL values (not by all rows).

Short answer: INSERT adds rows, UPDATE changes existing ones, DELETE removes them.

In detail:

INSERT INTO users (id, name) VALUES (1, 'Anna'), (2, 'Boris');

UPDATE users SET name = 'Anna A.' WHERE id = 1;

DELETE FROM users WHERE id = 2;

⚠️ Gotcha: UPDATE/DELETE without WHERE affect all eligible rows. Check the predicate before executing. PostgreSQL TRUNCATE is transactional and can be rolled back; it takes ACCESS EXCLUSIVE, fires ON TRUNCATE triggers rather than ON DELETE triggers, and is not MVCC-safe for old snapshots. Other DBMSs have different transaction rules.

Short answer: UPSERT = INSERT, and on a uniqueness conflict — UPDATE (or nothing). In PostgreSQL it's INSERT ... ON CONFLICT; in the standard/Oracle/SQL Server it's MERGE.

In detail:

PostgreSQL:

INSERT INTO counters (key, value) VALUES ('hits', 1)
ON CONFLICT (key)
DO UPDATE SET value = counters.value + EXCLUDED.value;   -- increment if present

-- or just ignore the duplicate:
INSERT INTO users (email, name) VALUES ('a@b.c', 'A')
ON CONFLICT (email) DO NOTHING;

MERGE (SQL standard):

MERGE INTO target t
USING source s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.val = s.val
WHEN NOT MATCHED THEN INSERT (id, val) VALUES (s.id, s.val);

⚠️ Gotcha: ON CONFLICT requires a matching unique index/constraint on the specified columns. EXCLUDED holds the values that were being inserted. MySQL has its own INSERT ... ON DUPLICATE KEY UPDATE.

PostgreSQL also supports MERGE (PG15+), but MERGE is not an interchangeable concurrency-safe UPSERT: a concurrent insert can cause a uniqueness error. For a key-based insert-or-update under concurrent writers, PostgreSQL ON CONFLICT DO UPDATE provides the specific atomic guarantee.

Short answer: a plain VIEW is a saved query (computed on every access, evaluated using the current query snapshot); a materialized view physically stores the result (fast reads, but the data must be refreshed).

In detail:

-- A plain view: a named subquery, no data stored
CREATE VIEW active_users AS
SELECT id, name FROM users WHERE active = TRUE;

-- Materialized: the result is stored on disk
CREATE MATERIALIZED VIEW dept_stats AS
SELECT dept, COUNT(*) cnt, AVG(salary) avg_sal FROM employees GROUP BY dept;

REFRESH MATERIALIZED VIEW dept_stats;             -- refresh (locks reads)
REFRESH MATERIALIZED VIEW CONCURRENTLY dept_stats; -- allows SELECT; needs a suitable full UNIQUE index

⚠️ Gotcha: data in a materialized view is stale until a REFRESH — don't use it for real-time. A plain VIEW doesn't speed up a heavy query (it runs every time) — it's an abstraction, not a cache. Updatability of a VIEW (INSERT/UPDATE through it) is limited to simple views.

A normal view reads according to the current transaction snapshot, not necessarily the latest wall-clock commit. CONCURRENTLY requires an already populated materialized view and a UNIQUE index on plain columns covering all rows; refreshes of the same view are serialized.

Short answer: an index is a structure (usually a B-tree) that speeds up search/sorting by columns at the cost of slower writes and extra space.

In detail: it speeds up WHERE, JOIN by key, ORDER BY, and UNIQUE checks. It slows down INSERT/UPDATE/DELETE (the index has to be maintained) and takes up disk.

CREATE INDEX idx_emp_dept ON employees(dept_id);
CREATE INDEX idx_emp_dept_salary ON employees(dept_id, salary);  -- composite

⚠️ Gotcha: a plain B-tree usually cannot narrow a search on lower(name) or a leading-wildcard pattern. An expression index or an appropriate pg_trgm index can. Returning a large fraction of rows can favor a sequential scan; having few distinct values alone does not rule out an index on a rare value. Verify with EXPLAIN.

Short answer: DDL — defining structure; DML — working with data; DCL — privileges; TCL — transaction control.

In detail:

Category	Expansion	Commands	Purpose
DDL	Data Definition Language	CREATE, ALTER, DROP, TRUNCATE	object structure
DML	Data Manipulation Language	SELECT*, INSERT, UPDATE, DELETE	data
DCL	Data Control Language	GRANT, REVOKE	access privileges
TCL	Transaction Control Language	BEGIN, COMMIT, ROLLBACK, SAVEPOINT	transactions

* SELECT is sometimes put in a separate category, DQL (Data Query Language).

⚠️ Gotcha: in most DBMSs, DDL triggers an implicit COMMIT (for example, MySQL, Oracle) — you can't roll back a CREATE TABLE with ROLLBACK. PostgreSQL is a pleasant exception: DDL is transactional and can be rolled back.

Short answer: First decide whether you want every employee earning the second distinct non-NULL salary, or one employee in a sorted list. They are different questions.

-- All employees with the second distinct salary; ties are preserved.
SELECT name, salary FROM (
  SELECT name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
  WHERE salary IS NOT NULL
) ranked
WHERE rnk = 2;

-- Only the salary value, not the employees:
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- DISTINCT does handle ties for the salary-value question:
SELECT DISTINCT salary FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC LIMIT 1 OFFSET 1;

If fewer than two distinct non-NULL salaries exist, the rank/DISTINCT queries return no row and MAX returns NULL. For the second employee instead, use ORDER BY salary DESC NULLS LAST, id LIMIT 1 OFFSET 1 with a unique id.

⚠️ Gotcha: PostgreSQL sorts NULL first by default with DESC. Exclude unknown salaries or choose NULLS LAST explicitly; a plain OFFSET over employees does not find the second distinct salary.

Short answer: GROUP BY the relevant columns plus HAVING COUNT(*) > 1.

In detail:

-- Find duplicate emails and their counts
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Delete duplicates, keeping the row with the lowest id (PostgreSQL)
DELETE FROM users u
USING users d
WHERE u.email IS NOT DISTINCT FROM d.email AND u.id > d.id;

-- Alternative via a window function
WITH ranked AS (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
  FROM users
)
DELETE FROM users WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

⚠️ Gotcha: when looking for duplicates across several columns, group by all of them. Before deleting, always run a SELECT first to make sure you've picked the "extra" rows correctly.

These examples treat two NULL emails as duplicates. If NULL means an unknown email that should be kept independently, filter email IS NOT NULL in both the detection and deletion rules.

Short answer: a window function ROW_NUMBER/RANK with PARTITION BY group ORDER BY metric, then a filter on the rank.

In detail:

-- Top 3 highest-paid employees in each department
WITH ranked AS (
  SELECT name, dept, salary,
         ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC NULLS LAST, id) AS rn
  FROM employees
)
SELECT name, dept, salary
FROM ranked
WHERE rn <= 3;

⚠️ Gotcha: ROW_NUMBER gives up to N rows per group. RANK includes ties at the Nth position; DENSE_RANK selects the top N distinct metric values and can yield a different result. For tie-preserving ranks, rank by the metric alone: adding unique id to that window ORDER BY eliminates the ties. Filter the computed rank in the outer query.

Short answer: A relational database models tables and relationships with declarative constraints and SQL. NoSQL is a broad family: key-value, document, wide-column and graph systems. Choose a concrete engine by its model and guarantees, not the label.

PostgreSQL and MySQL are strong candidates for related business entities, joins and changing queries. PostgreSQL also supports JSONB for flexible attributes. MongoDB supports document validation, joins via lookup and multi-document transactions. Key-value and wide-column engines often optimize predictable partition-key access; graph engines emphasize traversals.

Compare transaction scope and isolation, read consistency, query flexibility, partition-key distribution, durability and operational cost. Horizontal scale is possible in both families, with different coordination and query tradeoffs.

⚠️ Gotcha: NoSQL does not universally mean schemaless, faster, non-ACID or free horizontal scaling. A new engine should solve a measured access/model problem without silently weakening the required invariants.