CASE, Views & Transactions
Add conditional logic with CASE, store reusable queries with views, and protect data with transactions.
Real queries do more than retrieve raw data. You often need to transform values (pass/fail based on score), store complex queries for reuse (views), or ensure that a group of changes either all succeed or all fail (transactions). This stage covers all three.
Definition
A VIEW is a stored query that appears as a virtual table. A TRANSACTION is a sequence of operations performed as a single logical unit that either all succeed or all fail.
Required step: If you do not have these tables yet, run this setup in the playground before continuing. It creates the tables used by every example in this stage.
CREATE DATABASE IF NOT EXISTS school;
USE school;
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100)
);
CREATE TABLE IF NOT EXISTS enrollments (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
course_name VARCHAR(100) NOT NULL,
score DECIMAL(5,2),
grade CHAR(1),
FOREIGN KEY (student_id) REFERENCES students(id)
);
CREATE TABLE IF NOT EXISTS accounts (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
balance DECIMAL(10,2) NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS log (
id INT AUTO_INCREMENT PRIMARY KEY,
message TEXT NOT NULL
);
INSERT INTO students (name, email) VALUES
('Alice', 'alice@school.edu'),
('Bob', 'bob@school.edu'),
('Charlie', 'charlie@school.edu');
INSERT INTO enrollments (student_id, course_name, score) VALUES
(1, 'Math', 90), (1, 'Science', 85), (2, 'Math', 78);
INSERT INTO accounts (name, balance) VALUES
('Alice', 1000), ('Bob', 500);CASE Expressions
CASE adds conditional logic to SQL. It works like if/else in programming languages. You can use it in SELECT, WHERE, ORDER BY, and even UPDATE statements.
Simple CASE in SELECT
SELECT name, score,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
ELSE 'F'
END AS grade
FROM students
JOIN enrollments ON students.id = enrollments.student_id;Converts numeric scores to letter grades. Each WHEN checks a condition. ELSE handles anything not matched. END closes the CASE.
CASE in ORDER BY
SELECT name, score FROM students
JOIN enrollments ON students.id = enrollments.student_id
ORDER BY
CASE
WHEN score >= 90 THEN 1
WHEN score >= 80 THEN 2
ELSE 3
END;Sorts by grade tier: A students first, then B, then everything else. CASE in ORDER BY lets you define custom sort orders.
CASE in UPDATE
UPDATE enrollments
SET grade = CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
ELSE 'F'
END;Updates the grade column based on score for all rows. CASE in UPDATE is efficient because it updates in one pass instead of multiple UPDATE statements.
Views
A view is a stored query that acts like a virtual table. You can query it just like a real table, but it does not store data. It runs the underlying query every time.
Create a view
CREATE VIEW student_grades AS
SELECT s.name, e.course_name, e.score,
CASE
WHEN e.score >= 90 THEN 'A'
WHEN e.score >= 80 THEN 'B'
WHEN e.score >= 70 THEN 'C'
ELSE 'F'
END AS grade
FROM students s
JOIN enrollments e ON s.id = e.student_id;Creates a view called student_grades. Now you can query it like a table: SELECT * FROM student_grades WHERE grade = 'A'.
Query a view
SELECT name, grade FROM student_grades
WHERE grade = 'A';Queries the view just like a real table. MySQL runs the underlying SELECT and returns the filtered results.
Drop a view
DROP VIEW IF EXISTS student_grades;Removes the view. Does not affect the underlying tables. IF EXISTS prevents an error if the view does not exist.
Use cases:When to Use Views
Simplify complex queries. Hide JOINs and calculations behind a simple name.
Security. Expose only certain columns or rows to users without giving access to the base tables.
Consistency. Define a calculation once and reuse it everywhere.
Transactions
A transaction groups multiple statements so they all succeed or all fail. If any statement fails, you can roll back everything to the state before the transaction started. This is essential for data integrity.
Basic transaction
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;Transfers 100 from account 1 to account 2. If either UPDATE fails, you can ROLLBACK to undo both. COMMIT saves both changes permanently.
Rollback on error
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- If something goes wrong:
ROLLBACK;ROLLBACK undoes all changes since START TRANSACTION. The database returns to the state it was in before the transaction began.
SAVEPOINT
START TRANSACTION;
INSERT INTO log (message) VALUES ('Step 1');
SAVEPOINT sp1;
INSERT INTO log (message) VALUES ('Step 2');
SAVEPOINT sp2;
INSERT INTO log (message) VALUES ('Step 3');
ROLLBACK TO sp1; -- undoes Step 2 and Step 3, keeps Step 1
COMMIT;SAVEPOINT creates a bookmark. ROLLBACK TO undoes everything after that point. You can then commit what remains.
Core concepts:ACID Properties
Atomicity: All statements in a transaction succeed, or none do.
Consistency: A transaction brings the database from one valid state to another.
Isolation: Concurrent transactions do not interfere with each other.
Durability: Once committed, changes survive crashes and restarts.
What does a view store?
What does CASE do in a SELECT statement?
What happens if you ROLLBACK a transaction?
Key Takeaways
- CASE adds conditional logic: it works in SELECT, WHERE, ORDER BY, and UPDATE.
- Views store reusable queries. They do not store data. They run the query each time.
- Transactions group statements so they all succeed or all fail (atomicity).
- COMMIT saves changes permanently. ROLLBACK undoes everything since START TRANSACTION.
- SAVEPOINT creates checkpoints for partial rollbacks within a transaction.
Ready to test your knowledge?
Take a Quiz