You do not just open a SQL client and start firing random queries. Database testing follows a structured process. Here is how experienced QA engineers approach it — step by step.
Understand the Schema: Get the ER diagram or run DESCRIBE on all tables. Know the tables, their relationships (foreign keys), column types, and constraints. You cannot test what you do not understand.
Identify Test Scenarios: Map each feature to its database operations. User registration? That is an INSERT into users. Money transfer? That is an UPDATE on two accounts + INSERT into transactions. Write scenarios for happy path, edge cases, and error cases.
Set Up Test Data: Create the data you need. Sometimes you insert it manually. Sometimes you use scripts. Make sure you have data for boundary values, NULL values, special characters, and large volumes.
Write and Run Test Queries: Write SQL to verify each scenario. Run your queries BEFORE and AFTER performing actions via the UI. Compare expected results with actual results.
Verify and Document Results: Did the query return what you expected? If not, log it as a bug. Include the SQL query, expected result, actual result, and the state of related tables.
Clean Up Test Data: Remove or reset the data you created. Never leave test data in shared environments. Other testers might pick it up and get confused.
-- STEP 1: Understand the schema
DESCRIBE accounts;
DESCRIBE transactions;
-- STEP 2 & 3: Set up test data and check initial state
SELECT account_id, balance FROM accounts WHERE account_id IN (101, 102);
-- Account 101: balance = 50000.00
-- Account 102: balance = 20000.00
-- STEP 4: Perform transfer of Rs 5000 from 101 to 102 via UI
-- Then verify with SQL:
-- Check sender balance decreased
SELECT balance FROM accounts WHERE account_id = 101;
-- Expected: 45000.00
-- Check receiver balance increased
SELECT balance FROM accounts WHERE account_id = 102;
-- Expected: 25000.00
-- Check transaction record was created
SELECT * FROM transactions
WHERE sender_account = 101
AND receiver_account = 102
AND amount = 5000.00
ORDER BY created_at DESC
LIMIT 1;
-- Should return exactly 1 row with status = 'completed'
-- STEP 5: Verify total money in system is unchanged
-- (Conservation check — no money created or destroyed)
SELECT SUM(balance) FROM accounts WHERE account_id IN (101, 102);
-- Expected: 70000.00 (same as before transfer: 50000 + 20000)Always do the "conservation check" for financial features. The total money in the system before the transaction should equal the total money after. If Rs 5000 left one account, exactly Rs 5000 should arrive in another. No more, no less.
Q: Describe your approach to database testing for a new feature.
A: First, I study the schema — ER diagram, table structures, relationships, and constraints. Then I map the feature to database operations (which tables are affected, what INSERT/UPDATE/DELETE happens). I prepare test data covering happy path, edge cases, and negative scenarios. I run SQL queries before and after each action to verify the expected changes. I check data integrity, constraint enforcement, cascade behavior, and timestamp updates. Finally, I document results and clean up test data.
Key Point: Follow a structured process: understand schema, identify scenarios, set up data, run queries, verify results, clean up.