SQL for QA Engineers: Database Testing Fundamentals

AlternateQA Team•July 17, 2026

A QA engineer who can write SQL can test what the UI can't show. Learn the essential SQL queries, database testing strategies, and data validation techniques that will elevate your testing to a new level.

Why QA Engineers Need SQL

Most bugs have a data dimension. A user sees the wrong balance, a report shows duplicate records, or a cascading delete removes more data than intended. These bugs are invisible to UI testing because the UI shows what it's told — you need to look at the source of truth: the database.

SQL (Structured Query Language) is the universal language for querying relational databases (PostgreSQL, MySQL, SQL Server, SQLite). For a QA engineer, SQL skills are a force multiplier. With a few well-crafted queries, you can:

Verify that API calls are actually persisting the correct data
Find data integrity violations that would never surface in UI testing
Create precisely-tailored test data
Validate complex business rules that the UI simplifies away
Debug discrepancies between what the UI shows and what's actually stored

Essential SQL for QA: The Core Queries

SELECT: Reading the Data

-- Get all users created in the last 7 days
SELECT id, email, created_at, status
FROM users
WHERE created_at >= NOW() - INTERVAL '7 days'
ORDER BY created_at DESC;

-- Find a specific user by email
SELECT * FROM users WHERE email = 'testuser@example.com';

Counting and Aggregation

-- Count total orders per user (useful after an API call that should create an order)
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
ORDER BY order_count DESC;

-- Find the average order value
SELECT AVG(total_amount) as avg_order_value FROM orders;

-- Check for duplicate email addresses (should be zero)
SELECT email, COUNT(*) as occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

JOINs: Connecting Related Data

-- Get order details with user information (INNER JOIN)
SELECT 
    o.id as order_id,
    u.email,
    o.total_amount,
    o.status,
    o.created_at
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC;

-- Find users who have never placed an order (LEFT JOIN)
SELECT u.id, u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

Checking Data After CRUD Operations

This is the most common QA use case. After running a test that creates, updates, or deletes data, immediately query the database to verify:

-- After a user registration API call:
-- Verify user was created with correct data
SELECT id, email, status, email_verified_at
FROM users
WHERE email = 'newtest@example.com';
-- Expected: 1 row, status='inactive', email_verified_at=NULL

-- After an email verification:
SELECT email_verified_at, status FROM users
WHERE email = 'newtest@example.com';
-- Expected: email_verified_at is NOT NULL, status='active'

Database Testing Strategies

1. Data Integrity Testing

Data integrity means the data in your database is accurate, consistent, and valid. Key checks:

Referential Integrity: Verify that foreign key relationships are enforced. Can you create an order with a non-existent user_id?

-- Check for orphaned orders (orders with no corresponding user)
SELECT o.id, o.user_id
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;
-- Expected: 0 rows

Null Constraints: Verify that required fields cannot be null.

-- Check for users with null email (should be impossible if constrained)
SELECT COUNT(*) FROM users WHERE email IS NULL;
-- Expected: 0

Uniqueness Constraints: As shown above, check for duplicate records in fields that should be unique.

2. Boundary Value Testing at the Database Level

What happens when data reaches a column's maximum size? Test that the application handles the database's column length constraints gracefully.

-- Check the max length of the username column in the schema
SELECT column_name, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'username';
-- Then test with a string of exactly that length, and one character longer

3. Cascading Delete Testing

When you delete a parent record, what happens to its children? This is a frequently broken behavior.

-- Before deleting a user, note how many related records exist
SELECT COUNT(*) FROM orders WHERE user_id = 42;
SELECT COUNT(*) FROM sessions WHERE user_id = 42;

-- Delete the user (or trigger deletion via the API)
-- DELETE FROM users WHERE id = 42;

-- After deletion, verify cascades happened as designed
SELECT COUNT(*) FROM orders WHERE user_id = 42;  -- Should be 0 if cascade delete
SELECT COUNT(*) FROM sessions WHERE user_id = 42;  -- Should be 0

4. Transaction Testing

Financial or inventory applications must process transactions atomically. If a payment is charged but the order creation fails, the user is charged for nothing. Test:

What happens when one step of a multi-step operation fails?
Does the system roll back correctly?
Are there orphaned records after a failed transaction?

Test Data Management with SQL

Creating Test Data

-- Insert a test user with a specific role
INSERT INTO users (email, password_hash, role, status)
VALUES ('qa_test_admin@test.com', '$2b$10$hash', 'admin', 'active')
RETURNING id;

-- Create a bulk set of test products for performance testing
INSERT INTO products (name, price, stock_quantity)
SELECT 
    'Test Product ' || generate_series,
    (RANDOM() * 100 + 1)::numeric(10,2),
    FLOOR(RANDOM() * 1000)
FROM generate_series(1, 1000);

Cleaning Up Test Data

Always clean up test data after your tests. A reliable cleanup query is as important as the setup:

-- Clean up test users and all related data
DELETE FROM orders WHERE user_id IN (SELECT id FROM users WHERE email LIKE '%@test.com');
DELETE FROM users WHERE email LIKE '%@test.com';

Practical Tips for QA Engineers

Read-only access: Ask for SELECT-only database access on production. You never want to accidentally run a DELETE on production. Write INSERT/UPDATE/DELETE queries only against development or staging.
Always use WHERE clauses: Before running any UPDATE or DELETE, run it as a SELECT first to verify you're targeting the right rows.
Use a dedicated SQL client: DBeaver (free), TablePlus, or pgAdmin for PostgreSQL are excellent tools for QA work.
Understand your schema: Spend time reading the database schema (table definitions, column types, constraints, indexes). It reveals the data model and common edge cases.
Compare UI data to DB data: This is the most powerful skill. When something looks wrong in the UI, query the database to see if it's a display bug or a data bug.

SQL is one of the highest-leverage skills a QA engineer can develop. It opens a window into the actual state of the application that no UI can fully reveal, and it empowers you to write significantly more thorough, evidence-based test cases.