SQL for QA: The Queries You Actually Need

A UI test that clicks "Submit Order" and sees a confirmation screen doesn't tell you whether the order was actually written to the database. SQL does. This article covers the five query patterns that handle 80% of QA database work: SELECT with WHERE, JOINs, COUNT and GROUP BY, NULL checks, and how to run queries directly from Playwright tests to verify data persistence.

Why QA engineers need SQL

UI tests verify what the user sees. SQL verifies what actually happened.

When a test clicks "Submit Order" and sees a confirmation screen, you know the UI responded. You don't know if the order was saved. You don't know if the right status was set. You don't know if the foreign key was written correctly. The only way to verify that is to look at the database directly.

QA SQL use cases:

• Verify data was saved after a UI action (form submit, file upload, payment)
• Set up test data directly instead of going through 10 UI clicks
• Check data the UI doesn't expose (audit logs, internal flags, soft-deleted records)
• Reproduce bugs by checking what state the database was in when something failed
• Validate migrations after a deployment changed the schema

When a job posting says "SQL experience required," this is what they mean. Not stored procedures, not query optimization, not database administration. SELECT statements with conditions.

The tool: any SQL client

You don't need to know a specific database product. The query syntax is nearly identical across PostgreSQL, MySQL, and SQLite. For practice:

• TablePlus (Mac/Windows): clean interface, free tier is enough
• DBeaver: free, works with every database type
• psql: PostgreSQL command line, always available
• Any Playwright test with a pg or mysql2 package can also run queries directly

For the examples below, the structure is based on lab.becomeqa.com's database schema.

Pattern 1: SELECT

The most common thing you'll do:

-- Get all users
SELECT * FROM users;

-- Get specific columns
SELECT id, email, created_at FROM users;

-- Get one user by email
SELECT * FROM users WHERE email = 'admin@becomeqa.com';

-- Get users created in the last 7 days
SELECT * FROM users WHERE created_at > NOW() - INTERVAL '7 days';

-- After a registration test, verify the user was created
SELECT id, email, role, is_active 
FROM users 
WHERE email = 'testuser_1234567@test.com';

If this returns a row, user was saved. If it returns nothing, the registration silently failed even though the UI showed success.

Pattern 2: WHERE with conditions

Combining conditions:

-- AND: both conditions must be true
SELECT * FROM items 
WHERE status = 'completed' AND user_id = 42;

-- OR: either condition must be true
SELECT * FROM items 
WHERE status = 'pending' OR status = 'in_progress';

-- IN: match any value in a list
SELECT * FROM items 
WHERE status IN ('pending', 'in_progress', 'completed');

-- NOT: exclude rows
SELECT * FROM items WHERE status != 'deleted';

-- LIKE: partial string match (% is wildcard)
SELECT * FROM users WHERE email LIKE '%@test.com';

-- IS NULL: check for missing values
SELECT * FROM items WHERE deleted_at IS NULL;
SELECT * FROM items WHERE deleted_at IS NOT NULL;

-- Verify soft-delete worked (deleted_at should be set)
SELECT id, title, deleted_at 
FROM items 
WHERE id = 99;

-- Find all test data to clean up after a test run
SELECT * FROM users WHERE email LIKE '%testuser_%@test.com';

Pattern 3: JOIN

Real data lives across multiple tables. A travel item belongs to a user. An order belongs to a customer and contains products. You need JOIN to see the full picture.

-- Basic JOIN: items with their owner's email
SELECT items.id, items.title, items.status, users.email
FROM items
JOIN users ON items.user_id = users.id;

-- Filter the joined result
SELECT items.id, items.title, users.email
FROM items
JOIN users ON items.user_id = users.id
WHERE users.email = 'admin@becomeqa.com';

The pattern is always:

SELECT [columns you want]
FROM [main table]
JOIN [related table] ON [how they connect]
WHERE [optional filter]

-- After adding an item as admin, verify it's linked to the right user
SELECT items.title, items.status, users.email AS owner
FROM items
JOIN users ON items.user_id = users.id
WHERE items.title = 'Tokyo'
ORDER BY items.created_at DESC
LIMIT 1;

Pattern 4: COUNT and aggregate functions

When you need numbers, not rows:

-- How many users are there?
SELECT COUNT(*) FROM users;

-- How many active users?
SELECT COUNT(*) FROM users WHERE is_active = true;

-- How many items per status?
SELECT status, COUNT(*) AS total
FROM items
GROUP BY status;

-- Most recent item creation date
SELECT MAX(created_at) FROM items;

-- Total number of items per user
SELECT user_id, COUNT(*) AS item_count
FROM items
GROUP BY user_id
ORDER BY item_count DESC;

-- After a bulk import test, verify the right number of records were created
SELECT COUNT(*) FROM items WHERE created_at > '2026-05-15 10:00:00';

-- Verify test data is isolated (no cross-contamination between test runs)
SELECT user_id, COUNT(*) FROM items GROUP BY user_id;

Pattern 5: ORDER BY and LIMIT

Control which rows you get and in what order:

-- Most recently created items first
SELECT * FROM items ORDER BY created_at DESC;

-- Oldest first
SELECT * FROM items ORDER BY created_at ASC;

-- Only the 5 most recent
SELECT * FROM items ORDER BY created_at DESC LIMIT 5;

-- Page 2 of results (rows 11–20)
SELECT * FROM items ORDER BY id LIMIT 10 OFFSET 10;

-- After a test creates an item, grab the one that was just created
SELECT * FROM items 
WHERE user_id = 42 
ORDER BY created_at DESC 
LIMIT 1;

Putting it together: a full verification query

A test flow: user logs in, adds a travel item named "Paris", marks it as "Completed." Here's how you verify the complete operation in SQL:

SELECT 
    items.id,
    items.title,
    items.status,
    items.created_at,
    users.email AS owner
FROM items
JOIN users ON items.user_id = users.id
WHERE items.title = 'Paris'
    AND items.status = 'completed'
    AND users.email = 'admin@becomeqa.com'
ORDER BY items.created_at DESC
LIMIT 1;

If this returns a row, the entire flow worked from start to finish. If it returns nothing, something failed silently between login and status update.

Using SQL in Playwright tests

You can run SQL directly from your test code using a database client library:

import { test, expect } from '@playwright/test';
import { Client } from 'pg'; // npm install pg

test('item is saved to database after creation', async ({ page }) => {
    // Do the UI action
    await page.goto('/');
    // ... login, add item named 'Tokyo' ...

    // Verify in the database
    const db = new Client({ connectionString: process.env.DATABASE_URL });
    await db.connect();
    
    const result = await db.query(
        'SELECT * FROM items WHERE title = $1 ORDER BY created_at DESC LIMIT 1',
        ['Tokyo']
    );
    
    expect(result.rows.length).toBe(1);
    expect(result.rows[0].status).toBe('planned');
    
    await db.end();
});

This pattern is powerful: UI action + database verification in one test. The UI test proves the app responded correctly; the database query proves the data was actually persisted.

Common mistakes

What you don't need (yet)

• Subqueries
• Window functions (ROW_NUMBER, RANK)
• CTEs (WITH clauses)
• Stored procedures and functions
• Database indexes and query planning
• Schema design and normalization

These matter for database developers. For QA, you read data. The five patterns above are the whole job 95% of the time.

FAQ

PostgreSQL. It's the most common in modern web applications, has the best tooling, and the syntax is standard enough that switching to MySQL or SQLite takes minutes. Install TablePlus, connect to any PostgreSQL instance, and practice there.

Yes. Sites like sqlfiddle.com and db-fiddle.com let you create tables and run queries in the browser without installing anything. Create a users table and an items table, insert some rows, and practice the five patterns above.

Both. SQL is invaluable for manual debugging. When you can't figure out why a test is failing, check the database. It's also valuable in automated tests for data verification that the UI can't provide. Start with debugging, add automated database assertions once you're comfortable with the queries.

For QA purposes, almost nothing. PostgreSQL uses $1 for parameters, MySQL uses ?, but the SELECT/WHERE/JOIN syntax is identical. The five patterns above work in all three.