Supporting track

SQL for Testers

Backend validation, data setup and the interview classics.

What this track covers

SELECT & filteringJoinsGroup by & havingSubqueriesWindow functionsIndexes basicsData validation queriesTransactions

Validating UI against the database

Reconcile what the UI shows with what the database stores to catch mapping bugs.

Why it is required: End-to-end confidence needs backend verification, not just screen assertions.

Example: Compare the order total shown in the UI with the sum of line items in the DB.

sql

1SELECT o.id,
2       o.total_amount,
3       SUM(i.qty * i.unit_price) AS computed_total
4FROM orders o
5JOIN order_items i ON i.order_id = o.id
6WHERE o.id = 91
7GROUP BY o.id, o.total_amount
8HAVING o.total_amount <> SUM(i.qty * i.unit_price);

Expected result

Zero rows means the UI total and stored total agree.

Common mistakes

Running validation queries against production
Ignoring rounding and currency precision
No cleanup of data inserted by tests

How do you verify that a UI action actually persisted correctly?

Related interview questions

Open bank

Write a query to find the second highest salary.
Use DENSE_RANK() in a subquery, or the classic MAX with a nested exclusion.
Explain the difference between INNER JOIN, LEFT JOIN, and how you'd use them to validate test data relationships.
INNER JOIN returns only rows with matches in both tables, while LEFT JOIN returns all rows from the left table plus matched data from the right, with NULLs where no match exists — the latter is essential for finding orphaned records.
How do GROUP BY and HAVING differ, and when do you need HAVING instead of WHERE?
WHERE filters individual rows before grouping happens, while HAVING filters aggregated groups after GROUP BY has computed them, so you use HAVING when your condition depends on an aggregate like COUNT or SUM.
How would you use a window function to validate ordering or detect gaps in sequential test data?
Window functions like ROW_NUMBER(), RANK(), and LAG()/LEAD() let you compute values across a set of rows related to the current row without collapsing them into groups, which is ideal for detecting sequence gaps or comparing a row to the previous one.
How do indexes affect query performance, and what's a scenario where a missing index caused a test to time out?
An index creates a sorted lookup structure (typically a B-tree) so the database can find matching rows without scanning the entire table, turning an O(n) full table scan into a much faster O(log n) lookup.
When would you use a CTE instead of a subquery, and are there performance differences?
A CTE (WITH clause) improves readability by naming an intermediate result set you can reference multiple times, and recursive CTEs handle hierarchical data that a plain subquery cannot express at all, though most databases optimize non-recursive CTEs similarly to subqueries.

Quick reference available

Open the matching cheat sheet for last-minute revision.