🟢 NULL Values

NULL Values: Think of NULL as an empty box on a survey form — and note that it is not the same as a box containing 0.

Think of NULL as an empty box on a survey form — and note that it is not the same as a box containing 0. Someone who writes "0" under "how many siblings?" has answered; someone who leaves it blank has not. NULL means 'unknown', not 'no value' — a blank phone number field on a customer form doesn't mean the customer has no phone; it means the value was never entered. This nuance fundamentally separates SQL from Java: in Java `null == null` returns `true`; in SQL `NULL = NULL` returns `NULL` — neither true nor false. Why was such a complex behavior designed? Because it is unknowable whether two unknowns are equal — if two customer records have a blank phone field, are they the same customer? Unknown. In Java you use `Objects.isNull(value)` or `value == null`; in SQL `IS NULL` or `IS NOT NULL` is mandatory — `= NULL` always returns 0 rows. For a QA engineer NULL's most insidious risk is this: if you write `WHERE error_msg = NULL` while querying test data with optional fields, the query returns zero rows and the assertion passes — yet rows with NULL values may exist in the table; that false PASS hides a real regression.

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.

Step by Step: SQL — GROUP BY / CTE / Window

Aggregation or Window?

Determine: aggregation (summarizing) or Window (row-by-row computing)?

For aggregation, select the summary column with GROUP BY

Apply condition on groups with HAVING — like WHERE but after aggregation

Make modular with CTE

Modularize query with WITH cte_name AS (...) SELECT ... FROM cte_name

Add OVER(PARTITION BY)

For Window, use SUM(col) OVER (PARTITION BY partition ORDER BY sort)

What is the SQL GROUP BY and Window Function query writing order?

Interactive Example: test_results Table