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

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 4

What are the different types of SQL joins?

Accepted Answer

**Target Audience:** Beginners who know SELECT and WHERE on one table and now need data from two tables.
**Difficulty:** Easy
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, which runs the examples on this page) and PostgreSQL 16.15; MySQL and SQL Server notes come from their documentation and are marked as such.

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

Question 5

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

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 8

How do you aggregate values into a single string?

Accepted Answer

**Target Audience:** Beginners who know `GROUP BY` and want to produce "one row with a list", and candidates who need the dialect differences that interviewers love to ask.
**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, SQL Server and Oracle syntax is cited from their documentation, not run.

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

Question 9

How do you calculate year-over-year or month-over-month growth in SQL?

Accepted Answer

**Target Audience:** Beginners who can write `GROUP BY` and want to compare one period with the one before it, and candidates preparing for analytics interviews.
**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. `LAG()` is standard SQL and also exists in MySQL 8.0+ and SQL Server; the date functions differ per engine and are named where used.

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

Question 10

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

SQLDECODED.

Easy

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

What is the CASE expression?

What is the difference between WHERE and HAVING?

What are the different types of SQL joins?

What does DISTINCT do and what is its cost?

Medium

How do you read an EXPLAIN query plan?

How does GROUP BY work with aggregate functions?

How do you aggregate values into a single string?

How do you calculate year-over-year or month-over-month growth in SQL?

Why might EXISTS outperform a JOIN for existence checks?

How does SQL handle NULL values?

What is a view and a materialized view?

What is the difference between UNION and UNION ALL?

Hard

How do recursive CTEs work?

How do you optimize a slow SQL query?