Oracle Database 12c: SQL Fundamentals — Question 24

You need to write a SQL statement that returns employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department.
Which statement accomplishes this task?

Answer options

• A. SELECT a.emp_name, a.sal, b.dept_id, MAX(sal) FROM employees a, departments b WHERE a.dept_id = b.dept_id AND a.sal < MAX(sal) GROUP BY b.dept_id;
• B. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal;
• C. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a WHERE a.sal < (SELECT MAX(sal) maxsal FROM employees b GROUP BY dept_id);
• D. SELECT emp_name, sal, dept_id, maxsal FROM employees, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) WHERE a.sal < maxsal;

Correct answer: B

Explanation

Option B is correct because it correctly retrieves the employee's name, salary, department ID, and the maximum salary per department, filtering for those earning less than that maximum. Option A is incorrect as it attempts to use MAX(sal) without a proper subquery for maximum salary. Option C does not link the department ID to the maximum salary correctly, and option D has a syntax issue with the reference to 'a.sal' which is not defined in the outer query.