SQL for Testers: The Exact Queries You Need for QA Validation

Most SQL tutorials teach you to build databases. This one teaches you to interrogate them — the way a QA engineer does.

As a tester, you rarely create tables or design schemas. What you do constantly is query existing data to verify that your application behaves correctly. Did the order actually get saved? Did the user's balance update? Did the status change after the API call? SQL answers all of these questions directly.

This guide covers exactly the SQL you need to do your job as a manual tester, automation engineer, or SDET — with real test scenarios for every query.

Why Every QA Engineer Needs SQL

The UI can lie to you.

A user sees "Order confirmed" on screen. But did the order actually land in the database? Was the correct discount applied? Did the inventory count decrement? You cannot know from the frontend alone.

Direct database queries let you:

• Verify test data before running a test
• Assert backend state after a test (independent of the API response)
• Debug failures when the UI shows incorrect data
• Check data integrity across multiple tables after complex operations
• Generate test reports on test execution history

In interviews, being able to write SQL sets you apart from 80% of manual testers who only validate through the UI.

Setting Up a Practice Database

You need a database to practise against. The fastest options:

Option 1 — SQLiteOnline: Open sqliteonline.com in a browser. No installation, runs entirely in the browser. Good enough for all queries in this guide.

Option 2 — MySQL locally: Install MySQL Community Edition. Create a practice schema.

For this guide, assume you are testing an e-commerce application. The relevant tables are:

-- Users table
users (id, name, email, created_at, is_active)

-- Orders table  
orders (id, user_id, status, total_amount, created_at)

-- Order items
order_items (id, order_id, product_id, quantity, unit_price)

-- Products table
products (id, name, category, price, stock_quantity)

The SELECT Statement — Reading Data

Every query starts with SELECT. It tells the database which columns to return.

-- Get all columns from users
SELECT * FROM users;

-- Get specific columns only
SELECT id, name, email FROM users;

-- Get all orders
SELECT id, status, total_amount FROM orders;

QA use case: After a registration test, verify the new user was saved:

SELECT id, name, email, is_active
FROM users
WHERE email = 'testuser@example.com';

If this returns a row, the registration succeeded and the data is correct. If it returns nothing, the backend has a bug.

WHERE — Filtering Results

WHERE filters rows based on conditions. This is your most-used clause.

-- Find all pending orders
SELECT id, user_id, total_amount
FROM orders
WHERE status = 'pending';

-- Find orders above a certain value
SELECT id, total_amount
FROM orders
WHERE total_amount > 1000;

-- Find users created today
SELECT id, name, email
FROM users
WHERE DATE(created_at) = CURDATE();

QA use case: After submitting an order in your test, verify its status:

SELECT id, status, total_amount
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 1;

This fetches the most recent order for user 42. If status = 'pending' and the amount matches what was submitted, the test passes.

AND, OR, NOT — Combining Conditions

-- Active users who registered this year
SELECT name, email
FROM users
WHERE is_active = 1
AND YEAR(created_at) = 2026;

-- Orders that are either cancelled or refunded
SELECT id, status
FROM orders
WHERE status = 'cancelled'
OR status = 'refunded';

-- All orders that are NOT completed
SELECT id, status
FROM orders
WHERE status != 'completed';

ORDER BY and LIMIT — Controlling Output

-- Show most recent orders first
SELECT id, status, created_at
FROM orders
ORDER BY created_at DESC;

-- Show the 10 most expensive orders
SELECT id, total_amount
FROM orders
ORDER BY total_amount DESC
LIMIT 10;

-- Show 5 orders starting from the 11th row (pagination check)
SELECT id, total_amount
FROM orders
ORDER BY id
LIMIT 5 OFFSET 10;

QA use case: When testing pagination in the API, verify the database returns the correct slice:

-- Page 3 of orders (10 per page)
SELECT id, status
FROM orders
ORDER BY id
LIMIT 10 OFFSET 20;

COUNT, SUM, AVG — Aggregate Functions

These are essential for data integrity checks.

-- How many users exist?
SELECT COUNT(*) AS total_users FROM users;

-- How many orders does user 42 have?
SELECT COUNT(*) AS order_count
FROM orders
WHERE user_id = 42;

-- Total revenue from completed orders
SELECT SUM(total_amount) AS total_revenue
FROM orders
WHERE status = 'completed';

-- Average order value
SELECT AVG(total_amount) AS avg_order_value
FROM orders;

QA use case: After a bulk import test that should have created 100 user accounts:

SELECT COUNT(*) FROM users WHERE created_at >= '2026-04-01 10:00:00';

If the count is not 100, there is a data bug.

GROUP BY — Aggregating by Category

-- Count orders by status
SELECT status, COUNT(*) AS count
FROM orders
GROUP BY status;

-- Total revenue per user
SELECT user_id, SUM(total_amount) AS total_spent
FROM orders
WHERE status = 'completed'
GROUP BY user_id
ORDER BY total_spent DESC;

QA use case: After running a promotion test, verify the discount applied correctly across order types:

SELECT status, COUNT(*) AS count, SUM(total_amount) AS total
FROM orders
WHERE created_at >= '2026-04-01'
GROUP BY status;

JOINS — Querying Across Tables

Joins are where most testers get stuck. But the concept is simple: combine rows from two tables where a column matches.

INNER JOIN

Returns only rows where there is a match in both tables.

-- Get order details with the user's name
SELECT o.id AS order_id, u.name, u.email, o.status, o.total_amount
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

QA use case: Verify that after a test purchase, the order is correctly linked to the right user:

SELECT o.id, u.name, u.email, o.status
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.email = 'testuser@example.com'
ORDER BY o.created_at DESC
LIMIT 1;

LEFT JOIN

Returns all rows from the left table, plus matching rows from the right table. Non-matching rows get NULL.

-- All users, with their order count (including users with no orders)
SELECT u.id, u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

QA use case: Verify that a newly registered user has zero orders (data integrity check):

SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.email = 'newuser@example.com'
GROUP BY u.id, u.name;

Three-Table Join

-- Full order breakdown: user, order, items, product names
SELECT
  u.name AS customer,
  o.id AS order_id,
  o.status,
  p.name AS product,
  oi.quantity,
  oi.unit_price
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.id = 1001;

QA use case: After a test order is placed, verify all line items are correctly stored with right quantities and prices.

NULL Handling

NULL is not zero and not an empty string. It means "no value." Testing NULL handling is important because it is a common source of bugs.

-- Find orders with no discount applied (NULL discount)
SELECT id, total_amount
FROM orders
WHERE discount_amount IS NULL;

-- Find users who have never provided a phone number
SELECT id, name
FROM users
WHERE phone IS NULL;

Important: WHERE phone = NULL does NOT work. Always use IS NULL or IS NOT NULL.

LIKE — Pattern Matching

-- Users whose name starts with 'A'
SELECT name, email FROM users WHERE name LIKE 'A%';

-- Users with a Gmail address
SELECT name, email FROM users WHERE email LIKE '%@gmail.com';

-- Products containing 'phone' in the name
SELECT name, price FROM products WHERE name LIKE '%phone%';

QA use case: After a search feature test, verify the database contains the expected matching records.

IN — Matching a List

-- Orders with specific statuses
SELECT id, status
FROM orders
WHERE status IN ('pending', 'processing', 'on_hold');

-- Specific users by ID
SELECT name, email
FROM users
WHERE id IN (1, 5, 10, 42);

BETWEEN — Range Checks

-- Orders placed in March 2026
SELECT id, total_amount, created_at
FROM orders
WHERE created_at BETWEEN '2026-03-01' AND '2026-03-31 23:59:59';

-- Products priced between 500 and 2000
SELECT name, price
FROM products
WHERE price BETWEEN 500 AND 2000;

Subqueries — Queries Inside Queries

A subquery is a SELECT inside another SELECT. Useful for complex validation.

-- Users who have placed at least one order
SELECT name, email
FROM users
WHERE id IN (
  SELECT DISTINCT user_id FROM orders
);

-- Products that have never been ordered
SELECT name, price
FROM products
WHERE id NOT IN (
  SELECT DISTINCT product_id FROM order_items
);

QA use case: After disabling a product, verify it no longer appears in any pending orders.

DISTINCT — Remove Duplicates

-- All unique order statuses currently in the system
SELECT DISTINCT status FROM orders;

-- All unique categories with at least one product
SELECT DISTINCT category FROM products;

UPDATE and DELETE — Use With Extreme Care

You generally should not run UPDATE or DELETE in a test environment without explicit approval. But sometimes you need to reset test data.

-- Reset a test user's order count (test data cleanup)
DELETE FROM orders WHERE user_id = 999 AND created_at > '2026-04-01';

-- Reset a test product's stock
UPDATE products SET stock_quantity = 100 WHERE id = 50;

Always use a WHERE clause. A DELETE FROM orders; with no WHERE deletes everything. Run SELECT first to verify what you are about to modify.

Real QA Test Scenarios

Scenario 1: Order Total Validation

After placing an order with items costing ₹200, ₹350, and ₹150 (total = ₹700):

SELECT 
  o.id,
  o.total_amount,
  SUM(oi.quantity * oi.unit_price) AS calculated_total
FROM orders o
INNER JOIN order_items oi ON o.id = oi.order_id
WHERE o.id = (SELECT MAX(id) FROM orders WHERE user_id = 42)
GROUP BY o.id, o.total_amount;

If total_amount matches calculated_total, the order calculation is correct.

Scenario 2: Stock Decrement After Purchase

Before purchase: SELECT stock_quantity FROM products WHERE id = 10; → returns 50.

After purchase of 3 units: run the same query → should return 47.

Scenario 3: User Deactivation

After deactivating user account 42 through the admin panel:

SELECT is_active FROM users WHERE id = 42;

Should return 0. If it returns 1, the deactivation API has a bug.

Scenario 4: Duplicate Order Prevention

Verify a user cannot place the same order twice in quick succession (idempotency check):

SELECT COUNT(*) AS duplicate_count
FROM orders
WHERE user_id = 42
AND created_at > NOW() - INTERVAL 1 MINUTE;

If count > 1, the backend is not handling duplicate submissions.

SQL Interview Questions for QA Engineers

Q1: What is the difference between WHERE and HAVING?

WHERE filters rows before aggregation. HAVING filters groups after aggregation. You cannot use aggregate functions in WHERE.

-- Wrong: WHERE with aggregate
SELECT user_id, COUNT(*) FROM orders WHERE COUNT(*) > 5 GROUP BY user_id;

-- Correct: HAVING for post-aggregation filter
SELECT user_id, COUNT(*) AS order_count 
FROM orders 
GROUP BY user_id 
HAVING order_count > 5;

Q2: What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows with matches in both tables. LEFT JOIN returns all rows from the left table, with NULLs where no match exists in the right table.

Q3: How would you find duplicate records in a table?

SELECT email, COUNT(*) AS count
FROM users
GROUP BY email
HAVING count > 1;

Q4: What does DISTINCT do?

It removes duplicate rows from the result set. SELECT DISTINCT status FROM orders returns each unique status once, even if many orders share the same status.

Q5: How do you get the second-highest salary from an employee table?

SELECT MAX(salary) 
FROM employees 
WHERE salary < (SELECT MAX(salary) FROM employees);

Summary

SQL is one of the highest-value skills a QA engineer can add. Every application stores data in a database, and every test ultimately comes down to data correctness.

Start with SELECT, WHERE, and ORDER BY. Add JOIN when you are comfortable. Practice with real applications — pick any web app you use daily and imagine what its database looks like. Write the queries that would verify the features you use.

Within a week of practice, you will be writing queries faster than most developers, because testers think in terms of "what should this data look like" — which is exactly how SQL is written.