Question 1

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 2

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

Accepted Answer

**Target Audience:** Beginners choosing types for their first tables, and candidates who need the engine-specific facts behind "it depends".
**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 (UTF-8 database), both run for this page. MySQL and SQL Server facts 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 a

Question 3

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 4

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 5

What is the difference between EXISTS and IN?

Accepted Answer

**Target Audience:** Beginners who know a basic WHERE clause and have seen both forms in code reviews; anyone who has heard "EXISTS is faster" and wants to know when that is true.
**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 behaviour is cited from its manual.

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

Question 6

What is a CTE (Common Table Expression)?

Accepted Answer

**Target Audience:** Beginners who can write a subquery and want cleaner multi-step queries, and candidates preparing for the performance follow-up.
**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. CTEs are standard SQL; MySQL added them in 8.0. Version facts are cited inline.

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

Question 7

What is the difference between NOT IN and NOT EXISTS when NULLs are involved?

Accepted Answer

**Target Audience:** Anyone who has written "find the rows that have no match" and been surprised by an empty result; beginners meeting NULL logic for the first time.
**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 you also learn the exa

Question 8

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

Accepted Answer

**Target Audience:** Developers who have added an index and been surprised by the plan, and candidates who need to explain the planner choice with real thresholds instead of "it depends".
**Difficulty:** Medium
**Engines:** PostgreSQL 16.15 for every plan (a deterministic 100000-row table generated inside each example), plus SQLite 3.45.0 through sql.js 1.10.2 for the runnable example. The exact switch points are from this data and these settings; other data will switch at other points.

> **How

Question 9

What is a self join?

Accepted Answer

**Target Audience:** Beginners who know a basic join and want to see a table joined to itself, and candidates preparing for the 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. Self joins are plain standard SQL and work the same way in MySQL and SQL Server.

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

Question 10

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

Accepted Answer

**Target Audience:** Developers who know JOINs and have seen a basic recursive CTE; the applied interview version of "walk a hierarchy".
**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; every query below was run on both and returned the same rows.

> **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 interview

SQLDECODED.

Easy

When would you use a transaction explicitly?

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

Medium

How do you aggregate values into a single string?

Why might EXISTS outperform a JOIN for existence checks?

What is the difference between EXISTS and IN?

What is a CTE (Common Table Expression)?

What is the difference between NOT IN and NOT EXISTS when NULLs are involved?

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

What is a self join?

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

Hard

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?

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