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

What is the CASE expression?

Accepted Answer

**Target Audience:** Beginners who can write SELECT, WHERE and GROUP BY and need "if this, then that" logic inside a query.
**Difficulty:** Easy
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, which runs the examples on this page) and PostgreSQL 16.15; SQL Server and MySQL notes are marked with their source.

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

Question 4

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 5

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 6

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 7

What is a covering index?

Accepted Answer

**Target Audience:** Beginners who know what an index is and keep hearing "make it a covering index", plus candidates who need to prove it with a query plan.
**Difficulty:** Medium
**Engines:** SQLite 3.45.0 through sql.js 1.10.2 (the runnable examples, plans from `EXPLAIN QUERY PLAN`) and PostgreSQL 16.15 (the Index Only Scan example). SQL Server and MySQL are described from their documentation and were not run here.

> **How to read this doc:** Each idea is explained in plain language first, t

Question 8

How does GROUP BY work with aggregate functions?

Accepted Answer

**Target Audience:** Beginners writing their first "total per category" query, and candidates who need the single-value rule and the engine differences.
**Difficulty:** Medium
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16. MySQL behaviour is cited from its manual.

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

Question 9

What is the difference between COUNT(*), COUNT(col), and COUNT(DISTINCT col)?

Accepted Answer

**Target Audience:** Beginners who have written `COUNT(*)` and want to know why a report's numbers disagree; candidates preparing for the classic COUNT 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. MySQL's multi-column form is cited from its manual.

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

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 difference between a primary key and a foreign key?

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

What is the CASE expression?

What do COALESCE and NULLIF do?

Medium

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

How do you read an EXPLAIN query plan?

What is a covering index?

How does GROUP BY work with aggregate functions?

What is the difference between COUNT(*), COUNT(col), and COUNT(DISTINCT col)?

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

Why might EXISTS outperform a JOIN for existence checks?

How does SQL handle NULL values?

Hard

Serializable isolation pitfalls — write skew, retry storms, and when advisory locks beat SSI?

How do you optimize a slow SQL query?

Why does column order matter in a composite index?

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