SQL for QA Engineers
Interview Preparation Guide
Comprehensive Q&A; for QA Lead Interview
Role: QA Lead / Senior QA Engineer
Level: Intermediate to Advanced
This guide contains detailed questions and model answers to help you prepare confidently for your
interview. Each answer includes key concepts, code examples where applicable, and pro tips.
Topics Covered
1. SQL Fundamentals
2. Intermediate SQL
3. SQL for QA - Data Validation
1. SQL Fundamentals
Q: What is the difference between DDL, DML, DCL, and TCL?
DDL (Data Definition Language): Defines database structure.
CREATE, ALTER, DROP, TRUNCATE
DML (Data Manipulation Language): Manipulates data.
SELECT, INSERT, UPDATE, DELETE
DCL (Data Control Language): Controls access.
GRANT, REVOKE
TCL (Transaction Control Language): Manages transactions.
COMMIT, ROLLBACK, SAVEPOINT
-- DDL
CREATE TABLE users (id INT, name VARCHAR(100));
ALTER TABLE users ADD COLUMN email VARCHAR(200);
-- DML
INSERT INTO users VALUES (1, 'Alice', 'alice@[Link]');
UPDATE users SET name='Bob' WHERE id=1;
DELETE FROM users WHERE id=1;
-- TCL
BEGIN;
UPDATE accounts SET balance=balance-500 WHERE id=1;
UPDATE accounts SET balance=balance+500 WHERE id=2;
COMMIT;
Q: What is the difference between WHERE and HAVING?
WHERE: Filters rows before grouping. Cannot use aggregate functions.
HAVING: Filters groups after GROUP BY. Can use aggregate functions.
Rule: WHERE works on individual rows. HAVING works on grouped results.
-- WHERE: filter before group
SELECT department, COUNT(*)
FROM employees
WHERE status = 'active'
GROUP BY department;
-- HAVING: filter after group
SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
-- Combined
SELECT department, AVG(salary) AS avg_sal
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING AVG(salary) > 50000;
Pro Tip: This is one of the most common SQL interview questions. Always remember: WHERE before
GROUP BY, HAVING after.
Q: Explain the different types of JOINs in SQL.
INNER JOIN: Returns rows that have matching values in BOTH tables.
LEFT JOIN (LEFT OUTER): All rows from left table + matching from right. NULL if no match on
right.
RIGHT JOIN (RIGHT OUTER): All rows from right table + matching from left.
FULL OUTER JOIN: All rows from both tables. NULL where no match.
CROSS JOIN: Cartesian product — every row of left × every row of right.
SELF JOIN: Table joined with itself — used for hierarchical data.
-- INNER JOIN
SELECT o.order_id, [Link]
FROM orders o
INNER JOIN customers c ON o.customer_id = [Link];
-- LEFT JOIN (includes customers with no orders)
SELECT [Link], o.order_id
FROM customers c
LEFT JOIN orders o ON [Link] = o.customer_id;
-- SELF JOIN (employee-manager hierarchy)
SELECT [Link] AS Employee, [Link] AS Manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = [Link];
Q: What is the difference between TRUNCATE, DELETE, and DROP?
DELETE: Removes specific rows. Can have WHERE clause. Can be rolled back. Triggers fire.
Slow for large tables.
TRUNCATE: Removes ALL rows. No WHERE clause. Cannot be rolled back (in most DBs). Much
faster. Resets identity/sequence.
DROP: Removes the entire table including structure. Cannot be rolled back.
-- DELETE specific rows
DELETE FROM orders WHERE status = 'cancelled';
-- TRUNCATE - clear all data
TRUNCATE TABLE temp_logs;
-- DROP - remove table entirely
DROP TABLE temp_logs;
-- Check: Can DELETE be rolled back?
BEGIN;
DELETE FROM orders;
ROLLBACK; -- rows restored!
-- TRUNCATE cannot be rolled back in most DBs
Pro Tip: TRUNCATE vs DELETE is a classic interview question. Key difference: TRUNCATE resets
auto-increment, DELETE doesn't.
2. Intermediate SQL
Q: What are window functions? Explain with examples.
Window functions perform calculations across a set of table rows that are related to the current
row, without collapsing rows like GROUP BY does.
Key window functions:
- ROW_NUMBER(): Unique row number per partition
- RANK(): Rank with gaps for ties
- DENSE_RANK(): Rank without gaps
- LAG(): Value from previous row
- LEAD(): Value from next row
- SUM() OVER(): Running total
- AVG() OVER(): Moving average
-- ROW_NUMBER: rank employees by salary in each dept
SELECT name, department, salary,
ROW_NUMBER() OVER(
PARTITION BY department
ORDER BY salary DESC
) AS rank_in_dept
FROM employees;
-- Running total
SELECT order_date, amount,
SUM(amount) OVER(ORDER BY order_date) AS running_total
FROM orders;
-- LAG: compare with previous row
SELECT month, revenue,
LAG(revenue) OVER(ORDER BY month) AS prev_month,
revenue - LAG(revenue) OVER(ORDER BY month) AS change
FROM monthly_revenue;
Pro Tip: Window functions are heavily tested for senior/lead roles. Practice ROW_NUMBER, RANK, and
running totals.
Q: Write a query to find the second highest salary.
Multiple approaches exist. Each tests different SQL knowledge.
-- Method 1: Subquery (most common)
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
-- Method 2: LIMIT/OFFSET
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
-- Method 3: Dense Rank (handles ties correctly)
SELECT salary FROM (
SELECT salary,
DENSE_RANK() OVER(ORDER BY salary DESC) AS dr
FROM employees
) ranked
WHERE dr = 2;
-- Nth highest salary (parameterized with N=3)
SELECT salary FROM (
SELECT salary,
DENSE_RANK() OVER(ORDER BY salary DESC) AS dr
FROM employees
) r WHERE dr = 3;
Pro Tip: Always use DENSE_RANK for Nth highest — it handles duplicate salaries correctly.
Q: What is a CTE (Common Table Expression) and when do you use it?
A CTE is a named temporary result set defined using the WITH clause. It exists only for the
duration of the query.
Advantages over subqueries:
- More readable and maintainable
- Can be referenced multiple times in the same query
- Supports recursive queries
When to use:
- Breaking complex queries into logical steps
- Recursive hierarchies (org chart, category tree)
- Avoiding repetition of subqueries
-- Basic CTE
WITH high_earners AS (
SELECT * FROM employees
WHERE salary > 80000
)
SELECT department, COUNT(*) AS count
FROM high_earners
GROUP BY department;
-- Multiple CTEs
WITH
active_users AS (
SELECT * FROM users WHERE status = 'active'
),
user_orders AS (
SELECT user_id, COUNT(*) AS order_count
FROM orders GROUP BY user_id
)
SELECT [Link], o.order_count
FROM active_users u
JOIN user_orders o ON [Link] = o.user_id;
-- Recursive CTE (org hierarchy)
WITH RECURSIVE org AS (
SELECT id, name, manager_id, 1 AS level
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT [Link], [Link], e.manager_id, [Link]+1
FROM employees e JOIN org o ON e.manager_id = [Link]
)
SELECT * FROM org ORDER BY level;
Q: What is the difference between UNION and UNION ALL?
UNION: Combines results of two queries and removes duplicate rows. Slower due to
deduplication.
UNION ALL: Combines results including duplicates. Faster.
Requirements: Both queries must have the same number of columns with compatible data types.
When to use UNION ALL: When duplicates are acceptable or impossible — always prefer it for
performance.
-- UNION (removes duplicates)
SELECT email FROM customers
UNION
SELECT email FROM suppliers;
-- UNION ALL (keeps duplicates, faster)
SELECT 'Customer' AS type, name FROM customers
UNION ALL
SELECT 'Supplier', name FROM suppliers;
-- Practical: combine logs from multiple months
SELECT * FROM logs_jan
UNION ALL
SELECT * FROM logs_feb
UNION ALL
SELECT * FROM logs_mar;
3. SQL for QA - Data Validation
Q: What SQL queries do you use most in QA for data validation?
As a QA, SQL is used to verify test data, confirm database state after operations, and debug
failures.
Common validation queries:
1. Count verification — records created/updated/deleted as expected
2. Null checks — required fields not null
3. Duplicate detection — no duplicates in unique columns
4. Referential integrity — foreign keys are valid
5. Data range validation — values within expected range
6. Before/after comparison — data changed correctly
-- 1. Count check
SELECT COUNT(*) FROM orders WHERE status='completed';
-- 2. Null check
SELECT * FROM users WHERE email IS NULL OR name IS NULL;
-- 3. Duplicate check
SELECT email, COUNT(*) FROM users
GROUP BY email HAVING COUNT(*) > 1;
-- 4. Orphan records (broken FK)
SELECT o.* FROM orders o
LEFT JOIN customers c ON o.customer_id = [Link]
WHERE [Link] IS NULL;
-- 5. Range validation
SELECT * FROM products WHERE price < 0 OR price > 100000;
-- 6. Before-after (use in @Before/@After)
SELECT COUNT(*) FROM orders; -- store before
-- run POST /api/orders
SELECT COUNT(*) FROM orders; -- verify +1
Pro Tip: Show examples of using SQL in your automation framework to validate backend state after API calls.
Q: Write a query to find duplicate records and delete them keeping one.
A classic QA data cleanup scenario. Use ROW_NUMBER to identify duplicates, then delete all but
the first occurrence.
-- Find duplicates
SELECT email, COUNT(*) as cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- View all duplicate rows
SELECT * FROM users
WHERE email IN (
SELECT email FROM users
GROUP BY email HAVING COUNT(*) > 1
);
-- Delete duplicates keeping lowest id
DELETE FROM users
WHERE id NOT IN (
SELECT MIN(id)
FROM users
GROUP BY email
);
-- Alternative using ROW_NUMBER (for SQL Server/PostgreSQL)
WITH cte AS (
SELECT id,
ROW_NUMBER() OVER(PARTITION BY email ORDER BY id) AS rn
FROM users
)
DELETE FROM cte WHERE rn > 1;
Q: Explain indexes in SQL. How do they affect QA testing?
An index is a data structure that speeds up SELECT queries on a column by creating a sorted
lookup structure. Similar to an index in a book.
Types:
- Primary Index: Auto-created on Primary Key
- Unique Index: Enforces uniqueness
- Composite Index: Multiple columns
- Full-text Index: For text search
QA Impact:
- Missing index: Slow test data queries on large datasets
- Unique index violation: Test data must have unique values for indexed columns
- Index can be used to verify data integrity constraints during testing
-- Create index
CREATE INDEX idx_users_email ON users(email);
-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
-- Composite index
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);
-- Check if index is used (EXPLAIN)
EXPLAIN SELECT * FROM users WHERE email = 'test@[Link]';
-- Drop index
DROP INDEX idx_users_email;
Pro Tip: For QA Lead, understand EXPLAIN/EXPLAIN ANALYZE to check query performance — important
for performance test validation.
Q: What is a stored procedure and how is it relevant to QA?
A stored procedure is a precompiled set of SQL statements stored in the database. It can accept
parameters, execute complex logic, and return results.
QA Relevance:
- Can be called to set up test data efficiently
- Used to verify complex business logic at DB level
- Stored procedures themselves should be tested
- QA may need to call stored procedures via JDBC/API
Advantages: Reusable, faster execution, reduced network traffic.
-- Create stored procedure
CREATE PROCEDURE GetUserOrders(
IN user_id INT,
IN status_filter VARCHAR(50)
)
BEGIN
SELECT [Link], [Link], [Link], o.created_at
FROM orders o
WHERE o.user_id = user_id
AND (status_filter IS NULL
OR [Link] = status_filter)
ORDER BY o.created_at DESC;
END;
-- Call it
CALL GetUserOrders(42, 'completed');
-- In Java (JDBC)
CallableStatement cs = conn
.prepareCall("{call GetUserOrders(?,?)}");
[Link](1, 42);
[Link](2, "completed");
ResultSet rs = [Link]();