Why Can E.AVG_SAL Be Added to Only One Average-Salary Query?
Examine these statements which execute successfully: Both statements display departments ordered by their average salaries. Which two are true? (Choose two.) - 
Community Votes
100% of anonymous learners picked answer BC. Votes are pick records left by other test-takers — they are not the verified answer.
Community Insight
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.
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.
Community Discussion (4 comments)
Comments & Corrections
No comments yet — spotted an error or have a note? Share it below.
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
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.