T-SQL Subqueries

SELECT CurrentEmployees.DepartmentID,
       MAX(CurrentEmployees.Salary) AS [Third Highest Salary]
 FROM Employees AS CurrentEmployees, (SELECT CurrentEmployees.DepartmentID,
                                           MAX(CurrentEmployees.Salary) AS [Second Highest Salary]
                                      FROM Employees AS CurrentEmployees, (SELECT DepartmentID,
                                                                                   MAX(Salary) AS [Highest Salary]
                                                                             FROM Employees
                                                                             GROUP BY DepartmentID) AS PreviousEmployees
                                      WHERE CurrentEmployees.DepartmentID = PreviousEmployees.DepartmentID AND
                                           CurrentEmployees.Salary < PreviousEmployees.[Highest Salary]
                                      GROUP BY CurrentEmployees.DepartmentID) AS PreviousEmployees
 WHERE CurrentEmployees.DepartmentID = PreviousEmployees.DepartmentID AND
       CurrentEmployees.Salary < PreviousEmployees.[Second Highest Salary]
 GROUP BY CurrentEmployees.DepartmentID

-----------------------------------------------------------------------------------------------------------------------------------

SELECT TOP(10) Employees.FirstName,
       Employees.LastName,
       Employees.DepartmentID
 FROM Employees, (SELECT DepartmentID,
                         AVG(Salary) AS [Average Salary For Department]
                   FROM Employees
                   GROUP BY DepartmentID) AS DepartmentsAndTheirAverageSalaries
 WHERE Employees.DepartmentID = DepartmentsAndTheirAverageSalaries.DepartmentID AND
       Employees.Salary > DepartmentsAndTheirAverageSalaries.[Average Salary For Department]
 ORDER BY Employees.DepartmentID