﻿ SQL: List the highest-paid employees in each department

# SQL Exercise: List the highest-paid employees in each department

## SQL subqueries on employee Database: Exercise-33 with Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

33. From the following table, write a SQL query to find those employees who receive the highest salary of each department. Return employee name and department ID.

Sample table: employees

Sample Solution:

-- This SQL query aims to list the highest-paid employees in each department by using a correlated subquery to find the maximum salary for each department.
-- SELECT statement begins
SELECT
e.emp_name, -- Selects the employee name
e.dep_id -- Selects the department ID
FROM
employees e -- Specifies the 'employees' table with alias 'e' to retrieve data from
WHERE
e.salary = ( -- Filters the employees based on the condition that their salary equals the maximum salary for their department
SELECT
MAX(salary) -- Finds the maximum salary for each department using a subquery
FROM
employees e2 -- Specifies a correlated subquery by referencing the 'employees' table again with alias 'e2'
WHERE
e2.dep_id = e.dep_id -- Correlates the subquery with the main query by comparing the department IDs
);

Sample Output:

emp_name | dep_id
----------+--------
KAYLING  |   1001
BLAZE    |   3001
SCARLET  |   2001
FRANK    |   2001
(4 rows)

Explanation:

• This SQL code aims to list the highest-paid employees in each department.

• The SELECT statement selects the employee name (emp_name) and the department ID (dep_id) from the 'employees' table.

• The main query filters the employees based on the condition that their salary equals the maximum salary for their respective department.

• This condition is evaluated using a correlated subquery, where the subquery selects the maximum salary (MAX(salary)) for each department.

• The subquery is correlated with the main query by referencing the 'employees' table again with a different alias (e2) and comparing the department IDs (dep_id).

• By matching the department IDs in the main query and the subquery, the main query ensures that it retrieves only those employees whose salary matches the maximum salary for their department.

## Practice Online

Structure of employee Database:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: List the employees who are working as a manager.
Next SQL Exercise: Average of the maximum and minimum salary of employees.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿