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

What is a CHECK constraint?

Accepted Answer

**Target Audience:** Beginners who have written CREATE TABLE and want the database to refuse bad data, and candidates who need the NULL and 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 learn the exa

Question 4

What is database normalization?

Accepted Answer

**Target Audience:** Beginners who have built a few tables and want to understand why schemas are split the way they are, plus candidates who need to define 1NF to BCNF precisely.
**Difficulty:** Medium
**Engines:** The idea is engine independent. Every query here ran on SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples use) and PostgreSQL 16.15 with the same results; the one difference found is called out in section 4.

> **How to read this doc:** Each idea is first explained in pl

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

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

Accepted Answer

**Target Audience:** Beginners who need to remove data safely, and candidates preparing for the engine-specific follow-ups (transactions, locks, identity, space).
**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, including a two-session test on PostgreSQL. MySQL and SQL Server behaviour is cited from their manuals.

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

Question 7

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

Accepted Answer

**Target Audience:** Developers designing a catalogue, settings or metadata store where attributes vary per row, and candidates facing a schema design question that tests trade-offs rather than syntax.
**Difficulty:** Hard
**Engines:** PostgreSQL 16.15 for everything `jsonb`, run in a scratch database for this page. The EAV examples ran on SQLite 3.45.0 (sql.js 1.10.2, the page engine) and the type trap was also run on PostgreSQL 16 with the same answer. MySQL facts are cited.

> **How to read t

Question 8

What is table partitioning?

Accepted Answer

**Target Audience:** Developers whose biggest table keeps growing and who keep hearing "just partition it", and candidates who need to explain what partitioning does and does not solve.
**Difficulty:** Hard
**Engines:** PostgreSQL 16.15 (declarative partitioning; every plan and error here was produced on it) and SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable example uses; it has no partitioning, so the example builds it by hand). MySQL facts are from its manual and marked as not run here.

SQLDECODED.

Easy