1 · Orientation
Where SQL enters the testing cycle
~0.8 h
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 unitsthe order is free · the steps below are the order the author suggests, not a conditionView: map · steps
2 · Orientation
Reading a schema you did not design
~1 h
by the author’s design, after “Where SQL enters the testing cycle”
3 · Orientation
Staying safe on a shared test environment
~1 h
by the author’s design, after “Where SQL enters the testing cycle”
4 · Precise retrieval
A SELECT that answers exactly one question
~0.9 h
by the author’s design, after “Reading a schema you did not design”
5 · Precise retrieval
NULL and the logic that has three answers
~1.1 h
by the author’s design, after “A SELECT that answers exactly one question”
6 · Precise retrieval
Types, conversions and collation
~1 h
by the author’s design, after any one of these: “Reading a schema you did not design”, “A SELECT that answers exactly one question”
7 · Precise retrieval
Dates, times and the last day of the month
~1.1 h
by the author’s design, after any one of these: “A SELECT that answers exactly one question”, “Types, conversions and collation”
8 · Across tables
INNER and LEFT: which rows the join throws away
~1 h
by the author’s design, after any one of these: “A SELECT that answers exactly one question”, “NULL and the logic that has three answers”
9 · Across tables
Orphans and links that point at nothing
~1 h
by the author’s design, after “INNER and LEFT: which rows the join throws away”
10 · Across tables
When a join multiplies your rows
~1.1 h
by the author’s design, after “INNER and LEFT: which rows the join throws away”
11 · Across tables
Set operations as reconciliation
~1 h
by the author’s design, after any one of these: “NULL and the logic that has three answers”, “INNER and LEFT: which rows the join throws away”
12 · Aggregation
GROUP BY and recomputing the report
~1 h
by the author’s design, after any one of these: “A SELECT that answers exactly one question”, “When a join multiplies your rows”
13 · Aggregation
~1 h
by the author’s design, after “GROUP BY and recomputing the report”
14 · Aggregation
~1 h
by the author’s design, after any one of these: “When a join multiplies your rows”, “GROUP BY and recomputing the report”
15 · Aggregation
Window functions for the tester
~1.1 h
by the author’s design, after any one of these: “HAVING and finding duplicates”, “Subqueries and CTEs”
16 · Data before and after the test
Test data: setup and idempotent teardown
~1.1 h
by the author’s design, after any one of these: “Staying safe on a shared test environment”, “Set operations as reconciliation”
17 · Data before and after the test
Verifying what the application actually wrote
~1.1 h
by the author’s design, after any one of these: “Window functions for the tester”, “Test data: setup and idempotent teardown”
18 · Data before and after the test
Reading views, procedures and triggers
~1 h
by the author’s design, after any one of these: “Test data: setup and idempotent teardown”, “Verifying what the application actually wrote”
19 · Data before and after the test
Turning a check into a deterministic assertion
~1.1 h
by the author’s design, after any one of these: “Test data: setup and idempotent teardown”, “Verifying what the application actually wrote”
20 · Optional
Optional: build ENGAGE locally and run the checks
~1.5 h
by the author’s design, after any one of these: “Reading a schema you did not design”, “Turning a check into a deterministic assertion”
21 · Optional
Optional: slow queries and blocked environments
~0.8 h
by the author’s design, after any one of these: “Staying safe on a shared test environment”, “Dates, times and the last day of the month”
22 · Optional
Optional: client data, PII and AI assistants
~0.7 h
by the author’s design, after “Where SQL enters the testing cycle”
23 · Optional
Optional: end-to-end migration reconciliation
~1 h
by the author’s design, after 2 of these: “Orphans and links that point at nothing”, “Set operations as reconciliation”, “HAVING and finding duplicates”
24 · Optional
Optional: why the schema looks like that
~0.7 h
by the author’s design, after any one of these: “Reading a schema you did not design”, “GROUP BY and recomputing the report”
final · in the browser, without the assistant
Exam
closed · enrol on the course
The path in words
- Stage 1 · Where SQL enters the testing cycle — ~0.8 h
- Stage 2 · Reading a schema you did not design — by the author’s design, after “Where SQL enters the testing cycle”; ~1 h
- Stage 2 · Staying safe on a shared test environment — by the author’s design, after “Where SQL enters the testing cycle”; ~1 h
- Stage 2 · Optional: client data, PII and AI assistants — by the author’s design, after “Where SQL enters the testing cycle”; optional; ~0.7 h
- Stage 3 · A SELECT that answers exactly one question — by the author’s design, after “Reading a schema you did not design”; ~0.9 h
- Stage 4 · NULL and the logic that has three answers — by the author’s design, after “A SELECT that answers exactly one question”; ~1.1 h
- Stage 4 · Types, conversions and collation — by the author’s design, after any one of these: “Reading a schema you did not design”, “A SELECT that answers exactly one question”; ~1 h
- Stage 5 · Dates, times and the last day of the month — by the author’s design, after any one of these: “A SELECT that answers exactly one question”, “Types, conversions and collation”; ~1.1 h
- Stage 5 · INNER and LEFT: which rows the join throws away — by the author’s design, after any one of these: “A SELECT that answers exactly one question”, “NULL and the logic that has three answers”; ~1 h
- Stage 6 · Orphans and links that point at nothing — by the author’s design, after “INNER and LEFT: which rows the join throws away”; ~1 h
- Stage 6 · When a join multiplies your rows — by the author’s design, after “INNER and LEFT: which rows the join throws away”; ~1.1 h
- Stage 6 · Set operations as reconciliation — by the author’s design, after any one of these: “NULL and the logic that has three answers”, “INNER and LEFT: which rows the join throws away”; ~1 h
- Stage 6 · Optional: slow queries and blocked environments — by the author’s design, after any one of these: “Staying safe on a shared test environment”, “Dates, times and the last day of the month”; optional; ~0.8 h
- Stage 7 · GROUP BY and recomputing the report — by the author’s design, after any one of these: “A SELECT that answers exactly one question”, “When a join multiplies your rows”; ~1 h
- Stage 7 · Test data: setup and idempotent teardown — by the author’s design, after any one of these: “Staying safe on a shared test environment”, “Set operations as reconciliation”; ~1.1 h
- Stage 8 · HAVING and finding duplicates — by the author’s design, after “GROUP BY and recomputing the report”; ~1 h
- Stage 8 · Subqueries and CTEs — by the author’s design, after any one of these: “When a join multiplies your rows”, “GROUP BY and recomputing the report”; ~1 h
- Stage 8 · Optional: why the schema looks like that — by the author’s design, after any one of these: “Reading a schema you did not design”, “GROUP BY and recomputing the report”; optional; ~0.7 h
- Stage 9 · Window functions for the tester — by the author’s design, after any one of these: “HAVING and finding duplicates”, “Subqueries and CTEs”; ~1.1 h
- Stage 9 · Optional: end-to-end migration reconciliation — by the author’s design, after 2 of these: “Orphans and links that point at nothing”, “Set operations as reconciliation”, “HAVING and finding duplicates”; optional; ~1 h
- Stage 10 · Verifying what the application actually wrote — by the author’s design, after any one of these: “Window functions for the tester”, “Test data: setup and idempotent teardown”; ~1.1 h
- Stage 11 · Reading views, procedures and triggers — by the author’s design, after any one of these: “Test data: setup and idempotent teardown”, “Verifying what the application actually wrote”; ~1 h
- Stage 11 · Turning a check into a deterministic assertion — by the author’s design, after any one of these: “Test data: setup and idempotent teardown”, “Verifying what the application actually wrote”; ~1.1 h
- Stage 12 · Optional: build ENGAGE locally and run the checks — by the author’s design, after any one of these: “Reading a schema you did not design”, “Turning a check into a deterministic assertion”; 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.8 h |
| 2 | Reading a schema you did not design Locate an unfamiliar table or column by querying INFORMATION_SCHEMA rather than browsing the object explorer | ~1 h |
| 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 | ~1 h |
| 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.9 h |
| 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 | ~1.1 h |
| 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 | ~1 h |
| 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 | ~1.1 h |
| 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 | ~1 h |