Practice Exercises

Time to put everything together. These exercises go from basic to advanced. Try to write the SQL yourself before looking at the hints. The best way to learn stored procedures and triggers is by writing and breaking them.

Exercise 1: Create a Withdraw Procedure

Write a stored procedure called withdraw_cash that takes an account_id and amount. It should: check that the account exists and is active, check sufficient balance, maintain a minimum balance of Rs 1000 (cannot withdraw below this), deduct the amount, and log the withdrawal in a transaction_log table.

-- Skeleton — fill in the logic
DELIMITER //

CREATE PROCEDURE withdraw_cash(
  IN p_account_id VARCHAR(20),
  IN p_amount DECIMAL(12,2),
  OUT p_result VARCHAR(100)
)
BEGIN
  DECLARE v_balance DECIMAL(12,2);
  DECLARE v_min_balance DECIMAL(12,2) DEFAULT 1000.00;

  -- TODO: Add error handler
  -- TODO: Check account exists and is active
  -- TODO: Check balance >= amount + minimum balance
  -- TODO: Deduct amount
  -- TODO: Log withdrawal
  -- TODO: Set result
END //

DELIMITER ;

Exercise 2: Write Test Cases for withdraw_cash

Write at least 6 test cases covering:

1. Valid withdrawal with sufficient balance
2. Withdrawal that would breach minimum balance (balance = 5000, withdraw 4500, min = 1000)
3. Withdrawal of exact available amount (balance - min_balance)
4. Withdrawal from a frozen account
5. Withdrawal of zero or negative amount
6. Withdrawal from a non-existent account

Exercise 3: Build an Audit Trigger

Create an AFTER UPDATE trigger on the employees table that logs changes to the salary column. The audit log should capture: employee_id, old_salary, new_salary, changed_by (CURRENT_USER), changed_at (NOW). The trigger should only fire when salary actually changes — not when other columns are updated.

Exercise 4: Test a Trigger Chain

Given this scenario: inserting a row into order_items fires a trigger that updates the order total in the orders table. The orders table has its own audit trigger that logs total_amount changes. Write SQL to test the entire chain — insert an item, verify the order total changed, and verify the audit log was created.

Exercise 5: JDBC Test Class

Write a Java TestNG class that tests the withdraw_cash procedure using CallableStatement. Include @BeforeMethod to set up test data, @Test methods for valid and invalid withdrawals, and @AfterMethod to rollback. Verify both the OUT parameter result and the actual database state.

Exercise 6: Debug This Procedure

This procedure has three bugs. Find them all.

DELIMITER //

CREATE PROCEDURE apply_discount(
  IN p_order_id INT,
  IN p_discount_percent DECIMAL(5,2),
  OUT p_new_total DECIMAL(12,2)
)
BEGIN
  DECLARE v_current_total DECIMAL(12,2);

  SELECT total_amount INTO v_current_total
  FROM orders WHERE order_id = p_order_id;

  -- Bug 1: What if the order does not exist?

  SET p_new_total = v_current_total - (v_current_total * p_discount_percent);
  -- Bug 2: Discount percent should be divided by 100

  UPDATE orders SET total_amount = p_new_total
  WHERE order_id = p_order_id;

  -- Bug 3: No check for negative discount or discount > 100%
END //

DELIMITER ;