Question 1

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 2

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 3

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 4

What is SQL injection and how do parameterized queries prevent it?

Accepted Answer

**Target Audience:** Beginners learning SQL, and developers preparing for backend or security interview questions
**Difficulty:** Easy
**Engines:** SQLite (examples run on sql.js 1.10.2 in the page and checked with Python sqlite3 on SQLite 3.45.1) and PostgreSQL 16.15. The idea is the same in every database and every driver.

> **How to read this doc:** Every new term is explained in plain words first. When it is a term interviewers expect you to know, it is tagged `📌 Interview term:` so you ca

Question 5

What do COALESCE and NULLIF do?

Accepted Answer

**Target Audience:** Beginners who have met NULL and want the two tools that tame it, and candidates who need 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. SQL Server and MySQL variants are cited from their documentation.

> **How to read this doc:** Each idea is explained in plain language first, then tagged with `📌 Interview term:` so you learn the words an interviewer expects. Every re

Question 6

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 7

Top-N per group with RANK() OVER (PARTITION BY) — how do you get top 2 per department without collapsing rows?

Accepted Answer

**Target Audience:** Beginners who have seen `GROUP BY` and want to return the best N rows of every group, and candidates preparing for the tie and performance 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. The window-function pattern is standard SQL and also works in MySQL 8.0+ and SQL Server.

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

Question 8

Covering indexes (INCLUDE) and Bitmap Heap Scans — how do you eliminate heap fetches in 2025?

Accepted Answer

**Target Audience:** Developers who already know [what a covering index is](/interview-question/what-is-a-covering-index) and now face a real PostgreSQL plan with a Bitmap Heap Scan they want gone.
**Difficulty:** Medium
**Engines:** PostgreSQL 16.15 for every plan here (100000 generated rows per example, run in a throwaway database), and SQLite 3.45.0 through sql.js 1.10.2 for the runnable example. Version facts for PostgreSQL 11 and 18 are cited from the release notes.

> **How to read this do

Question 9

How do you read an EXPLAIN query plan?

Accepted Answer

**Target Audience:** Beginners who have been handed an `EXPLAIN` output and do not know where to start, and candidates who need to walk an interviewer through a plan line by line.
**Difficulty:** Medium
**Engines:** PostgreSQL 16.15 for every PostgreSQL plan (tables generated inside each example, small enough that statistics are exact), SQLite 3.45.0 through sql.js 1.10.2 for the runnable example. MySQL and SQL Server are described from their documentation, not run here.

> **How to read this do

Question 10

EXPLAIN ANALYZE shows Seq Scan on a 10M-row table — how do you diagnose and fix it?

Accepted Answer

**Target Audience:** Developers who have just seen `Seq Scan` in a plan and want a repeatable way to decide whether it is a problem, and candidates preparing for the "walk me through it" version of this question.
**Difficulty:** Medium
**Engines:** PostgreSQL 16.15 (all plans on this page were produced on it) and SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable example on this page uses). The diagnosis applies to MySQL and SQL Server too (`EXPLAIN` / actual execution plans); their plan word

SQLDECODED.

Easy

What is the logical order of execution of a SQL query?

What is the difference between WHERE and HAVING?

What are NOT NULL, DEFAULT and other column constraints?

What is SQL injection and how do parameterized queries prevent it?

What do COALESCE and NULLIF do?

What is a CHECK constraint?

Medium

Top-N per group with RANK() OVER (PARTITION BY) — how do you get top 2 per department without collapsing rows?

Covering indexes (INCLUDE) and Bitmap Heap Scans — how do you eliminate heap fetches in 2025?

How do you read an EXPLAIN query plan?

EXPLAIN ANALYZE shows Seq Scan on a 10M-row table — how do you diagnose and fix it?

What is a view and a materialized view?

What are foreign key cascade options?

Filtered vector search with pgvector — how do you combine WHERE category = 'X' with ORDER BY embedding <=> $1?

What is the difference between UNION and UNION ALL?

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?

What is a LATERAL join?

Why does column order matter in a composite index?

pgvector cosine similarity index in 2025-26 — HNSW vs IVFFlat for semantic search at scale?

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

What is table partitioning?