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

What is a CROSS JOIN and when is it useful?

Accepted Answer

**Target Audience:** Beginners who have seen INNER and LEFT JOIN and want to know what the "join with no condition" is for, plus candidates who want 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.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

Question 5

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

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

Question 9

What is the difference between EXISTS and IN?

Accepted Answer

**Target Audience:** Beginners who know a basic WHERE clause and have seen both forms in code reviews; anyone who has heard "EXISTS is faster" and wants to know when that is true.
**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 behaviour is cited from its manual.

> **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:`

Question 10

Does a filter in the ON clause behave differently from WHERE in a LEFT JOIN?

Accepted Answer

**Target Audience:** Anyone who has written a LEFT JOIN and been surprised by missing rows; interview candidates who need to explain why.
**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 behaviour is defined by the SQL standard and is the same in MySQL and SQL Server.

> **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term

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 are the different types of SQL joins?

What is a CROSS JOIN and when is it useful?

Medium

What is database normalization?

How do you read an EXPLAIN query plan?

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

Why might EXISTS outperform a JOIN for existence checks?

What is the difference between EXISTS and IN?

Does a filter in the ON clause behave differently from WHERE in a LEFT JOIN?

What is denormalization and when should you use it?

How do you paginate efficiently in SQL?

Hard

How do you optimize a slow SQL query?