---- We want to find employees making over $150K/yr in marketing or sales.
---- However, the department_id of marketing and sales teams are split into
---- dozens of regions, so we'll have to use the boss' department to
---- narrow the list of employees.  That's fine, though, as we need the
---- bosses information, too.

SELECT boss.employee_id as boss_id,    boss.first_name        as boss_name, 
       boss_j.job_title as boss_title, boss_d.department_name as boss_dept,
       emp.employee_id  as emp_id,     emp.first_name         as emp_name, 
       emp_j.job_title  as emp_title,  emp_d.department_name  as emp_dept,
       emp.salary       as emp_salary

---- Standard table hookup for the boss

FROM HR.employees   boss
JOIN HR.jobs        boss_j ON boss_j.job_id        = boss.job_id
JOIN HR.departments boss_d ON boss_d.department_id = boss_e.department_id

---- Standard table hookup for the employee

     -- tie the employee to their boss
JOIN HR.employees   emp   ON emp.boss_id         = boss.employee_id
JOIN HR.jobs        emp_j ON emp_j.job_id        = emp.job_id
JOIN HR.departments emp_d ON emp_d.department_id = emp_e.department_id

---- Now select the employees we're looking for.

WHERE boss.department_name IN ('MARKETING', 'SALES')
AND   emp.salary > 150000;