Q: What is the difference between a primary key and a unique constraint?

Both enforce uniqueness, but a table has **one primary key** (not null, the main identifier, often clustered) while it can have **multiple unique constraints**, which may allow a NULL (depending on the DB). Use unique constraints for alternate keys like email.

Q: What is the logical order of execution of a SQL query?

Logically: **FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT**. This is why column aliases defined in SELECT can't be used in WHERE (evaluated earlier) but can be used in ORDER BY (evaluated later).

Q: What is the CASE expression?

**CASE** is SQL's conditional: `CASE WHEN cond THEN a ELSE b END`. Use it in SELECT for derived columns, in ORDER BY for custom ordering, or with aggregates for conditional/pivot-style counts (`SUM(CASE WHEN ... THEN 1 ELSE 0 END)`).

Q: What is the difference between WHERE and HAVING?

**WHERE** filters individual rows before grouping; **HAVING** filters groups after `GROUP BY` and can use aggregate functions (e.g. `HAVING COUNT(*) > 5`). You can't use aggregates in WHERE.

Question 1

What is the difference between a primary key and a foreign key?

Accepted Answer

A **primary key** uniquely identifies each row in a table (unique + not null). A **foreign key** references a primary key in another table, enforcing **referential integrity** so you can't reference a non-existent row.

Question 2

Top-N per group with RANK() OVER (PARTITION BY) — how do you get top 2 per department without collapsing rows?

Accepted Answer

`RANK() OVER (PARTITION BY dept ORDER BY score DESC)` numbers rows *within each partition* without collapsing them; wrap in a CTE and filter `WHERE rnk <= N` for top-N per group. Ties share a rank and leave gaps — use `DENSE_RANK` or `ROW_NUMBER` when gaps matter.

Question 3

What is the difference between a primary key and a unique constraint?

Accepted Answer

Both enforce uniqueness, but a table has **one primary key** (not null, the main identifier, often clustered) while it can have **multiple unique constraints**, which may allow a NULL (depending on the DB). Use unique constraints for alternate keys like email.

Question 4

What is the logical order of execution of a SQL query?

Accepted Answer

Logically: **FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT**. This is why column aliases defined in SELECT can't be used in WHERE (evaluated earlier) but can be used in ORDER BY (evaluated later).

Question 5

What is the CASE expression?

Accepted Answer

**CASE** is SQL's conditional: `CASE WHEN cond THEN a ELSE b END`. Use it in SELECT for derived columns, in ORDER BY for custom ordering, or with aggregates for conditional/pivot-style counts (`SUM(CASE WHEN ... THEN 1 ELSE 0 END)`).

Question 6

What is the difference between WHERE and HAVING?

Accepted Answer

**WHERE** filters individual rows before grouping; **HAVING** filters groups after `GROUP BY` and can use aggregate functions (e.g. `HAVING COUNT(*) > 5`). You can't use aggregates in WHERE.

Question 7

What are the different types of SQL joins?

Accepted Answer

**INNER JOIN** returns rows matching in both tables. **LEFT JOIN** returns all left rows plus matches (NULLs where none). **RIGHT JOIN** is the mirror. **FULL OUTER JOIN** returns all rows from both, matched where possible. **CROSS JOIN** is the Cartesian product.

Question 8

What does DISTINCT do and what is its cost?

Accepted Answer

**DISTINCT** removes duplicate rows from a result set by sorting or hashing — which adds cost on large sets. If duplicates come from a join, fixing the join or using `EXISTS` is often better than masking them with DISTINCT.

Question 9

When would you use a transaction explicitly?

Accepted Answer

Wrap multiple related statements in a **transaction** when they must succeed or fail together — e.g. transferring money (debit one account, credit another). It guarantees atomicity and a consistent state, rolling back all changes if any step fails.

Question 10

Seq Scan vs Index Scan vs Index-Only Scan — when does Postgres choose each?

Accepted Answer

The planner estimates cost from `seq_page_cost` vs `random_page_cost` × selectivity. Highly selective (`<5%` rows) → `Index Scan`; moderately selective → `Bitmap Heap Scan`; all columns in index + `VACUUM` clean → `Index-Only Scan`; otherwise → `Seq Scan`.

SQLDECODED.

Transmission log

What is the difference between a primary key and a foreign key?

Top-N per group with RANK() OVER (PARTITION BY) — how do you get top 2 per department without collapsing rows?

What is the difference between a primary key and a unique constraint?

What is the logical order of execution of a SQL query?

What is the CASE expression?

What is the difference between WHERE and HAVING?

What are the different types of SQL joins?

What does DISTINCT do and what is its cost?

When would you use a transaction explicitly?

Seq Scan vs Index Scan vs Index-Only Scan — when does Postgres choose each?

UPSERT with INSERT … ON CONFLICT — how do you insert or update atomically without races?

What do COALESCE and NULLIF do?

What is a CHECK constraint?

What is the difference between CHAR, VARCHAR, and TEXT?

Advanced UPSERT patterns — conditional updates, xmax detection, and soft-delete-aware indexes?

Covering indexes (INCLUDE) and Bitmap Heap Scans — how do you eliminate heap fetches in 2025?

What is a subquery and what is a correlated subquery?

How do you read an EXPLAIN query plan?

LATERAL JOIN — how do you fetch the top 3 orders per customer efficiently?

What is a covering index?

How does GROUP BY work with aggregate functions?

What are COMMIT, ROLLBACK, and SAVEPOINT?

How do you aggregate values into a single string?

What is the difference between COUNT(*), COUNT(col), and COUNT(DISTINCT col)?

EXPLAIN ANALYZE shows Seq Scan on a 10M-row table — how do you diagnose and fix it?

Why might EXISTS outperform a JOIN for existence checks?

How does SQL handle NULL values?

What is a view and a materialized view?

What are transaction isolation levels?

What are foreign key cascade options?

JSONB querying and GIN indexing — how do you query and index schemaless data efficiently?

What is the difference between EXISTS and IN?

Filtered vector search with pgvector — how do you combine WHERE category = 'X' with ORDER BY embedding <=> $1?

What is a CTE (Common Table Expression)?

How do you update a table based on another table?

What is an UPSERT?

What are ACID properties?

What is the difference between UNION and UNION ALL?

FOR UPDATE vs FOR UPDATE NOWAIT vs FOR UPDATE SKIP LOCKED — which to use when?

How do you read EXPLAIN ANALYZE — cost, actual time, rows, and Buffers?

How do you paginate efficiently in SQL?

What are the common aggregate functions?

What is the difference between DELETE, TRUNCATE, and DROP?

Transaction isolation levels in Postgres — what anomalies does each level prevent?

What is a stored procedure?

What is a self join?

Recursive CTE for an org chart — how do you list all reports (direct + indirect) of a manager?

What is an index and how does it speed up queries?

What do GROUP BY ROLLUP and CUBE do?

What is a database trigger?

What is database normalization?

Serializable isolation pitfalls — write skew, retry storms, and when advisory locks beat SSI?

JSONB vs normalized columns vs EAV — how do you model flexible attributes without killing query performance?

RANK() vs DENSE_RANK() vs ROW_NUMBER() — when does the gap matter and how do you choose?

How do recursive CTEs work?

What do the LAG and LEAD window functions do?

What is a LATERAL join?

SKIP LOCKED for job queues — how do you build a concurrent queue without race conditions?

How do you optimize a slow SQL query?

What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?

How do you pivot rows into columns?

Why does column order matter in a composite index?

How do you find the Nth highest salary?

What are window functions?

pgvector cosine similarity index in 2025-26 — HNSW vs IVFFlat for semantic search at scale?

Recursive CTE with cycle detection — how do you handle circular manager references and produce a search order?

What is a deadlock and how do you prevent it?

LATERAL vs Window Function vs Correlated Subquery for top-N per group — which scales when?

What is the difference between a clustered and non-clustered index?

What is table partitioning?

Difficulty mix

Tested topics

Decryption key