1. 查询有员工的部门的平均工资，要求显示部门编号，部门名，部门所在地
   select avg(e.salary) ,d.department_id,d.department_name,l.street_address from hr.employees e left join hr.departments d
   on e.department_id = d.department_id 
   left join hr.locations l
   on d.location_id = l.location_id
   group by d.department_id ,d.department_name,l.street_address
   
   
2. 查询总工资最多的部门，显示部门编号，部门名
   select sum(e.salary) ,d.department_id ,d.department_name from hr.employees e left join hr.departments d
   on e.department_id = d.department_id 
   group by d.department_id ,d.department_name
   having sum(e.salary) = (
       select max(sum(e.salary)) from hr.employees e left join hr.departments d
       on e.department_id = d.department_id 
       group by d.department_id
   )
   
3. 查找和143在同一个部门和同一个岗位的比他工资高的员工。
   select * from hr.employees e0 where e0.department_id = 
   (select e.department_id from hr.employees e where e.employee_id  =143)
   and e0.job_id = 
   (
   select e.job_id from hr.employees e where e.employee_id  =143
   )
   and e0.salary > 
   (
   select e.salary from hr.employees e where e.employee_id  =143
   )

4. 查找比90部门中任意一个员工的工资低的所有员工（不包括90部门）。
   select * from hr.employees e0 where e0.department_id != 90 and 
   e0.salary < any    (
      select e.salary from hr.employees e where e.department_id =90
   )

5. 查找每个部门工资第二高的员工。
   
   select max(s2.salary), s2.department_id from 
   (select * from
   (select e.salary ||'-'|| e.department_id as sd, e.* from hr.employees e) s1
   where s1.sd not in
   (select max(e.salary) ||'-'||e.department_id from hr.employees e
   group by e.department_id)
   ) s2 group by s2.department_id

6. 查询所在部门所在城市为'South San Francisco'
   select * from hr.employees e left join hr.departments d
   on e.department_id = d.department_id
   left join hr.locations l
   on d.location_id = l.location_id
   where l.city ='South San Francisco'
   
7. 查询所有部门的平均工资，无人员部门平均工资记作0。
   nvl函数
   select nvl( avg(e.salary),0), d.department_id from hr.departments d left join hr.employees e 
   on e.department_id = d.department_id
   group by d.department_id
   
8. 查询工作变更超过一次的所有员工。
   select count(e.first_name),e.employee_id from hr.employees e
   left join hr.job_history h
   on e.employee_id = h.employee_id
   group by e.employee_id
   having count(e.first_name) >1

9. 查找工资最高的员工。
select * from 
(select rownum r, e1.* from 
  (
   select e.* from hr.employees e  order by e.salary desc
  ) e1
) e2 where e2.r =1

10. 查询工资第5高的员工。
select * from 
(select rownum r, e1.* from 
  (
   select e.* from hr.employees e  order by e.salary desc
  ) e1
) e2 where e2.r =5

11. 平均工资第5高的部门，里面的工资倒数第3的人
select s4.* from 
(select rownum r, s3.* from
(select * from hr.employees e where e.department_id = 
(
select department_id from 
(
  select rownum r, s1.* from 
  (select  avg(e.salary) as avgsalary  , e.department_id from hr.employees e 
    group by e.department_id
    order by avg(e.salary) desc
  ) s1 
)s2 where s2.r = 5
) order by salary asc
) s3
) s4 where s4.r = 3

12. 手下人数第15多的经理
select manager_id from 
(select rownum r, s1.* from 
(select count(*), e.manager_id from hr.employees e
group by e.manager_id 
order by count(*) desc
) s1
) s2 where s2.r = 15