Question 1

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

How do you find duplicate rows in a table?

Accepted Answer

**Target Audience:** Beginners who know `SELECT` and want the standard duplicate-finding pattern, and candidates who want the details interviewers probe (NULLs, case, collation).
**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

Question 6

When would you use a transaction explicitly?

Accepted Answer

**Target Audience:** Beginners who know `INSERT` and `UPDATE` and want to know when wrapping them in `BEGIN ... COMMIT` matters, and candidates who want concrete scenarios rather than a definition.
**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. Notes on MySQL and drivers are cited.

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

Question 7

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 8

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 9

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 10

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

SQLDECODED.

Easy

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?

How do you find duplicate rows in a table?

When would you use a transaction explicitly?

What is a CROSS JOIN and when is it useful?

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?

Advanced UPSERT patterns — conditional updates, xmax detection, and soft-delete-aware indexes?

What is a subquery and what is a correlated subquery?

How do you read an EXPLAIN query plan?

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?

What are transaction isolation levels?

Hard

RANK() vs DENSE_RANK() vs ROW_NUMBER() — when does the gap matter and how do you choose?

SKIP LOCKED for job queues — how do you build a concurrent queue without race conditions?

How do you optimize a slow SQL query?

How do you pivot rows into columns?

How do you solve gaps-and-islands problems in SQL?

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

What is a deadlock and how do you prevent it?

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