How can you modify this query to show each employee's salary and the highest salary in their department?

hard📝 Application Q9 of 15

SQL - Subqueries

How can you modify this query to show each employee's salary and the highest salary in their department?

SELECT employee_id, salary, (SELECT MAX(salary) FROM employees) AS max_dept_salary FROM employees;
Assuming employees has department_id column.

AAdd GROUP BY department_id to outer query

BUse WHERE salary = MAX(salary) in subquery

CRemove subquery and use JOIN instead

DChange subquery to: (SELECT MAX(salary) FROM employees e2 WHERE e2.department_id = employees.department_id)

Step-by-Step Solution

Solution:

Step 1: Identify problem
Current subquery returns max salary overall, not per department.
Step 2: Fix correlation
Modify subquery to filter by department_id matching outer row for per-department max.
Final Answer:
Change subquery to: (SELECT MAX(salary) FROM employees e2 WHERE e2.department_id = employees.department_id) -> Option D
Quick Check:
Correlate subquery by department_id for correct max salary [OK]

Quick Trick: Correlate subquery with outer query for per-group values [OK]

Common Mistakes:

MISTAKES

Using unfiltered MAX salary for all employees
Adding GROUP BY without fixing subquery
Misusing WHERE with aggregate functions

Master "Subqueries" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions

More SQL Quizzes

How can you modify this query to show each employee's salary and the highest salary in their department?

Step 1: Identify problem

Step 2: Fix correlation

Final Answer:

Quick Check:

Want More Practice?