Question 1

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

Accepted Answer

**Target Audience:** Beginners meeting relational tables for the first time, and anyone who wants the precise rules an interviewer expects.
**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. MySQL and SQL Server differences are cited from their manuals.

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

Question 2

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 3

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 4

What is the CASE expression?

Accepted Answer

**Target Audience:** Beginners who can write SELECT, WHERE and GROUP BY and need "if this, then that" logic inside a query.
**Difficulty:** Easy
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, which runs the examples on this page) and PostgreSQL 16.15; SQL Server and MySQL notes are marked with their source.

> **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 and error

Question 5

What is the difference between WHERE and HAVING?

Accepted Answer

**Target Audience:** Beginners who have just met GROUP BY and are unsure which filter goes where.
**Difficulty:** Easy
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, which runs the examples on this page) and PostgreSQL 16.15; MySQL notes come from its manual and are marked.

> **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 and error messages were produced by actually

Question 6

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 7

How do you find duplicate rows in a table?

Accepted Answer

**Target Audience:** Beginners who know `SELECT` and want the standard duplicate-finding pattern, and candidates who want the details interviewers probe (NULLs, case, collation).
**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. MySQL behaviour is cited from its manual.

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

Question 8

When would you use a transaction explicitly?

Accepted Answer

**Target Audience:** Beginners who know `INSERT` and `UPDATE` and want to know when wrapping them in `BEGIN ... COMMIT` matters, and candidates who want concrete scenarios rather than a definition.
**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. Notes on MySQL and drivers are cited.

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

Question 9

What are NOT NULL, DEFAULT and other column constraints?

Accepted Answer

**Target Audience:** Beginners writing their first CREATE TABLE, and candidates who need the migration details that come up in senior interviews.
**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. Version facts are cited from the PostgreSQL and SQLite release notes.

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

Question 10

What is a CROSS JOIN and when is it useful?

Accepted Answer

**Target Audience:** Beginners who have seen INNER and LEFT JOIN and want to know what the "join with no condition" is for, plus candidates who want the engine details.
**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. MySQL behaviour is cited from its manual.

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

SQLDECODED.

Easy

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

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 does DISTINCT do and what is its cost?

How do you find duplicate rows in a table?

When would you use a transaction explicitly?

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?

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?

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?

Hard

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 is a LATERAL join?

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

How do you pivot rows into columns?

Why does column order matter in a composite index?