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

  1. Stufe 1

    ~0,8 Std.

  2. Stufe 2

    ~2,7 Std.

    laut Plan des Autors nach „Where SQL enters the testing cycle“

  3. Stufe 3

    ~0,9 Std.

  4. Stufe 4

    ~2,1 Std.

    Precise retrieval

  5. Stufe 5

    ~2,1 Std.

  6. Stufe 6

    ~3,9 Std.

  7. Stufe 7

    ~2,1 Std.

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

      GROUP BY and recomputing the report

      ~1 Std.

    • 16 · Data before and after the test · laut Plan des Autors nach einer beliebigen davon: „Staying safe on a shared test environment“, „Set operations as reconciliation“

      Test data: setup and idempotent teardown

      ~1,1 Std.

  8. Stufe 8

    ~2,7 Std.

    • 13 · Aggregation · laut Plan des Autors nach „GROUP BY and recomputing the report“

      HAVING and finding duplicates

      ~1 Std.

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

      Subqueries and CTEs

      ~1 Std.

    • 24 · Optional · laut Plan des Autors nach einer beliebigen davon: „Reading a schema you did not design“, „GROUP BY and recomputing the report“

      Optional: why the schema looks like that

      ~0,7 Std.

  9. Stufe 9

    ~2,1 Std.

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

      Window functions for the tester

      ~1,1 Std.

    • 23 · Optional · laut Plan des Autors nach 2 davon: „Orphans and links that point at nothing“, „Set operations as reconciliation“, „HAVING and finding duplicates“

      Optional: end-to-end migration reconciliation

      ~1 Std.

  10. Stufe 10

    ~1,1 Std.

    • 17 · Data before and after the test · laut Plan des Autors nach einer beliebigen davon: „Window functions for the tester“, „Test data: setup and idempotent teardown“

      Verifying what the application actually wrote

      ~1,1 Std.

  11. Stufe 11

    ~2,1 Std.

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

  12. Stufe 12

    ~1,5 Std.

  13. Prüfung

    gesperrt · in den Kurs einschreiben

    • Abschluss · im Browser, ohne den Assistenten

      Prüfung

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.

Die übrigen 16 Einheiten anzeigen