AvgSalaryByDepartmentID

SELECT DISTINCT avgSalary.[Average Salary], e.DepartmentID, d.Name
FROM Departments AS d
JOIN Employees AS e ON e.DepartmentID = d.DepartmentID
JOIN (
    SELECT AVG(Salary) AS [Average Salary], DepartmentID
    FROM Employees
    GROUP BY DepartmentID
    HAVING DepartmentID = 1
) AS avgSalary
ON avgSalary.DepartmentID = e.DepartmentID