SQL Basics for Testers

What you see on the screen is only what the application chooses to show. The real truth lives in the database. When a user registers, places an order or cancels a booking, a tester often needs to confirm that the correct rows were created or updated. That is why most manual and API testing job descriptions in India list "basic SQL" as a requirement. You do not need to be a database administrator; you need to read data confidently.

Why Testers Need SQL

• Back-end validation: confirm that UI and API actions save the right data.
• Test data preparation: find existing users, orders or products that match a test condition.
• Defect investigation: attach the exact database state to a bug report.
• Data migration and report testing: compare counts and totals between systems.

Sample Tables

We will use two tables from an e-commerce app.

users

orders

SELECT, WHERE and ORDER BY

-- All columns of all users
SELECT * FROM users;

-- Specific columns with a condition
SELECT name, email FROM users WHERE city = 'Pune';

-- Orders above Rs 1,000 that are not cancelled, highest first
SELECT order_id, amount, status
FROM orders
WHERE amount > 1000 AND status <> 'CANCELLED'
ORDER BY amount DESC;

-- Pattern matching and ranges
SELECT * FROM users WHERE email LIKE '%@example.com';
SELECT * FROM orders WHERE order_date BETWEEN '2026-02-01' AND '2026-02-28';

Aggregates: COUNT, SUM and GROUP BY

-- How many orders exist?
SELECT COUNT(*) AS total_orders FROM orders;

-- Number of orders and total value per status
SELECT status, COUNT(*) AS order_count, SUM(amount) AS total_value
FROM orders
GROUP BY status;

-- Users who placed more than one order (HAVING filters groups)
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 1;

JOINs

Data is spread across tables and connected by keys. Here orders.user_id refers to users.user_id.

-- INNER JOIN: only users who have orders
SELECT u.name, o.order_id, o.amount, o.status
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;

-- LEFT JOIN: all users, including those with no orders
SELECT u.name, COUNT(o.order_id) AS orders_placed
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.name;

In the LEFT JOIN result, Anita Nair appears with 0 orders. An INNER JOIN would leave her out entirely.

Validating Data After UI Actions

Suppose you place an order on the app as Priya for Rs 899 using coupon SAVE100. After the confirmation screen shows order ID 104, verify the back end:

SELECT o.order_id, o.amount, o.status, u.email
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE o.order_id = 104;

Check that exactly one row exists, the amount is 899 (not the pre-discount value), the status is the expected initial status, and it belongs to the correct user. Similarly, after a new registration, query users by email and confirm the city, timestamps and that the password is stored hashed, never as plain text.

Classic Interview Queries

Assume an employees table with columns emp_id, name, dept and salary.

-- Second highest salary (works in all major databases)
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Nth highest salary using DENSE_RANK (here N = 3)
SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 3;

-- Find duplicate emails in users
SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Highest salary in each department
SELECT dept, MAX(salary) AS max_salary
FROM employees
GROUP BY dept;

-- Users who have never placed an order
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.order_id IS NULL;

Quick Reference