SQL for QA Engineers | Rishabh Ranjan

Introduction

As a QA Engineer, understanding SQL is crucial for backend testing, data validation, and ensuring data integrity. This tutorial will teach you the SQL skills you need to excel in QA roles.

💡 What You'll Learn

Write SELECT queries to retrieve data
Filter and sort data effectively
Use JOIN operations to combine tables
Apply aggregation functions for data analysis
Validate test data in databases

🎮 Interactive SQL Playground

Practice SQL queries right here! We've created a sample database with users, orders, and products tables. Try running queries below:

📊 Database Schema

👥 users

🔑 user_id

first_name

last_name

email

country

status

created_at

📦 orders

🔑 order_id

🔗 user_id

total

created_at

🛍️ products

🔑 product_id

product_name

price

SQL Query Editor

What is SQL?

SQL (Structured Query Language) is a standard language for managing and manipulating relational databases. It allows you to:

Query data from databases
Insert, update, and delete records
Create and modify database structures
Control access to data

Why SQL for QA?

QA engineers use SQL to:

Verify data integrity after CRUD operations
Set up test data for automation
Validate backend calculations
Debug issues by querying logs and databases

Basic Queries

SELECT Statement

The SELECT statement is used to retrieve data from a database.

SQL

-- Select all columns from users table
SELECT * FROM users;

-- Select specific columns
SELECT first_name, last_name, email FROM users;

-- Select with alias
SELECT first_name AS "First Name", email AS "Email Address" FROM users;

WHERE Clause

Filter records based on conditions.

SQL

-- Filter by single condition
SELECT * FROM users WHERE status = 'active';

-- Multiple conditions with AND
SELECT * FROM users WHERE status = 'active' AND country = 'India';

-- Multiple conditions with OR
SELECT * FROM users WHERE country = 'India' OR country = 'USA';

-- Using IN operator
SELECT * FROM users WHERE country IN ('India', 'USA', 'UK');

ORDER BY

Sort results in ascending or descending order.

SQL

-- Sort by name (ascending by default)
SELECT * FROM users ORDER BY first_name;

-- Sort descending
SELECT * FROM users ORDER BY created_at DESC;

-- Sort by multiple columns
SELECT * FROM users ORDER BY country ASC, created_at DESC;

Filtering Data

LIKE Operator

Search for patterns in text data.

SQL

-- Names starting with 'A'
SELECT * FROM users WHERE first_name LIKE 'A%';

-- Names ending with 'son'
SELECT * FROM users WHERE last_name LIKE '%son';

-- Names containing 'john'
SELECT * FROM users WHERE first_name LIKE '%john%';

-- Email from gmail
SELECT * FROM users WHERE email LIKE '%@gmail.com';

BETWEEN Operator

SQL

-- Users created in a date range
SELECT * FROM users 
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- Age range
SELECT * FROM users WHERE age BETWEEN 25 AND 35;

NULL Values

SQL

-- Find records with NULL email
SELECT * FROM users WHERE email IS NULL;

-- Find records with non-NULL email
SELECT * FROM users WHERE email IS NOT NULL;

SQL Joins

Joins combine rows from two or more tables based on related columns.

INNER JOIN

Returns records that have matching values in both tables.

SQL

-- Get users with their orders
SELECT users.first_name, users.email, orders.order_id, orders.total
FROM users
INNER JOIN orders ON users.user_id = orders.user_id;

LEFT JOIN

Returns all records from the left table, and matched records from the right table.

SQL

-- Get all users, including those without orders
SELECT users.first_name, users.email, orders.order_id
FROM users
LEFT JOIN orders ON users.user_id = orders.user_id;

⚠️ Common QA Scenario

Use LEFT JOIN to find users who haven't placed any orders (NULL order_id):

SELECT users.first_name, users.email
FROM users
LEFT JOIN orders ON users.user_id = orders.user_id
WHERE orders.order_id IS NULL;

Aggregation Functions

Perform calculations on multiple rows and return a single value.

SQL

-- COUNT: Number of users
SELECT COUNT(*) AS total_users FROM users;

-- SUM: Total order amount
SELECT SUM(total) AS total_revenue FROM orders;

-- AVG: Average order value
SELECT AVG(total) AS average_order_value FROM orders;

-- MAX and MIN
SELECT MAX(total) AS highest_order, MIN(total) AS lowest_order FROM orders;

-- GROUP BY: Count users by country
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country
ORDER BY user_count DESC;

-- HAVING: Filter grouped results
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country
HAVING COUNT(*) > 100;

Subqueries

A query nested inside another query.

SQL

-- Find users who placed orders above average
SELECT first_name, email
FROM users
WHERE user_id IN (
    SELECT user_id 
    FROM orders 
    WHERE total > (SELECT AVG(total) FROM orders)
);

-- Find products never ordered
SELECT product_name
FROM products
WHERE product_id NOT IN (SELECT DISTINCT product_id FROM order_items);

QA Testing Scenarios

Scenario 1: Verify User Registration

SQL

-- Check if user was created with correct data
SELECT * FROM users 
WHERE email = 'test@example.com' 
AND status = 'active' 
AND created_at >= NOW() - INTERVAL 1 MINUTE;

Scenario 2: Validate Order Total

SQL

-- Verify order total matches sum of items
SELECT 
    o.order_id,
    o.total AS order_total,
    SUM(oi.quantity * oi.price) AS calculated_total
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_id = 12345
GROUP BY o.order_id, o.total;

Scenario 3: Find Duplicate Records

SQL

-- Find duplicate email addresses
SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Practice Exercises

Exercise 1: Basic SELECT

Write a query to select all active users from India, ordered by registration date (newest first).

Show Solution

SELECT * FROM users 
WHERE status = 'active' AND country = 'India'
ORDER BY created_at DESC;

Exercise 2: Aggregation

Find the total number of orders and total revenue for each user.

Show Solution

SELECT 
    u.first_name,
    u.email,
    COUNT(o.order_id) AS total_orders,
    SUM(o.total) AS total_revenue
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.first_name, u.email
ORDER BY total_revenue DESC;

Exercise 3: Complex Query

Find users who have placed more than 5 orders in the last 30 days.

Show Solution

SELECT 
    u.user_id,
    u.first_name,
    u.email,
    COUNT(o.order_id) AS order_count
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
WHERE o.created_at >= NOW() - INTERVAL 30 DAY
GROUP BY u.user_id, u.first_name, u.email
HAVING COUNT(o.order_id) > 5
ORDER BY order_count DESC;

Summary

Congratulations! You've learned the essential SQL skills for QA testing:

✅ Key Takeaways

SELECT statements for data retrieval
WHERE, LIKE, BETWEEN for filtering
JOIN operations to combine tables
Aggregation functions (COUNT, SUM, AVG)
Subqueries for complex queries
Practical QA testing scenarios

Next Steps

Practice writing queries on sample databases
Apply SQL in your test automation frameworks
Learn about database transactions and ACID properties
Explore advanced topics like window functions and CTEs