Oracle SQL WHERE and ORDER BY: Filtering and Sorting — 1Z0-071 Exam Guide

1Z0-071 Exam Prep Chapter 3 Volume 1

The WHERE clause and ORDER BY are in every exam section — not just the filtering topic. The exam tests whether you know exactly which rows each operator selects, how BETWEEN and LIKE behave at the boundaries, and how NULL values sort. Getting these wrong costs marks across multiple questions.

This post covers every filtering operator and sort behaviour the exam tests in Chapter 3.

In this post

1. Comparison operators
2. BETWEEN — inclusive boundaries
3. IN and NOT IN
4. LIKE — pattern matching
5. LIKE with the ESCAPE clause
6. AND, OR, NOT — logical operators and precedence
7. ORDER BY — sorting rules
8. ORDER BY with NULLs
9. ORDER BY with column alias and position
10. Quick reference
11. Practice questions

01 — Comparison operators

The basic comparison operators work on numbers, strings, and dates. String and date comparisons follow alphabetical and chronological order respectively.

-- Numeric comparison
WHERE salary = 5000
WHERE salary > 5000
WHERE salary != 5000    -- also written as <> or ^=

-- String comparison — case sensitive in Oracle by default
WHERE last_name = 'King'    -- matches 'King' only, NOT 'king' or 'KING'
WHERE last_name > 'M'       -- all names alphabetically after M

-- Date comparison
WHERE hire_date > '01-JAN-2020'
WHERE hire_date BETWEEN TO_DATE('01-JAN-2020', 'DD-MON-YYYY')
                      AND    TO_DATE('31-DEC-2020', 'DD-MON-YYYY')

Operator	Meaning	Alternative form
=	Equal to	—
!=	Not equal to	<> or ^=
>	Greater than	—
<	Less than	—
>=	Greater than or equal	—
<=	Less than or equal	—

String comparisons are case sensitive: WHERE last_name = 'king' will not return an employee whose name is stored as 'King'. Oracle stores and compares strings exactly as entered. The exam tests this with questions about why a filter returns zero rows when the case does not match.

02 — BETWEEN — inclusive boundaries

BETWEEN tests whether a value falls within a range. Both boundaries are inclusive — the boundary values themselves are included in the result.

SELECT last_name, salary
FROM   employees
WHERE  salary BETWEEN 5000 AND 10000;
-- Returns rows where salary >= 5000 AND salary <= 10000
-- Employees earning exactly 5000 or exactly 10000 ARE included

-- Equivalent longer form
WHERE salary >= 5000 AND salary <= 10000

-- NOT BETWEEN excludes the range including boundaries
WHERE salary NOT BETWEEN 5000 AND 10000
-- Returns salary < 5000 OR salary > 10000
-- Employees earning exactly 5000 or 10000 are NOT included

-- BETWEEN works on strings and dates too
WHERE last_name BETWEEN 'A' AND 'C'
-- Names starting with A or B — 'C' is included only if the name is exactly 'C'
-- 'Chan' starts with C but 'Chan' > 'C' alphabetically, so it IS included

Lower bound must come first: BETWEEN 10000 AND 5000 returns zero rows — Oracle does not automatically flip the range. The smaller value must always be written first.

03 — IN and NOT IN

IN tests whether a value matches any value in a list. It is shorthand for multiple OR conditions joined together.

SELECT last_name, job_id
FROM   employees
WHERE  job_id IN ('IT_PROG', 'SA_REP', 'AD_PRES');
-- Equivalent to: WHERE job_id = 'IT_PROG' OR job_id = 'SA_REP' OR job_id = 'AD_PRES'

-- NOT IN excludes all values in the list
WHERE  department_id NOT IN (10, 20, 30);
-- Returns all rows except those in departments 10, 20, or 30

-- NULL in an IN list is silently ignored
WHERE  department_id IN (10, 20, NULL);
-- Returns rows in dept 10 or 20 — the NULL has no effect on IN

-- NULL in a NOT IN list causes ZERO rows to be returned
WHERE  department_id NOT IN (10, 20, NULL);
-- Returns ZERO rows — same trap as NOT IN with a subquery returning NULL

IN with a list vs IN with a subquery: The NULL-in-NOT-IN trap applies whether the list is hard-coded or comes from a subquery. If any value in the list is NULL, NOT IN returns no rows at all. IN is not affected by NULLs in the list — they are simply skipped.

04 — LIKE — pattern matching

LIKE matches string patterns using two wildcard characters. It is the only operator that allows partial string matching in a WHERE clause.

Wildcard	Matches	Example
%	Any sequence of zero or more characters	'S%' matches 'S', 'Smith', 'SQL'
_	Exactly one character	'_ing' matches 'King', 'Ring', 'Sing'

-- % wildcard: zero or more characters
WHERE last_name LIKE 'S%'       -- starts with S
WHERE last_name LIKE '%son'     -- ends with son
WHERE last_name LIKE '%ar%'    -- contains ar anywhere

-- _ wildcard: exactly one character
WHERE last_name LIKE '_ing'    -- exactly 4 chars, ending in ing
WHERE last_name LIKE '__a%'    -- third character is 'a'

-- NOT LIKE: exclude the pattern
WHERE last_name NOT LIKE 'A%'  -- names that do NOT start with A

-- LIKE is case sensitive
WHERE last_name LIKE 's%'       -- matches 'smith' but NOT 'Smith'

% matches zero characters too: LIKE 'S%' matches a name that is exactly 'S' (zero characters after S). The exam tests this edge case with questions asking whether a single-character value matches a pattern like 'A%' — it does.

05 — LIKE with the ESCAPE clause

When you need to search for a literal % or _ character, you define an escape character using the ESCAPE clause. The escape character in front of a wildcard turns it into a literal character.

-- Search for names that contain a literal underscore character
WHERE job_id LIKE 'SA\_%' ESCAPE '\'
-- \ is the escape character here (any single character can be used)
-- \_ means: match a literal underscore, not any single character
-- Matches: 'SA_REP', 'SA_MAN' etc.

-- Search for values containing a literal percent sign
WHERE comments LIKE '%50\%%' ESCAPE '\'
-- Matches any string containing '50%'
-- The \% is a literal % sign, the outer % wildcards are still wildcards

Any single character can be the escape character: The ESCAPE clause is followed by a single-character string. It is most commonly backslash but the exam may use any character. The rule is: whatever character follows ESCAPE, that character in front of % or _ makes it literal.

06 — AND, OR, NOT — logical operators and precedence

When multiple conditions are combined, operator precedence determines which conditions are evaluated first. Getting precedence wrong is a common source of exam mistakes.

-- AND: both conditions must be TRUE
WHERE salary > 5000 AND department_id = 50

-- OR: at least one condition must be TRUE
WHERE salary > 5000 OR department_id = 50

-- NOT: inverts the condition
WHERE NOT (salary > 5000)   -- same as salary <= 5000

-- Precedence trap: AND binds tighter than OR
WHERE job_id = 'SA_REP'
   OR  job_id = 'IT_PROG'
  AND  salary > 8000;
-- Evaluated as: job_id = 'SA_REP' OR (job_id = 'IT_PROG' AND salary > 8000)
-- Returns ALL SA_REP employees + IT_PROG employees earning over 8000

-- Use parentheses to force the intended logic
WHERE (job_id = 'SA_REP' OR job_id = 'IT_PROG')
  AND  salary > 8000;
-- Returns SA_REP OR IT_PROG employees who ALSO earn over 8000

Priority	Operator
1 (highest)	Comparison operators (=, >, LIKE, BETWEEN, IN, IS NULL)
2	NOT
3	AND
4 (lowest)	OR

AND before OR — always: In any expression mixing AND and OR without parentheses, AND is evaluated first. The exam frequently uses this to produce unexpected row counts. When in doubt, add parentheses — they override precedence and make the intent explicit.

07 — ORDER BY — sorting rules

ORDER BY controls the sequence of rows in the result set. Without ORDER BY, Oracle makes no guarantee about row order — the same query run twice can return rows in different order.

-- Default sort order is ASC (ascending)
SELECT last_name, salary, department_id
FROM   employees
ORDER BY last_name;          -- A to Z

-- Explicit ASC and DESC
ORDER BY salary DESC         -- highest salary first
ORDER BY salary ASC          -- lowest salary first (same as default)

-- Multi-column sort: primary, then secondary
ORDER BY department_id ASC, salary DESC
-- Sort by department first (ascending)
-- Within each department, highest salary first
-- ASC/DESC applies independently to each column

ORDER BY is the last clause evaluated: It runs after SELECT, which means it can reference column aliases defined in the SELECT list. This is the one clause where SELECT aliases are available — they are not available in WHERE, GROUP BY, or HAVING.

08 — ORDER BY with NULLs

NULL values have a defined sort position in Oracle. The exam tests the default behaviour and the NULLS FIRST / NULLS LAST options.

-- Default NULL sort position
ORDER BY commission_pct ASC
-- NULLs appear LAST in ascending order (treated as larger than any value)

ORDER BY commission_pct DESC
-- NULLs appear FIRST in descending order (still treated as largest)

-- Override default NULL position
ORDER BY commission_pct ASC NULLS FIRST
-- NULLs at the top, then values in ascending order

ORDER BY commission_pct DESC NULLS LAST
-- Values in descending order, NULLs at the bottom

Sort direction	Default NULL position	Override
ASC	NULLs last	NULLS FIRST or NULLS LAST
DESC	NULLs first	NULLS FIRST or NULLS LAST

NULLs sort as if they are the largest value: In ASC order, largest values come last — so NULLs end up at the bottom. In DESC order, largest values come first — so NULLs end up at the top. The exam tests both directions and asks where NULLs appear.

09 — ORDER BY with column alias and position

ORDER BY accepts three forms: the column name, a column alias defined in the SELECT, or a column position number. All three are valid and all three appear on the exam.

SELECT employee_id,
       last_name,
       salary * 12 AS annual_sal
FROM   employees

-- Sort by column name
ORDER BY last_name;

-- Sort by alias defined in SELECT (only possible in ORDER BY)
ORDER BY annual_sal DESC;

-- Sort by column position number (1 = first SELECT column)
ORDER BY 3 DESC;   -- same as ORDER BY annual_sal DESC

-- Can also sort by a column not in the SELECT list
SELECT last_name, salary FROM employees
ORDER BY department_id;   -- valid even though department_id is not selected

Column alias in WHERE raises an error: WHERE annual_sal > 50000 fails if annual_sal is defined as an alias in the SELECT list. ORDER BY runs after SELECT so aliases are available there — WHERE runs before SELECT so they are not. This distinction is tested directly.

10 — Quick reference

Rule	Detail
String comparisons are case sensitive	'King' ≠ 'king' in a WHERE clause
BETWEEN is inclusive	Both boundary values are included in the result
BETWEEN lower bound first	BETWEEN 10000 AND 5000 returns zero rows
NOT IN with NULL in list	Returns zero rows — NULL makes every comparison UNKNOWN
% matches zero or more characters	'S%' matches 'S' (zero chars after S)
_ matches exactly one character	'_ing' matches 4-character names ending in ing
LIKE is case sensitive	's%' does not match 'Smith'
ESCAPE turns wildcard into literal	'\%' with ESCAPE '\' matches a literal % character
AND before OR (no parentheses)	AND has higher precedence than OR
ORDER BY default is ASC	No keyword needed for ascending sort
NULLs in ASC sort — last	Treated as the largest value
NULLs in DESC sort — first	Still treated as the largest value
Column alias usable in ORDER BY	Not usable in WHERE, GROUP BY, or HAVING
ORDER BY column position	ORDER BY 2 sorts by the second SELECT column

11 — Practice questions

Q1 — Which rows does this WHERE clause return?

WHERE department_id NOT IN (10, 20, NULL)

A) All rows where department_id is not 10 and not 20 B) All rows where department_id is not 10, not 20, and not NULL C) Zero rows D) All rows — NULL in a NOT IN list is silently ignored

Q2 — The COMMISSION_PCT column allows NULLs. A query uses ORDER BY commission_pct ASC. Where do the NULL rows appear in the result?

A) At the top — NULLs sort before all other values in ASC order B) At the bottom — NULLs are treated as the largest value in ASC order C) NULLs are excluded from sorted results D) In a random position — NULL sort order is undefined

Q3 — How many rows does this WHERE clause return from a table where 30 employees have job_id = 'SA_REP', 20 have job_id = 'IT_PROG', and 10 have salary > 8000 among the IT_PROG group?

WHERE job_id = 'SA_REP' OR job_id = 'IT_PROG' AND salary > 8000

A) 10 rows — AND and OR have equal precedence, evaluated left to right B) 40 rows — OR is evaluated first: (SA_REP OR IT_PROG) then AND salary C) 40 rows — AND is evaluated first: SA_REP OR (IT_PROG AND salary > 8000) D) 10 rows — AND is evaluated first: SA_REP OR (IT_PROG AND salary > 8000)

Oracle SQL 1Z0-071 Exam Prep Guide — Complete 4-Volume Set

19 chapters  ·  1,480 practice questions  ·  1,249 SQL examples
Chapter 3 covers every filtering operator and sort scenario on the exam — with practice sets built around the boundary, precedence, and NULL traps that cost the most marks. Instant PDF download.

Get the full guide — $25 →

Published by https://www.apps-dba.com — Oracle DBA and certification resources.