🧪 SQL for QA — Real Testing Scenarios

SQL for QA — Real Testing Scenarios: Think of SQL in QA as a restaurant inspector's right to walk into the kitchen.

Think of SQL in QA as a restaurant inspector's right to walk into the kitchen. The plate at the table may look perfect and the waiter may insist everything is fine — the inspector still goes behind the counter and checks the fridge temperature. The UI is your table; the database is the kitchen. "Payment successful ✅" on screen does not mean `status='PAID'` was actually written to the `orders` table. So if the UI already shows the right message, isn't checking the database just extra fussiness? Quite the opposite — the most expensive bugs live precisely in that gap. When the screen reports success but the record is written half-way, your test goes GREEN and the customer believes the order exists; the problem surfaces weeks later during financial reconciliation. A UI assertion verifies *what the user was told*; a SQL assertion verifies *what actually happened*. Those are two different questions, and asking only the first leaves your test far narrower than you think. The Java parallel is familiar: verify the response with Selenium or REST Assured, then pull the same record over JDBC and `assertEquals` on it. The difference being that the HTTP response is the application's **claim**, while the database row is the **outcome**. SQL sits at both ends of this job: BEFORE the test you INSERT/UPDATE a known starting state (so the test never depends on whatever random production data happens to exist — the number one source of flaky tests), and AFTER the test you SELECT to confirm what was really written. There is a third moment too: bug investigation. Pin a vague "it breaks sometimes" report to a time range with `SELECT ... WHERE created_at BETWEEN ...` and you will often find the breakage lines up exactly with a deploy.

Use Case 1: Find All Failed Tests in Last 7 Days

Micro Lab: SQL — GROUP BY / CTE / Window

Replace the TODO line with the critical line from the expected solution. This is not a real runtime; the goal is to reinforce writing the correct structure in a controlled way.

What Does NOW() - INTERVAL 7 DAY Actually Compute?

The MOMENT NOW() runs…

The MOMENT NOW() runs, it returns the server's current date/time — this value is RECALCULATED every time the query runs, it's not a fixed date.

NOW() - INTERVAL 7 DAY produces…

NOW() - INTERVAL 7 DAY produces the timestamp exactly 7 days BEFORE this moment — e.g. if today is July 18, the result is July 11.

The WHERE run_date >= ... condition…

The WHERE run_date >= ... condition keeps every row AT OR AFTER this threshold — a record from 8 days ago falls OUTSIDE the boundary and never enters the result.

status = 'FAIL' combined in the SAME WHERE…

When status = 'FAIL' is combined in the SAME WHERE with AND, the database looks for rows satisfying BOTH conditions — order doesn't matter, the optimizer evaluates them together.

The second query's GROUP BY test_name…