Finding the Highest Salary in a Department

Many organizations store employee data in relational databases. A common task is to determine the highest salary within a specific department. This challenge will test your ability to use the MAX() aggregate function in SQL to efficiently find the maximum salary for a given department.

Problem Description

You are given a table named Employees with the following columns:

• EmployeeID (INT): Unique identifier for each employee.
• EmployeeName (VARCHAR): Name of the employee.
• Department (VARCHAR): Department the employee belongs to.
• Salary (DECIMAL): Employee's salary.

Your task is to write a SQL query that, given a specific department name, returns the highest salary within that department. The query should only return a single value: the maximum salary. Consider cases where a department might be empty (no employees) or where the salary column contains NULL values. The MAX() function should correctly handle NULL values (ignoring them).

Examples

Example 1:

Input: Department = 'Sales'

Employees Table:
EmployeeID | EmployeeName | Department | Salary
-----------|--------------|------------|--------
1          | Alice        | Sales      | 60000
2          | Bob          | Marketing  | 55000
3          | Charlie      | Sales      | 75000
4          | David        | Sales      | 65000

Output: 75000
Explanation: The highest salary in the 'Sales' department is 75000.

Example 2:

Input: Department = 'Engineering'

Employees Table:
EmployeeID | EmployeeName | Department | Salary
-----------|--------------|------------|--------
1          | Alice        | Sales      | 60000
2          | Bob          | Marketing  | 55000
3          | Charlie      | Sales      | 75000
4          | David        | Sales      | 65000

Output: NULL
Explanation: There are no employees in the 'Engineering' department.  The query should return NULL in this case.

Example 3: (Edge Case - NULL Salaries)

Input: Department = 'HR'

Employees Table:
EmployeeID | EmployeeName | Department | Salary
-----------|--------------|------------|--------
1          | Eve          | HR         | 50000
2          | Frank        | HR         | NULL
3          | Grace        | HR         | 55000

Output: 55000
Explanation: The `MAX()` function ignores NULL values. The highest non-NULL salary in the 'HR' department is 55000.

Constraints

• The Employees table will always exist.
• The Department column will contain valid department names (strings).
• The Salary column will contain numeric values (DECIMAL). NULL values are possible.
• The department name provided as input will always be a valid department name present in the Employees table, or a department that does not exist.
• The query should be efficient and return the result quickly.

Notes

• You will need to use the MAX() aggregate function.

• You will need to use a WHERE clause to filter the employees by department.

• Consider how to handle the case where a department has no employees. Returning NULL is the expected behavior in this scenario.

• Pseudocode:

  FUNCTION FindMaxSalary(department_name):
    // Query the Employees table
    // Filter rows where Department equals department_name
    // Apply the MAX() aggregate function to the Salary column
    // Return the result
  END FUNCTION