Functions & transformation — quiz

Why does a join fail on values that look identical?

1. Wrong data type
2. Trailing whitespace or case differences
3. Missing index
4. Too many rows

Answer: Trailing whitespace or case differences — TRIM and case normalisation on import prevent both.

WHERE UPPER(city) = 'HOUSTON' is slow because:

1. UPPER is expensive
2. The function prevents an index seek
3. Strings are slow
4. It needs a subquery

Answer: The function prevents an index seek — Wrapping the column in a function blocks the index.

SELECT 7 / 2 in PostgreSQL returns:

1. 3.5
2. 3
3. 4
4. An error

Answer: 3 — Integer division discards the remainder. Cast to get 3.5.

Which prevents a division-by-zero error?

1. COALESCE(x, 0)
2. NULLIF(x, 0)
3. ABS(x)
4. ROUND(x)

Answer: NULLIF(x, 0) — NULLIF makes the denominator null, so the result is null rather than an error.

Why does BETWEEN '2024-01-01' AND '2024-01-31' miss rows?

1. It excludes the first day
2. The end date means midnight, excluding most of the 31st
3. BETWEEN is exclusive
4. Dates cannot use BETWEEN

Answer: The end date means midnight, excluding most of the 31st — With a timestamp column, the end bound is 00:00:00 on the 31st.

Grouping monthly by a formatted string causes:

1. An error
2. Alphabetical sorting instead of chronological
3. Duplicate rows
4. Slower joins

Answer: Alphabetical sorting instead of chronological — April sorts before January as text. Truncate to a date instead.

price - discount returns null when discount is null. Fix:

1. NULLIF(discount, 0)
2. COALESCE(discount, 0)
3. ISNULL(price)
4. Cast to numeric

Answer: COALESCE(discount, 0) — COALESCE substitutes zero so the arithmetic works.

NULLIF(a, b) returns:

1. b when a is null
2. null when a equals b, otherwise a
3. The first non-null
4. Always null

Answer: null when a equals b, otherwise a — It generates a null on a match — the opposite job to COALESCE.