Stepwise-Lab

Restricting and sorting: 1Z0-071 practice questions

Questions in the style of exam 1Z0-071 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;
  1. It fails: OFFSET must come before FETCH (ORA-00933: SQL command not properly ended).
  2. It fails: Oracle reads OFFSET as a column name it cannot find (ORA-00904: invalid identifier).
  3. It runs: it skips one row and returns the next two.
  4. 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. A clause after FETCH ... ONLY cannot be parsed, so 19c raises ORA-00933 and Oracle 26ai ORA-03049 (SQL keyword 'OFFSET' is not syntactically valid). 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;
  1. Banana
  2. apple
  3. cherry
  4. 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. A developer wants the employees of departments 20 and 30 and writes this query. What happens on Oracle Database 19c?

SELECT e.last_name
FROM   employees e
WHERE  e.department_id = 20 OR 30;
  1. It fails with ORA-00920: invalid relational operator.
  2. It returns the employees of departments 20 and 30.
  3. It returns every employee, because 30 counts as TRUE.
  4. It fails with ORA-01722: invalid number.
Show the answer

Answer: A

Each side of OR must be a complete condition, and 30 on its own is not one, so 19c stops with ORA-00920. Oracle does not repeat the column for you: write e.department_id = 20 OR e.department_id = 30, or e.department_id IN (20, 30). Reading 30 as TRUE is what Oracle 26ai does, because it has a BOOLEAN type; there the query silently returns all 9 rows. There is no text to convert, so ORA-01722 does not apply.

4. 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;
  1. De Haan, Ernst, Grant
  2. De Haan, Ernst, Grant, Hunold
  3. Ernst, Grant
  4. 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.