select employeeid, firstname, d.avgsal
from employee,
(select departmentid, avg(salary)
from employee
group by departmentid) d
where employee.departmentid = d.departmentid
from employee,
(select departmentid, avg(salary)
from employee
group by departmentid) d
where employee.departmentid = d.departmentid