The correct syntax for a T-SQL subquery and a possible join

Question

What would be the correct syntax and join (if any) of a subquery that would return all of the employees first and last name from the employee’s table, and return their department name from the department table, but only those employees who more than the average salary for their department? Thanks for your answers

Answer 1

This query should give you what you are looking for.

select firstName, lastName, departmentName 
from Employees e join 
   (select departmentID, departmentName, AVG(salary) AS averageSalary 
     from Department d 
     join Employees e ON e.departmentID=d.departmentID 
     group by departmentId, departmentName) ds
on ds.departmentID=e.departmentID
where e.salary>ds.AverageSalary

(PS: I agree with the comment above. It's SO etiquette to post what you have tried so far. You were lucky this time! :-)