Question 1

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

Accepted Answer

**Target Audience:** Beginners who know what a table is, and candidates who want the NULL details interviewers like to probe.
**Difficulty:** Easy
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. SQL Server and MySQL behaviour is cited from their documentation.

> **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact wor

Question 2

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

Accepted Answer

**Target Audience:** Beginners who can write a simple SELECT and want to understand why some queries fail with "column does not exist".
**Difficulty:** Easy
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15. MySQL and SQL Server differences are noted where they matter.

> **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer

Question 3

What does DISTINCT do and what is its cost?

Accepted Answer

**Target Audience:** Beginners who use `SELECT DISTINCT` and want to know what it really does and why it can make a query slow.
**Difficulty:** Easy
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, which runs the examples on this page) and PostgreSQL 16.15; plans were captured on both. MySQL notes are marked as not run.

> **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. All output

Question 4

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

Accepted Answer

**Target Audience:** Beginners who have seen `GROUP BY` and want to return the best N rows of every group, and candidates preparing for the tie and performance follow-ups.
**Difficulty:** Medium
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. The window-function pattern is standard SQL and also works in MySQL 8.0+ and SQL Server.

> **How to read this doc:** Every idea is explained in plain language first

Question 5

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

Accepted Answer

**Target Audience:** Developers who already use `INSERT ... ON CONFLICT` and now hit the follow-ups: stale overwrites, "was it created?", and soft-deleted rows.
**Difficulty:** Medium
**Engines:** PostgreSQL 16.15 (`xmax`, locking behaviour) and SQLite 3.45.0 through sql.js 1.10.2 (the runnable examples). PostgreSQL 17 `MERGE ... RETURNING` is cited, not run (the local server is 16).

> **How to read this doc:** Plain language first, then a `📌 Interview term:` callout with the precise vocabular

Question 6

How do you aggregate values into a single string?

Accepted Answer

**Target Audience:** Beginners who know `GROUP BY` and want to produce "one row with a list", and candidates who need the dialect differences that interviewers love to ask.
**Difficulty:** Medium
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL, SQL Server and Oracle syntax is cited from their documentation, not run.

> **How to read this doc:** Every idea is explained in plain language first, then t

Question 7

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

Accepted Answer

**Target Audience:** Beginners who have written `COUNT(*)` and want to know why a report's numbers disagree; candidates preparing for the classic COUNT question.
**Difficulty:** Medium
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16. MySQL's multi-column form is cited from its manual.

> **How to read this doc:** Each idea is explained in plain language first, then tagged with `📌 Interview term:` so you learn the words an interview

Question 8

Why might EXISTS outperform a JOIN for existence checks?

Accepted Answer

**Target Audience:** Developers who filter with joins out of habit and want to understand duplicates, semi joins and the plans behind "EXISTS is faster"; beginners who know what a JOIN is.
**Difficulty:** Medium
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page; MySQL notes cite its manual.

> **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so

Question 9

How does SQL handle NULL values?

Accepted Answer

**Target Audience:** Beginners who know SELECT and WHERE, and anyone who has been surprised by a query that "lost" rows.
**Difficulty:** Medium
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16. MySQL, SQL Server and Oracle differences are cited where they matter.

> **How to read this doc:** Each idea is explained in plain language first, then tagged with `📌 Interview term:` so you learn the words an interviewer expects. Every resul

Question 10

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

Accepted Answer

**Target Audience:** Developers running semantic search on PostgreSQL with pgvector, and candidates preparing for backend interviews
**Difficulty:** Medium
**Engines:** PostgreSQL with pgvector. pgvector is not installed on the verification machine, so the behaviour is shown with plain-SQL simulations that ran on sql.js 1.10.2 (SQLite) and a PostgreSQL 16.15 plan; pgvector SQL is marked "not run here" and its settings are cited from the [pgvector README](https://github.com/pgvector/pgvector).

>

SQLDECODED.

Easy

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

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

What does DISTINCT do and what is its cost?

Medium

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

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

How do you aggregate values into a single string?

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

Why might EXISTS outperform a JOIN for existence checks?

How does SQL handle NULL values?

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

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

Hard

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

How do you optimize a slow SQL query?

How do you pivot rows into columns?

Why does column order matter in a composite index?