Day02

1. 显示部门编号、部门名字、该部门的员工数、每个部门的平均工资，部门负责人信息，包括姓名、薪水、职业；平均工资保留2位小数，千分位分隔符显示；结果按部门升序

select d.department_id,
         d.department_name,
         temp.t_count,
         to_char(ROUND(temp.sal, 2), '999,999.99'),
         m.last_name,
         m.salary,
          m.job_id
    from employees m,
         departments d,
         (select department_id dno,
                 count(last_name) t_count,
                 avg(salary) sal
            from employees
           group by department_id) temp
   where d.manager_id = m.employee_id
     and d.department_id = temp.dno
   order by d.department_id

2 显示员工数最多的部门信息，显示部门ID、名称、部门员工数，部门的主管经理姓名

select distinct d.department_id, d.department_name,temp.t_count,m.first_name
  from employees e,
       departments d,
       employees m,
       (select  department_id did, count(department_id) t_count
          from employees
         group by department_id
        having count(department_id) >= all (select count(department_id)
                                             from employees
                                             group by department_id)) temp
 where d.department_id = temp.did 
       and d.manager_id=m.employee_id

3.显示工号、姓名、薪水、部门编号、薪资，薪资与部门平均工资的差异情况；按照部门ID排序

elect e.employee_id,
       e.last_name,
       e.department_id,
       e.salary,
       (e.salary - tsal.t_sal)
  from employees e,
       departments d,
       (select department_id dno, avg(salary) t_sal
          from employees
         group by department_id) tsal
 where d.department_id = tsal.dno
   and d.department_id = e.department_id
 order by e.department_id

--分析函数
select e.employee_id,
       e.last_name,
       e.salary,
       e.department_id,
       e.salary - (avg(e.salary) over(partition by e.department_id))
  from employees e

4.周几录取的人数最少，显示人名和日期

select e.employee_id,
       e.first_name,
       e.last_name,
       to_char(e.hire_date, 'day'),
       temp.t_count

  from employees e,
       (select to_char(hire_date, 'day') t_weekday,
               count(employee_id) t_count
          from employees
         group by to_char(hire_date, 'day')
        having count(employee_id) <= all (select count(employee_id)
                                           from employees
                                          group by to_char(hire_date, 'day'))) temp
 where to_char(e.hire_date, 'day') = temp.t_weekday

5.查询所有hr用户下的索引

select * from all_indexes where owner='HR'

注意：要去到HR用户下，并且用户名区分大小写,否则查不到