Retrieving Data with SELECT practice questions

Worked examples for Retrieving Data with SELECT

1. Question 1

   The employees table contains the following rows (only the relevant columns are shown): | dept_id | manager_id | |---------|------------| | 10 | NULL | | 20 | 100 | | 20 | 100 | | 20 | 101 | | 30 | 106 | | 30 | 106 | | 30 | NULL | | 10 | 100 | How many rows does the following query return? ```sql SELECT DISTINCT dept_id, manager_id FROM employees; ```

   1. A. 8

      Assumes DISTINCT evaluates uniqueness over all columns in the underlying table row — including emp_id — rather than only the columns named in the select list. Because every employee has a unique emp_id, no row would be eliminated under that assumption. DISTINCT operates strictly on the projected columns (dept_id and manager_id here), not on the full underlying row.

   2. B. 3

      Counts only the unique dept_id values (10, 20, 30) as if DISTINCT scoped to the first selected column alone. DISTINCT is a qualifier on the entire select list, so both dept_id and manager_id are evaluated together when testing for duplicate rows.

   3. C. 5

      Treats any two rows sharing a NULL manager_id as a single duplicate regardless of their dept_id — as if NULL collapsed globally across the column. Two rows are duplicates under DISTINCT only when every selected column matches; (10, NULL) and (30, NULL) differ on dept_id, so they remain two distinct rows.

   4. D. 6Correct answer

      DISTINCT compares the combination of all selected column values. From the eight rows, (20, 100) appears for two employees and (30, 106) appears for two employees; removing one copy of each leaves six unique pairs: (10, NULL), (20, 100), (20, 101), (30, 106), (30, NULL), and (10, 100). Oracle treats two NULL values as equivalent for DISTINCT, but the two NULL-manager rows differ on dept_id so neither is eliminated.

   Explanation

   DISTINCT de-duplicates on the combination of every expression in the select list, not just the leftmost column. Two rows are considered duplicates only when all their projected column values match — including when both columns hold NULL, which Oracle treats as equivalent for this purpose. Among the eight employee rows, the pair (20, 100) appears twice and the pair (30, 106) appears twice; each duplicate is eliminated once, leaving six unique (dept_id, manager_id) combinations.

2. Question 2

   The following statement is run against the employees table. What is the result? ```sql SELECT salary AS pay, pay * 0.10 AS bonus FROM employees ```

   1. A. The statement succeeds, returning pay and bonus for every employee.

      Assumes an alias becomes usable immediately by later select-list items. Oracle does not expose a select-list alias to sibling expressions, so the reference fails rather than computing bonus.

   2. B. The statement fails with ORA-00904 ("PAY": invalid identifier).Correct answer

      A column alias defined in the select list is not visible to any other expression in the same select list, so the reference to pay in the next item is an unknown identifier and Oracle raises ORA-00904.

   3. C. The statement fails with ORA-00923 (FROM keyword not found where expected).

      ORA-00923 flags a malformed select/FROM boundary; here the FROM clause is well-formed. The failure is the unknown identifier pay, not a missing FROM keyword.

   4. D. The statement fails with ORA-00936 (missing expression).

      ORA-00936 signals a missing expression, such as an empty select list. Every item here has an expression, so this is not the error that occurs.

   Explanation

   A column alias names an output column but is not an in-scope identifier elsewhere in the same query block — neither in the WHERE clause nor in another select-list expression. Reusing such an alias within the select list therefore raises an invalid-identifier error rather than evaluating it; to reuse a computed value you repeat the expression or wrap the query in an inline view.

3. Question 3

   Which query returns a single column whose values combine each department's id and name in the form `20:Engineering` — the numeric id, a colon, then the name?

   1. A. SELECT dept_id || ':' || dept_name FROM departmentsCorrect answer

      || concatenates its operands into one string, and Oracle implicitly converts the NUMBER dept_id to characters, yielding one column of values such as 20:Engineering.

   2. B. SELECT dept_id + ':' + dept_name FROM departments

      + is arithmetic addition, not string concatenation (a SQL Server habit). Oracle tries to convert ':' to a number to add it and raises ORA-01722, so the query returns nothing.

   3. C. SELECT dept_id, ':', dept_name FROM departments

      Commas separate select-list items, so this returns three columns (the id, a literal ':' column, and the name) instead of one concatenated value.

   4. D. SELECT dept_id || ':' dept_name FROM departments

      Only the first || is present. dept_id || ':' is a single expression and the trailing dept_name is parsed as its column alias, so each value is just the id and colon (e.g. 20:) with the name never appended.

   Explanation

   The concatenation operator || joins character strings and implicitly converts a non-character operand such as a NUMBER to text, producing one combined column. + performs numeric addition rather than concatenation, a comma yields separate columns, and a bare column name written after an expression is read as an alias rather than another operand.

4. Question 4

   What is the result of executing the following statement against `hr-mini`? ```sql SELECT DISTINCT dept_id FROM employees ORDER BY salary; ```

   1. A. The statement runs, returning the distinct dept_id values ordered by salary

      This assumes ORDER BY can reference any base-table column even under DISTINCT; the DISTINCT restriction forbids ordering by a column that is not selected, so the statement does not run.

   2. B. ORA-00979

      ORA-00979 ('not a GROUP BY expression') is the GROUP BY analogue; this confuses the DISTINCT ordering rule with GROUP BY, but there is no GROUP BY clause here.

   3. C. ORA-01791Correct answer

      Under DISTINCT, every ORDER BY expression must appear in the select list; salary was projected away, so Oracle raises ORA-01791 (not a SELECTed expression).

   4. D. ORA-00904

      ORA-00904 flags an invalid identifier; salary is a real column of employees, so the name resolves — the failure is the DISTINCT/ORDER BY rule, not an unknown identifier.

   Explanation

   When a query uses DISTINCT (or its synonym UNIQUE), duplicate elimination happens before ordering, so every ORDER BY expression must also appear in the select list. Ordering by a column that was projected away is rejected with ORA-01791. This differs from an ordinary SELECT, where ORDER BY may reference any column of the underlying rows.

5. Question 5

   The `employees` table contains the following `dept_id` and `commission` values (one row per employee): | dept_id | commission | |---------|------------| | 10 | NULL | | 20 | 0.10 | | 20 | 0.15 | | 20 | NULL | | 30 | 0.05 | | 30 | NULL | | 30 | 0.20 | | 10 | NULL | How many rows does the following query return? ```sql SELECT DISTINCT dept_id, commission FROM employees; ``` ```sql SELECT DISTINCT dept_id, commission FROM employees ```

   1. A. 3

      Counts only the distinct dept_id values (10, 20, 30) as though DISTINCT applied to dept_id alone. DISTINCT de-duplicates on the full (dept_id, commission) pair, so the same dept_id can appear multiple times when it is paired with different commission values — dept_id 20 appears three times in the result, once for each distinct commission it has.

   2. B. 7Correct answer

      DISTINCT de-duplicates on the combination of all selected columns. The exhibit shows eight rows; the only repeated pair is (10, NULL), which appears for two employees. For deduplication purposes Oracle treats two NULLs in the same column position as equal, so those two rows collapse to one. The remaining seven (dept_id, commission) pairs are all unique, giving seven rows.

   3. C. 8

      Assumes NULLs are never treated as duplicates under DISTINCT — a confusion with the three-valued logic of WHERE predicates, where NULL = NULL evaluates to UNKNOWN and two NULL values never match each other. For DISTINCT deduplication Oracle applies different semantics: two NULLs in the same column position are considered equal, so the duplicate (10, NULL) pair collapses to one row rather than both being retained.

   4. D. 4

      Equals the count of employees whose commission is not NULL — a confusion between DISTINCT and a WHERE commission IS NOT NULL filter. DISTINCT does not discard rows where a selected column is NULL; it retains one representative row per unique (dept_id, commission) combination, including combinations where commission is NULL.

   Explanation

   DISTINCT de-duplicates on the combination of every column in the select list. When two rows share identical values in all selected positions — including NULL — Oracle treats them as the same row and retains only one. This NULL-equality rule for DISTINCT is the opposite of how NULLs behave in WHERE predicates, where NULL = NULL evaluates to UNKNOWN and two NULLs never match. The correct row count follows from identifying the one repeated (dept_id, commission) pair in the exhibit, recognising that Oracle's DISTINCT collapses it, and subtracting one from the eight-row total.

6. Question 6

   The `departments` table contains the following row: | dept_id | dept_name | location | |---------|-----------|----------| | 40 | Research | NULL | What does the following query return? ```sql SELECT dept_name || ' (' || location || ')' AS dept_info FROM departments WHERE dept_id = 40 ```

   1. A. The query returns no rows

      This conflates NULL in the SELECT list with NULL in a WHERE predicate. A NULL value produced by an expression in the SELECT clause does not filter the row out; only a WHERE predicate that evaluates to FALSE or NULL excludes rows. The predicate dept_id = 40 evaluates to TRUE, so the row is returned.

   2. B. NULL

      This reflects the misconception that NULL propagates through || the way it does through arithmetic operators (for example, 1 + NULL = NULL). Oracle's concatenation operator treats NULL as an empty string instead of propagating it, so the non-null operands are preserved in the result.

   3. C. Research ()Correct answer

      Oracle's || operator treats NULL as an empty string: 'Research' || ' (' yields 'Research (', then 'Research (' || NULL yields 'Research (' (NULL contributes nothing), then 'Research (' || ')' yields 'Research ()'. The NULL location leaves the parentheses empty but does not suppress the surrounding literals.

   4. D. Research (NULL)

      This conflates a SQL NULL value with the four-character string literal 'NULL'. Oracle does not render a null column as the text 'NULL' during concatenation; it treats it as an empty string, so nothing appears between the parentheses.

   Explanation

   Oracle's concatenation operator (||) treats NULL as an empty string rather than propagating NULL through the result, which differs from arithmetic operators where any NULL operand yields NULL. A NULL column value in a concatenated expression simply contributes nothing to the output string, leaving surrounding literals intact. This Oracle-specific behaviour also means that a NULL produced in the SELECT clause has no effect on row inclusion — only WHERE-clause predicates control which rows appear.

7. Question 7

   You want the `salary` column to appear in the output under the exact heading `Annual Salary` — two words, with a single space between them and mixed-case letters preserved. Which SELECT clause runs without raising an error and produces that heading?

   1. A. SELECT salary AS Annual Salary FROM employees

      Misconception: an unquoted alias may contain a space. An unquoted alias must be a single legal identifier, so after AS Annual the parser meets Salary where it expects a comma or FROM and raises ORA-00923.

   2. B. SELECT salary AS 'Annual Salary' FROM employees

      Misconception: an alias can be written as a single-quoted string. Single quotes delimit string literals, not identifiers; Oracle rejects a quoted-literal alias with ORA-00923 (use double quotes for an alias).

   3. C. SELECT salary AS `Annual Salary` FROM employees

      Misconception: back-ticks quote identifiers (a MySQL habit). The back-tick is not a legal character in Oracle SQL, so this raises ORA-00911 (invalid character) before any alias is formed.

   4. D. SELECT salary AS "Annual Salary" FROM employeesCorrect answer

      A column alias enclosed in double quotation marks may contain spaces and keeps its exact case, so the heading reads Annual Salary. This is the only form that both parses and yields the requested spaced, mixed-case heading.

   Explanation

   Oracle folds an unquoted column alias to upper case and forbids spaces and special characters in it; to preserve case or embed a space you must enclose the alias in double quotation marks. Single quotes mark string literals and back-ticks are not valid Oracle syntax, so both are parse errors rather than aliases.

8. Question 8

   Which query returns a result set with exactly two columns, where the first column shows each department's `location` and the second shows its `dept_name`?

   1. A. SELECT dept_name, location FROM departments

      Same two columns but written dept_name, location, so the output order is reversed. A result set's column order follows the select list, not the order the columns appear in the table definition.

   2. B. SELECT * FROM departments

      * projects every column of departments (dept_id, dept_name, location) — three columns, not the two the question asks for.

   3. C. SELECT location || dept_name FROM departments

      || concatenates the two values into one combined string, so this returns a single column rather than two separate ones.

   4. D. SELECT location, dept_name FROM departmentsCorrect answer

      The select list names location then dept_name, so the result has exactly those two columns in that left-to-right order — precisely what the request describes.

   Explanation

   A SELECT's projection lists the exact columns to return, and the result set has one column per select-list item in the order written. Selecting all columns with *, reversing the two names, or concatenating with || changes the shape or order rather than producing the two named columns in the requested sequence.