SQL for QA Engineers and Testers

Difference between WHERE and HAVING?

WHERE filters rows before aggregation; HAVING filters aggregated groups after GROUP BY.

Inner join vs left join?

Inner returns matching rows in both tables; left returns all rows from the left plus matches (or nulls) from the right.

How would you find the second highest salary?

SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp); or use DENSE_RANK() in a window.

What is a window function?

A function that computes a value over a partition of rows without collapsing them — useful for rank, running totals, and per-group calculations.

Explain a CTE.

A WITH-clause query that names a temporary result set, improving readability and enabling recursion.

What does EXPLAIN tell you?

The query plan: which indexes are used, join order, estimated rows, and cost — used to spot full scans and missing indexes.

When would you NOT add an index?

On low-cardinality columns, on small tables, or when write-heavy workloads make index maintenance dominate gains.

How do you validate a paginated API response with SQL?

Query the underlying table with the same filters, sort, limit, and offset; assert the response matches the rows returned.

ACID — what does it mean?

Atomicity, Consistency, Isolation, Durability — properties guaranteeing reliable transactions.

How do you clean up test data safely?

Use seeded, namespaced data; clean by namespace inside transactions or per-suite hooks; never DELETE without explicit WHERE.