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
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;

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 condition on the right table in a LEFT JOIN must go in ON, not in WHERE, otherwise LEFT turns into INNER:

-- 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: in standard SQL, the SELECT can only use columns from GROUP BY or aggregates. SELECT name, dept, COUNT(*) ... GROUP BY dept is an error (name is neither grouped nor aggregated). PostgreSQL forbids it; MySQL (in non-strict mode) silently returns an arbitrary name.

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: you can't write WHERE AVG(salary) > 100000 — aggregates in WHERE are forbidden. Conversely, don't push simple row filters into HAVING — it's slower.

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: NOT IN with a subquery that may return NULL produces an EMPTY result (because of three-valued logic). Use NOT EXISTS instead of NOT IN.

Short answer: a subquery that references columns of the outer query and runs 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: in old PostgreSQL (<12) CTEs were an "optimization fence" (always materialized). Since PG 12+ they're inlined unless MATERIALIZED is specified. A CTE by itself does NOT speed things up — it's primarily about readability.

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
  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
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates ORDER BY level;

⚠️ Gotcha: with cycles in the data (A->B->A) the recursion will loop forever. Protection: cap the depth (WHERE level < 100) or keep an array of visited nodes and check via NOT = ANY(path).

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) — n rows back, with a default value instead of NULL.

⚠️ 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.

Short answer: 1NF — atomic values, no repeating groups; 2NF — 1NF + no partial dependency on part of a composite key; 3NF — 2NF + no transitive dependencies; BCNF — strict 3NF (every determinant is a superkey).

In depth:

The original bad table:

orders(order_id, product1, product2, customer_name, customer_city, city_zip)

1NF — atomicity. You can't store a list in a single cell (product1, product2). Split repeating groups into rows:

CREATE TABLE order_items (
  order_id INT,
  product  VARCHAR,
  qty      INT,
  PRIMARY KEY (order_id, product)
);

2NF — no partial dependency on PART of a composite key. If PK = (order_id, product), but customer_name depends only on order_id (part of the key), that's a violation. Extract it:

CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT);
CREATE TABLE order_items (order_id INT, product VARCHAR, qty INT,
                          PRIMARY KEY (order_id, product));

3NF — no transitive dependencies (a non-key attribute depends on another non-key attribute). customer_city depends on customer_id, and city_zip on customer_city -> transitive. Extract them:

CREATE TABLE customers (customer_id INT PRIMARY KEY, name VARCHAR, city_id INT);
CREATE TABLE cities    (city_id INT PRIMARY KEY, name VARCHAR, zip VARCHAR);

BCNF — every determinant is a superkey. It resolves the rare 3NF case where there are overlapping candidate keys and a non-key determinant. Example: a table (student, course, teacher) where teacher -> course (a teacher teaches exactly one course), but teacher is not a key -> BCNF is violated; split into (student, teacher) and (teacher, course).

⚠️ Gotcha: "3NF is enough for most cases" — yes, but don't confuse things: 2NF only matters with a COMPOSITE key (with a single-column PK there can be no partial dependencies).

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: a PK implicitly creates a unique index and NOT NULL. UNIQUE in most DBMSs allows multiple NULLs (NULL != NULL), whereas a PK does not.

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: the order of columns in a composite key/index matters for whether the index can be used. An index on (a, b) speeds up filtering by a and by (a, b), but NOT by b alone.

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, CHECK, DEFAULT — rules the DBMS enforces automatically.

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.

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; a lost update there raises a serialization error.

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

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

⚠️ Gotcha: implementations differ from the standard. For example, in MySQL Repeatable Read via MVCC doesn't see inserts, but gap locks can produce deadlocks. Serializable in PostgreSQL (SSI) may roll back transactions with a could not serialize access error — the application must be able to retry.

Short answer: ACID guarantees the reliability of an individual transaction; isolation levels are a trade-off between correctness under concurrency and performance.

In detail: strict Serializable behaves like sequential execution (no anomalies) but is expensive (locks/rollbacks). Weaker levels are faster but allow anomalies. You pick the minimum level at which your logic stays correct. For money operations — a high level or explicit locks; for analytics — Read Committed is usually enough.

⚠️ Gotcha: "I'll just set Serializable everywhere" — throughput drops sharply and deadlocks/retries grow. Isolation is chosen per specific operation.

Short answer: pessimistic — lock the row up front (SELECT ... FOR UPDATE), assuming a conflict is likely; optimistic — don't lock, but on write check whether it changed (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.

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 a WHERE affect the ENTIRE table. Before running, check the condition with a separate SELECT. Unlike DELETE, TRUNCATE clears the whole table quickly, but doesn't fire triggers and often can't be rolled back as flexibly.

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.

Short answer: a plain VIEW is a saved query (computed on every access, the data is always fresh); 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; -- without a lock (needs a 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.

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. (More in the PostgreSQL file.)

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: an index isn't used if a function is applied to the column (WHERE lower(name) = ... without a functional index), with LIKE '%abc' (leading wildcard), or with low selectivity (few unique values). Too many indexes hurt writes.

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: via DENSE_RANK, a subquery with MAX, or LIMIT 1 OFFSET 1.

In detail:

-- Approach 1: window function (most reliable, handles ties)
SELECT name, salary FROM (
  SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t WHERE rnk = 2;

-- Approach 2: subquery (the max among those below the global max)
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Approach 3: LIMIT/OFFSET (does NOT handle duplicate salaries!)
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

⚠️ Gotcha: a plain LIMIT 1 OFFSET 1 breaks when the top salaries are equal. Clarify whether you need the "second-highest SALARY" (use DISTINCT/DENSE_RANK) or "the second employee in order." DENSE_RANK is more correct than ROW_NUMBER for "the N-th highest value."

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 = 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.

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) AS rn
  FROM employees
)
SELECT name, dept, salary
FROM ranked
WHERE rn <= 3;

⚠️ Gotcha: the choice of function depends on the tie semantics: ROW_NUMBER gives exactly 3 rows (arbitrarily among equals), while RANK/DENSE_RANK may return more on ties. Add a tiebreaker to ORDER BY. The filter on rn must be in a subquery/CTE — you can't use a window function in the main query's WHERE.

Short answer: relational (PostgreSQL, MySQL) — for structured data, complex relationships, transactions, and ACID; NoSQL (document, key-value, columnar, graph) — for flexible schema, horizontal scaling, and specific access patterns.

In detail:

Criterion	Relational (SQL)	NoSQL
Schema	strict, upfront	flexible/schemaless
Relationships/JOIN	a strong point	weak or none
Transactions/ACID	full-featured	limited (depends)
Scaling	vertical (+ replicas/sharding are harder)	horizontal by design
Queries	powerful SQL, aggregations	by key/access pattern
Example uses	finance, ERP, accounting	catalogs, sessions, logs, social graphs

Choice: if you need transactions, complex ad-hoc queries, and referential integrity → SQL. If you need a flexible schema, a huge volume of simple key-based operations, and horizontal scale → an appropriate NoSQL. A hybrid is often used (polyglot persistence).

⚠️ Gotcha: "NoSQL = always faster/more scalable" is a myth. Modern relational databases scale too (partitioning, replicas, extensions). NoSQL pays for scale with the loss of flexible queries and (often) strong consistency. Choose by access patterns and consistency requirements, not by hype.