Write a stored procedure that returns the department name and the second highest salary.

To get the department name and the second highest salary, you can use a window function to rank salaries within each department. In T‑SQL:

CREATE PROCEDURE dbo.GetDeptSecondHighest
AS
BEGIN
    SELECT DepartmentName,
           MAX(Salary) AS SecondHighestSalary
    FROM (
        SELECT d.DepartmentName,
               e.Salary,
               DENSE_RANK() OVER (PARTITION BY d.DepartmentID ORDER BY e.Salary DESC) AS SalaryRank
        FROM Employees e
        JOIN Departments d ON e.DepartmentID = d.DepartmentID
    ) AS ranked
    WHERE SalaryRank = 2
    GROUP BY DepartmentName;
END;

The DENSE_RANK assigns 1 to the highest salary, 2 to the second highest, etc. The outer query filters for rank 2 and groups by department. Complexity is O(n) for scanning the tables plus O(n log n) for the window function, which is acceptable for typical datasets. The procedure can be called with EXEC dbo.GetDeptSecondHighest;.