﻿ SQL - Highest salary drawar in a department

# SQL Exercises: Highest salary drawar in a department

## SQL SUBQUERY: Exercise-54 with Solution

From the following table, write a SQL query to find those employees who earn the highest salary in a department. Return department ID, employee name, and salary.

Sample table: employees

Sample Solution:

``````SELECT department_id, first_name || ' ' || last_name AS Employee_name, salary
FROM employees a
WHERE salary =
(SELECT MAX(salary)
FROM employees
WHERE department_id = a.department_id);
``````

Sample Output:

```department_id	employee_name	salary
90		Steven King			24000.00
60		Alexander Hunold	9000.00
100		Nancy Greenberg		12000.00
30		Den Raphaely		11000.00
80		John Russell		14000.00
0		Kimberely Grant		7000.00
10		Jennifer Whalen		4400.00
20		Michael Hartstein	13000.00
40		Susan Mavris		6500.00
70		Hermann Baer		10000.00
110		Shelley Higgins		12000.00
```

Code Explanation:

The said query in SQL that selects the "department_id", concatenated "first_name" and "last_name" as "Employee_name", and "salary" from the 'employees' table. It only selects the employees who have the maximum salary in their department. The subquery retrieves the maximum salary for each department, and the outer query filters the employees by comparing their salary with the maximum salary of their respective department.

Visual Presentation:

Alternative Solutions:

Using JOINs and Subqueries:

``````
SELECT a.department_id,
a.first_name || ' ' || a.last_name AS Employee_name,
a.salary
FROM employees a
JOIN (
SELECT department_id, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id
) b ON a.department_id = b.department_id
WHERE a.salary = b.max_salary;
``````

Using a Window Function:

``````
SELECT department_id,
first_name || ' ' || last_name AS Employee_name,
salary
FROM (
SELECT department_id,
first_name,
last_name,
salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees
) ranked
WHERE rank = 1;
``````

Using a Correlated Subquery with EXISTS:

``````
SELECT a.department_id,
a.first_name || ' ' || a.last_name AS Employee_name,
a.salary
FROM employees a
WHERE EXISTS (
SELECT 1
FROM employees b
WHERE b.department_id = a.department_id
GROUP BY b.department_id
HAVING MAX(b.salary) = a.salary
);
``````

## Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Display the details of departments managed by Susan.
Next SQL Exercise: Employees who did not have any job in the past.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

## SQL: Tips of the Day

Is SQL syntax case sensitive?

The SQL Keywords are case-insensitive (SELECT, FROM, WHERE, etc), but are often written in all caps. However in some setups table and column names are case-sensitive. MySQL has a configuration option to enable/disable it. Usually case-sensitive table and column names are the default on Linux MySQL and case-insensitive used to be the default on Windows, but now the installer asked about this during setup. For MSSQL it is a function of the database's collation setting.

Ref: https://bit.ly/3R2iyNZ

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook