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

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 5

What are COMMIT, ROLLBACK, and SAVEPOINT?

Accepted Answer

**Target Audience:** Beginners who have seen `BEGIN` and `COMMIT` and want to know exactly what each command does, and candidates preparing for the savepoint and error-recovery 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. `SAVEPOINT`, `ROLLBACK TO SAVEPOINT` and `RELEASE SAVEPOINT` are standard SQL and also exist in MySQL InnoDB; SQL Server spells it `SAVE TRANSACTION

Question 6

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 7

What is the difference between UNION and UNION ALL?

Accepted Answer

**Target Audience:** Beginners combining results from two tables for the first time, and candidates who need the exact rules and the cost.
**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.

> **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. Every result and error m

Question 8

How do you delete duplicate rows but keep one?

Accepted Answer

**Target Audience:** Developers who can find duplicates and now need to remove them without losing data, and candidates preparing for the classic "delete duplicates, keep one" 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.15, both run for this page. MySQL and SQL Server forms are cited, not run.

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

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

How do you solve gaps-and-islands problems in SQL?

Accepted Answer

**Target Audience:** Learners who know `ROW_NUMBER()` and `LAG()` and want to apply them to runs and holes in data, and candidates preparing for one of the classic hard SQL interview puzzles.
**Difficulty:** Hard
**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 functions are standard SQL; the date arithmetic is engine-specific and is shown for both.

> **How to read this doc:** Every idea is exp

SQLDECODED.

Easy