1Z0-149: Restricting and Sorting Data study guide

What this domain tests

Restricting and sorting tests WHERE predicates, comparison and logical operators, pattern matching, NULL tests, and ORDER BY behavior.

Core rules

WHERE filters individual rows before grouping. It cannot contain group functions.
Comparison operators include =, <> or !=, >, >=, <, <=. BETWEEN a AND b is inclusive at both ends.
IN (list) is a compact set of equality tests. LIKE uses % for any string (including empty) and _ for exactly one character; ESCAPE defines an escape character.
Test NULL only with IS NULL or IS NOT NULL; = NULL is unknown and returns no rows.
Logical precedence is NOT, then AND, then OR. Parentheses make intent explicit.
ORDER BY is the last clause. Default order is ascending; ascending puts NULLs last, descending puts NULLs first unless NULLS FIRST/NULLS LAST is specified.
You may sort by an alias, a column position, or an expression.

Worked example

SELECT last_name, salary FROM employees WHERE hire_date BETWEEN DATE '2005-01-01' AND DATE '2005-12-31' AND salary > 5000 ORDER BY salary DESC NULLS LAST;

BETWEEN includes both January 1 and December 31. AND binds tighter than OR, so no extra parentheses are needed here. The sort is descending, and NULLS LAST overrides the default descending placement of NULLs.

Traps

Using NOT IN with a list containing NULL: the predicate can become unknown for every row.
Expecting LIKE 'A_' to match any length; it matches exactly two characters.
Sorting by a position that is not in the select list in a DISTINCT query.
Forgetting that character comparisons can be case-sensitive and NLS-dependent.
Putting ORDER BY before WHERE.