--prikazati sljedece podatke: id, ime i preime djelatnika, id odjela, placu djelatnika
-- razliku place djelatnika u odnosu na prosjecnu placu odjela kojem pripada
-- razliku place djelatnika u odnosu na prosjecnu placu radnog mjesta kojem pripada

WITH
agr_dpt AS
(SELECT department_id, round(avg (salary), 2) AS prosj_pl_dpt
FROM employees
GROUP BY department_id), --ono po cemu grupiramo ide i u SELECT

agr_job AS
(SELECT job_id, round(avg (salary), 2) AS prosj_pl_job
FROM employees
GROUP BY job_id)
SELECT e.employee_id, e.first_name, e.last_name, e.department_id, e.salary, ad.prosj_pl_dpt, e.salary - ad.prosj_pl_dpt AS razlika_dpt
e.salary -aj.prosj_pl_job AS razlika.job e.job_id
FROM employees e JOIN agr_dpt ad ON e.department_id = ad.department_id
    JOIN agr_job aj ON e.job_id = aj.job_id
ORDER BY 1;