What is the highest salary in each department using SQL?

In many tech interviews, especially for positions involving database management and analysis, a common question revolves around how to extract specific data from a database. One frequently asked question is about how to find the highest salary in each department using SQL. Understanding this concept is critical for demonstrating proficiency in SQL and database querying.

To solve the problem, let’s assume we have a table named employees with the following structure:

• employee_id: A unique identifier for each employee.
• name: The name of the employee.
• department: The department where the employee works.
• salary: The salary of the employee.

Here is a sample structure of the employees table:

To find the highest salary in each department, we can use the GROUP BY clause combined with the MAX() function in SQL. Here’s how to write the SQL query:

1SELECT department, MAX(salary) AS highest_salary
2FROM employees
3GROUP BY department;

In this query, we are selecting two columns. The first is the department, and the second is the highest salary in that department, which we calculate using the MAX(salary) function. The GROUP BY department clause categorizes the results by department, allowing us to calculate the maximum salary within each group.

When you run the above SQL query against our sample employees table, you will receive the following result:

This output correctly shows the highest salary for each department.

It is also common to need both the employee's name and their highest salary. To achieve this in SQL, we can use a subquery. The subquery will first find the highest salary per department, and then we will join this with the original table to get the corresponding employee names.

Here’s how you can achieve this:

1SELECT e.name, e.salary, e.department
2FROM employees e
3JOIN (
4    SELECT department, MAX(salary) AS highest_salary
5    FROM employees
6    GROUP BY department
7) as max_salaries 
8ON e.department = max_salaries.department AND e.salary = max_salaries.highest_salary;

In this query, we are joining the original employees table e with a derived table called max_salaries that contains the highest salaries per department. This will yield the names of employees who earn the highest salary in their respective departments.

This approach illustrates not only how to fetch the highest salary but also demonstrates the importance of using joins and subqueries to expand on basic queries in SQL. Mastering these concepts is essential for anyone looking to succeed in tech interview scenarios related to SQL and data analysis.