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, 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;
Explanation:
function MAX(column_name)
Incorrect answer:
A:
invalid statement
C:
inner query return more than one line
D:
column maxsal does not exists.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 5-7