-- ============================================
-- SQL Lab - 9: SAVEPOINT & Partial Rollback
-- Student: Prashant Ranjan
-- Roll Number: 24MC3035
-- Platform: mycompiler.io (MySQL)
-- Topic: SAVEPOINT, ROLLBACK TO SAVEPOINT, Partial Rollback
-- ============================================

-- ============================================
-- SETUP: Create and populate students table
-- ============================================

-- Drop existing table if exists
DROP TABLE IF EXISTS students;

-- Create students table with fee columns
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(30),
    study_year INT,
    marks INT,
    amount_paid INT DEFAULT 0,
    balance INT DEFAULT 1000
);

-- Insert initial data
INSERT INTO students (student_id, name, department, study_year, marks, amount_paid, balance) VALUES
(1, 'Amit', 'CSE', 2, 78, 0, 1000),
(2, 'Neha', 'ECE', 1, 65, 0, 1000),
(3, 'Ravi', 'CSE', 2, 82, 0, 1000),
(4, 'Priya', 'ME', 3, 71, 0, 1000),
(5, 'Ankit', 'CSE', 3, 90, 0, 1000),
(6, 'Sneha', 'EEE', 2, 68, 0, 1000);

-- Display initial data
SELECT '===== INITIAL STUDENTS TABLE =====' AS '';
SELECT student_id, name, amount_paid, balance FROM students ORDER BY student_id;

-- ============================================
-- TASK 1: Basic SAVEPOINT and Rollback to s1
-- ============================================

SELECT '===== TASK 1: BASIC SAVEPOINT AND ROLLBACK TO s1 =====' AS '';

SELECT 'Before transaction:' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id IN (1,2);

START TRANSACTION;
SELECT 'Transaction started' AS '';

-- First update
UPDATE students SET amount_paid = amount_paid + 100, balance = balance - 100 WHERE student_id = 1;
SELECT 'After first update (Student 1 paid 100):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 1;

-- Create savepoint s1
SAVEPOINT s1;
SELECT 'SAVEPOINT s1 created' AS '';

-- Second update
UPDATE students SET amount_paid = amount_paid + 200, balance = balance - 200 WHERE student_id = 2;
SELECT 'After second update (Student 2 paid 200):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 2;

-- Rollback to savepoint s1 (undoes only second update)
ROLLBACK TO s1;
SELECT 'Rolled back to SAVEPOINT s1 (Student 2 update undone)' AS '';

SELECT 'After ROLLBACK TO s1:' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id IN (1,2);

-- Commit the transaction (only first update remains)
COMMIT;
SELECT 'Transaction committed' AS '';

SELECT 'Final state after COMMIT (only Student 1 payment saved):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id IN (1,2);

-- ============================================
-- TASK 2: Multiple SAVEPOINTS and Rollback to s2
-- ============================================

SELECT '===== TASK 2: MULTIPLE SAVEPOINTS AND ROLLBACK TO s2 =====' AS '';

-- Reset data for clean demonstration
UPDATE students SET amount_paid = 0, balance = 1000 WHERE student_id IN (3,4,5);

SELECT 'Before transaction (reset state):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id IN (3,4,5);

START TRANSACTION;
SELECT 'Transaction started' AS '';

-- Update 1
UPDATE students SET amount_paid = amount_paid + 100, balance = balance - 100 WHERE student_id = 3;
SAVEPOINT s1;
SELECT 'After update 1 (Student 3 paid 100) - SAVEPOINT s1 created:' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 3;

-- Update 2
UPDATE students SET amount_paid = amount_paid + 150, balance = balance - 150 WHERE student_id = 4;
SAVEPOINT s2;
SELECT 'After update 2 (Student 4 paid 150) - SAVEPOINT s2 created:' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 4;

-- Update 3
UPDATE students SET amount_paid = amount_paid + 200, balance = balance - 200 WHERE student_id = 5;
SELECT 'After update 3 (Student 5 paid 200):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 5;

-- Rollback to savepoint s2 (undoes only update 3)
ROLLBACK TO s2;
SELECT 'Rolled back to SAVEPOINT s2 (Student 5 update undone)' AS '';

SELECT 'After ROLLBACK TO s2:' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id IN (3,4,5);

-- Commit (keeps updates 1 and 2)
COMMIT;
SELECT 'Transaction committed' AS '';

SELECT 'Final state (updates 1 and 2 saved, update 3 rolled back):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id IN (3,4,5);

-- ============================================
-- TASK 3: Payment Scenario with Partial Rollback
-- ============================================

SELECT '===== TASK 3: PAYMENT SCENARIO WITH PARTIAL ROLLBACK =====' AS '';

-- Reset Student 1 for this scenario
UPDATE students SET amount_paid = 0, balance = 1000 WHERE student_id = 1;

SELECT 'Before payment scenario:' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 1;

START TRANSACTION;
SELECT 'Starting payment transaction' AS '';

-- Step 1: Initial payment of 300
UPDATE students SET amount_paid = amount_paid + 300, balance = balance - 300 WHERE student_id = 1;
SAVEPOINT after_300;
SELECT 'After ₹300 payment:' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 1;

-- Step 2: Additional payment of 200
UPDATE students SET amount_paid = amount_paid + 200, balance = balance - 200 WHERE student_id = 1;
SAVEPOINT after_500;
SELECT 'After additional ₹200 (total ₹500):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 1;

-- Step 3: Try to add another 600 (would exceed? Let's check)
-- Suppose we realize this is too much, so we rollback to after_500
ROLLBACK TO after_500;
SELECT 'Rolled back to after_500 (kept ₹500 payment, removed any further changes)' AS '';

SELECT 'Final payment amount (₹500 saved):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 1;

COMMIT;
SELECT 'Transaction committed - ₹500 payment saved' AS '';

-- ============================================
-- TASK 4: DELETE + SAVEPOINT + Rollback
-- ============================================

SELECT '===== TASK 4: DELETE + SAVEPOINT + ROLLBACK =====' AS '';

SELECT 'Before delete operation:' AS '';
SELECT * FROM students WHERE student_id = 6;

START TRANSACTION;
SELECT 'Transaction started' AS '';

-- Delete student 6
DELETE FROM students WHERE student_id = 6;
SELECT 'After DELETE (Student 6 removed) - No rows returned:' AS '';
SELECT 'Count of student 6: ' AS '';
SELECT COUNT(*) AS count FROM students WHERE student_id = 6;

-- Create savepoint
SAVEPOINT before_restore;
SELECT 'SAVEPOINT before_restore created' AS '';

-- Perform another operation (update)
UPDATE students SET marks = marks + 5 WHERE student_id = 5;
SELECT 'After additional UPDATE (Student 5 marks increased):' AS '';
SELECT student_id, name, marks FROM students WHERE student_id = 5;

-- Rollback to savepoint (restores deleted student but keeps the update)
ROLLBACK TO before_restore;
SELECT 'Rolled back to SAVEPOINT before_restore' AS '';

SELECT 'Student 6 restored:' AS '';
SELECT * FROM students WHERE student_id = 6;

SELECT 'Student 5 marks (update kept):' AS '';
SELECT student_id, name, marks FROM students WHERE student_id = 5;

-- Commit
COMMIT;
SELECT 'Transaction committed' AS '';

-- ============================================
-- TASK 5: Error Handling Using SAVEPOINT (MySQL-compatible version)
-- ============================================

SELECT '===== TASK 5: ERROR HANDLING USING SAVEPOINT =====' AS '';

-- Reset data
UPDATE students SET amount_paid = 0, balance = 1000 WHERE student_id IN (2,3);

SELECT 'Before error handling demo:' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id IN (2,3);

START TRANSACTION;
SELECT 'Transaction started' AS '';

-- Valid update 1
UPDATE students SET amount_paid = amount_paid + 100, balance = balance - 100 WHERE student_id = 2;
SAVEPOINT valid_update;
SELECT 'Valid update completed (Student 2 paid 100):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 2;

-- Simulate error detection by checking balance (using SELECT to show condition)
SELECT 'Checking if Student 3 has sufficient balance for payment...' AS '';
SET @current_balance = (SELECT balance FROM students WHERE student_id = 3);
SELECT CONCAT('Student 3 current balance: ', @current_balance) AS balance_info;

-- If balance < 200, we'll rollback to savepoint (simulating error)
-- Using a conditional approach with a dummy update that we'll rollback
UPDATE students SET amount_paid = amount_paid + 200, balance = balance - 200 WHERE student_id = 3 AND balance >= 200;

-- Check if update affected any row
SELECT 'Attempted to update Student 3 (only if balance >= 200)' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 3;

-- Since balance is 1000, update succeeded. But to simulate error, we'll rollback anyway
ROLLBACK TO valid_update;
SELECT 'ERROR SIMULATED! Rolled back to valid_update - Student 3 update undone' AS '';

SELECT 'After error handling (only Student 2 payment saved):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id IN (2,3);

COMMIT;
SELECT 'Transaction committed' AS '';

-- ============================================
-- TASK 6: Compare Full Rollback vs Partial Rollback
-- ============================================

SELECT '===== TASK 6: COMPARE FULL ROLLBACK VS PARTIAL ROLLBACK =====' AS '';

-- Reset data
UPDATE students SET amount_paid = 0, balance = 1000 WHERE student_id IN (4,5);

SELECT 'Initial state for comparison:' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id IN (4,5);

-- DEMO 1: Partial Rollback
SELECT '--- PARTIAL ROLLBACK DEMO ---' AS '';
START TRANSACTION;
UPDATE students SET amount_paid = amount_paid + 100, balance = balance - 100 WHERE student_id = 4;
SAVEPOINT sp1;
UPDATE students SET amount_paid = amount_paid + 200, balance = balance - 200 WHERE student_id = 5;
SELECT 'After both updates (before partial rollback):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id IN (4,5);
ROLLBACK TO sp1;
SELECT 'After PARTIAL ROLLBACK (only Student 4 update remains):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id IN (4,5);
COMMIT;
SELECT 'Partial rollback committed' AS '';

-- Reset again
UPDATE students SET amount_paid = 0, balance = 1000 WHERE student_id IN (4,5);

-- DEMO 2: Full Rollback
SELECT '--- FULL ROLLBACK DEMO ---' AS '';
START TRANSACTION;
UPDATE students SET amount_paid = amount_paid + 100, balance = balance - 100 WHERE student_id = 4;
UPDATE students SET amount_paid = amount_paid + 200, balance = balance - 200 WHERE student_id = 5;
SELECT 'After both updates (before full rollback):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id IN (4,5);
ROLLBACK;
SELECT 'After FULL ROLLBACK (both updates undone):' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id IN (4,5);

SELECT 'Comparison: Partial rollback keeps some changes, full rollback undoes ALL changes' AS '';

-- ============================================
-- TASK 7: Multi-Step Payment Using SAVEPOINT
-- ============================================

SELECT '===== TASK 7: MULTI-STEP PAYMENT USING SAVEPOINT =====' AS '';

-- Reset Student 1
UPDATE students SET amount_paid = 0, balance = 1000 WHERE student_id = 1;

SELECT 'Starting multi-step payment process:' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 1;

START TRANSACTION;
SELECT 'Transaction started' AS '';

-- Step 1: Pay 200
UPDATE students SET amount_paid = amount_paid + 200, balance = balance - 200 WHERE student_id = 1;
SAVEPOINT step1;
SELECT 'Step 1: Paid ₹200' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 1;

-- Step 2: Pay 300
UPDATE students SET amount_paid = amount_paid + 300, balance = balance - 300 WHERE student_id = 1;
SAVEPOINT step2;
SELECT 'Step 2: Paid additional ₹300 (Total ₹500)' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 1;

-- Step 3: Pay 250
UPDATE students SET amount_paid = amount_paid + 250, balance = balance - 250 WHERE student_id = 1;
SAVEPOINT step3;
SELECT 'Step 3: Paid additional ₹250 (Total ₹750)' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 1;

-- Suppose customer wants to cancel step 3
ROLLBACK TO step2;
SELECT 'Customer cancelled Step 3 - Rolled back to step2' AS '';
SELECT 'Final payment: ₹500 (Steps 1 and 2 kept, Step 3 undone)' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 1;

COMMIT;
SELECT 'Transaction committed - ₹500 payment saved' AS '';

-- ============================================
-- TASK 8: Mixed Operations with Nested Logic Simulation
-- ============================================

SELECT '===== TASK 8: MIXED OPERATIONS WITH NESTED LOGIC SIMULATION =====' AS '';

-- Reset data
UPDATE students SET amount_paid = 0, balance = 1000, marks = 65 WHERE student_id = 2;
UPDATE students SET marks = 82 WHERE student_id = 3;

SELECT 'Before mixed operations:' AS '';
SELECT student_id, name, amount_paid, balance, marks FROM students WHERE student_id IN (2,3);

START TRANSACTION;
SELECT 'Transaction started' AS '';

-- Operation 1: Update marks
UPDATE students SET marks = marks + 5 WHERE student_id = 2;
SAVEPOINT op1;
SELECT 'Op1: Increased marks for Student 2 by 5' AS '';
SELECT student_id, name, marks FROM students WHERE student_id = 2;

-- Operation 2: Update payment
UPDATE students SET amount_paid = amount_paid + 150, balance = balance - 150 WHERE student_id = 2;
SAVEPOINT op2;
SELECT 'Op2: Student 2 paid ₹150' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 2;

-- Operation 3: Update another student
UPDATE students SET marks = marks + 10 WHERE student_id = 3;
SAVEPOINT op3;
SELECT 'Op3: Increased marks for Student 3 by 10' AS '';
SELECT student_id, name, marks FROM students WHERE student_id = 3;

-- Operation 4: Update payment for student 3
UPDATE students SET amount_paid = amount_paid + 100, balance = balance - 100 WHERE student_id = 3;
SELECT 'Op4: Student 3 paid ₹100' AS '';
SELECT student_id, name, amount_paid, balance FROM students WHERE student_id = 3;

SELECT 'After all operations:' AS '';
SELECT student_id, name, amount_paid, balance, marks FROM students WHERE student_id IN (2,3);

-- Simulate need to rollback only the last two operations
ROLLBACK TO op2;
SELECT 'Rolled back to op2 (Ops 3 and 4 undone, Ops 1 and 2 kept)' AS '';

SELECT 'Final state after partial rollback:' AS '';
SELECT student_id, name, amount_paid, balance, marks FROM students WHERE student_id IN (2,3);

COMMIT;
SELECT 'Transaction committed' AS '';

-- ============================================
-- OBSERVATION QUESTIONS (Answers)
-- ============================================

SELECT '===== OBSERVATION QUESTIONS =====' AS '';

SELECT '----- Question 1 -----' AS '';
SELECT 'What is SAVEPOINT?' AS 'Question';
SELECT 'Answer: SAVEPOINT is a marker within a transaction that allows partial rollback.' AS 'Answer';
SELECT 'It lets you undo specific parts of a transaction without rolling back the entire transaction.' AS 'Explanation';

SELECT '----- Question 2 -----' AS '';
SELECT 'Difference between ROLLBACK and ROLLBACK TO?' AS 'Question';
SELECT 'Answer: ROLLBACK undoes the ENTIRE transaction. ROLLBACK TO savepoint undoes only changes after that savepoint.' AS 'Answer';
SELECT 'ROLLBACK ends the transaction; ROLLBACK TO keeps the transaction active.' AS 'Explanation';

SELECT '----- Question 3 -----' AS '';
SELECT 'Can we rollback after COMMIT?' AS 'Question';
SELECT 'Answer: NO. Once COMMIT is executed, changes become permanent and cannot be rolled back.' AS 'Answer';
SELECT 'COMMIT ends the transaction and saves all changes to the database permanently.' AS 'Explanation';

SELECT '----- Question 4 -----' AS '';
SELECT 'Can multiple SAVEPOINTS exist?' AS 'Question';
SELECT 'Answer: YES. You can create multiple savepoints within a single transaction.' AS 'Answer';
SELECT 'Each savepoint must have a unique name within the transaction.' AS 'Explanation';

SELECT '----- Question 5 -----' AS '';
SELECT 'What happens when rolling back to earlier savepoint?' AS 'Question';
SELECT 'Answer: All savepoints created after that savepoint are released (deleted).' AS 'Answer';
SELECT 'Changes made after the earlier savepoint are undone, but the transaction remains active.' AS 'Explanation';

-- ============================================
-- FINAL SUMMARY
-- ============================================

SELECT '===== FINAL SUMMARY =====' AS '';
SELECT 'Final Students Table State:' AS '';
SELECT student_id, name, amount_paid, balance, marks FROM students ORDER BY student_id;

SELECT 'SAVEPOINT Concepts Demonstrated:' AS '';
SELECT '✓ SAVEPOINT - Create a marker within transaction' AS '';
SELECT '✓ ROLLBACK TO savepoint - Partial rollback' AS '';
SELECT '✓ Multiple savepoints - s1, s2, s3 within same transaction' AS '';
SELECT '✓ Release savepoints - Rolling back to earlier releases later ones' AS '';
SELECT '✓ Partial vs Full rollback - Selective undo vs complete undo' AS '';
SELECT '✓ Error handling - Using savepoints for graceful error recovery' AS '';
SELECT '✓ Nested operations - Complex multi-step transactions' AS '';

-- ============================================
-- END OF LAB - 9
-- ============================================

SELECT '===== ALL TASKS COMPLETED SUCCESSFULLY =====' AS '';
SELECT 'Tasks Completed:' AS '';
SELECT '✓ Task 1: Basic SAVEPOINT and rollback to s1' AS '';
SELECT '✓ Task 2: Multiple SAVEPOINTS and rollback to s2' AS '';
SELECT '✓ Task 3: Payment scenario with partial rollback' AS '';
SELECT '✓ Task 4: DELETE + SAVEPOINT + rollback' AS '';
SELECT '✓ Task 5: Error handling using SAVEPOINT' AS '';
SELECT '✓ Task 6: Compare full rollback vs partial rollback' AS '';
SELECT '✓ Task 7: Multi-step payment using SAVEPOINT' AS '';
SELECT '✓ Task 8: Mixed operations with nested logic simulation' AS '';
SELECT '✓ Observation Questions Answered' AS '';

-- ============================================
-- End of Lab - 9
-- ============================================