============================================================
Exercise 1: Rent a DVD (Basic Transaction)
[Scenario] When a customer rents a DVD, you need to:
- Create a rental record
- Create a payment record
Both must succeed or fail together!
============================================================

	>>> Try this SQL
   
START TRANSACTION;

-- Step 1: Record the rental
-- Customer 1 rents inventory item 367 (store 1, staff 1)
INSERT INTO rental (rental_date, inventory_id, customer_id, staff_id)
VALUES (NOW(), 367, 1, 1);

-- Get the rental_id we just created
SET @rental_id = LAST_INSERT_ID();

-- Step 2: Record the payment ($4.99 rental fee)
INSERT INTO payment (customer_id, staff_id, rental_id, amount, payment_date)
VALUES (1, 1, @rental_id, 4.99, NOW());

-- Check if everything looks good
SELECT * FROM rental WHERE rental_id = @rental_id;
SELECT * FROM payment WHERE rental_id = @rental_id;

-- If everything is correct:
COMMIT;
-- If something is wrong:
-- ROLLBACK;

============================================================
Exercise 2: Test ROLLBACK (Intentional Failure)
[Scenario] Practice rolling back when something goes wrong
============================================================

	>>> Try this SQL

START TRANSACTION;

-- Create a new customer
INSERT INTO customer (store_id, first_name, last_name, email, address_id, create_date)
VALUES (1, 'John', 'Doe', 'john.doe@sakilacustomer.org', 5, NOW());

SET @new_customer_id = LAST_INSERT_ID();

-- Check the customer was created
SELECT * FROM customer WHERE customer_id = @new_customer_id;

-- Oops! We made a mistake. Cancel everything!
ROLLBACK;

-- Now check again - the customer should NOT exist
SELECT * FROM customer WHERE customer_id = @new_customer_id;