第六章分组函数课后练习和作业

– 练习

• 练习一

  --  1.查询部门20的员工，每个月的工资总和及平均工资。
      select sum(sal) 每个月工资总和, avg(nvl(sal,0)) 平均工资
        from emp
       where deptno = 20; 
  
  --  2.查询工作在CHICAGO的员工人数，最高工资及最低工资。
      select count(*) 员工人数,
             max(e.sal) 最高工资，
             min(e.sal) 最低工资
        from emp e, dept d
       where e.deptno = d.deptno
         and d.loc = 'CHICAGO';
  
  --  3.查询员工表中一共有几种岗位类型。
      select count(distinct e.deptno) 岗位类型数
        from emp e, dept d
       where e.deptno = d.deptno;
  

• 练习二

  --  1.查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，
  ->  工资总和，平均工资。
      select d.deptno 部门编号,
             d.dname  部门名称,
             count(e.ename) 部门人数,
             max(e.sal) 最高工资,
             min(e.sal) 最低工资,
             sum(e.sal) 工资总和,
             avg(e.sal) 平均工资
        from emp e 
        right outer join dept d
          on (e.deptno = d.deptno)
       group by d.deptno, d.dname;
  
  --  2.查询每个部门，每个岗位的部门编号，部门名称，岗位名称，岗位人数，最高工资，
  ->  最低工资，工资总和，平均工资。
      select d.deptno 部门编号,
             d.dname  部门名称,
             e.job    岗位名称,
             count(e.job) 岗位人数,
             max(e.sal) 最高工资,
             min(e.sal) 最低工资,
             sum(e.sal) 工资总和,
             avg(e.sal) 平均工资
        from emp e
        full outer join dept d 
          on (e.deptno = d.deptno)
       group by d.deptno, d.dname, e.job
       order by d.deptno;
  
  --  3.查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息。
  ->   select count(e.ename) 经理管理人数,
             e.mgr 经理编号,
             mgr.ename 经理姓名
        from emp e, emp mgr
       where e.mgr = mgr.empno(+)
       group by e.mgr, mgr.ename;
  

• 练习三

  --  1.查询部门人数大于4的部门编号，部门名称，部门人数。
      select d.deptno 部门编号, d.dname 部门名称, count(e.ename) 部门人数
        from emp e, dept d
       where e.deptno(+) = d.deptno
       group by d.deptno, d.dname
      having count(e.ename) > 4;
  
  --  2.查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，
  ->  部门平均工资，并按照部门人数升序排序。
      select d.deptno 部门编号,
             d.dname 部门名称,
             count(e.ename) 部门人数,
             avg(e.sal) 部门平均工资
        from emp e, dept d
       where e.deptno(+) = d.deptno
       group by d.deptno, d.dname
      having avg(e.sal) > 2000
       order by count(e.ename);
  

– 课后作业

--  1.查询部门平均工资在2500元以上的部门名称及平均工资。
    select d.dname 部门名称, avg(e.sal) 平均工资
      from emp e, dept d
     where e.deptno(+) = d.deptno
     group by d.deptno, d.dname
    having avg(e.sal) > 2500;

--  2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，
->  并按平均工资降序排序。
    select job 岗位, avg(sal) 平均工资
      from emp
     where ename not like 'SA%'
     group by job
    having avg(sal) > 2500
     order by avg(sal) desc;

--  3.查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到
->  整数位。
    select d.dname 部门名称,
           round(min(e.sal)) 最低工资,
           round(max(e.sal)) 最高工资
      from emp e, dept d
     where e.deptno(+) = d.deptno
     group by d.deptno, d.dname
    having count(e.ename) > 2
     order by d.deptno;

--  4.查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。 
    select job 岗位, sum(sal) 工资和
      from emp
     group by job
    having job <> 'SALESMAN'
       and sum(sal) >= 2500;

--  5.显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，
->  不包括最低工资小于3000的，
->  按最低工资由高到低排序。
    select mgr.empno 经理号码,
           mgr.ename 经理姓名,
           min(e.sal) 下属员工最低工资
      from emp e, emp mgr
     where e.mgr = mgr.empno(+)
     group by mgr.empno, mgr.ename
    having min(e.sal) >= 3000
     order by min(e.sal) desc;

--  6.写一个查询，显示每个部门最高工资和最低工资的差额。
    select max(e.sal) 部门最高工资, 
           min(e.sal) 部门最低工资, 
           (nvl(max(e.sal) - min(e.sal), 0)) 最高与最低工资的差额
      from emp e, dept d
     where e.deptno(+) = d.deptno
     group by d.deptno
     order by d.deptno;