Skip to content
SDETRole
SDETRole

Practice / SQL

SQL Practice Lab for Testers

Testers use SQL to verify what the UI cannot show: duplicate records, orphan rows, totals that do not reconcile, flaky tests hiding in run history. Every query here runs on a real SQLite database in your browser and is compared with the correct answer, on the sample data and on altered data.

Challenges
13
Beginner
4
Intermediate
6
Advanced
3
Solved
0

Challenges

  1. 01 Customers with no city on file Find active customers whose city is missing. The classic first trap: `= NULL` never matches anything.
  2. 02 Count failures per test Rank test cases by how often they failed across all CI builds. Filter, join, group, then sort with a tie-breaker.
  3. 03 Second-highest price The interview favourite, with a twist: two products share the top price, so LIMIT 1 OFFSET 1 gives the wrong answer.
  4. 04 Duplicate customer emails Find email addresses registered more than once, including duplicates that differ only by letter case.
  5. 05 Customers who never ordered NOT IN looks right and returns nothing. One guest order with a NULL customer_id is enough to break it.
  6. 06 Find orphan order items Order lines point at products that no longer exist. Find them with an anti-join, the standard data-integrity check.
  7. 07 Pass rate per suite on the latest main build Compute a percentage per suite for the newest main build. Watch for integer division, skipped tests and a hard-coded build id.
  8. 08 Detect flaky tests from rerun history A test that both failed and passed on the same build is flaky. A test that failed twice is just broken. Tell them apart.
  9. 09 Average time to close defects Average days from report to fix, per severity. Subtracting date strings gives 0 in SQLite, so you need julianday().
  10. 10 Clean up feature-branch test data Delete feature-branch builds and their test runs without leaving orphans. The order of the two DELETEs matters.
  11. 11 Reconcile orders with payments Find orders whose item total does not match what was paid. Joining items and payments directly double-counts both.
  12. 12 Top customer in each city The highest-spending customer per city, ties included. A classic use of RANK() with PARTITION BY.
  13. 13 Spot test duration regressions Compare each test's duration on a main build with its previous passing run on main, using LAG(), and flag big slowdowns.

Why testers need SQL

The UI shows what the application chooses to show. The database shows what actually happened. Testers use SQL to confirm that an order was written with the right total, to find customers created twice by a double-click, to spot order items whose order was deleted, and to mine test-run history for flaky tests. Most SDET interview loops include at least one query, and the questions are usually these practical checks rather than textbook exercises.

The lab uses two databases: a small shop (customers, products, orders, order items, payments) and a test-results database (builds, test runs, defects). Every challenge is a task you might do in a real sprint.

A suggested order

How queries are checked

Your SQL runs on SQLite compiled to WebAssembly, in a Web Worker in your browser, on a fresh in-memory database for every check. The result set is compared with the reference solution: same columns in the same order and the same rows, in order only when the task asks for ORDER BY. Extra checks then alter the data, for example by adding a second duplicate or a NULL, and run your query again. A query that hard-codes the expected ids passes the first check and fails the rest, which is the point: a data check that only works on today's data is not a check.

SQLite is close to standard SQL for everything here (joins, CTEs, window functions), but date functions differ between databases. The challenges note this where it matters, so you can translate to PostgreSQL or MySQL in an interview.

Common mistakes

  • Comparing with = NULL or <> NULL. Any comparison with NULL is unknown, so the row is filtered out.
  • Joining a one-to-many table and then summing, which counts the parent value once per child row. Aggregate the child table first, then join.
  • Using COUNT(column) when you meant COUNT(*): the first skips NULLs.
  • Writing a DELETE without first running the same WHERE clause as a SELECT.

Frequently asked questions

Which SQL dialect does the lab use?

SQLite, running in your browser through WebAssembly. Joins, aggregates, CTEs and window functions behave as in PostgreSQL and MySQL; date functions are the main difference.

Why did my query pass the sample data but fail another check?

Later checks change the data, for example by adding a NULL or another duplicate. Your query probably relies on something true only for the sample rows, such as a specific id or a single match.

What SQL do QA engineers need for interviews?

SELECT with WHERE, NULL handling, joins (especially LEFT JOIN for finding missing rows), GROUP BY with HAVING, subqueries or CTEs, and for senior roles window functions such as ROW_NUMBER.

Is any data sent to a server?

No. The database and your queries stay in your browser.

Type to search across SDETRole.

Full-text search by Pagefind · runs in your browser Open search page →