Oracle Exam 1Z0-071 Topic 8 Question 108 Discussion

D . All remaining employee names will appear in descending order F. The names of employees with maximum salary will appear first in descending order

Comprehensive and Detailed Explanation WITH all Reference:

Analyzing the given SQL query:

ORDER BY CASE WHEN salary = (SELECT max(salary) FROM employees) THEN 'A' ELSE last_name END, last_name DESC;

Here's what happens in the query:

The CASE statement checks if the employee's salary is equal to the maximum salary in the table.

If an employee has the maximum salary, they are assigned a constant value 'A', else their last_name is used for sorting.

Since 'A' comes before any possible last name value in ascending order, all employees with the maximum salary will be listed first.

After the CASE expression, the query specifies last_name DESC. This means that within each group sorted by the CASE expression, the names will be sorted in descending order.

Therefore, the employees with the maximum salary will appear first, and within that group, if there is more than one employee with the same maximum salary, they will be sorted in descending order by last name (F). Then, all remaining employee names will appear in descending order (D).

Oracle Documentation on CASE Expressions: CASE Expressions

Oracle Documentation on Sorting Query Results: Sorting Query Results