In Oracle SQL, aggregation functions such as AVG and MAX cannot be nested directly inside each other and must be used in conjunction with GROUP BY on the column(s) that are not included in the aggregate function.
Also, the HAVING clause filters groups after aggregation is applied.
A). This query will not execute successfully because it improperly nests MAX inside AVG. Oracle SQL does not allow this type of nested aggregation without a subquery.
B). This query will not execute successfully for the same reason as A, it improperly nests MAX inside AVG.
C). Similar to A and B, this query improperly nests SUM inside MAX, which is not allowed without a subquery.
D). This query will execute successfully. It filters rows based on the HIRE_DATE using a correct date format (assuming '9' refers to '09' or '1999' due to the NLS_DATE_FORMAT being 'DD-MON-RR'), then groups the remaining rows by DEPT_ID and calculates the sum of SALARY for each department.
E). This query will not execute successfully because it improperly nests MAX inside AVG without a subquery, and it incorrectly attempts to GROUP BY SALARY, which is already being aggregated.
References:
* Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Aggregate Functions"
* Oracle Database SQL Language Reference, 12c Release 1 (12.1): "GROUP BY Clause"
* Oracle Database SQL Language Reference, 12c Release 1 (12.1): "HAVING Clause"