How would you write a SQL query to find the second highest salary per department, including department name, employee name, and salary?

This question is about writing a SQL query that will fetch the second highest salary for each department, including department name, employee name, and salary. This type of query will show your understanding of SQL subqueries and ranking functions, as you need these concepts to solve the problem correctly. To answer this question:

1. Identify the three tables: department, employee, and salary; and the relationship between them.
2. Understand how to use the 'rank' or 'dense_rank' functions. You need to rank salaries within each department.
3. Since the question is asking for the second highest salary, focus on how to filter out all except those with rank 2.

Here is an example of a SQL query that can answer this:

SELECT D.name as Department, E.name as Employee, s.salary as 'Second Highest Salary'
FROM (
SELECT department_id, salary,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as salary_rank
FROM Employee
) s
JOIN Employee E ON s.employee_id = E.employee_id
JOIN Department D ON D.department_id = E.department_id
WHERE s.salary_rank = 2

This SQL query first creates a subquery that returns a new table containing the rank of each employee's salary within their department. Then it joins this table with the original Employee and Department tables to get the corresponding employee and department names for salaries with rank 2.

Here's another example of how to write a SQL query for this scenario:

WITH SalaryRanks AS
(
SELECT Department.Name as 'Department',
Employee.Name as 'Employee',
Salary.Amount as 'Salary',
DENSE_RANK() OVER (PARTITION BY Department.DepartmentID ORDER BY Salary.Amount DESC) as 'Rank'
FROM Department
JOIN Employee ON Department.DepartmentID = Employee.DepartmentID
JOIN Salary ON Employee.EmployeeID = Salary.EmployeeID
)
SELECT Department, Employee, Salary
FROM SalaryRanks
WHERE Rank = 2

In this query, a common table expression (CTE), 'SalaryRanks', is first created which ranks employees' salaries inside their departments. Later, it simply selects those rows from the CTE where Rank equals 2 to get the required output.