📝 Practice & Reference

Practice & Reference: Learning SQL from reading alone is like learning to swim from a book — you have to get in the water.

Learning SQL from reading alone is like learning to swim from a book — you have to get in the water. A book can describe the stroke, but you only learn when to breathe after you have swallowed once. Open the editor, run the query, hit the error, fix it. So why is reading especially insufficient here, when the syntax is this simple? Because SQL's difficulty is not in its syntax but in its **silent wrongness**. Bad Java code refuses to compile and prints a red error; a bad SQL query usually runs perfectly and hands you the WRONG table. Forget a join condition and you get no error — you get 40,000 rows instead of 200. Write `WHERE x = NULL` and you get no error — you get zero rows. You do not catch that class of mistake by reading; you catch it by putting the result you expected next to the result you got. In Java the compiler is your first reviewer; in SQL that role is vacant, and you are the one who has to fill it. So build the habit: before any destructive statement, run a SELECT with the same WHERE and look at the row count. For QA this way of practising converts directly into work: when an interview asks you to "write this query", you answer by running it in your head rather than reciting it; on the job you become the person who finds the root cause without holding up the pipeline. Try every exercise yourself first, and only open the solution once your own answer is written.

Exercise 1: Query Failed Test Runs

Given a test_runs table with columns: id, test_name, status (PASS/FAIL/SKIP), duration_ms, run_date. Write THREE queries: (a) all failed runs today, (b) count of each status, (c) slowest 3 tests.

DATE() extracts just the date part of a DATETIME. CURDATE() returns today. These are MySQL functions — PostgreSQL uses CURRENT_DATE.

Exercise 2: Multi-Table Join

You have three tables: users (id, name, email), test_cases (id, title, category), results (id, user_id, test_case_id, status, run_date). Write a query showing: tester name, test case title, status, and run_date — only for tests run in the last 30 days, ordered by most recent first.

Start from the "results" table (the junction table linking users and test_cases) and JOIN outward. This avoids accidental cartesian products.

Exercise 3: CTE + Window Function — Rank Testers by Pass Rate

Using the results table (user_id, status, sprint), write a query that ranks testers by their pass rate PER SPRINT using a CTE to calculate stats and RANK() window function. Show: sprint, tester name, total tests, pass count, pass rate %, rank within sprint.

Two CTEs: first aggregates raw counts, second calculates rate and joins user names. The final SELECT adds the window function. Splitting into CTEs makes each step debuggable.

Quick Reference Card

Bookmark db-fiddle.com for quick experiments. Always test your WHERE clause with a SELECT before running DELETE or UPDATE — one missing WHERE can wipe your entire table.

A function that reduces multiple rows to a single summary value: COUNT, SUM, AVG, MIN, MAX.

An integer that automatically increases for each new row. MySQL term; SQLite uses INTEGER PRIMARY KEY, PostgreSQL uses SERIAL.