1Z0-082 practice question 5 of 25

Which two statements are true about the ORDER BY clause when used with a SQL statement containing a SET operator such as UNION? (Choose two.)

Full set: 142 questions
Answer options
A: Column positions must be used in the ORDER BY clause
B: Only column names from the first SELECT statement in the compound query are recognized Correct answer
C: The first column in the first SELECT of the compound query with the UNION operator is used by default to sort output in the absence of an ORDER BY clause Correct answer
D: Each SELECT statement in the compound query must have its own ORDER BY clause
E: Each SELECT statement in the compound query can have its own ORDER BY clause
Correct Answer:
B. Only column names from the first SELECT statement in the compound query are recognized; C. The first column in the first SELECT of the compound query with the UNION operator is used by default to sort output in the absence of an ORDER BY clause
Explanation

A compound query accepts exactly one ORDER BY clause placed at the very end. That clause may refer only to expressions appearing in the first SELECT list; therefore only those column names (or their positions/aliases) are recognised. When UNION (without ALL) is used and no ORDER BY is supplied, the duplicate-elimination sort returns the rows in ascending order beginning with the first select-list column, so B and C are true.

Why Incorrect

A. Numeric positions are permitted but not mandatory; names or aliases from the first SELECT may also be used.
D. Separate ORDER BY clauses per SELECT are illegal unless each SELECT is nested; at the top level only one is allowed.
E. Multiple ORDER BY clauses at the same query level are rejected.

References

1. Oracle Database SQL Language Reference 19c, “Set Operators” – ORDER BY appears only once, after the last SELECT.

2. Same section – UNION returns the result in sorted order by default.

3. Oracle Database SQL Language Reference 19c, Table 7-3 – ORDER BY can use column names, aliases, or positions from first query block.

Scroll to Top