Restricting and sorting: 1Z0-171 practice questions
Questions in the style of exam 1Z0-171 on this topic. Answer each one first, then open its answer and explanation.
What this topic covers
- Rules of precedence for operators in an expression
- Limiting rows returned in a SQL statement
- Substitution variables
- The DEFINE and VERIFY commands
- Sorting data
Practice questions
1. What happens when this statement runs?
SELECT e.employee_id
FROM employees e
ORDER BY e.salary
FETCH FIRST 2 ROWS ONLY OFFSET 1 ROWS;- It fails: OFFSET must come before FETCH (ORA-03049: SQL keyword 'OFFSET' is not syntactically valid).
- It fails: Oracle reads OFFSET as a column name it cannot find (ORA-00904: invalid identifier).
- It runs: it skips one row and returns the next two.
- It runs: it returns the first two rows and ignores OFFSET.
Show the answer
Answer: A
The row limiting clause has a fixed order: ORDER BY, then OFFSET, then FETCH. Nothing may follow FETCH ... ONLY, so Oracle 26ai raises ORA-03049 and names OFFSET and what it followed (19c said only ORA-00933). Write ORDER BY e.salary OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY to skip one row and return two.
2. With the default binary sort (NLS_SORT = BINARY), which value is in the first row?
SELECT w
FROM (SELECT 'apple' AS w FROM dual
UNION ALL SELECT 'Banana' FROM dual
UNION ALL SELECT 'cherry' FROM dual)
ORDER BY w;- Banana
- apple
- cherry
- None: ORDER BY cannot follow an inline view with UNION ALL
Show the answer
Answer: A
A binary sort compares character codes, and all uppercase letters come before all lowercase letters, so Banana sorts before apple and cherry. Alphabetical order ignoring case needs ORDER BY UPPER(w) (or a linguistic NLS_SORT). Sorting an inline view is allowed.
3. The employees table holds the last names De Haan, Ernst, Grant, Hunold, King, Kochhar, Lorentz, Partners and Russell. Which names does this query return?
SELECT e.last_name
FROM employees e
WHERE e.last_name BETWEEN 'D' AND 'H'
ORDER BY e.last_name;- De Haan, Ernst, Grant
- De Haan, Ernst, Grant, Hunold
- Ernst, Grant
- None: BETWEEN compares only numbers and dates
Show the answer
Answer: A
BETWEEN 'D' AND 'H' means >= 'D' AND <= 'H', compared character by character. 'De Haan' starts with 'D' and is longer, so it sorts after 'D' and passes the lower bound. 'Hunold' starts with 'H' but is longer, so it sorts after 'H' and fails the upper bound. To include every name starting with H, write last_name >= 'D' AND last_name < 'I'. BETWEEN works on text, numbers and dates.
4. employees has 9 rows, and one of them has a NULL department_id. How many rows do these two queries return in total?
SELECT e.employee_id FROM employees e
WHERE e.department_id BETWEEN 10 AND 20;
SELECT e.employee_id FROM employees e
WHERE e.department_id NOT BETWEEN 10 AND 20;- 8
- 9
- 10
- 0
Show the answer
Answer: A
For the row with a NULL department_id, BETWEEN 10 AND 20 is UNKNOWN, and NOT BETWEEN is NOT UNKNOWN, which is still UNKNOWN. WHERE keeps only TRUE rows, so that employee appears in neither result: 4 + 4 = 8. Only IS NULL finds the missing row.