Saltar al contenido
L-SYSTEM

recomendadointermedio

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.

Tu lección es en tu idioma: la dirige tu asistente.

  • Material: inglés
  • Examen: inglés; se hace en el sitio, sin traducir
Cómo funciona

Tu asistente toma el material del curso de nuestro servidor y dirige la lección en tu idioma.

El examen se hace en el sitio, sin asistente. Las preguntas del examen no se traducen: así se ha decidido para todos los cursos.

Cómo conectar un asistente

Recorrido por el curso

· 24 unidades

orden libre · los niveles de abajo son el orden que sugiere el autor, no una condiciónVista: mapa · niveles

  1. Nivel 1

    ~0,8 h

  2. Nivel 2

    ~2,7 h

    según el diseño del autor, después de «Where SQL enters the testing cycle»

  3. Nivel 3

    ~0,9 h

  4. Nivel 4

    ~2,1 h

    Precise retrieval

  5. Nivel 5

    ~2,1 h

    • 7 · Precise retrieval · según el diseño del autor, después de cualquiera de estas: «A SELECT that answers exactly one question», «Types, conversions and collation»

      Dates, times and the last day of the month

      ~1,1 h

    • 8 · Across tables · según el diseño del autor, después de cualquiera de estas: «A SELECT that answers exactly one question», «NULL and the logic that has three answers»

      INNER and LEFT: which rows the join throws away

      ~1 h

  6. Nivel 6

    ~3,9 h

    • 9 · Across tables · según el diseño del autor, después de «INNER and LEFT: which rows the join throws away»

      Orphans and links that point at nothing

      ~1 h

    • 10 · Across tables · según el diseño del autor, después de «INNER and LEFT: which rows the join throws away»

      When a join multiplies your rows

      ~1,1 h

    • 11 · Across tables · según el diseño del autor, después de cualquiera de estas: «NULL and the logic that has three answers», «INNER and LEFT: which rows the join throws away»

      Set operations as reconciliation

      ~1 h

    • 21 · Optional · según el diseño del autor, después de cualquiera de estas: «Staying safe on a shared test environment», «Dates, times and the last day of the month»

      Optional: slow queries and blocked environments

      ~0,8 h

  7. Nivel 7

    ~2,1 h

    • 12 · Aggregation · según el diseño del autor, después de cualquiera de estas: «A SELECT that answers exactly one question», «When a join multiplies your rows»

      GROUP BY and recomputing the report

      ~1 h

    • 16 · Data before and after the test · según el diseño del autor, después de cualquiera de estas: «Staying safe on a shared test environment», «Set operations as reconciliation»

      Test data: setup and idempotent teardown

      ~1,1 h

  8. Nivel 8

    ~2,7 h

    • 13 · Aggregation · según el diseño del autor, después de «GROUP BY and recomputing the report»

      HAVING and finding duplicates

      ~1 h

    • 14 · Aggregation · según el diseño del autor, después de cualquiera de estas: «When a join multiplies your rows», «GROUP BY and recomputing the report»

      Subqueries and CTEs

      ~1 h

    • 24 · Optional · según el diseño del autor, después de cualquiera de estas: «Reading a schema you did not design», «GROUP BY and recomputing the report»

      Optional: why the schema looks like that

      ~0,7 h

  9. Nivel 9

    ~2,1 h

    • 15 · Aggregation · según el diseño del autor, después de cualquiera de estas: «HAVING and finding duplicates», «Subqueries and CTEs»

      Window functions for the tester

      ~1,1 h

    • 23 · Optional · según el diseño del autor, después de 2 de estas: «Orphans and links that point at nothing», «Set operations as reconciliation», «HAVING and finding duplicates»

      Optional: end-to-end migration reconciliation

      ~1 h

  10. Nivel 10

    ~1,1 h

    • 17 · Data before and after the test · según el diseño del autor, después de cualquiera de estas: «Window functions for the tester», «Test data: setup and idempotent teardown»

      Verifying what the application actually wrote

      ~1,1 h

  11. Nivel 11

    ~2,1 h

    Data before and after the test · según el diseño del autor, después de cualquiera de estas: «Test data: setup and idempotent teardown», «Verifying what the application actually wrote»

  12. Nivel 12

    ~1,5 h

  13. Examen

    cerrada · inscríbete en el curso

    • final · en el navegador, sin el asistente

      Examen

El recorrido en palabras
  • Nivel 1 · Where SQL enters the testing cycle — ~0,8 h
  • Nivel 2 · Reading a schema you did not design — según el diseño del autor, después de «Where SQL enters the testing cycle»; ~1 h
  • Nivel 2 · Staying safe on a shared test environment — según el diseño del autor, después de «Where SQL enters the testing cycle»; ~1 h
  • Nivel 2 · Optional: client data, PII and AI assistants — según el diseño del autor, después de «Where SQL enters the testing cycle»; opcional; ~0,7 h
  • Nivel 3 · A SELECT that answers exactly one question — según el diseño del autor, después de «Reading a schema you did not design»; ~0,9 h
  • Nivel 4 · NULL and the logic that has three answers — según el diseño del autor, después de «A SELECT that answers exactly one question»; ~1,1 h
  • Nivel 4 · Types, conversions and collation — según el diseño del autor, después de cualquiera de estas: «Reading a schema you did not design», «A SELECT that answers exactly one question»; ~1 h
  • Nivel 5 · Dates, times and the last day of the month — según el diseño del autor, después de cualquiera de estas: «A SELECT that answers exactly one question», «Types, conversions and collation»; ~1,1 h
  • Nivel 5 · INNER and LEFT: which rows the join throws away — según el diseño del autor, después de cualquiera de estas: «A SELECT that answers exactly one question», «NULL and the logic that has three answers»; ~1 h
  • Nivel 6 · Orphans and links that point at nothing — según el diseño del autor, después de «INNER and LEFT: which rows the join throws away»; ~1 h
  • Nivel 6 · When a join multiplies your rows — según el diseño del autor, después de «INNER and LEFT: which rows the join throws away»; ~1,1 h
  • Nivel 6 · Set operations as reconciliation — según el diseño del autor, después de cualquiera de estas: «NULL and the logic that has three answers», «INNER and LEFT: which rows the join throws away»; ~1 h
  • Nivel 6 · Optional: slow queries and blocked environments — según el diseño del autor, después de cualquiera de estas: «Staying safe on a shared test environment», «Dates, times and the last day of the month»; opcional; ~0,8 h
  • Nivel 7 · GROUP BY and recomputing the report — según el diseño del autor, después de cualquiera de estas: «A SELECT that answers exactly one question», «When a join multiplies your rows»; ~1 h
  • Nivel 7 · Test data: setup and idempotent teardown — según el diseño del autor, después de cualquiera de estas: «Staying safe on a shared test environment», «Set operations as reconciliation»; ~1,1 h
  • Nivel 8 · HAVING and finding duplicates — según el diseño del autor, después de «GROUP BY and recomputing the report»; ~1 h
  • Nivel 8 · Subqueries and CTEs — según el diseño del autor, después de cualquiera de estas: «When a join multiplies your rows», «GROUP BY and recomputing the report»; ~1 h
  • Nivel 8 · Optional: why the schema looks like that — según el diseño del autor, después de cualquiera de estas: «Reading a schema you did not design», «GROUP BY and recomputing the report»; opcional; ~0,7 h
  • Nivel 9 · Window functions for the tester — según el diseño del autor, después de cualquiera de estas: «HAVING and finding duplicates», «Subqueries and CTEs»; ~1,1 h
  • Nivel 9 · Optional: end-to-end migration reconciliation — según el diseño del autor, después de 2 de estas: «Orphans and links that point at nothing», «Set operations as reconciliation», «HAVING and finding duplicates»; opcional; ~1 h
  • Nivel 10 · Verifying what the application actually wrote — según el diseño del autor, después de cualquiera de estas: «Window functions for the tester», «Test data: setup and idempotent teardown»; ~1,1 h
  • Nivel 11 · Reading views, procedures and triggers — según el diseño del autor, después de cualquiera de estas: «Test data: setup and idempotent teardown», «Verifying what the application actually wrote»; ~1 h
  • Nivel 11 · Turning a check into a deterministic assertion — según el diseño del autor, después de cualquiera de estas: «Test data: setup and idempotent teardown», «Verifying what the application actually wrote»; ~1,1 h
  • Nivel 12 · Optional: build ENGAGE locally and run the checks — según el diseño del autor, después de cualquiera de estas: «Reading a schema you did not design», «Turning a check into a deterministic assertion»; opcional; ~1,5 h

Programa · 24 unidades

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 h
2Reading a schema you did not design

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

~1 h
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 h
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 h
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 h
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 h
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 h
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 h
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 h
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 h
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 h
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 h
13HAVING and finding duplicates

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

~1 h
14Subqueries and CTEs

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

~1 h
15Window functions for the tester

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

~1,1 h
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 h
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 h
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 h
19Turning a check into a deterministic assertion

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

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

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

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

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

~0,7 h
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 h
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 h