Restricting and Sorting Data
Domain 2 tests row filtering with WHERE and deterministic ordering with ORDER BY. The exam expects precise predicate behavior, especially with NULL, wildcards, ranges, and multiple sort keys.
Core rules
- Comparison operators include =, != or <>, >, >=, <, <=. BETWEEN is inclusive. IN tests membership. LIKE uses % for any string and _ for one character.
- Combine predicates with AND, OR, and NOT. AND binds more tightly than OR; use parentheses to make intent explicit.
- ORDER BY sorts ascending by default. Use DESC for descending. Oracle defaults to NULLS LAST for ascending and NULLS FIRST for descending, but explicit NULLS FIRST or NULLS LAST is safer.
- ORDER BY can use a column alias, an expression, or a column position. It cannot use a SELECT alias inside WHERE.
- Row limiting can use FETCH FIRST n ROWS ONLY, optionally with OFFSET.
Worked example
SELECT last_name, salary FROM employees WHERE hire_date BETWEEN DATE '2020-01-01' AND DATE '2020-12-31' AND last_name LIKE 'S%' ORDER BY salary DESC NULLS LAST FETCH FIRST 5 ROWS ONLY;
The WHERE keeps employees hired in 2020 whose last name starts with S. BETWEEN includes both boundary dates. ORDER BY places highest salary first and puts NULL salaries last. FETCH FIRST returns at most five rows after sorting.
Traps
NOT IN with a NULL in the list can produce no rows because comparisons become unknown. LIKE patterns are case-sensitive unless NLS settings or functions change behavior. A leading wildcard such as %son generally prevents ordinary index use. Omitting ORDER BY makes FETCH results nondeterministic. Sorting by a column position that is not in the SELECT list can surprise you.
Key points
- BETWEEN is inclusive at both ends.
- AND has higher precedence than OR.
- Default NULL ordering follows ASC or DESC unless overridden.
- NOT IN and NULL can eliminate all rows.
- FETCH requires ORDER BY for predictable results.