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 using a school fee payments scenario.
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.
Start fresh: Click Reset in the sidebar to clear your database, then run the setup below.
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),
FOREIGN KEY (student_id) REFERENCES students(id)
);
CREATE TABLE IF NOT EXISTS fee_payments (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
payment_date DATE NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'pending',
FOREIGN KEY (student_id) REFERENCES students(id)
);
CREATE TABLE IF NOT EXISTS student_audit (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
action VARCHAR(50) NOT NULL,
details TEXT NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (student_id) REFERENCES students(id)
);
INSERT INTO students (name, email) VALUES
('Alice', '[email protected]'),
('Bob', '[email protected]'),
('Charlie', '[email protected]');
INSERT INTO enrollments (student_id, course_name, score) VALUES
(1, 'Math', 90), (1, 'Science', 85), (2, 'Math', 78);
INSERT INTO fee_payments (student_id, amount, payment_date, status) VALUES
(1, 500.00, '2026-01-15', 'paid'),
(2, 500.00, '2026-01-15', 'pending');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.
Setup: Add a grade column to the enrollments table for the CASE examples below.
ALTER TABLE enrollments ADD COLUMN grade CHAR(1);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 fee_payments SET status = 'paid' WHERE id = 2;
INSERT INTO student_audit (student_id, action, details)
VALUES (2, 'payment_recorded', 'Bob payment of 500.00 recorded');
COMMIT;Records a payment and logs it atomically. If either statement fails, you can ROLLBACK to undo both. COMMIT saves both changes permanently.
Rollback on error
START TRANSACTION;
UPDATE fee_payments SET status = 'paid' WHERE id = 99;
INSERT INTO student_audit (student_id, action, details)
VALUES (2, 'payment_recorded', 'Invalid payment attempt');
-- 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 student_audit (student_id, action, details) VALUES (1, 'step_1', 'Beginning fee processing');
SAVEPOINT sp1;
INSERT INTO student_audit (student_id, action, details) VALUES (1, 'step_2', 'Validating payment');
SAVEPOINT sp2;
INSERT INTO student_audit (student_id, action, details) VALUES (1, 'step_3', 'Recording payment');
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