SQL for Testers: A Working Day
Answer a tester's everyday questions with SQL against a real shop database. Find the rows behind a complaint, join tables to find what is missing, count and group for a report, prepare test data safely, and check what a release did to the data.
43 lessons · 522 stepscertificate
43 lessons · 522 steps
certificateCourse program
The full outline. Lessons open when the course does.
Morning: into the database
How this day works9 steps
Why a tester needs SQL12 steps
SELECT and DISTINCT11 steps
The schema: keys, relations and reading a diagram11 steps
Databases, DBMSs and connecting with DBeaver14 steps
The support queue: finding rows
Working the queue: filtering rows with WHERE11 steps
Comparisons: amounts, boundaries and dates12 steps
AND, OR, NOT and brackets12 steps
Lists of values: IN and NOT IN12 steps
Searching text with LIKE11 steps
Ranges with BETWEEN12 steps
Empty values: IS NULL and IS NOT NULL11 steps
ORDER BY: row order in a report11 steps
LIMIT and top N11 steps
The order a database runs a query13 steps
The whole picture: several tables
INNER JOIN: two tables together11 steps
LEFT JOIN: finding what is missing12 steps
RIGHT JOIN: the same question from the other side12 steps
UNION and UNION ALL: two lists in one12 steps
Comparing two lists in full13 steps
Self-join: pairs of rows inside one table13 steps
The report for the team: counting and grouping
COUNT: how many in all12 steps
SUM and AVG: totals and averages14 steps
MIN and MAX: the bounds of a range14 steps
GROUP BY: counting per group14 steps
HAVING: filtering after grouping12 steps
CASE WHEN: a column of your own by condition12 steps
Subqueries: a query inside a query11 steps
EXISTS and NOT EXISTS: is there any at all13 steps
The test environment: preparing data
INSERT: adding rows for a test12 steps
UPDATE: changing rows that already exist12 steps
DELETE: cleaning up after a run13 steps
The release evening: structure, constraints, transactions
CREATE TABLE: a table for a check12 steps
Migrations and ALTER TABLE: what to check after a release12 steps
DROP and TRUNCATE: remove a table or empty it11 steps
Constraints: what the database won't let you break12 steps
Transactions: BEGIN, COMMIT, ROLLBACK11 steps
Review: the end of the day11 steps
After hours
WITH: a long query in steps12 steps
Window functions: numbering, duplicates and first in a group15 steps
LAG, LEAD and running totals13 steps
One query, three engines12 steps
Traps that pass the tests16 steps
The author
Artsiom Rusau – QA engineer and course author. He has tested software since 2019 and has taught software testing since 2020.