Explain how to write a SQL query that uses an inner query and ranking functions to retrieve the second highest salary per department by joining the employee and salary tables.

To get the second highest salary in each department you can use a window function such as DENSE_RANK or ROW_NUMBER. First join the employee and salary tables on employee_id. Then apply the ranking over each department ordered by salary descending. Finally filter for rank = 2. A typical query:

SELECT department, employee_name, employee_id, salary

FROM (

SELECT e.department,

     e.employee_name,
     e.employee_id,
     s.salary,
     DENSE_RANK() OVER (PARTITION BY e.department ORDER BY s.salary DESC) AS rnk

FROM employee e

JOIN salary s ON e.employee_id = s.employee_id

) AS ranked

WHERE rnk = 2;

This query runs in O(n log n) time due to the sort performed by the window function. It handles ties correctly: if multiple employees share the same salary, they receive the same rank, and the next distinct salary gets the next rank. If you want the second distinct salary regardless of ties, use ROW_NUMBER instead of DENSE_RANK.