1Z0-071 practice question 9 of 35

Which statement will execute successfully?

Full set: 326 questions
Answer options
A: SELECT 1, 2 FROM DUAL UNION SELECT 3, 4 FROM DUAL ORDER BY 1, 2; Correct answer
B: SELECT 3 FROM DUAL UNION SELECT 4 FROM DUAL ORDER BY 3 ;
C: SELECT 1, 2 FROM DUAL UNION SELECT 3, 4 FROM DUAL ORDER BY 3, 4;
D: SELECT 1 FROM DUAL UNION SELECT 2 FROM DUAL ORDER BY 1, 2;
Correct Answer:
A. SELECT 1, 2 FROM DUAL UNION SELECT 3, 4 FROM DUAL ORDER BY 1, 2;
Explanation

When using set operators like UNION, the ORDER BY clause can only appear once at the end of the entire statement. It can reference columns by their position in the SELECT list. Statement A has two columns in its SELECT list and correctly uses ORDER BY 1, 2 to sort by the first and then the second column. This is valid syntax and will execute successfully. The other options refer to column positions that do not exist in their respective SELECT lists, which will cause an ORA-01785 error.

Why Incorrect

B. The SELECT list has only one column, but the ORDER BY clause refers to position 3, which does not exist.
C. The SELECT list has two columns, but the ORDER BY clause refers to positions 3 and 4, which do not exist.
D. The SELECT list has one column, but the ORDER BY clause refers to position 2, which does not exist.

References

Oracle Database SQL Language Reference, 19c, "Set Operators". This section states: "The orderbyclause can appear only once at the end of the compound query."

Oracle Database SQL Language Reference, 19c, "ORDER BY Clause". The documentation specifies: "If you specify position, then it must be an integer from 1 to the number of expressions in the select list." This rule is violated by options B, C, and D.

Scroll to Top