Evaluate the SQL statement: SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM…
2024
Evaluate the SQL statement:
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;
Which of the following statements is correct?
- A.
The statement gives an error at line 1.
- B.
The statement gives an error at line 6.
- C.
The statement produces the employee name, salary, department ID, and maximum salary earned in the employee department for all departments that pay less salary than the maximum salary paid in the company.
- D.
The statement produces the employee name, salary, department ID, and maximum salary earned in the employee department for all employees who earn less than the maximum salary in their department.
Attempted by 3 students.
Show answer & explanation
Correct answer: D
Concept
An inline view -- a subquery placed in the FROM clause -- is treated as a temporary, unnamed table for the duration of the outer query. When that subquery includes a GROUP BY, its output is one row PER GROUP, not one row overall. Joining the outer table to this grouped result on the grouping column attaches each outer row to the aggregate value computed for its OWN group, never a single database-wide aggregate.
Application
The inline view (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b groups all employees by dept_id and computes the highest salary within each department, producing one (dept_id, maxsal) pair per department.
The outer query aliases the base table as a and joins it to b with a.dept_id = b.dept_id, so every employee row is paired with the maxsal computed for that same employee's own department -- never the maxsal of any other department.
The filter a.sal < b.maxsal keeps only the rows where an individual employee's own salary is strictly less than the maximum salary earned within their own department.
The SELECT list (emp_name, sal, dept_id, maxsal) then returns, for each qualifying employee, their name, salary, department id, and their own department's maximum salary.
Cross-check
Cross-check: because the join key is dept_id (not a constant), the comparison is always against the requesting employee's own department-level maximum -- an employee in one department is never compared to another department's maxsal. Any employee whose own salary equals their department's maximum is automatically excluded, since a value can never be strictly less than itself -- this holds even when multiple employees are tied at that maximum. This rules out any reading based on a single, company-wide maximum.
The query therefore lists every employee who earns less than the highest salary paid within their own department, together with that department's maximum salary, department id, and their own salary -- an employee-level, per-department comparison, not a department-level, company-wide one.