Restricting and Sorting Data practice questions

Worked examples for Restricting and Sorting Data

1. Question 1

   The `employees` table holds eight rows. Four have a commission (Grace 0.2, Carol 0.15, Bob 0.1, Eve 0.05) and four have `commission` set to NULL (Alice, Dave, Frank, Heidi); `emp_id` is unique. You must return **only the three highest commissions, largest first** — a row whose `commission` is NULL must never reach the result, and the sort must be fully deterministic. Which query does that?

   1. A. SELECT first_name, commission FROM employees ORDER BY commission DESC, emp_id FETCH FIRST 3 ROWS ONLY;

      Assumes DESC also pushes NULLs to the end. It does not: NULLS FIRST is the default for descending order, so the three rows fetched are the NULL-commission employees Alice, Dave and Frank — exactly the rows that had to be excluded.

   2. B. SELECT first_name, commission FROM employees ORDER BY commission DESC NULLS LAST, emp_id FETCH FIRST 3 ROWS ONLY;Correct answer

      Direction and null placement are specified independently, so DESC NULLS LAST puts the largest commission first and defers every NULL past the fetched rows; emp_id breaks any tie, making the result Grace 0.2, Carol 0.15, Bob 0.1.

   3. C. SELECT first_name, commission FROM employees ORDER BY commission NULLS LAST, emp_id FETCH FIRST 3 ROWS ONLY;

      Stating NULLS LAST does keep the NULLs out of the first three rows, but it does not change the direction: with no DESC the sort is ascending, so this returns the three *lowest* commissions (Eve 0.05, Bob 0.1, Carol 0.15) instead of the three highest.

   4. D. SELECT first_name, commission FROM employees ORDER BY 1 DESC NULLS LAST, emp_id FETCH FIRST 3 ROWS ONLY;

      Treats the integer as a reference to the intended sort column. A bare integer in ORDER BY is a one-based position in the *select list*, and position 1 there is first_name, so this sorts names descending and returns Heidi, Grace and Frank.

   Explanation

   In an ORDER BY item the sort direction and the null-placement default are two separate settings: ascending defaults to NULLS LAST while descending defaults to NULLS FIRST, so asking for the largest values first *and* NULLs at the end requires writing both DESC and NULLS LAST — each keyword alone gives one half of the requirement and the opposite default for the other. Because the row limit is applied after the sort, that null placement decides which rows survive FETCH FIRST, not merely how they are arranged. A bare integer in ORDER BY is a select-list position rather than a name, so it sorts whichever column happens to sit at that position, and a unique trailing key such as emp_id is what makes the ordering deterministic when the leading key ties.

2. Question 2

   What is the result of executing the following statement? ```sql SELECT first_name, salary FROM employees ORDER BY 3 DESC ```

   1. A. The statement fails with ORA-01785: ORDER BY item must be the number of a SELECT-list expression.Correct answer

      A positional ORDER BY item must be an integer between 1 and the number of expressions in the SELECT list. The list projects two expressions, so 3 is out of range and the statement is rejected at parse time with ORA-01785.

   2. B. The rows are returned sorted by LAST_NAME descending, because the positional item counts the columns of the EMPLOYEES table.

      Represents the misconception that a positional ORDER BY item counts the columns of the underlying table. Oracle resolves the integer against the SELECT list of the query only, and that list has just two expressions here, so no table column is consulted.

   3. C. The statement fails with ORA-00904: invalid identifier.

      Assumes an unresolvable ORDER BY item is always reported as a name-resolution failure. ORA-00904 is raised when ORDER BY names a column or alias that does not exist; an integer is resolved positionally, not as an identifier, so an out-of-range position raises the positional error instead.

   4. D. The statement succeeds and returns the rows in an unspecified order, because a number in ORDER BY is a constant expression with the same value for every row.

      Applies select-list intuition — where 3 would be a literal — to ORDER BY. A bare integer in ORDER BY is never treated as a constant; it is always a reference to a SELECT-list position, so it cannot silently degrade to a no-op sort.

   Explanation

   An integer in the ORDER BY clause is a positional reference to an expression in the query's SELECT list, not a numeric literal and not an offset into the base table's column list. The integer must fall between 1 and the number of expressions actually projected; anything larger has nothing to point at and the statement is rejected before any rows are produced. Sorting by a column that is not projected is still possible, but it requires naming that column rather than guessing a position.

3. Question 3

   A report must list the last name of every employee whose **last name is exactly five characters long and whose final character is a lowercase `n`**. A name that ends in `n` but is longer or shorter than five characters must not appear, and a name that merely contains an `n` somewhere must not appear. Which query returns exactly that set of rows?

   1. A. SELECT last_name FROM employees WHERE last_name LIKE '____n'Correct answer

      Each `_` matches exactly one character, so four underscores followed by a literal `n` match a string of exactly five characters whose fifth character is `n` — precisely the requested set (SQL Language Reference, LIKE condition).

   2. B. SELECT last_name FROM employees WHERE last_name LIKE '%n'

      Treats `%` as interchangeable with a run of `_` wildcards. `%` matches zero or more characters, so this anchors only the final `n` and imposes no length restriction — every name ending in `n` qualifies regardless of length, so shorter names are wrongly included.

   3. C. SELECT last_name FROM employees WHERE last_name LIKE '%n%'

      Assumes a pattern wrapped in `%` still anchors the `n` at the end. A trailing `%` matches zero or more characters after the `n`, so `'%n%'` is satisfied by any name that merely contains an `n` anywhere, including in the middle.

   4. D. SELECT last_name FROM employees WHERE last_name LIKE '____N'

      Assumes LIKE matching is case-insensitive. Pattern matching compares character by character using the session sort, which is case-sensitive by default, so an uppercase `N` in the pattern does not match a lowercase `n` in the data and no rows are returned.

   Explanation

   In a LIKE pattern, `_` matches exactly one character and `%` matches zero or more, so a fixed-length requirement must be expressed with one underscore per character position — using `%` anywhere in the pattern drops the length constraint entirely, and a trailing `%` also drops the end anchor. Pattern matching is case-sensitive under the default sort, so the literal characters in the pattern must match the data exactly in case.

4. Question 4

   The EMPLOYEES table holds these eight rows (COMMISSION shown, NULL where the employee earns no commission): ```text | FIRST_NAME | COMMISSION | |---|---| | Alice | NULL | | Bob | 0.10 | | Carol | 0.15 | | Dave | NULL | | Eve | 0.05 | | Frank | NULL | | Grace | 0.20 | | Heidi | NULL | ``` What value does the following query return? ```sql SELECT first_name FROM employees ORDER BY commission DESC, first_name FETCH FIRST 1 ROW ONLY ```

   1. A. Grace

      This assumes DESC means the largest commission (0.20, Grace) comes first. That would be true only if the NULLs were pushed to the end — the query would have to say ORDER BY commission DESC NULLS LAST for Grace to lead.

   2. B. AliceCorrect answer

      Correct. In Oracle the default null ordering for a DESC sort key is NULLS FIRST, so the four commission-less employees sort ahead of every non-NULL commission. The second sort key, FIRST_NAME, breaks the tie among them in ascending order, putting Alice first, and FETCH FIRST 1 ROW ONLY returns that row.

   3. C. Eve

      Eve has the smallest non-NULL commission (0.05), so she would lead only under an ascending sort of the non-NULL values, not under DESC.

   4. D. Heidi

      This treats the FIRST_NAME tie-breaker as inheriting DESC from the preceding sort key. ASC/DESC applies to each sort key separately, and FIRST_NAME has no DESC of its own, so the NULL-commission group is ordered A-to-Z (Alice, Dave, Frank, Heidi), not Z-to-A.

   Explanation

   ORDER BY builds a sort key list in which the direction (ASC/DESC) and the null placement (NULLS FIRST/NULLS LAST) are set per key. Oracle's defaults are NULLS LAST for an ascending key and NULLS FIRST for a descending key, so a DESC sort with no explicit NULLS clause reports the NULL rows before any data values. Each subsequent key also defaults to ASC independently of the keys before it, so a bare tie-breaker column sorts ascending even after a DESC key, and FETCH FIRST 1 ROW ONLY is applied after the whole ordering is established.

5. Question 5

   The employees in department 20 are: ``` LAST_NAME SALARY --------- ------ Chen 6000 Diaz 7500 Novak 4800 ``` What value does this query return? ```sql SELECT last_name, salary * 12 AS annual_pay FROM employees WHERE dept_id = 20 ORDER BY annual_pay FETCH FIRST 1 ROW ONLY ```

   1. A. Novak, 57600Correct answer

      ORDER BY may reference the select-list alias ANNUAL_PAY, and with no direction keyword the sort is ascending, so the smallest annual pay (4800 * 12 = 57600) is the single row fetched.

   2. B. Diaz, 90000

      Represents the misconception that ORDER BY defaults to descending for numeric sort keys. Oracle's default is ASC, so the largest ANNUAL_PAY is the last row, not the first.

   3. C. Chen, 72000

      Represents the misconception that ORDER BY cannot resolve a select-list alias and silently falls back to sorting on the first select-list column (LAST_NAME ascending). ORDER BY does resolve ANNUAL_PAY, so the sort is on the computed salary, not on the name.

   4. D. The statement fails with ORA-00904: "ANNUAL_PAY": invalid identifier

      Represents the misconception that a column alias is illegal everywhere outside the select list. An alias is indeed invalid in WHERE, but ORDER BY is applied after the select list is projected, so referencing ANNUAL_PAY there is legal.

   Explanation

   ORDER BY is evaluated after the select list is projected, so it can reference a column alias defined there — unlike WHERE, which is evaluated before aliases exist. When no direction keyword follows the sort key, Oracle sorts ascending, so a FETCH FIRST 1 ROW ONLY returns the smallest value of the aliased expression rather than the largest.

6. Question 6

   In the EMPLOYEES table, COMMISSION is NULL for Alice (EMP_ID 100), Dave (103), Frank (105) and Heidi (107). The employees who do earn a commission are Bob (101, 0.10), Carol (102, 0.15), Eve (104, 0.05) and Grace (106, 0.20). Which query returns exactly the three employees with the highest commission — Grace, then Carol, then Bob — and never lets a NULL-commission employee reach the top of the list?

   1. A. SELECT emp_id, first_name, commission AS bonus_rate FROM employees ORDER BY bonus_rate DESC, emp_id FETCH FIRST 3 ROWS ONLY

      Assumes NULLS LAST is the default in every direction. It is not: for a descending sort Oracle defaults to NULLS FIRST, so the three rows returned are the NULL-commission employees with the smallest EMP_IDs (Alice, Dave, Frank).

   2. B. SELECT emp_id, first_name, commission AS bonus_rate FROM employees ORDER BY bonus_rate DESC NULLS LAST, emp_id FETCH FIRST 3 ROWS ONLYCorrect answer

      DESC ranks the largest commission first and the explicit NULLS LAST overrides the descending default of NULLS FIRST, pushing the four NULL rows to the bottom. The select-list alias BONUS_RATE is a legal ORDER BY item, and EMP_ID makes the order fully deterministic, so the first three rows are Grace (0.20), Carol (0.15) and Bob (0.10).

   3. C. SELECT emp_id, first_name, commission AS bonus_rate FROM employees ORDER BY 3 NULLS LAST, emp_id FETCH FIRST 3 ROWS ONLY

      Treats NULLS LAST as if it also set the sort direction. Direction and null placement are independent; with no DESC the sort is ascending, so this returns the three smallest commissions (Eve, Bob, Carol).

   4. D. SELECT emp_id, first_name, commission AS bonus_rate FROM employees ORDER BY 1 DESC NULLS LAST, emp_id FETCH FIRST 3 ROWS ONLY

      Miscounts the positional reference: 1 names the first item of the select list, EMP_ID, not the commission. The rows come back ordered by descending EMP_ID (Heidi, Grace, Frank), and the NULLS LAST has no effect because EMP_ID is a primary key and never NULL.

   Explanation

   ORDER BY sorts ascending unless DESC is written, and Oracle's default null placement is tied to that direction: NULLS LAST for ascending order, NULLS FIRST for descending order. Ranking the largest values first while still pushing NULLs to the bottom therefore needs DESC together with an explicit NULLS LAST — the two are independent controls. An ORDER BY item may be a select-list alias or a positional number, where the number counts positions in the select list, not columns of the table; adding a unique second sort key makes the result deterministic before the row-limiting clause slices off the top rows.

7. Question 7

   A SQL*Plus session runs: ``` SQL> DEFINE lname = King SQL> SET VERIFY OFF SQL> SELECT emp_id, salary 2 FROM employees 3 WHERE last_name = &lname; ``` `EMPLOYEES.LAST_NAME` is `VARCHAR2(30)`, and exactly one employee has the last name `King` (emp_id 100, salary 9000). Because `VERIFY` is `OFF`, SQL*Plus echoes nothing. The statement below is that query with the substitution variable reference already resolved — it is the text the server receives. What happens when it runs? ```sql SELECT emp_id, salary FROM employees WHERE last_name = King ```

   1. A. The statement fails with ORA-00942.

      Expects the bare substituted word to be resolved in the object namespace, as a table or view name. A bare identifier appearing in an expression is resolved against the columns of the tables in the FROM clause, so the failure is an identifier error rather than an object-not-found error.

   2. B. The statement fails with ORA-00904.Correct answer

      DEFINE stores King as a CHAR value and substitution replaces &lname with that text exactly, adding no quotation marks. Oracle therefore parses King as a column reference, and EMPLOYEES has no such column, so the parser raises ORA-00904: "KING": invalid identifier. Writing the reference as '&lname' is what turns the value into a character literal.

   3. C. The statement fails with ORA-01722.

      Treats &lname as a bind variable that carries a typed value Oracle must convert, so a mismatch would surface as invalid number. Substitution is a client-side textual replacement performed before the statement is ever parsed, so the failure occurs at parse time, not at conversion time.

   4. D. The statement runs and returns one row: 100, 9000.

      Assumes SQL*Plus supplies the quotation marks a CHAR-typed variable needs when it substitutes. It never does — and quotes typed around the value in the DEFINE command are stripped when the value is stored — so the quoting must be written into the statement itself.

   Explanation

   A substitution variable is a client-side macro, not a bind variable: SQL*Plus replaces the reference with the stored text character for character before any SQL reaches the server, and it supplies no quotation marks of its own. A CHAR value used where a character literal is required must therefore be referenced inside single quotes in the statement text; without them the value is handed to the parser as a bare identifier, which Oracle resolves against the columns of the queried tables and rejects when no such column exists. This is why interactive scripts quote character and date variable references but leave numeric ones unquoted.

8. Question 8

   A developer wants the employees whose yearly pay exceeds 60000 and runs the following statement against the `employees` table. What is the result of executing it? ```sql SELECT last_name, salary * 12 AS annual_pay FROM employees WHERE annual_pay > 60000 ORDER BY annual_pay ```

   1. A. The statement executes successfully and returns the employees whose annual pay exceeds 60000, sorted by annual pay.

      Assumes a SELECT-list alias is visible everywhere in the statement. WHERE is evaluated before the SELECT list is projected, so the alias does not exist yet when the predicate is resolved; only ORDER BY, which runs last, can see it.

   2. B. The statement fails with ORA-00923: FROM keyword not found where expected.

      Assumes an arithmetic expression cannot carry an AS alias, making the SELECT list malformed. Aliasing a computed expression is legal, so the SELECT list parses cleanly and ORA-00923 is not raised.

   3. C. The statement fails with ORA-00979: not a GROUP BY expression.

      Confuses the WHERE restriction with aggregate/grouping rules. salary * 12 is a row-level expression, not an aggregate, and the query has no GROUP BY or aggregate function, so no grouping rule is violated.

   4. D. The statement fails with ORA-00904: "ANNUAL_PAY": invalid identifier.Correct answer

      A column alias is defined by the SELECT list, which is evaluated after WHERE, so ANNUAL_PAY is not a known identifier during predicate resolution and Oracle raises ORA-00904. Repeating the expression (WHERE salary * 12 > 60000) or wrapping the query in an inline view is required instead.

   Explanation

   Oracle resolves a query's clauses in a logical order in which the row source and the WHERE restriction are processed before the SELECT list produces its projected columns and their aliases. A column alias therefore does not exist as an identifier inside WHERE (or GROUP BY / HAVING) and referencing it there raises the invalid-identifier error, even though the very same alias is legal in ORDER BY because sorting happens after projection. To filter on a computed value, repeat the expression in the WHERE clause or filter over an inline view that already exposes the alias as a column.