When does Excel break?

Drag to grow your dataset. See what survives.

Why exactly does Excel die?

1. No real indexing — every filter scans the whole sheet.
2. No safe concurrency — two people editing the same cell can corrupt data.
3. No enforced rules — "fees must be > 0" is just a hope.
4. Complex questions get painful — try "students who attended >80% but paid late" across 6 months.
5. Weak audit and recovery — Excel assumes things go right. Databases assume things go wrong.

Excel isn't bad. It's just not designed for what databases are designed for.

Same goal, two mindsets

Find all active students.

active = []
for s in students:
    if s.is_active:
        active.append(s)
return active

SELECT *
FROM students
WHERE is_active = 1;

No loops. No counters. You describe the result; the engine picks the strategy — which index, what order, whether to parallelize.

The restaurant analogy

The cleanest way to feel the difference.

The four pieces

🎮 Find Aisha Khan

She's in row 37 of this 50-row table. Pick a search strategy and watch the difference.

Anatomy of a table

Hover the row or column highlights to see what each piece is called.

🎮 Pick the right tool

For each use case — which type fits better? Tap an option, see why.

Side by side

Run the transaction

One user action, three database steps. Run the happy path, then crash it on purpose.

Every serious database is designed around these four guarantees. Click any card for the deep version.

The hierarchy

Click any node to see what it actually means.

The rule to remember

The three options

🎮 Feel the latency

Hit each "send" button. The difference is the whole reason.

Why Docker beats native install

Image vs container — click to compare

🎮 Configure your container

Fill the form like you would in Docker Desktop. Hit "Start" — wrong or missing values show what would break.

Connect with Beekeeper Studio

Beekeeper is the UI that talks to your container. Cleaner than MySQL Workbench, beginner-friendly.

Every SQL command belongs to one of five families. Click a card to see what it does.

🎮 Which family does each command belong to?

Tap the family for each command. Wrong picks turn red.

DDL vs DML — the house analogy

The two families you'll use most. One shapes the house; the other lives in it.

The essentials

-- create a database
CREATE DATABASE tuf_sql_2;

-- list all databases
SHOW DATABASES;

-- switch into one (all later queries run inside it)
USE tuf_sql;

-- delete a database (irreversible!)
DROP DATABASE tuf_sql_2;

-- safe create: no error if it already exists
CREATE DATABASE IF NOT EXISTS tuf_sql;

DROP DATABASE is irreversible in most setups — it removes the whole database. Handle with care.

What each column can hold

A data type defines what kind of value a column stores.

🎮 Pick the right type

For each thing you need to store, tap the best data type.

Create & drop a table

CREATE TABLE IF NOT EXISTS users (
  user_id          INT,
  name             VARCHAR(30),
  email            VARCHAR(50),
  is_active        BOOLEAN,
  account_balance  DECIMAL(10,2),
  signup_datetime  DATETIME
);

SHOW TABLES;          -- list tables in the current database
DROP TABLE IF EXISTS users;   -- IF EXISTS avoids an error if missing

Insert rows

-- single row
INSERT INTO users (user_id, name, email)
VALUES (1, 'Raj', 'raj@gmail.com');

-- multiple rows at once
INSERT INTO users (user_id, name, email)
VALUES
  (2, 'Mark',   'mark@gmail.com'),
  (3, 'Albert', 'albert@gmail.com'),
  (4, 'Peter',  'peter@gmail.com');

Read rows + alias

SELECT * FROM users;          -- everything (avoid on huge tables)

SELECT
  name,
  email AS mail_id            -- AS renames the column in output
FROM users;

Avoid SELECT * on big tables in real systems. Ask only for the columns you need.

Keys are how rows stay unique and how tables connect. Click a card.

See the relationship

A foreign key in orders points at the primary key of users.

CREATE TABLE orders (
  order_id   INT PRIMARY KEY,
  user_id    INT,
  amount     DECIMAL(10,2),
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

If a table is referenced by a foreign key, drop the child table first (drop orders before users).

Primary key vs UNIQUE

Easy to confuse — here's the clean split.

🎮 Will the foreign key let it in?

The users table has user_id 1 and 2. Predict PASS or FAIL for each order insert — the FK rejects "ghost" users.

A relationship links two tables via a primary key + foreign key. This is why we call them relational databases. Click a type.

Many-to-Many needs a junction table

A student joins many batches; a batch has many students. A third table in the middle holds the pairs.

SELECT students.name, batches.batch_name
FROM enrollments
JOIN students ON enrollments.student_id = students.student_id
JOIN batches  ON enrollments.batch_id   = batches.batch_id;

🎮 What relationship is this?

For each scenario, pick the relationship type.

Constraints are rules the database enforces for you. Click one.

🎮 Will the insert survive?

Our users table has these rules. Predict PASS or FAIL for each insert.

CREATE TABLE users (
  user_id   INT PRIMARY KEY AUTO_INCREMENT,
  name      VARCHAR(30),
  email     VARCHAR(50) NOT NULL UNIQUE,
  age       INT CHECK (age >= 15 AND age <= 30),
  is_active BOOLEAN DEFAULT TRUE
);

🎮 Which one fits?

For each scenario, pick the right representation.

How they behave in queries

Never write WHERE col = NULL — it never matches. Use IS NULL. And COUNT(col) skips NULLs, while COUNT(*) counts every row.

When you run a query, the database doesn't answer instantly. It goes through three stages.

SELECT name, email
FROM customers
WHERE city = 'Mumbai';

How an index works

You saw the speed difference in the find-Aisha game. Here's what's actually happening.

An index is a separate, automatically-sorted mini-table holding two things: a sorted copy of the column (e.g. all emails A→Z) and a pointer to the real row. Because it's sorted, the database uses binary search — 1,000,000 rows might need only ~20 lookups instead of a million.

-- jump straight to a value instead of scanning
CREATE INDEX idx_user_email ON users(email);

-- one index covering two columns searched together
CREATE INDEX idx_name_city ON users(full_name, city);

Full scan vs index lookup

🎮 Should you index this column?

Tap yes or no for each. Think about how often it's searched vs how often it changes.

The order the database really runs it

You write SELECT first, but the engine starts with FROM.

🎮 Pick your columns

Tick the columns you want. The query and result update live.

SELECT country, population FROM countries;

SELECT can do more than fetch

-- math on the fly, named with AS
SELECT country, population / area AS density FROM countries;

-- a static label column
SELECT country, 'In Asia region' AS note FROM countries;

WHERE checks every row: condition true → row stays, false → row blocked. It runs before SELECT, so you can filter on a column you don't display.

SELECT * FROM countries WHERE region = 'Asia';

✓ = row kept, ✗ = row blocked by the gatekeeper.

Swap any of these into the WHERE playground above.

Two conditions on every row. Flip the connector and watch which countries survive.

SELECT * FROM countries
WHERE population > 50000000 AND area < 5000000;

The precedence trap

SQL applies AND before OR. Without parentheses you can get surprises — always group OR conditions.

-- "Accessories or Cables, AND under 20"
WHERE (category = 'Accessories' OR category = 'Cables')
  AND price < 20;   -- brackets force OR first

🎮 Compute population density

density = population / area. Hit the button to calculate it for every row on the fly.

Sort the rows, then keep only the top few. Classic "Top N" pattern.

SELECT country, area FROM countries ORDER BY area ASC LIMIT 5;

DISTINCT removes duplicate values. AS renames a column header for the output (the real table is unchanged).

SELECT DISTINCT region AS unique_region FROM countries;

🎮 How many rows comes back?

Read each query against the 5-row countries table and pick the row count.

Pick a column and a mode. Toggle normalization to treat empty strings and spaces as "missing" too.

SELECT id, email, city FROM users WHERE city IS NULL;

IN is a clean shortcut for many ORs. Pick cities to build the list, then flip IN ↔ NOT IN.

SELECT id, email, city FROM users WHERE city IN ('Delhi', 'Mumbai');

Inclusive range on last_purchase_inr. Both ends count. NULLs (never purchased) never match a range.

SELECT full_name, last_purchase_inr FROM users
WHERE last_purchase_inr BETWEEN 400 AND 800;

Type a pattern to match against email. % = any number of characters, _ = exactly one.

SELECT id, email FROM users WHERE email LIKE '%@tuf.com';

🎮 Pick the right tool for the job

For each goal, tap the operator you'd reach for.

The bucket model

Picture a pile of balls. GROUP BY color drops them into buckets by colour. Then an aggregate (count, sum, average…) runs inside each bucket and returns one row per bucket.

The employees table

10 employees across 3 projects. Note Dave's NULL experience and Hank's NULL hours.

🎮 Group & summarize

Choose how to bucket the rows and what to compute in each bucket.

The registrations table

10 sign-ups. Some have NULL names or emails; Aisha registers several times.

🎮 Count it different ways

Same table, very different answers depending on what (and how) you count.

WHERE filters rows before grouping; HAVING filters buckets after the math. That's why WHERE SUM(...) > 100 fails — the sum doesn't exist yet at the WHERE step.

✓ = bucket kept, ✗ = bucket filtered out by HAVING.

🎮 WHERE or HAVING?

For each filter, decide where it belongs.

🎮 What does each query return?

Read the aggregate query and pick the answer.

ROUND(n, d) rounds to d decimals — and d can be negative (round to tens, hundreds). ABS(n) drops the sign. NULL stays NULL for both.

Unlike MAX/MIN (which work down a column), these work across a row. If any value is NULL, the result is NULL — toggle the fix to see why.

IFNULL(col, x) swaps a NULL for x — but it does not catch empty strings. Toggle NULLIF(phone, '') to fold '' into NULL first, then watch Rohan/Imran/Tanya get caught too.

The coalesce_demo table

Three email columns with NULLs (and one empty string). COALESCE returns the first non-NULL.

Build a priority chain. Toggle the columns and order matters left-to-right. Add a default for when all are missing.

Quick reference

🎮 Pick the right tool

For each goal, tap the function you'd reach for.

The products table

🎮 Price classifier — drag the thresholds

Move the cut-offs and watch every product re-label live. NULL price → "Missing Price".

🎮 Searched or Simple?

Ranges and IS NULL → Searched. Mapping fixed codes → Simple.

🎮 PASS / FAIL threshold

The rules table. Slide the pass mark; NULL stays "Missing".

CASE + COALESCE: tiered bonus

NULL score → bonus 0 (via COALESCE), ≥90 → 20%, ≥75 → 10%, else 5%.

CASE
  WHEN COALESCE(performance_score, -1) = -1 THEN 0
  WHEN performance_score >= 90 THEN salary * 0.20
  WHEN performance_score >= 75 THEN salary * 0.10
  ELSE salary * 0.05
END AS bonus_amount

Put CASE inside SUM/COUNT to turn many rows into one side-by-side summary row — exactly how dashboards show "Revenue India vs USA" together. Pick a pivot:

🎮 What does CASE return?

Read each rule set and the row, then pick the result.

Collation decides comparison rules. Four flags you'll see in names like utf8mb4_0900_ai_ci:

🎮 Compare under different collations

The customers table

Neha and Aayush have no middle name (NULL). Watch what each function does.

CONCAT returns NULL if any argument is NULL (the NULL trap). CONCAT_WS(sep, …) sets the separator once and skips NULLs.

Type anything — include spaces, an accent (é), or an emoji 😀 — and see every function applied at once. Brackets [ ] reveal where spaces are.

🎮 Slice a string (1-based indexing)

🎮 Find a position

Both return the 1-based position (0 if not found). Only the argument order differs: LOCATE(needle, haystack) vs INSTR(haystack, needle).

🎮 REPLACE every occurrence

Swaps all matches (case-sensitive). Try a breadcrumb: replace / with > .

LIKE pattern matching — quick recap

% = any length (0+), _ = exactly one character. A comparison returns 1 (true) or 0 (false).

'plus_user'  LIKE 'plus%'      -- 1  (starts with plus)
'plus1user'  LIKE 'plus_us_r'  -- 1  (each _ matches one char)
'USR_8'      LIKE 'USR__'      -- 1  (one char after USR_)
'USR_99'     LIKE 'USR__'      -- 0  (two chars after)
'plus1user'  LIKE 'plus\_user' -- 0  (\_ = a literal underscore)

There's a full live LIKE playground (type a pattern, watch rows match) in the Filtering essentials module.

🎮 What does each call return?

Add a row. emp_id is the primary key — a duplicate is rejected. Leave department blank to insert NULL.

"Insert if new, otherwise update." Needs a primary/unique key to detect the collision. Pick how a conflict is handled, then try an existing id (301) vs a new one.

Set a column where a condition holds. Pick no WHERE to feel the "global update" danger — it hits every row.

DELETE vs TRUNCATE vs DROP

🎮 The AUTO_INCREMENT difference

Same goal — empty the table, then insert "Raj". But the next id depends on how you cleared it. Watch the counter.

Change the table's shape, not its rows. Add a column (NULL for existing rows), backfill it, then drop it.

🎮 Pick the right write operation

The two office tables

Two offices, two staff tables. Neha Sharma works in both.

🎮 Merge lab — watch duplicates appear & vanish

Flip between the two operators and choose which columns to merge. Rows SQL throws away are struck through.

Sales come from two channels. To total revenue or count packages, every transaction must survive — even a repeated Mouse. UNION ALL just pastes the rows together: no sort, no compare, no filter.

🎮 Consolidate sales — and why the total breaks under UNION

Same data, two operators. Notice the revenue total change when a real duplicate sale gets silently deleted.

Decision matrix

1. Are duplicates even possible? No → UNION ALL (faster, nothing to remove).
2. Do you want to keep them? Yes → UNION ALL (e.g. counting sales). No → UNION (e.g. a contact list).

The set explorer

Two customer lists. Pick a set operation and watch which buckets it pulls from. store_customers = Raj, Neha, Sana · online_customers = Raj, Amit, Sana.

Building INTERSECT by hand

Older MySQL has no INTERSECT. The mindset: if SQL won't hand you a command, express it with logic. Stack both lists with UNION ALL, group, and keep anything that appears more than once.

SELECT customer_name
FROM (
    SELECT customer_name FROM store_customers
    UNION ALL                      -- must be ALL, or counts collapse to 1
    SELECT customer_name FROM online_customers
) AS combined_list
GROUP BY customer_name
HAVING COUNT(*) > 1;               -- appeared in both → intersection

1. UNION ALL → [Raj, Neha, Sana, Raj, Amit, Sana]
2. GROUP BY → Raj (2), Neha (1), Sana (2), Amit (1)
3. HAVING COUNT(*) > 1 → Raj, Sana

🎮 UNION or UNION ALL?

Read each business goal and pick the right tool.

🎮 Predict the merged result

Lists A = [X, Y] and B = [Y, Z] unless noted. How many rows come back?

Everything below runs against these two. Note the deliberate gaps: customer 105/999 never existed, order 7 has a NULL customer, and several customers never ordered.

🎮 One query, five join types

Same customers c + orders o, same ON c.customer_id = o.customer_id. Only the join word changes. Rows filled with NULL are the "kept anyway" rows — proof of what each join preserves.

INNER JOIN aggregates cleanly

Because INNER drops unmatched rows, there are no stray NULLs to poison a SUM or COUNT. Revenue + order count per customer, computed live:

SELECT c.customer_id, c.name,
       COUNT(*)            AS orders_count,
       SUM(o.total_amount) AS revenue
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

🎮 Same filter, two places, very different results

We LEFT JOIN customers to orders and want only PAID orders. Put that condition in ON (during the join) versus WHERE (after) and watch the "zero-order" customers vanish.

The audit tables

A merger-style audit: keep every student and every payment. Note Raj has a NULL id, and payment for student 4 is an orphan (no such student).

🎮 Raw full join vs COALESCE master-id

MySQL has no FULL OUTER JOIN — you stitch LEFT JOIN + RIGHT JOIN with UNION. The raw version leaves the id NULL for the orphan payment (its student row doesn't exist). COALESCE falls back to the payment's id to build one clean key.

The employees table

Employee and manager live in the same table. Two aliases (e and m) let SQL treat one table as two and walk the hierarchy.

🎮 Build the org chart

CROSS JOIN — the Cartesian product

No ON. Every customer pairs with every order: 8 × 8 = 64 rows. Useful for generating grids (all customer–coupon pairs, all dates × products), dangerous by accident.

SELECT COUNT(*) FROM customers c CROSS JOIN orders o;   -- 64

🎮 Combination generator

The "combo meal" mindset: no key needed, you just want every pairing. Pick two small reference tables and watch the grid build itself.

IMPLICIT JOIN — the old comma style

Comma in FROM makes a Cartesian product; the WHERE condition trims it down to matches. Forget the WHERE and you've written an accidental CROSS JOIN.

SELECT c.customer_id, c.name, o.order_code, o.status
FROM customers c, orders o
WHERE c.customer_id = o.customer_id;   -- == INNER JOIN

NATURAL JOIN — auto-match by column name

Joins on every identically-named column automatically — here customer_id links customers to customer_loyalty. No ON needed.

SELECT c.customer_id, cl.tier
FROM customers c
NATURAL JOIN customer_loyalty cl
ORDER BY c.customer_id;

The multi-column trap

Now both customers and store_locations share two names — customer_id AND city. NATURAL JOIN silently matches on both, so a customer listed under a different city vanishes.

SELECT * FROM customers NATURAL JOIN store_locations;

🎮 Which join answers the question?

Read each business goal and choose the right join.

🎮 How many rows come back?

Using the same customers (8 rows) and orders (8 rows) from this module.

Where users live

MySQL stores every account — users and roles — in one system table: mysql.user. List them with SELECT user, host FROM mysql.user;. A user is defined not just by name, but by where they connect from — the host.

Click a host type

'raj'@'localhost' and 'raj'@'%' are different accounts. The host is half the identity.

User or role? Same table, different shape

Roles don't live in a separate table — they sit in mysql.user too. The tell isn't the table, it's the account properties: a user has a password and logs in; a role has no password and just holds privileges.

Roles are typically locked and password-less, so you can list them with:

SELECT user AS role_name, host
FROM mysql.user
WHERE account_locked = 'Y'
  AND (authentication_string IS NULL OR authentication_string = '')
ORDER BY user;

Everything below protects this one table. Run all GRANT/REVOKE statements as root/admin — only an admin can create users and hand out privileges.

CREATE TABLE students (
  student_id INT PRIMARY KEY AUTO_INCREMENT,
  email      VARCHAR(120) NOT NULL UNIQUE,
  full_name  VARCHAR(80)  NOT NULL,
  city       VARCHAR(60),
  status     ENUM('ACTIVE','BANNED') NOT NULL DEFAULT 'ACTIVE',
  created_at DATETIME NOT NULL
) ENGINE=InnoDB;

🎮 Tick privileges, watch what raj can do

Pick what you GRANT to 'raj'@'%' on tuf_sql.students. The statement rebuilds, and every operation raj might try is re-checked live. Notice DROP never appears in the list — it was never granted, so it always fails.

🎮 Move the scope, watch the blast radius

GRANT ALL hands over every privilege — but its real power depends entirely on where you point it. Switch the scope and watch how much of the server lights up as accessible.

Why roles?

Imagine 50 engineers. Granting SELECT to each one is 50 queries — and one typo is a security hole. Instead, grant once to a role, then link users to it. Change the role later and all 50 update instantly.

CREATE ROLE 'engineering';
GRANT SELECT, INSERT, UPDATE ON tuf_sql.students TO 'engineering';
GRANT 'engineering' TO 'raj_ip'@'192.168.65.1';

🎮 The "default role" trap

Granting a role to a user does not make it active at login. A common gotcha — toggle the switches and see whether raj_ip can actually run queries after connecting.

WITH GRANT OPTION — the power to delegate

By default a user can use a privilege but can't pass it on. Add WITH GRANT OPTION and they can re-grant the same privileges to others (but never privileges they don't hold themselves). You can test this toggle back in the GRANT playground.

GRANT SELECT, INSERT ON tuf_sql.students
TO 'raj'@'%'
WITH GRANT OPTION;

GRANT changes what a user can do. ALTER USER changes the account itself — its password or login status. Admin-only.

1 · Password rotation

Change a password periodically, or immediately after a suspected leak. The old password dies instantly; new connections need the new one.

ALTER USER 'tuf_support'@'localhost' IDENTIFIED BY 'Temp#5678';

2 · Emergency lock

Offboarding, suspicious activity, or a compromised device — block login instantly without deleting the account. Roles, grants and history stay intact, so you can restore access later.

ALTER USER 'tuf_support'@'localhost' ACCOUNT LOCK;

3 · Unlock

The reverse. Login works again with the existing password; nothing was lost during the lockdown.

ALTER USER 'tuf_support'@'localhost' ACCOUNT UNLOCK;

REVOKE removes privileges or roles. Same shape as GRANT, one keyword flips: TO becomes FROM. Run as admin.

Revoke a role from a user

REVOKE 'engineering' FROM 'raj_ip'@'192.168.65.1';

All privileges coming from that role vanish for the user.

Revoke specific privileges

REVOKE INSERT, UPDATE ON tuf_sql.students FROM 'raj'@'%';

Raj keeps anything still granted (e.g. SELECT) but loses the revoked ones — an INSERT now fails with "permission denied".

🎮 Which command?

Pick the right DCL / account command for each scenario.

🎮 Will it succeed?

raj has exactly SELECT, INSERT on tuf_sql.students — nothing else. Logged in as raj, does each statement work?

🎮 User or role?

Same mysql.user table — decide by the account's properties.

What is a connection?

A connection is a single live channel between your app and the MySQL server. Every connection has a connection_id, and it is stateful — session variables and in-progress transaction state live inside that one connection. Kill the connection and that state is gone.

🎮 Pool vs single connection

Opening a fresh DB connection every time is slow. A pool keeps several open and reuses them: it hands out a free one, runs the query, returns it. Fire three simultaneous requests each way and watch the difference.

Outside a transaction MySQL auto-commits each statement. Inside one, nothing is final until you COMMIT — and ROLLBACK throws all of it away, restoring the last committed state.

START TRANSACTION;
  UPDATE students SET status = 'ACTIVE' WHERE student_id = 1;
  UPDATE students SET city = 'Delhi'   WHERE student_id = 1;
COMMIT;   -- or ROLLBACK to discard both

🎮 Run a transaction, then keep or discard it

Start a transaction, apply the two updates, then choose COMMIT or ROLLBACK. The right panel is what another session sees — proof that uncommitted changes stay invisible (MVCC).

A SAVEPOINT is a bookmark inside a transaction. ROLLBACK TO SAVEPOINT undoes only the work after the bookmark and keeps everything before it — the transaction stays open.

🎮 Step through a savepoint

We insert an enrollment, bookmark, then attempt a balance transfer — but roll the transfer back to the savepoint while keeping the enrollment. Step through and watch which changes survive the final COMMIT.

What actually happens during a transaction?

Click each piece to see its job. Together they explain how InnoDB can change data, still let others read the old version, undo on rollback, and survive a crash.

Undo log vs Redo log — don't mix them up

A write takes a lock on its rows until commit/rollback. If another transaction wants the same row it waits — and if it waits too long, it times out and fails instead of hanging forever.

🎮 Build a deadlock

Two transactions grab locks in the opposite order. Step through and watch them lock each other out — then see how MySQL breaks the tie.

🎮 Which keyword?

Pick the right transaction control for each goal.

🎮 What survives?

Reason about the final state after each transaction.

🎮 Concept check

Connections, logs, locks & concurrency.

A small e-commerce slice. Note the deliberate gaps: users 2, 5, 6 never order, user 7 has an empty name and 8 a NULL one, the Keyboard is never sold, and order 102 is CANCELLED. Orders also carry a status and a placed_at_utc timestamp we'll use later.

🎮 One value per order row — no JOIN needed

When a subquery returns exactly one value, you can drop it straight into the SELECT list. It runs once per outer row, using that row's o.user_id. Pick what each order should look up:

🎮 The inner query builds the filter list

IN checks whether a value matches anything in a list. Instead of hardcoding IN (1, 3, 4), we let the subquery find the IDs. Watch the generated list, then the rows it keeps:

Subqueries vs JOINs — when to reach for each

Subqueries read like "Step 1, then Step 2"; JOINs are often faster for the engine. Reach for a subquery when you're filtering a WHERE against a separate list, or comparing each row to an aggregate (like an average).

🎮 Does any matching row exist?

EXISTS returns TRUE the instant the correlated subquery finds one row — values don't matter, so SELECT 1 is the idiom. NOT EXISTS is the opposite: true when nothing is found. Flip between them and raise the bar:

🎮 Four real questions, one pattern

Same presence/absence idea aimed at different tables and conditions:

⚠️ The NOT IN + NULL trap — live

"Which products were never sold?" answered two ways. With clean data both agree on Keyboard. Now inject one refund-style order whose product_id is NULL and watch NOT IN collapse to nothing while NOT EXISTS stays correct.

SELECT p.product_id, p.product
FROM products p
WHERE p.product_id NOT IN (
  SELECT o.product_id FROM orders o
);

SELECT p.product_id, p.product
FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.product_id = p.product_id
);

A correlated subquery references a column from the outer query, so it re-runs for every outer row — like a mini-query inside a loop. Think "for each employee, compute the average salary of their own department, then compare."

🎮 Each user's biggest order

Same idea, two angles — summarise the peak per user, or pull the actual winning order rows:

🎮 The looping logic in action

Three correlated patterns you'll reuse constantly — last-seen, running counts, and beating a group average:

🎮 Pick the right tool

Which clause fits each goal?

🎮 Predict the result

Reason about what each subquery returns over the tables above.

The high-level idea

1) Run a query that generates a result set. 2) Give that result a name — like a nickname for a table. 3) Write a final query that selects from the named result as if it were real.

WITH cte_name AS (
  -- any SELECT that returns a table-like result
  SELECT ...
)
SELECT ...
FROM cte_name;

The keyword is WITH (not "width"). The CTE exists only for the duration of that one final query — nothing is stored on disk; the engine computes it, uses it, and throws it away.

Always tabular — even one value

A CTE's output is always rows × columns. "One value" just means 1 row × 1 column; it can also be 0 rows or many.

WITH e_row AS (
  SELECT 1 AS n
)
SELECT n FROM e_row;   -- → one row: n = 1

Why bother?

Three teaching tables: restaurant ratings (note Burger Barn's NULL star — aggregates ignore it), a sales_date ledger, and a categories hierarchy where each row points at its parent. OrphanCat's parent (999) doesn't exist — deliberately broken.

🎮 Average ratings, then a clean filter

The CTE does the aggregation; the final query treats it like a table and filters. Pick a variant:

🎮 Top salesperson, one step at a time

Chain CTEs: each one uses the previous. Step through the pipeline and watch the intermediate results:

Restaurants above the overall average

SELECT restaurant, AVG(stars) AS avg_stars
FROM ratings
GROUP BY restaurant
HAVING AVG(stars) > (
  SELECT AVG(stars) FROM ratings
)
ORDER BY avg_stars DESC;

WITH baseline AS (
  SELECT AVG(stars) AS overall_avg
  FROM ratings
),
avg_by_restaurant AS (
  SELECT restaurant, AVG(stars) AS avg_stars
  FROM ratings
  GROUP BY restaurant
)
SELECT a.restaurant, a.avg_stars
FROM avg_by_restaurant a
CROSS JOIN baseline b
WHERE a.avg_stars > b.overall_avg
ORDER BY a.avg_stars DESC;

A recursive CTE has two parts glued by UNION ALL: the anchor query runs once and seeds the result; the recursive query references the CTE's own name and keeps adding rows until its condition fails. No stop condition → infinite loop.

🎮 The simplest recursion: count to N

Change the stop condition and watch the rows grow:

🎮 One pattern, five real questions

The recursive member joins categories back to the CTE — downwards on c.parent_id = ct.cat_id (children), upwards on c.cat_id = cp.parent_id (parents):

Temporary table version of the ratings summary

CREATE TEMPORARY TABLE tmp_avg_ratings AS
SELECT restaurant,
       AVG(stars)   AS avg_stars,
       COUNT(stars) AS ratings_count
FROM ratings
GROUP BY restaurant;

🎮 Pick the right tool

CTE, recursive CTE, or temp table?

🎮 Predict the result

Reason over the tables above.

🎮 Six functions, two spellings

Given @date_temp = '2029-08-19' and @datetime_temp = '2029-05-18 12:50:45', pull out any component. EXTRACT(part FROM value) is the SQL-standard spelling of the same thing:

The employees table — note Arjun's NULL joining date:

🎮 Break the date apart, then report on it

Never wrap the column in a function to filter it

WHERE YEAR(joining_date) = 2029
  AND MONTH(joining_date) = 2;

WHERE joining_date >= '2029-02-01'
  AND joining_date <  '2029-03-01';

This is called being SARGable (Search-ARGument-able). Wrapping the column in YEAR() forces the engine to compute the year for every row before checking; a plain range lets it jump straight to the right slice of the index. Same results, wildly different speed on 10 million rows.

The orders table (a DATE column — no time part):

🎮 Pick a window

BETWEEN is inclusive on both ends — fine on pure DATE columns. The half-open pattern (>= start AND < next-period-start) works everywhere and needs no month-length math:

⚠️ 🎮 Where did January 31st go?

On a DATETIME column, '2029-01-31' silently becomes '2029-01-31 00:00:00' — so BETWEEN keeps only the first second of the 31st. Try all three filters on the January logins and watch who survives:

SQL sees dates as numbers on a timeline: MIN = oldest, MAX = newest. The logins table:

🎮 Five timeline questions

🎮 Two rulers

DATEDIFF(end, start) always answers in whole days, ignoring time. TIMESTAMPDIFF(unit, start, end) counts complete units of your choice — note the argument order flips!

Minutes to deliver + a CASE verdict

SELECT order_id, order_placed_at, delivered_at,
  TIMESTAMPDIFF(MINUTE, order_placed_at, delivered_at) AS delivery_minutes,
  CASE
    WHEN delivered_at IS NULL THEN 'Pending'
    WHEN delivered_at < order_placed_at THEN 'Bad Data'
    WHEN TIMESTAMPDIFF(SECOND, order_placed_at, delivered_at) <= 60 * 30 THEN 'On Time'
    ELSE 'Slow'
  END AS delivery_status
FROM deliveries;

🎮 Pick the right tool

Function, pattern, or trap?

🎮 Predict the result

Reason over the tables above.