SQL for QA Engineers: T-SQL in Everyday Testing
A working course in T-SQL for QA engineers in professional services firms who already know the basics — SELECT, WHERE, a simple JOIN — and now need SQL as a daily testing instrument rather than as a topic. Everything is set in ENGAGE, the fictional engagement and billing platform of Aldervane Advisory LLP: clients, engagements, staff, timesheets, approvals, invoices, an audit log and a migration staging table. One schema, carried through every unit, so the examples are the ones you actually meet — a report total that disagrees with the raw rows, a record the UI claims it saved, a migration that quietly dropped a hundred time entries, a duplicate that only appears under a business key. Each construct is introduced the same way: the problem that makes you reach for it, the construct itself, and the price it charges. LEFT JOIN buys you the missing rows and charges you row multiplication. NOLOCK buys you speed on a shared environment and charges you dirty reads. A green test that never asked the right question costs more than a red one. Nineteen core units cover reading an unfamiliar schema, staying safe on a shared test environment, NULL and three-valued logic, data types and collation, dates and time zones, joins and orphan records, set operations as reconciliation, aggregation, subqueries, CTEs, window functions, test data setup and idempotent teardown, verifying what the application wrote, reading someone else's views and triggers, and turning a SQL check into a deterministic assertion. Five optional units add a local sandbox script, execution plans, client data and AI assistants, an end-to-end migration reconciliation checklist, and normalisation as an explanation of why the schema looks the way it does. Units are open in any order: come for the whole course, or come for the one thing that is biting you today. Scope note: the course teaches you to write queries and to read them. Practice covers both; the certification exam checks reading, recognition and knowledge of constructs, since a written query cannot be machine-verified here.
Path through the course · 24 units
any order · units can be taken in any order you likeView: map · steps
Stage 1
~0.7 h
1 · can start · Orientation
Where SQL enters the testing cycle
~0.7 h
Stage 2
~2.3 h
can start
2 · Orientation
Reading a schema you did not design
~0.8 h
3 · Orientation
Staying safe on a shared test environment
~0.8 h
22 · Optional
Optional: client data, PII and AI assistants
~0.7 h
Stage 3
~0.7 h
4 · can start · Precise retrieval
A SELECT that answers exactly one question
~0.7 h
Stage 4
~1.7 h
can start · Precise retrieval
Stage 5
~1.7 h
can start
7 · Precise retrieval
Dates, times and the last day of the month
~0.9 h
8 · Across tables
INNER and LEFT: which rows the join throws away
~0.8 h
Stage 6
~3.3 h
can start
9 · Across tables
Orphans and links that point at nothing
~0.8 h
10 · Across tables
When a join multiplies your rows
~0.9 h
11 · Across tables
Set operations as reconciliation
~0.8 h
21 · Optional
Optional: slow queries and blocked environments
~0.8 h
Stage 7
~1.7 h
can start
12 · Aggregation
GROUP BY and recomputing the report
~0.8 h
16 · Data before and after the test
Test data: setup and idempotent teardown
~0.9 h
Stage 8
~2.3 h
can start
13 · Aggregation
~0.8 h
14 · Aggregation
~0.8 h
24 · Optional
Optional: why the schema looks like that
~0.7 h
Stage 9
~1.9 h
can start
15 · Aggregation
Window functions for the tester
~0.9 h
23 · Optional
Optional: end-to-end migration reconciliation
~1 h
Stage 10
~0.9 h
17 · can start · Data before and after the test
Verifying what the application actually wrote
~0.9 h
Stage 11
~1.7 h
can start · Data before and after the test
Stage 12
~1.5 h
20 · can start · Optional
Optional: build ENGAGE locally and run the checks
~1.5 h
Exam
closed
final
Exam
The path in words
- Stage 1 · Where SQL enters the testing cycle — can start; ~0.7 h
- Stage 2 · Reading a schema you did not design — can start; ~0.8 h
- Stage 2 · Staying safe on a shared test environment — can start; ~0.8 h
- Stage 2 · Optional: client data, PII and AI assistants — can start; optional; ~0.7 h
- Stage 3 · A SELECT that answers exactly one question — can start; ~0.7 h
- Stage 4 · NULL and the logic that has three answers — can start; ~0.9 h
- Stage 4 · Types, conversions and collation — can start; ~0.8 h
- Stage 5 · Dates, times and the last day of the month — can start; ~0.9 h
- Stage 5 · INNER and LEFT: which rows the join throws away — can start; ~0.8 h
- Stage 6 · Orphans and links that point at nothing — can start; ~0.8 h
- Stage 6 · When a join multiplies your rows — can start; ~0.9 h
- Stage 6 · Set operations as reconciliation — can start; ~0.8 h
- Stage 6 · Optional: slow queries and blocked environments — can start; optional; ~0.8 h
- Stage 7 · GROUP BY and recomputing the report — can start; ~0.8 h
- Stage 7 · Test data: setup and idempotent teardown — can start; ~0.9 h
- Stage 8 · HAVING and finding duplicates — can start; ~0.8 h
- Stage 8 · Subqueries and CTEs — can start; ~0.8 h
- Stage 8 · Optional: why the schema looks like that — can start; optional; ~0.7 h
- Stage 9 · Window functions for the tester — can start; ~0.9 h
- Stage 9 · Optional: end-to-end migration reconciliation — can start; optional; ~1 h
- Stage 10 · Verifying what the application actually wrote — can start; ~0.9 h
- Stage 11 · Reading views, procedures and triggers — can start; ~0.8 h
- Stage 11 · Turning a check into a deterministic assertion — can start; ~0.9 h
- Stage 12 · Optional: build ENGAGE locally and run the checks — can start; optional; ~1.5 h
Programme · 24 units
| 1 | Where SQL enters the testing cycle Name the four moments in a test cycle where a database query is the only available evidence | ~0.7 h | can start |
| 2 | Reading a schema you did not design Locate an unfamiliar table or column by querying INFORMATION_SCHEMA rather than browsing the object explorer | ~0.8 h | can start |
| 3 | Staying safe on a shared test environment Convert every destructive statement from a SELECT with the identical FROM and WHERE, so the statement never exists without its filter | ~0.8 h | can start |
| 4 | A SELECT that answers exactly one question Convert a verification question into a query whose result can only occur if the system behaved correctly | ~0.7 h | can start |
| 5 | NULL and the logic that has three answers Explain why WHERE keeps only rows evaluating to true, and what that does to comparisons against NULL | ~0.9 h | can start |
| 6 | Types, conversions and collation Read precision and scale from a column definition and assert against the value the column can hold rather than the value entered | ~0.8 h | can start |
| 7 | Dates, times and the last day of the month Write date range filters as half-open intervals with >= and <, and explain why every inclusive upper bound has an edge case | ~0.9 h | can start |
| 8 | INNER and LEFT: which rows the join throws away Choose between INNER and LEFT from the verification question, using the test that a plausible answer of 'none' requires a left join | ~0.8 h | can start |
| 9 | Orphans and links that point at nothing Write orphan checks with NOT EXISTS and with the LEFT JOIN / IS NULL anti-join, testing the joined table's non-nullable key | ~0.8 h | can start |
| 10 | When a join multiplies your rows Explain why a join to a non-unique key multiplies rows and why an aggregate conceals the multiplication | ~0.9 h | can start |
| 11 | Set operations as reconciliation Reconcile two populations with EXCEPT in both directions, and explain why one direction alone can report success on a broken migration | ~0.8 h | can start |
| 12 | GROUP BY and recomputing the report Group by the column that makes a row unique rather than the column the report displays, and explain what merging does to the result | ~0.8 h | can start |
| 13 | HAVING and finding duplicates Find duplicates by grouping on the business key and filtering with HAVING COUNT(*) > 1 | ~0.8 h | can start |
| 14 | Subqueries and CTEs Distinguish scalar, derived-table and correlated subqueries, and identify where each fails | ~0.8 h | can start |
| 15 | Window functions for the tester Explain how a window function differs from GROUP BY, and choose between them from the question | ~0.9 h | can start |
| 16 | Test data: setup and idempotent teardown Write setup scripts that are idempotent, isolated and self-identifying, with teardown at the start rather than the end | ~0.9 h | can start |
| 17 | Verifying what the application actually wrote Enumerate the blast radius of a write — every table an operation should touch — before writing any verification query | ~0.9 h | can start |
| 18 | Reading views, procedures and triggers Retrieve the definition of a view, procedure, function or trigger, and search all module bodies for references to a table or column | ~0.8 h | can start |
| 19 | Turning a check into a deterministic assertion Choose between boolean, scalar and set assertion shapes, defaulting to the violation query | ~0.9 h | can start |
| 20 | Optional: build ENGAGE locally and run the checks Stand up a local SQL Server instance and create the ENGAGE database from the supplied script | ~1.5 h | can start |
| 21 | Optional: slow queries and blocked environments Read the four common plan operators and interpret the gap between estimated and actual row counts | ~0.8 h | can start |
| 22 | Optional: client data, PII and AI assistants Identify what counts as client data on a professional services engagement, beyond personal data | ~0.7 h | can start |
| 23 | Optional: end-to-end migration reconciliation Run a layered reconciliation — population, totals, set difference, duplicates, references, distribution — and state what each layer cannot detect | ~1 h | can start |
| 24 | Optional: why the schema looks like that State the single rule behind normalisation and translate it into the tester's version: duplicated facts imply a code path that is a test case | ~0.7 h | can start |