View the Exhibit and examine the data in the DEPARTMENTS tables. Evaluate the following SQL statement: SELECT department_id "DEPT_ID", department_name , 'b' FROM departments WHERE department_id=90…

Marisol N.Jun 5, 2026

The correct answers are A and C. In a UNION query, Oracle requires the ORDER BY clause to appear only once, at the very end, and it must reference columns by their position number or by an alias defined in the first SELECT. Option A uses ORDER BY 3, which is the positional reference to the third column in the result set, the literal 'b', and that is perfectly valid. Option C uses ORDER BY DEPT_ID, which is the column alias assigned in the first SELECT statement, and Oracle permits that alias to be used for sorting the entire UNION result. Option B fails because 'b' in single quotes is a string literal, not a column reference or a position number, so Oracle will throw an error rather than sort by it. Option D fails on two counts, the unquoted space in DEPT NAME makes it syntactically invalid, and even if you fixed the name to DEPT_NAME, that alias belongs to the second SELECT, not the first, so Oracle would not recognize it in the ORDER BY clause. The exam topic this maps to is "Grouping Related Data Using Set Operators," specifically the rules governing ORDER BY placement and reference in compound queries. Memory hook: in a UNION, the ORDER BY only knows the first SELECT, so use a number or that first query's alias.

Bao N.Jan 3, 2026

A and C are your correct picks here. In a UNION query the ORDER BY has to reference either a column position number like 3 or an alias defined in the first SELECT block like DEPT_ID, so option A works because 3 is a valid positional reference and C works because DEPT_ID is the alias on the first query's department_id column, while B fails because you cannot sort by a literal string value and D fails because DEPT_NAME is the alias on the second SELECT, not the first.

Samuel O.Jun 8, 2026

The key rule to burn into your brain here is that in a UNION query the ORDER BY clause must reference columns by either their position number or by the alias from the first SELECT block, full stop. ORDER BY 3 works because it just points to the third column by position regardless of what either SELECT calls it, and ORDER BY DEPT_ID works because DEPT_ID is the alias defined in the first SELECT. Option B trips people up because you think you can sort by the literal character value, but Oracle does not allow a string literal in ORDER BY, and D is the sneaky one because DEPT_NAME looks perfectly valid until you realize it is the alias from the second SELECT, which Oracle ignores entirely when resolving ORDER BY in a compound query. I hit almost this exact question on my actual exam sitting and I almost second-guessed myself on C because I kept thinking the alias conflict between the two blocks would cause an error, but then I remembered from a DBA colleague who drilled me on this, she said just pretend the second SELECT does not exist when you are writing the ORDER BY. Went with A and C, moved on, and when I passed I went back and confirmed that was right.

Orla P.Dec 28, 2025

Honestly my first instinct was to pick B because I saw 'b' sitting right there in the SELECT list and thought the engine might accept it as a reference to that column expression, but ORDER BY in a UNION will not take a string literal, it takes a positional number or a name. That pushed me to look harder at the alias rules, which is where C locked in for me, because DEPT_ID comes from the first SELECT block and that is the one that governs what aliases the ORDER BY can see. DEPT_NAME from the second block is invisible to ORDER BY, which knocks out D, and the space in "DEPT NAME" would be a syntax error on top of that. Position 3 in option A works cleanly because positional references are always fair game in a UNION, no alias ambiguity at all.

Hiroshi T.Dec 21, 2025

Right, so in a UNION query ORDER BY must reference the first SELECT's columns or positional numbers, not literals like 'b'. Does that help clarify why B fails?

Imani T.Jun 20, 2026

Took this exact style of question on my Oracle 11g exam back when I was still working help desk and studying on lunch breaks, and the trap they set is that the column alias in a UNION only comes from the first SELECT, so DEPT_NAME from the second query is invisible to ORDER BY but DEPT_ID from the first query is fair game, and numeric position 3 always works in a UNION. Marked A and C, moved on without second-guessing, and that one felt like a freebie once you know the rule.

Mei-Ling H.Dec 9, 2025

A and C are right. In UNION queries you sort by position number or the first query's column alias.

Grace U.Jun 11, 2026

This one got me on my actual exam too. I had narrowed it down to three options and nearly talked myself into B before the clock pressure made me second-guess everything. What saved me was remembering that in a UNION query, the ORDER BY clause lives outside both SELECT statements and can only sort by column position numbers or by aliases defined in the first SELECT. That knocks out B right away because you cannot sort by a string literal like 'b', the database has no idea what to do with that as a sort key. It also knocks out D, because DEPT NAME is the alias on the second SELECT, and the rule is that the first SELECT's aliases win for the whole result set. So A works because ORDER BY 3 is a positional reference to the third column regardless of what it is called, and C works because DEPT_ID is exactly the alias given to department_id in that first SELECT. Once I locked onto that single rule about the first SELECT owning the aliases, both answers clicked and I moved on with confidence.