Anonym

WITH avg_dep AS (
                  SELECT department_id AS dept_no, 
                  AVG(salary) AS avg
                  FROM employees
                  GROUP BY department_id
                 ),
    emp_o_avg AS(
                 SELECT e.last_name AS name, e.salary AS sal,e.department_id AS dept_no
                 FROM employees e 
                 WHERE e.salary> (SELECT AVG(d.salary)
                                  FROM employees d
                                  WHERE d.department_id=e.department_id))

SELECT a.name, a.sal, a.dept_no, b.avg
FROM emp_o_avg a
JOIN avg_dep b
ON b.dept_no = a.dept_no;