Question 1

Checking for NULL

Accepted Answer

IS NULL is the correct way to check for NULL values. The = operator always returns NULL (not true) when compared to NULL, so WHERE manager_id = NULL would return no rows. Only Alice has a NULL manager_id, so only her name is returned.

Question 2

COUNT(*) vs COUNT(column)

Accepted Answer

COUNT(*) counts every row regardless of NULL values - there are 3 rows so it returns 3. COUNT(amount) counts only rows where amount is not NULL - row 2 has NULL so it returns 2. This difference is easy to miss and frequently causes off-by-one bugs in aggregation queries.

Question 3

NULL = NULL

Accepted Answer

In SQL, NULL represents an unknown value. Comparing two unknowns produces another unknown, not true. This is why WHERE col = NULL never matches any rows - you must use IS NULL instead. This three-valued logic (true, false, null) is one of the most common sources of bugs in SQL.

Question 4

COALESCE basics

Accepted Answer

COALESCE returns the first non-NULL argument. For Alice, nickname is 'Ace' (not NULL) so it returns 'Ace'. For Bob, nickname is NULL so it falls through to name, returning 'Bob'. COALESCE is the standard way to substitute a fallback value when a column might be NULL.

Question 5

Filtering non-NULL rows

Accepted Answer

IS NOT NULL returns true only for rows where the column has an actual value. Gadget has a NULL category so it is excluded. Widget and Doohick both have 'Tools' as their category, so both are returned.

Question 6

COALESCE for safe arithmetic

Accepted Answer

Any arithmetic involving NULL produces NULL. salary + NULL is NULL, not salary. COALESCE(bonus, 0) converts NULL to 0 before the addition, so salary + 0 = salary. The last option, COALESCE(salary + bonus, 0), would return 0 for the whole expression if either operand is NULL - that is wrong if you want to preserve the salary.

Question 7

SUM with NULLs

Accepted Answer

SUM ignores NULL values, just like COUNT(column) does. It adds 100 + 200 = 300. The NULL row simply does not contribute. This behavior applies to AVG, MIN, MAX, and SUM - all aggregate functions skip NULLs. AVG(amount) would return 150, not 100, because it divides 300 by 2 (non-NULL rows), not 3.

Question 8

Three-valued logic

Accepted Answer

SQL uses three-valued logic: true, false, and NULL (unknown). Any comparison with NULL produces NULL. The WHERE clause only includes rows where the condition is true - rows where the condition is NULL are excluded, just like false rows. This is why WHERE col = NULL never matches anything: the result is NULL, which is not true.

Question 9

COALESCE with multiple fallbacks

Accepted Answer

COALESCE evaluates its arguments left to right and returns the first one that is not NULL. The first two arguments are NULL so they are skipped. The third argument is 42 - not NULL - so COALESCE returns 42 immediately without evaluating 99. You can chain as many fallbacks as you need; COALESCE is the standard tool for cascading defaults.