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?
A.
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;
B.
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);
C.
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;
D.
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;