Using Set Operators practice questions

From Oracle AI Database SQL (1Z0-171) (1Z0-171) · 22 questions on this topic

Using Set Operators practice questions from Oracle AI Database SQL (1Z0-171) (1Z0-171). This pack has 22 questions tagged Using Set Operators, drawn from its timed mock exams. 8 of them are worked through in full below — the question, every option, why each is right or wrong, and the explanation.

Worked examples for Using Set Operators

  1. Question 1

    What is the result of executing the following statement?

    SELECT last_name, salary
FROM   employees
WHERE  dept_id = 10
ORDER  BY salary DESC
UNION
SELECT last_name, salary
FROM   employees
WHERE  dept_id = 30

    1. A. It executes successfully: the department 10 rows come back first, sorted by SALARY descending, and the department 30 rows are appended after them in their own order.

      This is the "ORDER BY sorts just its own branch" misconception. A component query of a compound query is not an independently ordered result set that then gets concatenated; the set operator combines unordered row sources, so a per-branch ORDER BY has no meaning and Oracle rejects the statement at parse time instead of running it.

    2. B. ORA-03048: SQL reserved word ambiguously used in a compound queryCorrect answer

      Correct. In a compound query the ORDER BY clause is legal only after the final component query. Here ORDER BY appears on the first branch and is immediately followed by the UNION keyword, so the parser cannot tell whether the clause belongs to the branch or to the compound query and raises ORA-03048 — the compound-query-specific diagnostic for a reserved word used in an ambiguous position.

    3. C. It executes successfully: Oracle applies the ORDER BY to the whole UNION result, so every row comes back sorted by SALARY descending.

      This assumes Oracle silently relocates a misplaced ORDER BY to the end of the compound query. It does not. Placement is part of the syntax: to sort the combined result the clause must be written after the last component query, and until it is the statement does not parse at all.

    4. D. ORA-00933: SQL command not properly ended

      ORA-00933 is the generic "unexpected text after a complete statement" error, raised for things like trailing tokens after a single SELECT. Oracle diagnoses a set operator following a branch-level ORDER BY with the more specific compound-query error instead, so this is not the code returned here.

    Explanation

    A compound query built with UNION, UNION ALL, INTERSECT, or MINUS accepts a single ORDER BY clause, and it must follow the final component query, where it sorts the entire combined result. Writing ORDER BY on an earlier branch and then continuing with a set operator is a parse error, because the parser cannot attach the clause unambiguously. Oracle neither sorts that branch in isolation nor moves the clause to the end for you, so the statement never executes.

  2. Question 2

    Given the data below, how many rows does the following statement return?

    EMPLOYEES                        DEPARTMENTS
EMP_ID  COMMISSION  DEPT_ID      DEPT_ID
100     (null)      10           10
101     0.10        20           20
102     0.15        20           30
103     (null)      20           40
104     0.05        30
105     (null)      30
106     0.20        30
107     (null)      10
    SELECT dept_id FROM employees WHERE commission IS NULL
UNION
SELECT dept_id FROM departments

    1. A. 8

      Counts UNION as if it were UNION ALL — 4 rows from the first query plus 4 from the second. UNION removes duplicate rows from the combined result; only UNION ALL returns every row including duplicates.

    2. B. 4Correct answer

      The first query yields dept ids 10, 20, 30, 10 and the second yields 10, 20, 30, 40; UNION combines them and removes duplicates from the whole result, leaving the distinct set 10, 20, 30, 40 — four rows.

    3. C. 7

      Applies duplicate elimination inside each query separately (3 distinct ids + 4 distinct ids) and then concatenates. UNION's DISTINCT is applied to the combined result of both queries, not independently to each branch.

    4. D. 3

      Treats UNION as returning only the rows common to both queries, which is INTERSECT's behaviour. UNION returns every distinct row from either query, not just the shared ones.

    Explanation

    UNION concatenates the results of both queries and then eliminates duplicate rows across the entire combined result, so the row count is the number of distinct values in the union of the two sets — never the sum of the two inputs. UNION ALL is the operator that keeps every row from both queries, duplicates included, and INTERSECT is the one that narrows the result to rows common to both. Because duplicate removal happens once over the merged result rather than per branch, ids repeated within a single query and ids repeated across the two queries collapse alike.

  3. Question 3

    The employees table contains exactly the rows below:

    EMP_ID  SALARY  COMMISSION  DEPT_ID
100     9000    (null)      10
101     6000    0.10        20
102     7500    0.15        20
103     4800    (null)      20
104     5200    0.05        30
105     3900    (null)      30
106     8100    0.20        30
107     6700    (null)      10

    How many rows does the following statement return?

    SELECT dept_id FROM employees WHERE salary >= 6000
UNION ALL
SELECT dept_id FROM employees WHERE commission IS NULL

    1. A. 3

      Applies UNION's duplicate elimination to UNION ALL, collapsing the combined result to the distinct dept_id values 10, 20 and 30. UNION ALL performs no de-duplication at all.

    2. B. 4

      Reads the ALL keyword as "rows satisfying all of the queries", i.e. treats the statement as a multiset intersection of the two branches. UNION ALL is a concatenation, not an intersection; ALL modifies duplicate handling, not membership.

    3. C. 5

      Assumes UNION ALL only suppresses values of the second query that already appeared in the first (keeping duplicates within a branch). Every dept_id from the second branch already occurs in the first, so this reasoning drops all four of its rows; UNION ALL in fact compares nothing between branches.

    4. D. 9Correct answer

      UNION ALL returns all rows selected by either query, including every duplicate. The first branch (salary >= 6000: employees 100, 101, 102, 106, 107) returns 5 rows and the second (commission IS NULL: employees 100, 103, 105, 107) returns 4 rows, so the statement returns 5 + 4 = 9 rows.

    Explanation

    UNION ALL is the only one of the four set operators that does no duplicate elimination and no comparison between the two result sets: it simply concatenates them, so its row count is always the sum of the two branch row counts, no matter how much the branches overlap. Rows selected by both branches — here the employees who both earn at least 6000 and have a NULL commission — therefore appear twice. Switching to UNION would collapse the result to the distinct dept_id values instead.

  4. Question 4

    The employees table has columns first_name VARCHAR2(30), last_name VARCHAR2(30), salary NUMBER(8,2) and dept_id NUMBER(4).

    What is the result of executing the following statement?

    SELECT first_name AS name, salary
FROM   employees
WHERE  dept_id = 20
UNION ALL
SELECT last_name AS surname, salary
FROM   employees
WHERE  dept_id = 30
ORDER  BY surname

    1. A. It executes successfully and returns the rows of both branches sorted by the second branch's surname values.

      Represents the belief that any alias defined in any component query is visible to the compound query's ORDER BY. Aliases from the second and later branches are not part of the compound result's column names, so the statement fails before returning a row.

    2. B. It fails with ORA-00904: "SURNAME": invalid identifier.Correct answer

      The compound result takes its column names from the first component query, so its columns are NAME and SALARY. SURNAME, defined only in the second branch, is not a name the outermost ORDER BY can resolve, so the statement fails with ORA-00904.

    3. C. It fails with ORA-01789: query block has incorrect number of result columns.

      Represents the belief that branches must expose the same column names, not merely the same shape. ORA-01789 is raised only when the branches select a different number of expressions; here both select exactly two, so the column-count rule is satisfied.

    4. D. It fails with ORA-01785: ORDER BY item must be the number of a SELECT-list expression.

      Represents the belief that a compound query's ORDER BY accepts only positional numbers. Oracle accepts a column name or alias there as well — provided it comes from the first component query — so the failure is name resolution, not a positional-only restriction.

    Explanation

    The single ORDER BY of a compound query is correctly placed after the last component query, but placement alone is not enough: it is resolved against the compound result, whose column names and aliases come from the first component query only. Aliases introduced in the second or any later branch never surface as result column names, so referencing one is an invalid identifier rather than an ordering instruction. Sorting by that branch's expression requires either aliasing it in the first branch as well, or ordering by the positional number of the select-list item.

  5. Question 5

    The DEPARTMENTS table is defined as (dept_id NUMBER(4), dept_name VARCHAR2(30), location VARCHAR2(30)) and the EMPLOYEES table as (emp_id NUMBER(6), first_name VARCHAR2(30), last_name VARCHAR2(30), salary NUMBER(8,2), commission NUMBER(4,2), manager_id NUMBER(6), dept_id NUMBER(4), hire_date DATE).

    Which outcome results from executing the following statement?

    SELECT dept_id, dept_name
FROM   departments
UNION ALL
SELECT emp_id, hire_date
FROM   employees
ORDER  BY 1

    1. A. ORA-00933

      Represents the belief that a compound query cannot carry an ORDER BY after UNION ALL, or that the clause must sit inside the final query block. A single ORDER BY after the last branch is exactly where the rule requires it, so ORA-00933 (SQL command not properly ended) is not raised here.

    2. B. ORA-01789

      Represents the belief that mismatched column names or datatypes are reported as a result-column-count problem. ORA-01789 (query block has incorrect number of result columns) fires only when the branches project different numbers of columns; both branches here project exactly two.

    3. C. The statement executes successfully and returns 12 rows

      Represents the belief that UNION ALL, because it does no duplicate elimination, tolerates mismatched datatypes and implicitly converts the DATE to a character value. Datatype matching is required by every set operator, ALL included, so the statement never runs.

    4. D. ORA-01790Correct answer

      Corresponding expressions in the branches of a set operation must have the same datatype. Position 2 pairs dept_name (VARCHAR2) with hire_date (DATE), which Oracle will not implicitly convert across a set operator, so the statement fails with ORA-01790: expression must have same datatype as corresponding expression.

    Explanation

    Every set operator matches the branches column by ordinal position, and each pair of corresponding expressions must be of the same datatype; skipping duplicate elimination with ALL does not relax that requirement, and Oracle does not implicitly convert a DATE to a character value to make the branches compatible. Pairing a VARCHAR2 column with a DATE column in the second position therefore fails at parse time, before ordering is ever considered — the trailing ORDER BY on the last branch is itself legal placement.

  6. Question 6

    Consider only these EMPLOYEES rows (the two departments referenced by the query):

    EMP_IDDEPT_IDMANAGER_ID
    10010(null)
    10710100
    10430106
    10530106
    10630(null)

    How many rows does the following statement return?

    SELECT manager_id FROM employees WHERE dept_id = 10
UNION
SELECT manager_id FROM employees WHERE dept_id = 30

    1. A. 2

      Assumes set operators discard rows whose value is NULL, the way an equality predicate filters them out, leaving only 100 and 106. UNION is not a WHERE clause: a NULL is a legitimate value in the result set and is returned as one row.

    2. B. 3Correct answer

      The combined values are NULL, 100, 106, 106, NULL. UNION eliminates duplicates, and in duplicate-elimination and sorting operations Oracle treats two NULLs as equal, so the two NULL rows collapse into one, as do the two 106 rows: NULL, 100, 106.

    3. C. 4

      Applies the comparison rule NULL = NULL is UNKNOWN to duplicate elimination and therefore keeps both NULL rows as distinct. Duplicate elimination is not a comparison predicate — for it, and for sorting and grouping, Oracle treats nulls as equal to each other.

    4. D. 5

      Counts every row produced by both component queries, which is what UNION ALL would return. UNION performs duplicate elimination over the combined result, so the repeated 106 and the repeated NULL are each reduced to a single row.

    Explanation

    UNION returns the distinct rows selected by either component query, so duplicate elimination is applied across the combined result rather than within each branch. Duplicate elimination follows the same null semantics as sorting and grouping, where Oracle treats two nulls as equal to each other — the opposite of the three-valued comparison rule that makes NULL = NULL unknown. Repeated non-null values and repeated nulls therefore each collapse to one row, while UNION ALL would have kept every row.

  7. Question 7

    The EMPLOYEES table has columns EMP_ID, FIRST_NAME, LAST_NAME, SALARY, COMMISSION, MANAGER_ID, DEPT_ID and HIRE_DATE. Both query blocks below select the same two columns, in the same order, with the same datatypes, and neither block is parenthesized.

    What is the result of executing this statement?

    SELECT first_name, salary
FROM   employees
WHERE  dept_id = 20
ORDER  BY salary DESC
UNION
SELECT first_name, salary
FROM   employees
WHERE  commission IS NOT NULL

    1. A. ORA-01789

      ORA-01789 ('query block has incorrect number of result columns') is raised only when the branches of a set operation disagree on column count. Both branches here select exactly two columns, FIRST_NAME and SALARY, so the SELECT lists match and this error cannot occur.

    2. B. The statement executes and the ORDER BY on the first query block is silently ignored, so all rows are returned in unspecified order

      Oracle never silently discards a branch-level ORDER BY in a set operation. The restriction is enforced by the parser, so the statement fails before any branch is evaluated and no rows are returned at all.

    3. C. The statement executes: the DEPT_ID = 20 rows come back sorted by SALARY descending, followed by the COMMISSION IS NOT NULL rows in unspecified order

      This is the per-branch-ordering misconception. A compound query is one statement producing one result set; there is no notion of ordering one branch independently and concatenating it ahead of another. Oracle rejects the statement at parse time rather than producing this layout.

    4. D. ORA-03048Correct answer

      An unparenthesized query block that carries ORDER BY cannot be an operand of a set operator, so the parser raises ORA-03048 ('SET operator not allowed on the query block containing ORDER BY ...'). Moving the single ORDER BY to the end of the whole statement — or parenthesizing the branch — makes it legal.

    Explanation

    A compound query built with UNION, UNION ALL, INTERSECT or MINUS is a single statement, so it accepts at most one ORDER BY clause and that clause must follow the last query block, where it orders the combined result. Attaching ORDER BY to a bare (unparenthesized) branch makes that branch illegal as an operand of the set operator, and Oracle rejects the statement at parse time — no branch is evaluated and no rows come back. To sort the whole result, put one ORDER BY at the very end and reference the first query block's column names or positional column numbers; to sort inside a branch you must parenthesize it, and that inner ordering still does not survive into the combined result.

  8. Question 8

    In the schema, departments.dept_id is NUMBER(4) and departments.dept_name is VARCHAR2(30); employees.emp_id is NUMBER(6) and employees.salary is NUMBER(8,2). What is the result of executing the following statement?

    SELECT dept_id, dept_name FROM departments
UNION
SELECT emp_id, salary FROM employees

    1. A. ORA-01789: query block has incorrect number of result columns

      Assumes the only compatibility rule for a compound query is the column count. Both query blocks select two expressions, so the count rule is satisfied and this error is not raised; the failure comes from the datatype rule instead.

    2. B. ORA-01790: expression must have same datatype as corresponding expressionCorrect answer

      Corresponding expressions in the component queries of a compound query must be of the same datatype group. The second expressions pair a character column (dept_name) with a numeric column (salary), which Oracle rejects at parse time with ORA-01790.

    3. C. ORA-01722: invalid number

      Assumes Oracle implicitly converts the character values to numbers and then fails on data that does not look numeric. Set operators do not apply implicit character-to-number conversion across branches; the mismatch is rejected before any row is fetched.

    4. D. ORA-00904: invalid identifier

      Assumes the branches of a compound query must select identically named columns. Column names need not match — the result set simply takes its names from the first query — so no identifier error occurs.

    Explanation

    A compound query built with UNION, UNION ALL, INTERSECT, or MINUS requires each component query to select the same number of expressions, and each pair of corresponding expressions must be in the same datatype group. Column names are irrelevant: the result set inherits the names of the first query. Pairing a VARCHAR2 column with a NUMBER column violates the datatype rule, and Oracle raises the error while parsing rather than attempting any implicit conversion.

Practice all 22 Using Set Operators questions

Oracle AI Database SQL (1Z0-171) has the full set, inside timed mock exams that mirror real exam conditions — every question with a worked explanation.

Drill 20 questions on Using Set Operators →Open Oracle AI Database SQL (1Z0-171) →

Other topics in this pack