Restricting and Sorting Data practice questions

Worked examples for Restricting and Sorting Data

1. Question 1

   Consider the following statement executed against the `employees` table. What is the result? ```sql SELECT first_name, salary FROM employees ORDER BY 3; ```

   1. A. ORA-01785Correct answer

      A bare integer in ORDER BY is a 1-based reference to a SELECT-list column by its position. The SELECT list has only two expressions (first_name, salary), so position 3 is out of range and Oracle raises ORA-01785: 'ORDER BY item must be the number of a SELECT-list expression'.

   2. B. ORA-00904

      ORA-00904 (invalid identifier) is raised for an unknown column NAME. A bare integer in ORDER BY is parsed as a SELECT-list position, not as an identifier lookup, so the invalid-identifier error does not apply.

   3. C. ORA-00936

      ORA-00936 (missing expression) flags a syntactically incomplete clause. ORDER BY 3 is complete and parses fine; the failure is a semantic out-of-range position, not a missing expression.

   4. D. The statement runs successfully; ORDER BY 3 sorts by the constant value 3, leaving the rows in their inserted order.

      An unqualified integer in ORDER BY is ALWAYS a SELECT-list position reference, never a constant expression. It cannot silently sort by the literal 3, so an out-of-range position is rejected rather than ignored.

   Explanation

   In an ORDER BY clause an unqualified integer is interpreted as the ordinal position of a column in the SELECT list, not as a literal value. When that position exceeds the number of selected expressions, Oracle cannot map it to a column and rejects the statement. Because the select list here contains only two items, position 3 is invalid.

2. Question 2

   What value does the following query return? ```sql SELECT COUNT(*) FROM employees WHERE manager_id NOT IN (100, 101); ```

   1. A. 2Correct answer

      NOT IN (100, 101) expands to manager_id != 100 AND manager_id != 101. For Alice and Grace, whose manager_id is NULL, each comparison yields UNKNOWN, making the combined condition UNKNOWN and excluding those rows. Eve and Frank both have manager_id = 106, which is neither 100 nor 101, so both pass. No other employees qualify, giving COUNT(*) = 2.

   2. B. 4

      A candidate who treats NULL manager_id as 'not equal to 100 or 101 — therefore included' arrives at four by adding Alice and Grace to Eve and Frank. This mistakes NULL for a value that satisfies inequality comparisons; in Oracle, any comparison involving NULL yields UNKNOWN, and the row is excluded.

   3. C. 0

      Zero results occur only when a NULL appears inside the IN list itself — for example NOT IN (100, 101, NULL) — because every row then contains a comparison against NULL, always yielding UNKNOWN and suppressing all output. Here, NULLs reside in the column (manager_id), not the list; only the column-NULL rows are individually suppressed, not all rows.

   4. D. 8

      Eight is the total number of employees with no filtering applied. A candidate who ignores the NOT IN predicate and counts every row in the table arrives at this figure.

   Explanation

   NOT IN (a, b) is syntactic shorthand for column != a AND column != b. Whenever the column value is NULL, each != comparison yields UNKNOWN, and UNKNOWN AND UNKNOWN remains UNKNOWN — so NULL-valued rows are silently excluded, exactly as they are by any WHERE condition that does not explicitly test for NULL. This behaviour differs from placing NULL inside the IN list: NOT IN (..., NULL) produces UNKNOWN for every row regardless of the column value, suppressing the entire result. Knowing which side of IN holds the NULLs is essential to correctly predicting the output.

3. Question 3

   How many rows does the following query return? ```sql SELECT last_name FROM employees WHERE dept_id NOT IN (10, 40, NULL); ```

   1. A. 0Correct answer

      NOT IN (10, 40, NULL) expands to dept_id <> 10 AND dept_id <> 40 AND dept_id <> NULL. The final comparison is UNKNOWN for every row, and TRUE AND TRUE AND UNKNOWN evaluates to UNKNOWN, never TRUE, so no row qualifies regardless of the data.

   2. B. 6

      Counts the rows whose dept_id is neither 10 nor 40 (departments 20 and 30) by silently discarding the NULL list member. NOT IN cannot ignore a NULL; the NULL drags the whole predicate to UNKNOWN.

   3. C. 2

      Reads NOT IN as IN and counts the department-10 employees instead. The sense of the negation is inverted.

   4. D. The statement raises an ORA- error because NULL is not permitted inside an IN list

      A NULL inside an IN or NOT IN list is legal syntax; it changes the predicate's truth value to UNKNOWN rather than raising an error.

   Explanation

   A NULL member inside a NOT IN list makes the whole predicate impossible to satisfy: NOT IN is a chain of inequalities joined by AND, and comparing any value to NULL yields UNKNOWN, so the condition can never become TRUE and the query returns no rows for any data. A positive IN with a NULL member behaves differently, because there the NULL only contributes an UNKNOWN into an OR chain, which can still be TRUE for other members.

4. Question 4

   Which query returns the first names of all employees who do **not** earn a commission of 0.10, where the result must ALSO include every employee who receives no commission at all (a NULL commission)?

   1. A. SELECT first_name FROM employees WHERE commission <> 0.10 OR commission IS NULL;Correct answer

      commission <> 0.10 keeps every non-NULL value other than 0.10, and the added OR commission IS NULL brings back the rows the inequality silently dropped. Together they return everyone except the single employee whose commission is exactly 0.10.

   2. B. SELECT first_name FROM employees WHERE commission <> 0.10;

      For a NULL commission, commission <> 0.10 evaluates to UNKNOWN, not TRUE, so those rows are excluded. This misconception assumes <> handles NULL like an ordinary value; it returns only the non-NULL commissions that differ from 0.10.

   3. C. SELECT first_name FROM employees WHERE commission <> 0.10 OR commission IS NOT NULL;

      IS NOT NULL is the wrong NULL test here: it re-admits the 0.10 earner (whose commission is non-NULL) while still dropping the actual NULL rows. This confuses IS NOT NULL with IS NULL.

   4. D. SELECT first_name FROM employees WHERE commission IS NULL;

      This returns only the no-commission rows and forgets the non-NULL employees whose commission simply differs from 0.10. It answers half the requirement, keeping the NULLs but dropping the <> 0.10 group.

   Explanation

   A direct comparison such as commission <> 0.10 evaluates to UNKNOWN whenever commission is NULL, so those rows are silently filtered out rather than returned. To include NULL rows you must add an explicit OR commission IS NULL, because only the IS NULL / IS NOT NULL predicates can test for NULL. Using IS NOT NULL instead, or testing IS NULL alone, each captures the wrong half of the intended result.

5. Question 5

   How many rows does the following query return? ```sql SELECT first_name FROM employees WHERE hire_date BETWEEN DATE '2019-01-01' AND DATE '2020-12-31'; ```

   1. A. 2

      Counts only the two employees hired during calendar year 2019 (Bob on 2019-03-01 and Carol on 2019-07-22), implicitly treating the upper bound as exclusive of year 2020. Oracle's BETWEEN is fully inclusive, so Dave (2020-11-05) must also be counted.

   2. B. 3Correct answer

      BETWEEN DATE '2019-01-01' AND DATE '2020-12-31' is inclusive at both boundaries. Bob (2019-03-01), Carol (2019-07-22), and Dave (2020-11-05) all fall within the range. Alice (2018-01-15) and Grace (2017-06-12) precede the lower bound; Eve, Frank, and Heidi follow the upper bound.

   3. C. 4

      Adds a fourth employee — most likely Eve (2021-02-18) by misreading the upper date as 2021, or Alice (2018-01-15) by misreading the lower date as 2018. Neither boundary supports including a fourth row.

   4. D. 5

      Overcounts by treating the predicate as open-ended or misapplying both boundaries, sweeping in employees from outside the 2019-2020 window on both ends.

   Explanation

   Oracle's BETWEEN condition is always inclusive: a value exactly equal to either boundary satisfies the predicate, equivalent to hire_date >= DATE '2019-01-01' AND hire_date <= DATE '2020-12-31'. Three employees were hired within that window — in March 2019, July 2019, and November 2020 — and all five remaining employees fall outside it, either before 2019 or after 2020.

6. Question 6

   Which query returns the first names of all employees who earn a salary of at least 6700?

   1. A. SELECT first_name FROM employees WHERE salary > 6700;

      The strict '>' excludes an employee earning exactly 6700, dropping the boundary value that 'at least' is meant to include.

   2. B. SELECT first_name FROM employees WHERE salary <= 6700;

      Reverses the comparison and returns the lower earners (6700 or below) instead of those at or above the threshold.

   3. C. SELECT first_name FROM employees WHERE salary >= 6700;Correct answer

      >= keeps every salary greater than or equal to 6700, including an employee earning exactly 6700, which is what 'at least 6700' means.

   4. D. SELECT first_name FROM employees WHERE salary = 6700;

      Tests only for the exact salary 6700 rather than a range, returning a single employee instead of everyone at or above the threshold.

   Explanation

   'At least 6700' is an inclusive lower bound, which the >= operator expresses: it is TRUE when the salary is greater than or equal to the threshold. The strict > drops the boundary value, <= selects the opposite end of the range, and = matches only the exact figure.

7. Question 7

   The `departments` table contains the following rows: | dept_id | dept_name | location | |--------:|:---------------|:---------| | 10 | Administration | New York | | 20 | Engineering | San Jose | | 30 | Sales | Chicago | | 40 | Research | (null) | What value does the following query return? ```sql SELECT location FROM departments ORDER BY location DESC FETCH FIRST 1 ROW ONLY ```

   1. A. San Jose

      This is the greatest non-null location under a descending sort. It is chosen if one assumes DESC places NULLs LAST, pushing the Research row to the bottom instead of the top.

   2. B. Chicago

      This is the smallest location value and would come first only under ascending order. It ignores the explicit DESC keyword, reading the sort as ascending.

   3. C. NULLCorrect answer

      For a descending sort, Oracle's default null placement is NULLS FIRST, so the row whose location is NULL (Research) is ordered ahead of every real value and is returned first; its location is NULL.

   4. D. New York

      This is the location of the first-inserted department (Administration). It is chosen if one assumes a NULL in the sort column disables ordering and rows return in natural insertion order.

   Explanation

   When an ORDER BY clause does not specify NULLS FIRST or NULLS LAST, Oracle applies a default that depends on direction: NULLS LAST for ascending and NULLS FIRST for descending. Sorting location in descending order therefore places the NULL location ahead of every real value, so the first row's location is NULL. The non-null values would only surface first under a different direction or null-placement assumption.

8. Question 8

   A developer wants to list every employee's `first_name` and annual salary—computed as `salary * 12` and labeled `ann_sal`—sorted from highest to lowest annual salary. Which query accomplishes this **without** raising a runtime error?

   1. A. SELECT first_name, salary * 12 AS ann_sal FROM employees WHERE ann_sal > 0 ORDER BY ann_sal DESC

      Incorrect. The WHERE clause is evaluated before the SELECT list, so the alias `ann_sal` does not yet exist at that point. Oracle raises ORA-00904: 'ANN_SAL': invalid identifier.

   2. B. SELECT first_name, salary * 12 AS ann_sal FROM employees ORDER BY ann_sal DESCCorrect answer

      Correct. ORDER BY is the last clause Oracle evaluates, so SELECT-list aliases are fully defined by the time it executes. Referencing `ann_sal` in ORDER BY is valid and produces the required descending sort without any runtime error.

   3. C. SELECT first_name, salary * 12 AS ann_sal FROM employees HAVING ann_sal > 0 ORDER BY ann_sal DESC

      Incorrect. HAVING is evaluated before the SELECT list, so `ann_sal` is not yet defined and Oracle raises ORA-00904. Even if the alias were somehow resolved, using a non-aggregate expression in HAVING without a corresponding GROUP BY raises ORA-00937 (not a single-group group function).

   4. D. SELECT first_name, salary * 12 AS ann_sal FROM employees GROUP BY ann_sal ORDER BY ann_sal DESC

      Incorrect. GROUP BY is evaluated before the SELECT list, so `ann_sal` is not yet defined and Oracle raises ORA-00904. Additionally, `first_name` appears in the SELECT list but is absent from the GROUP BY clause, which would raise ORA-00979 (not a GROUP BY expression) even if the alias were accepted.

   Explanation

   Oracle processes query clauses in a fixed logical order—FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. A column alias is assigned during the SELECT phase, so every clause evaluated before SELECT cannot reference that alias and raises ORA-00904. ORDER BY is evaluated last and may freely reference SELECT-list aliases; adding DESC produces the required highest-to-lowest ordering.