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

What is denormalization and when should you use it?

Accepted Answer

**Target Audience:** Developers who know what normalization is and now face slow reads, plus candidates who need to discuss read/write trade-offs and sync mechanisms precisely.
**Difficulty:** Medium
**Engines:** The SQLite examples ran on SQLite 3.45.0 (sql.js 1.10.2, the page engine). Triggers and materialized views are shown on PostgreSQL 16.15, run in a scratch database for this page. SQLite has triggers but no materialized views.

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

Question 4

What is the difference between OLTP and OLAP databases?

Accepted Answer

**Target Audience:** Beginners who hear "OLTP" and "OLAP" in architecture discussions, and candidates who need to explain why companies run a separate data warehouse.
**Difficulty:** Medium
**Engines:** The SQL examples run on SQLite 3.45.0 (sql.js 1.10.2, the engine this page uses) and returned the same rows on PostgreSQL 16.15; the plan comparison ran on PostgreSQL 16.15. Facts about warehouse products are cited from their documentation, not run here.

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

Question 5

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

SQLDECODED.

Easy

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

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

Medium

What is denormalization and when should you use it?

What is the difference between OLTP and OLAP databases?

Hard

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