Structural testing checked the building. Now functional testing checks if everything inside works. Can you insert data? Can you read it back correctly? Does updating one record accidentally change another? Do triggers fire when they should? This is where most database bugs hide.
CRUD stands for Create, Read, Update, Delete. Every application performs these four operations. Every one of them can break in subtle ways.
CREATE: Insert a record via the UI. Verify in DB that all fields saved correctly — no truncation, no wrong encoding, correct timestamps.
READ: Query the record. Does SELECT return exactly what was inserted? Check for character encoding issues (Hindi names, special characters).
UPDATE: Modify a field via the UI. Verify only that field changed in DB. Check that updated_at timestamp changed. Check that other records were NOT affected.
DELETE: Delete a record. Verify it is gone from the table. Check that child records were handled (cascaded delete or set to NULL). Check that the record does not reappear on page refresh.
-- STEP 1: CREATE — Insert a new user via UI, then verify
SELECT * FROM users WHERE email = 'neha.gupta@email.com';
-- Verify: full_name, phone, created_at all correct
-- STEP 2: READ — Verify data matches what UI displays
SELECT account_number, balance, account_type
FROM accounts
WHERE user_id = 42;
-- Compare these values with what the dashboard shows
-- STEP 3: UPDATE — Change profile name via UI, then verify
SELECT full_name, updated_at FROM users WHERE user_id = 42;
-- Verify: name changed AND updated_at has new timestamp
-- STEP 4: DELETE — Delete account via UI, then verify
SELECT * FROM users WHERE user_id = 42;
-- Should return 0 rows
-- Also check child tables
SELECT * FROM orders WHERE user_id = 42;
SELECT * FROM accounts WHERE user_id = 42;
-- Should also return 0 rows if CASCADE DELETE is setTriggers are automatic actions that fire when data changes. A trigger might automatically update a balance after an order, log an audit trail, or calculate a tax. If the trigger is broken, the side effect does not happen — and nobody notices until it is too late.
-- Scenario: After inserting an order, a trigger should
-- deduct the amount from the account balance
-- Step 1: Check balance BEFORE the order
SELECT balance FROM accounts WHERE user_id = 42;
-- Result: 10000.00
-- Step 2: Insert an order (or place it via UI)
INSERT INTO orders (user_id, total_amount, status)
VALUES (42, 1500.00, 'completed');
-- Step 3: Check balance AFTER the order
SELECT balance FROM accounts WHERE user_id = 42;
-- Expected: 8500.00 (10000 - 1500)
-- If still 10000.00, the trigger did not fire!Stored procedures are pre-written SQL programs stored in the database. They handle complex business logic — like calculating interest, processing refunds, or generating reports. Testing them means calling them with valid and invalid inputs and checking the output.
-- Test a stored procedure that transfers money between accounts
-- Happy path: Valid transfer
CALL transfer_money(101, 102, 5000.00);
-- Verify: Account 101 balance decreased by 5000
-- Verify: Account 102 balance increased by 5000
-- Edge case: Transfer more than available balance
CALL transfer_money(101, 102, 9999999.00);
-- Expected: Should fail or return an error
-- Verify: Neither balance changed
-- Edge case: Transfer to non-existent account
CALL transfer_money(101, 99999, 500.00);
-- Expected: Should fail
-- Verify: Account 101 balance unchangedWhen testing stored procedures, always check the database state BEFORE and AFTER the call. Do not trust the return value alone. The procedure might say "success" but leave the data in a broken state.
Q: How do you test CRUD operations at the database level?
A: For each CRUD operation, I perform the action via the UI and then verify the result directly in the database using SQL. For CREATE, I check that all fields saved correctly without truncation. For READ, I compare the database values with what the UI displays. For UPDATE, I verify only the intended field changed and timestamps updated. For DELETE, I verify the record is removed and check cascade behavior on child tables. I also test boundary values, special characters, and NULL handling for each operation.
Key Point: Functional testing verifies CRUD operations, triggers, and stored procedures work correctly with real data.