DB ESM - HW2

SELECT
    cc.country_id AS COUNTRY_CODE,
    Nvl(emp.job_id, 'N\A') AS JOB,
    To_char(Avg(emp.salary), 'fm$999,999,990.00') AS AVG_SALARY,
    (SELECT Max(salary) FROM hr.employees in_emp
        JOIN hr.departments in_dep ON in_dep.department_id = in_emp.department_id
        JOIN hr.locations in_loc ON in_loc.location_id = in_dep.location_id
        WHERE in_loc.country_id = cc.country_id)    AS MAX_SALARY,
    Count(CASE WHEN tr.employee_id = emp.employee_id THEN 1 ELSE NULL END)AS JOB_CHANGES
FROM hr.employees emp
    INNER JOIN
        hr.departments dp ON dp.department_id = emp.department_id
    INNER JOIN
        hr.locations loc ON loc.location_id = dp.location_id
    RIGHT JOIN
        hr.countries cc ON cc.country_id = loc.country_id
    LEFT JOIN
    (SELECT DISTINCT jh.employee_id FROM hr.job_history jh) tr ON tr.employee_id = emp.employee_id
GROUP  BY cc.country_id, emp.job_id
ORDER  BY (CASE WHEN cc.country_id = 'US' THEN 0 ELSE 1 END),
          (CASE WHEN emp.job_id IS NULL THEN 1 ELSE 0 END);