The Complete SQL for Testers Guide (2026)

SQL for Testers

SQL for Testers  Queries, Joins & DB Testing

A UI can say "Order placed" while the database holds the wrong amount, a duplicate record, or nothing at all. SQL lets a tester check what really happened. This guide is the hub for everything SQL on NavTutorial: why testers need it, the core queries with worked examples, how to test a database, how to run SQL from automation, and a learning path through the detailed lessons.

Why Testers Need SQL

• Verify the back end — after a UI or API action, confirm the right rows were created, updated or deleted with the right values.
• Find hidden defects — duplicates, orphan records, wrong statuses, missing audit rows — none of which the UI shows.
• Prepare and clean test data — find a user in a particular state, or reset data before a run.
• Investigate bugs — narrow a UI defect down to bad data, a failed job or a wrong calculation.
• Pass interviews — SQL questions appear in almost every QA and SDET interview.

How much do you need? Solid SELECT queries, joins, grouping and subqueries cover most tester work. Writing stored procedures or tuning databases is rarely expected.

The Sample Data Used in This Guide

Every query below was run against two small tables:

• employees — id, name, dept, salary, manager_id, status, email. Six employees in QA and DEV; two share the top salary of 95,000; one has a NULL email; two share an email address.
• orders — order_id, emp_id, amount, status. Four orders, one belonging to a non-existent employee and one with a NULL amount.

The full table is printed in the SQL Cheat Sheet for Testers. The imperfections are deliberate — they're exactly the problems testers look for.

Part 1 — Query Basics

SQL command types

DDL defines structure (CREATE, ALTER, DROP, TRUNCATE); DML works with data (SELECT, INSERT, UPDATE, DELETE); DCL manages permissions (GRANT, REVOKE); TCL controls transactions (COMMIT, ROLLBACK). Testers mostly write SELECT. Lesson: Basic SQL Commands.

SELECT and filtering

SELECT name, salary
FROM employees
WHERE dept = 'QA'
  AND salary BETWEEN 50000 AND 90000
ORDER BY salary DESC;
-- → Naveen 85000, Asha 72000, Nikhil 60000

The operators you'll use most: =, <>, IN, BETWEEN, LIKE (% any characters, _ one), IS NULL, DISTINCT and LIMIT.

The NULL trap: WHERE email = NULL returns 0 rows — always. NULL means "unknown", so comparisons with it are never true. Use IS NULL (which finds the one employee without an email). Lesson: SELECT & Filtering.

Joins

-- Each employee with their manager (self join)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Orders that point to employees who don't exist — a data-integrity bug
SELECT o.*
FROM orders o
LEFT JOIN employees e ON e.id = o.emp_id
WHERE e.id IS NULL;
-- → order 12347, emp_id 9

INNER JOIN keeps only matches; LEFT JOIN keeps every left row with NULLs where nothing matches — which is exactly how you find missing or orphaned records. Lessons: SQL Joins Explained and SQL Joins Compared.

Aggregates and GROUP BY

SELECT dept, COUNT(*) AS total, AVG(salary) AS avg_salary
FROM employees
WHERE status = 'active'
GROUP BY dept
HAVING COUNT(*) >= 2;
-- → DEV 2 82500, QA 3 72333.33

WHERE filters rows before grouping; HAVING filters groups afterwards. Watch NULLs here too: COUNT(amount) and AVG(amount) skip NULL values, so on the orders table the average is taken over 3 orders, not 4. Lesson: Aggregates & Grouping.

Part 2 — Advanced Queries

Subqueries and rankings

-- Second-highest salary — correct even with a tie at the top
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
-- → 85000

-- Nth highest with a window function
SELECT DISTINCT salary FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees
) t WHERE rnk = 2;
-- → 85000

RANK leaves gaps after ties (1, 1, 3), DENSE_RANK doesn't (1, 1, 2), ROW_NUMBER numbers every row uniquely. Lesson: Subqueries & Nested Queries.

Changing data and transactions

START TRANSACTION;
UPDATE employees SET salary = salary + 1000 WHERE id = 2;   -- 72000 → 73000
ROLLBACK;                                                     -- back to 72000

Never run UPDATE or DELETE without a WHERE clause, and only against test databases. Wrapping test-data changes in a transaction and rolling back keeps the database clean. ACID — atomicity, consistency, isolation, durability — is a favourite interview topic. Lesson: INSERT, UPDATE, DELETE & ACID.

Views, indexes and normalisation

A view is a saved query; an index speeds up lookups on the columns you search by (at the cost of slower writes); normalisation (1NF, 2NF, 3NF) removes duplicated data so updates happen in one place. Testers meet these when checking reports built on views, investigating slow screens, and understanding why data lives in several tables. Lesson: Views, Indexes & Normalisation.

Part 3 — Database Testing

Database testing checks that data is stored, changed and protected correctly. The main areas:

-- Duplicate emails (a broken uniqueness rule)
SELECT email, COUNT(*) AS cnt
FROM employees
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;
-- → asha@x.com | 2

More ready-to-use checks: SQL Queries for Testers. Scenario questions: Scenario-Based SQL Questions.

Performance, briefly

Testers don't usually tune databases, but you should recognise the signs: a screen that's slow only with production-size data, a query scanning a whole table because a column isn't indexed, or SELECT * pulling far more data than needed. Lesson: SQL Performance & Best Practices.

Part 4 — SQL in Automation

Automated UI and API tests can verify the database directly with JDBC:

String sql = "SELECT status, amount FROM orders WHERE order_id = ?";
try (Connection con = DriverManager.getConnection(dbUrl, dbUser, System.getenv("DB_PASSWORD"));
     PreparedStatement ps = con.prepareStatement(sql)) {
    ps.setInt(1, orderId);
    try (ResultSet rs = ps.executeQuery()) {
        Assert.assertTrue(rs.next(), "order not saved");
        Assert.assertEquals(rs.getString("status"), "PAID");
    }
}

Use PreparedStatement with parameters (never string concatenation), read credentials from environment variables, keep database checks for the facts the UI or API can't show, and use a read-only test account where possible. Lesson: JDBC & Database Connectivity.

Learning Path

Practise: install MySQL or PostgreSQL locally (or use an online SQL playground), create two related tables with a few deliberate problems — a duplicate, a NULL, an orphan — and write a query to find each one.

Interview Preparation

The SQL questions testers are asked most: WHERE vs HAVING, the join types, second or Nth highest salary, finding duplicates, DELETE vs TRUNCATE vs DROP, primary vs foreign key, NULL handling, GROUP BY vs ORDER BY, and a scenario such as "how would you verify an order in the database?". Answers: Top 25 SQL Interview Questions for Testers.

Then practise writing queries under time pressure in the SQL Interview Simulator.

From Real Projects

Much of my testing on Apkope and Canolog was about data: prospect and customer data captured from many channels on Apkope, and records flowing between sales, inventory, finance and service on Canolog. Checking what the application shows is only half the job — the data behind it has to be right too, which is where SQL helps testers most. Start with SELECT, WHERE and JOIN — they cover most everyday testing checks.

📚 Official documentation: MySQL Reference Manual

Frequently Asked Questions

Why do software testers need SQL?

To verify back-end data after UI and API actions, find integrity problems the UI hides, prepare test data and investigate defects.

How much SQL should a tester know?

SELECT with filtering, NULL handling, joins, GROUP BY/HAVING, subqueries and basic data modification — enough to validate data confidently.

What are the types of SQL joins?

INNER, LEFT, RIGHT, FULL OUTER, plus self and cross joins. LEFT JOIN with IS NULL is the tester's favourite for finding missing or orphaned records.

What is the difference between WHERE and HAVING?

WHERE filters rows before grouping; HAVING filters groups after aggregation.

How do you find the Nth highest salary?

With a subquery for the second-highest, or DENSE_RANK() for any N.

What is database testing?

Checking that data is stored, changed and protected correctly — data validation, CRUD, integrity, business rules, transactions, migrations and security.

Related Guides

• SQL Cheat Sheet for Testers
• The Complete Java for Testers Guide
• The Complete API Testing Tutorial
• The Complete Manual Testing Tutorial