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.