SQL Interview Question - 1

Consider a table named Employee with the following schema:

CREATE TABLE Employee (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    department VARCHAR(100),
    salary DECIMAL(10, 2),
    hire_date DATE
);

Write an SQL query to find the employee(s) with the highest salary within each department.

Sample Table:

emp_id	emp_name	department	salary	hire_date
1	Alice	Sales	50000	2020-01-01
2	Bob	Sales	55000	2019-05-15
3	Charlie	HR	60000	2020-03-20
4	David	HR	58000	2018-11-10
5	Emma	Marketing	52000	2021-02-28
6	Frank	Marketing	53000	2019-08-10

Solution:

SELECT 
    emp_id,
    emp_name,
    department,
    salary
FROM 
    Employee e
WHERE 
    (department, salary) IN (
        SELECT 
            department,
            MAX(salary)
        FROM 
            Employee
        GROUP BY 
            department
    );

 

Explanation:

• The inner query (SELECT department, MAX(salary) FROM Employee GROUP BY department) finds the maximum salary for each department.
• The outer query selects all rows from the Employee table where the (department, salary) tuple matches those found by the inner query.
• This effectively retrieves the employee(s) with the highest salary within each department.

Share your alternate solution in comments.