How do you display employees having max/min salary department-wise (SQL, SQL server, select, inner join, development)?

 To display employees with the maximum and minimum salary in each department, you can use a combination of JOIN, GROUP BY, MAX(), MIN(), and INNER JOIN in SQL Server.

Here’s how you can do it step by step:

Step-by-Step Solution:

1. Assume the table structure:

   • Employees: Stores employee data.
     • Columns: EmployeeID, EmployeeName, Salary, DepartmentID
   • Departments: Stores department data.
     • Columns: DepartmentID, DepartmentName

2. Using Subqueries to Find Max and Min Salaries for Each Department:

   First, you would need to identify the maximum and minimum salaries for each department. Then, you will join that information back to the Employees table to find the employees associated with those salaries.

SQL Query:

-- Step 1: Find employees with max salary in each department
SELECT 
    e.EmployeeID,
    e.EmployeeName,
    e.Salary,
    d.DepartmentName,
    'Max Salary' AS SalaryType
FROM 
    Employees e
INNER JOIN 
    Departments d ON e.DepartmentID = d.DepartmentID
WHERE 
    e.Salary = (SELECT MAX(Salary) 
                FROM Employees 
                WHERE DepartmentID = e.DepartmentID)

UNION ALL

-- Step 2: Find employees with min salary in each department
SELECT 
    e.EmployeeID,
    e.EmployeeName,
    e.Salary,
    d.DepartmentName,
    'Min Salary' AS SalaryType
FROM 
    Employees e
INNER JOIN 
    Departments d ON e.DepartmentID = d.DepartmentID
WHERE 
    e.Salary = (SELECT MIN(Salary) 
                FROM Employees 
                WHERE DepartmentID = e.DepartmentID)
ORDER BY 
    d.DepartmentName, SalaryType DESC, e.Salary;

Explanation:

1. First Part (Max Salary):

   • We select employees who have the maximum salary in each department by using a correlated subquery in the WHERE clause.
   • The subquery (SELECT MAX(Salary) FROM Employees WHERE DepartmentID = e.DepartmentID) ensures that we are selecting only employees who have the maximum salary within their respective department.

2. Second Part (Min Salary):

   • Similarly, we use another subquery (SELECT MIN(Salary) FROM Employees WHERE DepartmentID = e.DepartmentID) to find employees with the minimum salary in each department.

3. UNION ALL:

   • The UNION ALL combines the results of the two queries—one for maximum salary employees and the other for minimum salary employees.

4. ORDER BY:

   • We order the results by DepartmentName and SalaryType (to distinguish between "Max Salary" and "Min Salary") and then by Salary to display in ascending order.

Result:

This query will display employees who have the maximum and minimum salary in each department along with the department name. The SalaryType column distinguishes whether the salary is the maximum or minimum in the department.

Example Output:

EmployeeID	EmployeeName	Salary	DepartmentName	SalaryType
101	Alice	120000	HR	Max Salary
102	Bob	90000	HR	Min Salary
201	Charlie	130000	IT	Max Salary
202	Dave	95000	IT	Min Salary

This will list employees in each department with their respective maximum and minimum salaries.