Why Can E.AVG_SAL Be Added to Only One Average-Salary Query?

1Z0-071 By islever Editorial Last updated

Using group functions OUTER joins Creating groups of data

Short answer

BC Adding E.AVG_SAL succeeds only in the inline-view average-salary statement, and both statements list departments that have no employees.

Question

Examine these statements which execute successfully: Both statements display departments ordered by their average salaries. Which two are true? (Choose two.) - Exhibit: Why Can E.AVG_SAL Be Added to Only One Average-Salary Query?
  1. Both statements will execute successfully if you add E.AVG_SAL to the select list.
  2. Both statements will display departments with no employees. Correct Answer
  3. Only the second statement will execute successfully if you add E.AVG_SAL to the select list. Correct Answer
  4. Only the first statement will display departments with no employees.
  5. Only the second statement will display departments with no employees.

Key Takeaways

Tests whether you know that a GROUP BY aggregate's column alias cannot be table-qualified (ORA-00904) while an inline-view column can, and that an outer join keeps departments with no employees — the trap is assuming only one statement preserves empty departments.

Two department average-salary statements both execute successfully, yet only the statement whose AVG_SAL value comes from an inline view accepts the qualified reference E.AVG_SAL, while both statements still list departments that have no employees. This page verifies options B and C against Oracle SQL behaviour, tested explanations and the community vote history.

Common mistake: The most common error is choosing D or E, assuming only one of the two statements keeps departments without employees; because both statements preserve every department row, option B is the correct claim. Others pick A, forgetting that adding E.AVG_SAL raises ORA-00904 in the GROUP BY statement.

Expert Analysis

Why the Answer Is Correct

Both statements report each department with its average salary, and option B is true because every statement keeps all department rows: the select lists carry the department columns and the salary data is attached through an outer join (or an outer-joined inline view), so a department with no employees still appears with a NULL average salary. Option C is true because in the statement whose average comes from a subquery/inline view, AVG_SAL is a real column of that row source and can legitimately be qualified as E.AVG_SAL, whereas in the GROUP BY statement AVG_SAL is merely a column alias for AVG(e.salary) and E.AVG_SAL is rejected with ORA-00904. That difference — what AVG_SAL actually is in each statement, not the join syntax — is what separates A from C. The image's promise that "Both statements display departments ordered by their average salaries" also confirms that neither statement filters out departments without employees.

Why the Other Options Are Wrong

A is wrong precisely because the first statement cannot accept E.AVG_SAL: qualifying the alias of a group function raises ORA-00904, so "both statements will execute successfully" is impossible. D and E are wrong for the same reason — each claims that only one of the pair returns departments with no employees, but both statements return them because both preserve the full department row set through an outer join. There is no inner join or filter in either statement that would suppress empty departments, so the exclusive wording of D and E contradicts the observed result. Only B and C are simultaneously consistent with the two statements executing successfully and ordering by average salary.

Community Comment Notes

khaleesi89 reports having tested it and notes that "the first select returns ORA-00904" when the qualified alias is added, and rejects D and E "because both statement have d.* in the select list", so both must show empty departments. NB196 first posted "Actually, I think C & D are the correct answers" but later revised to "B & C are the correct answers", which matches the tested Oracle behaviour. ogi33 recorded only "D E tested", showing how a quick test without reading the ORA-00904 message can point at the wrong pair. The page's 100 votes all sit on BC, consistent with the reasoning above.

Official Reference

These links open the official vendor documentation in a new tab.

Exam Strategy

Because this item is multi-select ("Choose two."), treat each option as an independent true/false claim and test two axes separately: can E.AVG_SAL be qualified in that statement, and does that statement keep empty departments? Eliminating options this way is far faster than hunting for one "right" statement, and it avoids the A/C trap where the aggregate alias looks table-qualifiable.

Frequently Asked Questions

Why does adding E.AVG_SAL to the first statement raise ORA-00904?

There AVG_SAL is only a column alias for the group function AVG(e.salary), not a table column, so Oracle refuses to qualify it with the table alias E and raises ORA-00904.

Why do both statements display departments with no employees?

Both keep every department row and attach salary data through an outer join or outer-joined inline view, so a department with no employees still appears with a NULL average salary.

More 1Z0-071 FAQ →

Community Votes

BC
100%

100% of anonymous learners picked answer BC. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 4 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 4

  1. NB196 👍 4
    Actually, I think C & D are the correct answers
  2. khaleesi89 👍 2
    Correct Answer: BC Tested A->The first select returns ORA-00904 D ->Wrong because both statement have d.* in the select list, so both will display departments with no employees E-> Wrong like D F -> Wrong like A
  3. NB196 👍 3
    B & C are the correct answers
  4. ogi33 👍 1
    D E tested

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

System and Object Privileges in Oracle Database

S-grade

Which Three Actions Can Be Performed Only With System Privileges?

S-grade

How Do You Calculate Days from 1 Jan 2019 to SYSDATE in Oracle?

S-grade

Which Two SQL Statements Execute on TRANSACTIONS Table?

S-grade

← Back to 1Z0-071 Study Guide