Chapter 10

Chapter 10 — Transactions and the CASE Expression

This final chapter brings together two topics that sit at the heart of production SQL. Transactions let you group multiple statements into a single all-or-nothing unit — essential any time one logical operation requires several writes. The CASE expression brings conditional logic directly into your queries, powering everything from status labels to conditional aggregation.

Part 1 — Transactions

Why transactions exist

Imagine a customer buys a book. You need to do two things: insert a row into orders and decrement stock in books. If the server crashes or an error occurs between the two statements, you end up with an order but no stock reduction — or vice versa. The database is now inconsistent.

A transaction makes those two writes atomic: either both happen, or neither does. The database moves from one consistent state to another without any in-between half-state being visible to other users.

Atomicity

All statements in a transaction succeed together, or none of them do. There is no partial commit.

Consistency

A transaction always moves the database from one valid state to another. Constraints and rules are enforced at commit.

Isolation

Concurrent transactions don't see each other's uncommitted changes. The level of isolation is configurable.

Durability

Once committed, a transaction's changes survive a crash. InnoDB writes to the redo log before confirming success.

The transaction statements

START TRANSACTION; -- or BEGIN; — marks the start of an explicit transaction COMMIT; -- make all changes permanent ROLLBACK; -- undo all changes since START TRANSACTION SAVEPOINT name; -- set a named checkpoint within the transaction ROLLBACK TO name; -- roll back to a savepoint without ending the transaction RELEASE SAVEPOINT name; -- discard a savepoint (frees the name)

A complete purchase transaction

▶

START TRANSACTION — all writes from here on are held in a private "undo buffer". Other sessions still see the old data.

INSERT a new row into orders

UPDATE books to reduce stock by the quantity ordered

✓

COMMIT — both changes become permanent and visible to everyone. The undo buffer is discarded.

✗

Or: ROLLBACK — if anything went wrong (foreign key error, constraint violation, application crash), both changes are undone as if the transaction never happened.

START TRANSACTION; -- Step 1: create the order INSERT INTO orders (customer_id, book_id, quantity, total_price, order_date) VALUES (2, 1, 1, 8.99, NOW()); -- Step 2: reduce stock UPDATE books SET stock = stock - 1 WHERE book_id = 1; -- Only commit if both succeeded COMMIT;

-- If something goes wrong, roll everything back START TRANSACTION; INSERT INTO orders (customer_id, book_id, quantity, total_price, order_date) VALUES (99, 1, 1, 8.99, NOW()); -- customer 99 doesn't exist! ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bookshop`.`orders`, CONSTRAINT `fk_orders_customer` ...) ROLLBACK; -- nothing was changed — the database is untouched

SAVEPOINT — partial rollbacks

Within a transaction you can plant named savepoints. Rolling back to a savepoint undoes everything after it while keeping everything before it. The transaction remains open — you can continue adding more statements or commit/rollback the whole thing:

START TRANSACTION; INSERT INTO authors (first_name, last_name) VALUES ('Terry', 'Pratchett'); SAVEPOINT author_inserted; -- checkpoint after inserting the author INSERT INTO books (author_id, title, price) VALUES (LAST_INSERT_ID(), 'Good Omens', 8.99); -- oops wrong price ROLLBACK TO author_inserted; -- undo only the book INSERT -- Terry Pratchett is still inserted; only the book was rolled back INSERT INTO books (author_id, title, price) VALUES (LAST_INSERT_ID(), 'Good Omens', 7.99); -- correct price COMMIT;

Autocommit — the default behaviour

By default MySQL runs in autocommit mode — every statement is automatically wrapped in its own transaction and committed immediately. This is why a bare DELETE FROM orders WHERE order_id = 5 takes effect straight away with no COMMIT needed.

Mode	How it works	When to use
Autocommit ON (default)	Each statement commits immediately. You can still use START TRANSACTION to open a multi-statement transaction — autocommit is suspended until COMMIT or ROLLBACK.	Most application code — quick one-off queries, reads, single-table inserts.
Autocommit OFF	`SET autocommit = 0` — every statement is implicitly inside a transaction. You must COMMIT manually or changes are lost when the session closes.	Rarely used directly. Some ORMs and connection pools manage this for you.

DDL statements auto-commit immediately and cannot be rolled back. CREATE TABLE, ALTER TABLE, and DROP TABLE implicitly commit the current transaction before executing. You cannot roll back a CREATE or DROP — even inside an explicit transaction. Always issue DDL outside of data-change transactions, and double-check before running in production.

Part 2 — The CASE Expression

CASE is SQL's way of writing if-then-else logic inside a query. It's an expression — it returns a value — so it can go anywhere a value is valid: the SELECT list, ORDER BY, WHERE, HAVING, and even inside aggregate functions.

Searched CASE (the common form)

CASE WHEN condition_1 THEN result_1 — evaluated top to bottom; first match wins WHEN condition_2 THEN result_2 WHEN condition_3 THEN result_3 ELSE default_result — if no WHEN matched; ELSE is optional END — required closing keyword

Simple CASE (equality only)

CASE column WHEN value_1 THEN result_1 — tests column = value_1 WHEN value_2 THEN result_2 ELSE default_result END

CASE evaluates top to bottom and stops at the first match. Put your most specific conditions first and the broadest catch-all last. If no WHEN matches and there's no ELSE, the result is NULL. Always add an ELSE unless NULL is a meaningful sentinel for "no match".

Stock status labels

SELECT title, stock, CASE WHEN stock = 0 THEN 'Out of stock' WHEN stock < 10 THEN 'Low stock' WHEN stock < 30 THEN 'In stock' ELSE 'Well stocked' END AS stock_status FROM books ORDER BY stock;

title	stock	stock_status
The Left Hand of Darkness	9	Low stock
Reasons to Stay Alive	18	In stock
Dune	27	In stock
Sapiens	31	Well stocked
The Midnight Library	42	Well stocked
And Then There Were None	55	Well stocked

CASE in ORDER BY — custom sort order

-- Sort by a business priority, not alphabetically or numerically SELECT title, stock FROM books ORDER BY CASE WHEN stock = 0 THEN 1 -- out of stock: highest priority WHEN stock < 10 THEN 2 -- low stock: second WHEN stock < 30 THEN 3 ELSE 4 END, stock ASC; -- secondary sort within each priority bucket

Simple CASE — mapping a code to a label

-- Map genre codes to display labels SELECT title, CASE genre WHEN 'Non-fiction' THEN '🔍 Non-fiction' WHEN 'Fiction' THEN '📖 Fiction' WHEN 'Sci-fi' THEN '🚀 Science Fiction' WHEN 'Mystery' THEN '🔎 Mystery' ELSE '📚 Other' END AS genre_label FROM books;

3. Conditional Aggregation

One of the most powerful patterns in SQL: put a CASE expression inside an aggregate function to aggregate only the rows that match a condition. This lets you pivot rows into columns, compute multiple metrics in a single pass, and avoid repeating GROUP BY queries.

Counting by category in one query

-- How many books fall into each stock status — in one row SELECT COUNT(CASE WHEN stock = 0 THEN 1 END) AS out_of_stock, COUNT(CASE WHEN stock < 10 AND stock > 0 THEN 1 END) AS low_stock, COUNT(CASE WHEN stock >= 10 THEN 1 END) AS in_stock FROM books;

out_of_stock	low_stock	in_stock
0	1	5

The CASE returns 1 when the condition matches and NULL otherwise. COUNT ignores NULLs — so it counts only the matching rows. No ELSE needed: the implicit ELSE is NULL, which COUNT skips.

Revenue split by genre in one row

SELECT SUM(CASE WHEN b.genre = 'Fiction' THEN o.total_price END) AS fiction_rev, SUM(CASE WHEN b.genre = 'Mystery' THEN o.total_price END) AS mystery_rev, SUM(CASE WHEN b.genre = 'Non-fiction' THEN o.total_price END) AS nonfiction_rev, SUM(CASE WHEN b.genre = 'Sci-fi' THEN o.total_price END) AS scifi_rev, SUM(o.total_price) AS total_rev FROM orders o JOIN books b ON o.book_id = b.book_id;

fiction_rev	mystery_rev	nonfiction_rev	scifi_rev	total_rev
17.98	20.97	18.98	9.99	67.92

Four different SUM calculations in a single table scan — no UNION or subqueries needed. This "pivot" pattern is extremely common in reporting queries.

Conditional aggregation with HAVING

-- Authors who have at least one book with low stock (< 15 copies) SELECT CONCAT(a.first_name, ' ', a.last_name) AS author, COUNT(b.book_id) AS total_books, COUNT(CASE WHEN b.stock < 15 THEN 1 END) AS low_stock_titles FROM authors a JOIN books b ON a.author_id = b.author_id GROUP BY a.author_id, a.first_name, a.last_name HAVING COUNT(CASE WHEN b.stock < 15 THEN 1 END) > 0 ORDER BY low_stock_titles DESC;

author	total_books	low_stock_titles
Ursula Le Guin	1	1

Using CASE in UPDATE — conditional bulk update

-- Apply different discounts based on genre in a single UPDATE UPDATE books SET price = ROUND( price * CASE WHEN genre = 'Mystery' THEN 0.85 -- 15% off Mystery WHEN genre = 'Sci-fi' THEN 0.90 -- 10% off Sci-fi ELSE 1.00 -- no change for others END, 2) WHERE genre IN ('Mystery', 'Sci-fi'); -- only affect these genres

Chapter Summary

Concept	Key points
START TRANSACTION / BEGIN	Opens an explicit transaction. Autocommit is suspended until COMMIT or ROLLBACK.
COMMIT	Makes all changes in the transaction permanent and visible to other sessions.
ROLLBACK	Undoes all changes since START TRANSACTION. Leaves the database as if the transaction never happened.
SAVEPOINT / ROLLBACK TO	Named checkpoints within a transaction for partial rollbacks. The transaction stays open after ROLLBACK TO.
ACID	Atomicity (all or nothing), Consistency (valid state to valid state), Isolation (concurrent transactions don't interfere), Durability (committed changes survive crashes).
Autocommit	On by default — each bare statement commits immediately. START TRANSACTION temporarily disables it for that block.
DDL and transactions	CREATE TABLE, ALTER TABLE, DROP TABLE implicitly commit the current transaction. Cannot be rolled back.
Searched CASE	CASE WHEN condition THEN result … ELSE default END. First matching WHEN wins. ELSE is optional — no match without ELSE returns NULL.
Simple CASE	CASE col WHEN val THEN result … END. Tests equality only. Shorter syntax for fixed value mappings.
Conditional aggregation	CASE inside SUM/COUNT/AVG. CASE returns a value or NULL; aggregate skips NULLs — effectively aggregating only matching rows. Powerful pivot pattern.

🎓

MySQL Foundations — Course Complete

You have completed all 10 chapters of MySQL Foundations.
You can now read, write, modify, and design relational databases with confidence.

Ch 1 · Relational Concepts Ch 2 · SELECT Basics Ch 3 · Sorting & Limiting Ch 4 · Data Types & NULL Ch 5 · Modifying Data Ch 6 · CREATE TABLE Ch 7 · Aggregates & GROUP BY Ch 8 · JOINs Ch 9 · String, Date & Numeric Functions Ch 10 · Transactions & CASE