Zum Inhalt springen
L-SYSTEM

empfohlenMittelstufe

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.

Ihre Lektion ist in Ihrer Sprache: Ihr Assistent leitet sie.

  • Material: Englisch
  • Prüfung: Englisch – wird auf der Website abgelegt, nicht übersetzt
So funktioniert’s

Ihr Assistent holt das Kursmaterial von unserem Server und leitet die Lektion in Ihrer Sprache.

Die Prüfung wird auf der Website abgelegt, ohne Assistenten. Prüfungsfragen werden nicht übersetzt: Das ist für jeden Kurs so festgelegt.

So verbinden Sie einen Assistenten

Weg durch den Kurs

· 24 Einheiten

freie Reihenfolge · die Stufen unten sind die vom Autor vorgeschlagene Reihenfolge, keine BedingungAnsicht: Karte · Stufen

6 · Precise retrieval

Types, conversions and collation

~1 Std.

laut Plan des Autors nach einer beliebigen davon: „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 Std.

laut Plan des Autors nach einer beliebigen davon: „A SELECT that answers exactly one question“, „Types, conversions and collation“

11 · Across tables

Set operations as reconciliation

~1 Std.

laut Plan des Autors nach einer beliebigen davon: „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 Std.

laut Plan des Autors nach einer beliebigen davon: „A SELECT that answers exactly one question“, „When a join multiplies your rows“

14 · Aggregation

Subqueries and CTEs

~1 Std.

laut Plan des Autors nach einer beliebigen davon: „When a join multiplies your rows“, „GROUP BY and recomputing the report“

15 · Aggregation

Window functions for the tester

~1,1 Std.

laut Plan des Autors nach einer beliebigen davon: „HAVING and finding duplicates“, „Subqueries and CTEs“

16 · Data before and after the test

Test data: setup and idempotent teardown

~1,1 Std.

laut Plan des Autors nach einer beliebigen davon: „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 Std.

laut Plan des Autors nach einer beliebigen davon: „Window functions for the tester“, „Test data: setup and idempotent teardown“

18 · Data before and after the test

Reading views, procedures and triggers

~1 Std.

laut Plan des Autors nach einer beliebigen davon: „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 Std.

laut Plan des Autors nach einer beliebigen davon: „Test data: setup and idempotent teardown“, „Verifying what the application actually wrote“

Abschluss · im Browser, ohne den Assistenten

Prüfung

gesperrt · in den Kurs einschreiben

Der Weg in Worten
  • Stufe 1 · Where SQL enters the testing cycle — ~0,8 Std.
  • Stufe 2 · Reading a schema you did not design — laut Plan des Autors nach „Where SQL enters the testing cycle“; ~1 Std.
  • Stufe 2 · Staying safe on a shared test environment — laut Plan des Autors nach „Where SQL enters the testing cycle“; ~1 Std.
  • Stufe 2 · Optional: client data, PII and AI assistants — laut Plan des Autors nach „Where SQL enters the testing cycle“; optional; ~0,7 Std.
  • Stufe 3 · A SELECT that answers exactly one question — laut Plan des Autors nach „Reading a schema you did not design“; ~0,9 Std.
  • Stufe 4 · NULL and the logic that has three answers — laut Plan des Autors nach „A SELECT that answers exactly one question“; ~1,1 Std.
  • Stufe 4 · Types, conversions and collation — laut Plan des Autors nach einer beliebigen davon: „Reading a schema you did not design“, „A SELECT that answers exactly one question“; ~1 Std.
  • Stufe 5 · Dates, times and the last day of the month — laut Plan des Autors nach einer beliebigen davon: „A SELECT that answers exactly one question“, „Types, conversions and collation“; ~1,1 Std.
  • Stufe 5 · INNER and LEFT: which rows the join throws away — laut Plan des Autors nach einer beliebigen davon: „A SELECT that answers exactly one question“, „NULL and the logic that has three answers“; ~1 Std.
  • Stufe 6 · Orphans and links that point at nothing — laut Plan des Autors nach „INNER and LEFT: which rows the join throws away“; ~1 Std.
  • Stufe 6 · When a join multiplies your rows — laut Plan des Autors nach „INNER and LEFT: which rows the join throws away“; ~1,1 Std.
  • Stufe 6 · Set operations as reconciliation — laut Plan des Autors nach einer beliebigen davon: „NULL and the logic that has three answers“, „INNER and LEFT: which rows the join throws away“; ~1 Std.
  • Stufe 6 · Optional: slow queries and blocked environments — laut Plan des Autors nach einer beliebigen davon: „Staying safe on a shared test environment“, „Dates, times and the last day of the month“; optional; ~0,8 Std.
  • Stufe 7 · GROUP BY and recomputing the report — laut Plan des Autors nach einer beliebigen davon: „A SELECT that answers exactly one question“, „When a join multiplies your rows“; ~1 Std.
  • Stufe 7 · Test data: setup and idempotent teardown — laut Plan des Autors nach einer beliebigen davon: „Staying safe on a shared test environment“, „Set operations as reconciliation“; ~1,1 Std.
  • Stufe 8 · HAVING and finding duplicates — laut Plan des Autors nach „GROUP BY and recomputing the report“; ~1 Std.
  • Stufe 8 · Subqueries and CTEs — laut Plan des Autors nach einer beliebigen davon: „When a join multiplies your rows“, „GROUP BY and recomputing the report“; ~1 Std.
  • Stufe 8 · Optional: why the schema looks like that — laut Plan des Autors nach einer beliebigen davon: „Reading a schema you did not design“, „GROUP BY and recomputing the report“; optional; ~0,7 Std.
  • Stufe 9 · Window functions for the tester — laut Plan des Autors nach einer beliebigen davon: „HAVING and finding duplicates“, „Subqueries and CTEs“; ~1,1 Std.
  • Stufe 9 · Optional: end-to-end migration reconciliation — laut Plan des Autors nach 2 davon: „Orphans and links that point at nothing“, „Set operations as reconciliation“, „HAVING and finding duplicates“; optional; ~1 Std.
  • Stufe 10 · Verifying what the application actually wrote — laut Plan des Autors nach einer beliebigen davon: „Window functions for the tester“, „Test data: setup and idempotent teardown“; ~1,1 Std.
  • Stufe 11 · Reading views, procedures and triggers — laut Plan des Autors nach einer beliebigen davon: „Test data: setup and idempotent teardown“, „Verifying what the application actually wrote“; ~1 Std.
  • Stufe 11 · Turning a check into a deterministic assertion — laut Plan des Autors nach einer beliebigen davon: „Test data: setup and idempotent teardown“, „Verifying what the application actually wrote“; ~1,1 Std.
  • Stufe 12 · Optional: build ENGAGE locally and run the checks — laut Plan des Autors nach einer beliebigen davon: „Reading a schema you did not design“, „Turning a check into a deterministic assertion“; optional; ~1,5 Std.

Lehrplan · 24 Einheiten

1Where SQL enters the testing cycle

Name the four moments in a test cycle where a database query is the only available evidence

~0,8 Std.
2Reading a schema you did not design

Locate an unfamiliar table or column by querying INFORMATION_SCHEMA rather than browsing the object explorer

~1 Std.
3Staying 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 Std.
4A 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 Std.
5NULL 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 Std.
6Types, 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 Std.
7Dates, 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 Std.
8INNER 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 Std.
9Orphans 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

~1 Std.
10When a join multiplies your rows

Explain why a join to a non-unique key multiplies rows and why an aggregate conceals the multiplication

~1,1 Std.
11Set operations as reconciliation

Reconcile two populations with EXCEPT in both directions, and explain why one direction alone can report success on a broken migration

~1 Std.
12GROUP 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

~1 Std.
13HAVING and finding duplicates

Find duplicates by grouping on the business key and filtering with HAVING COUNT(*) > 1

~1 Std.
14Subqueries and CTEs

Distinguish scalar, derived-table and correlated subqueries, and identify where each fails

~1 Std.
15Window functions for the tester

Explain how a window function differs from GROUP BY, and choose between them from the question

~1,1 Std.
16Test data: setup and idempotent teardown

Write setup scripts that are idempotent, isolated and self-identifying, with teardown at the start rather than the end

~1,1 Std.
17Verifying what the application actually wrote

Enumerate the blast radius of a write — every table an operation should touch — before writing any verification query

~1,1 Std.
18Reading 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

~1 Std.
19Turning a check into a deterministic assertion

Choose between boolean, scalar and set assertion shapes, defaulting to the violation query

~1,1 Std.
20Optional: 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 Std.
21Optional: slow queries and blocked environments

Read the four common plan operators and interpret the gap between estimated and actual row counts

~0,8 Std.
22Optional: client data, PII and AI assistants

Identify what counts as client data on a professional services engagement, beyond personal data

~0,7 Std.
23Optional: end-to-end migration reconciliation

Run a layered reconciliation — population, totals, set difference, duplicates, references, distribution — and state what each layer cannot detect

~1 Std.
24Optional: 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 Std.