Listing 4:Query That Finds the Employee with the Highest Salary in Each Job Type and the 
Highest Salary in Each Department

SELECT job, dept_id, last_name AS name, salary, max_dept_sal
FROM (SELECT dept_id, job, last_name,
 MAX(salary) OVER (PARTITION BY job) max_job_sal, salary,
 MAX(salary) OVER (PARTITION BY dept_id) max_dept_sal
 FROM employee) AS part_deptid
WHERE salary = max_job_sal