Commission list: operator precedence, null ordering, and LIKE wildcards
The marketing team needs a list of commission-eligible sales staff. EMPLOYEES holds 107 rows; DEPARTMENT_ID is null for unassigned staff and COMMISSION_PCT is null for 72 rows. A developer submits:
SELECT emp_name, commission_pct FROM employees WHERE department_id = 80 OR department_id = 20 AND commission_pct > 0.15 ORDER BY commission_pct DESC, emp_name;
It returns 41 rows. The analyst expected only people in departments 80 and 20 whose commission exceeds 0.15, and complains that the top of the output is a block of rows with a blank commission column. PHONE_NUMBER values look like '515.123.4567' or '011.44.1344.429268'.
1. Explain why 41 rows come back instead of the expected set, give the corrected WHERE clause, and say what the extra rows have in common.
Read the worked answer
AND binds tighter than OR, so the predicate parses as:
WHERE department_id = 80
OR (department_id = 20 AND commission_pct > 0.15)
Every department 80 row qualifies unconditionally, including rows where commission_pct is null or below the threshold. Those are the extra rows - the same block the analyst sees with a blank commission column.
WHERE (department_id = 80 OR department_id = 20)
AND commission_pct > 0.15
Judgement point: the commission_pct > 0.15 predicate also silently drops null commissions, because a comparison with null is UNKNOWN, not TRUE. If 'eligible' means 'null treated as zero', the predicate must be NVL(commission_pct,0) > 0.15; the row count has to be reconciled against the business definition, not against the developer's intent.
2. The analyst wants the highest commission first but the null-commission rows at the bottom. The developer says 'nulls sort lowest, so DESC already puts them last'. Is that right, and what is the correct ORDER BY?
Read the worked answer
It is not right. Oracle's default is NULLS LAST for ascending order and NULLS FIRST for descending order - the opposite of the intuitive reading. So ORDER BY commission_pct DESC places all 72 null rows at the top of the report.
ORDER BY commission_pct DESC NULLS LAST, emp_name ASC
A DESC sort on a nullable column is where this bug hides. In any report where nulls are meaningful, state NULLS FIRST/NULLS LAST explicitly rather than relying on the default.
3. Write the predicates for (a) phone numbers whose first three characters are anything, followed by a literal dot; and (b) any row whose PHONE_NUMBER literally contains an underscore. Explain the trap in each.
Read the worked answer
_ is a single-character wildcard; only % is multi-character.
(a) Three arbitrary characters, then a dot, then anything:
WHERE phone_number LIKE '___.%'
The trap: LIKE '___%' (three underscores then percent) means 'at least three characters, no dot required' and would match almost every row.
(b) A literal underscore has to be escaped, otherwise '%_%' matches every non-empty value:
WHERE phone_number LIKE '%\_%' ESCAPE '\'
Also remember LIKE is case-sensitive and matches the whole value - a pattern without a leading and trailing % is an exact-length match, not a 'contains' search.