第七章子查询课后练习和作业

– 练习

• 练习一

  --  1.查询入职日期最早的员工姓名，入职日期
      select ename 员工姓名, hiredate 入职日期
        from emp
       where hiredate = (select min(hiredate) from emp);
  
  --  2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称
      select e.ename 员工姓名, e.sal 工资, d.dname 部门名称
        from emp e, dept d
       where e.deptno = d.deptno
         and e.sal > (select e.sal from emp e where e.ename = 'SMITH')
         and d.loc = 'CHICAGO';
  
  --  3.查询入职日期比10部门入职日期最早的员工还要早的员工姓名，入职日期
      select ename 员工姓名, hiredate 入职日期
        from emp
       where hiredate < (select min(hiredate) from emp where deptno = 10);
  
  --  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) > (select avg(count(e.ename))
                                 from emp e, dept d
                                where e.deptno(+) = d.deptno
                                group by d.deptno);
  

• 练习二

  --  1.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工
      select ename 员工姓名, hiredate 入职日期
        from emp
       where hiredate > any(select hiredate from emp where deptno = 10)
         and deptno <> 10;
  
  --  2.查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工
      select ename 员工姓名, hiredate 入职日期
        from emp
       where hiredate > all(select hiredate from emp where deptno = 10)
         and deptno <> 10;
  
  --  3.查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工
      select ename 员工姓名, hiredate 入职日期
        from emp
       where job = any(select job from emp where deptno = 10)
         and deptno <> 10;
  

• 练习三

  --  1.查询职位及经理和10部门任意一个员工职位 !及! 经理相同的员工姓名，职位，
  ->  不包括10部门员工
      select ename 员工姓名, job 职位
        from emp
       where (job, mgr) in
             (select job, mgr from emp where deptno = 10)
         and deptno <> 10;
  
  --  2.查询职位及经理和10部门任意一个员工职位 !或! 经理相同的员工姓名，职位，
  ->  不包括10部门员工
      select ename 员工姓名, job 职位
        from emp
       where (job in
             (select job from emp where deptno = 10)
          or mgr in
             (select mgr from emp where deptno = 10))
         and deptno <> 10;
  

• 练习四

  --  1.查询比自己职位平均工资高的员工姓名、职位，部门名称，职位平均工资
      select e.ename  员工姓名,
             e.job    职位,
             d.dname  部门名称,
             a.avgsal 职位平均工资
        from emp e,
             dept d,
             (select e.job, avg(sal) avgsal from emp e group by e.job) a
       where e.deptno = d.deptno
         and e.job = a.job
         and e.sal > a.avgsal;
  
  --  2.查询职位和经理同员工SCOTT或BLAKE完全相同的员工姓名、职位，不包括SCOOT
  ->  和BLAKE本人。
      select *
        from emp e
       where (e.job, e.mgr) in
             (select e.job, e.mgr from emp e where e.ename in ('SCOTT', 'BLAKE'))
         and e.ename not in ('SCOTT', 'BLAKE');
  
  --  3.查询不是经理的员工姓名。
      select ename 员工姓名
        from emp
       where empno not in
             (select distinct mgr from emp where mgr is not null);
  

• 练习五

  --  1.查询入职日期最早的前5名员工姓名，入职日期。
      select rownum,e.ename 员工姓名, e.hiredate 入职日期
        from (select ename,hiredate from emp order by hiredate asc) e
       where rownum <= 5;
  
  --  2.查询工作在CHICAGO并且入职日期最早的前2名员工姓名，入职日期。
      select rownum, e.ename 员工姓名, e.hiredate 入职日期, e.loc
        from (select e.*,d.*
                from emp e, dept d
               where e.deptno = d.deptno
                 and d.loc = 'CHICAGO'
               order by hiredate asc) e
       where rownum <= 2;
  

• 练习六

  --  1.按照每页显示5条记录，分别查询第1页，第2页，第3页信息，要求显示员工姓名、
  ->  入职日期、部门名称。
      select e.rn       第1页,
             e.ename    员工姓名,
             e.hiredate 入职日期,
             e.dname    部门名称
        from (select rownum rn, e.*, d.*
                from emp e, dept d
               where e.deptno = d.deptno) e
       where e.rn <= 5;
  
      select e.rn       第2页,
             e.ename    员工姓名,
             e.hiredate 入职日期,
             e.dname    部门名称
        from (select rownum rn, e.*, d.*
                from emp e, dept d
               where e.deptno = d.deptno) e
       where e.rn > 5
         and rn <= 10;
  
      select e.rn       第3页,
             e.ename    员工姓名,
             e.hiredate 入职日期,
             e.dname    部门名称
        from (select rownum rn, e.*, d.*
                from emp e, dept d
               where e.deptno = d.deptno) e
       where e.rn > 10
         and rn <= 15;
  

• 练习七

  --  1.按照每页显示5条记录，分别查询工资最高的第1页，第2页，第3页信息，
  ->  要求显示员工姓名、入职日期、部门名称、工资。
      select e.rn       第1页,
             e.ename    员工姓名,
             e.hiredate 入职日期,
             e.dname    部门名称,
             e.sal      工资
        from (select rownum rn, ed.*
                from (select e.ename, e.hiredate, d.dname, e.sal
                        from emp e, dept d
                       where e.deptno = d.deptno
                       order by e.sal desc) ed
                where rownum <= 5) e;
  
      --  方法一，效率低
      select e.rn       第2页,
             e.ename    员工姓名,
             e.hiredate 入职日期,
             e.dname    部门名称,
             e.sal      工资
        from (select rownum rn, ed.*
                from (select e.ename, e.hiredate, d.dname, e.sal
                        from emp e, dept d
                       where e.deptno = d.deptno
                       order by e.sal desc) ed) e
       where e.rn > 5 and e.rn <= 10;
  
      --  方法二 ，效率高
      select e.rn       第2页,
             e.ename    员工姓名,
             e.hiredate 入职日期,
             e.dname    部门名称,
             e.sal      工资
        from (select rownum rn, ed.*
                from (select e.ename,
                             e.hiredate,
                             d.dname,
                             e.sal
                        from emp e, dept d
                       where e.deptno = d.deptno
                       order by e.sal desc) ed
               where rownum <= 10) e
       where e.rn > 5;
  
      select e.rn       第3页,
             e.ename    员工姓名,
             e.hiredate 入职日期,
             e.dname    部门名称,
             e.sal      工资
        from (select rownum rn, ed.*
                from (select e.ename,
                             e.hiredate,
                             d.dname,
                             e.sal
                        from emp e, dept d
                       where e.deptno = d.deptno
                       order by e.sal desc) ed
               where rownum <= 15) e
       where e.rn > 10;
  

– 课后作业

--  1.查询工资高于编号为7782的员工工资，并且和7566号员工从事相同工作的员工的编号、
->  姓名及工资。
    select empno 员工编号, ename 姓名, sal 工资
      from emp
     where sal > (select sal from emp where empno = 7782)
       and job = (select job from emp where empno = 7566)
       and empno <> 7566;

--  2.查询工资最高的员工姓名和工资。 
    select ename 员工姓名, sal 工资
      from emp
     where sal = (select max(sal) from emp);

--  3.查询部门最低工资高于20号部门最低工资的部门的编号、名称及部门最低工资。
    select d.deptno 部门编号,
           d.dname  部门名称,
           nvl(min(e.sal), 0) 部门最低工资
      from emp e, dept d
     where e.deptno(+) = d.deptno
     group by d.deptno, d.dname
    having nvl(min(e.sal), 0) > (select nvl(min(e.sal), 0)
                                   from emp e, dept d
                                  where e.deptno(+) = d.deptno
                                  group by d.deptno
                                 having d.deptno = 20);

--  4.查询员工工资为其部门最低工资的员工的编号和姓名及工资。
    select e.empno 员工编号, e.ename 姓名, e.sal 工资
      from emp e, dept d
     where e.deptno = d.deptno
     group by d.deptno, e.empno, e.ename, e.sal
    having(e.sal, d.deptno) in (select min(e.sal), e.deptno
                                  from emp e
                                 group by e.deptno);

--  5.显示经理是KING的员工姓名，工资。
    select e.ename 员工姓名, e.sal 工资
      from emp e, emp mgr
     where e.mgr = mgr.empno
       and mgr.ename = 'KING';

--  6.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。
    select e.ename 员工姓名, e.sal 工资, e.hiredate 参加工作时间
      from emp e
     where e.hiredate >
           (select hiredate from emp where ename = 'SMITH');

--  7.使用子查询的方式查询哪些职员在NEW YORK工作。
    select e.ename 员工姓名, d.loc 工作地点
      from emp e, dept d
     where e.deptno = d.deptno
       and d.loc = (select loc from dept where loc = 'NEW YORK');

--  8.写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇用日期，查询结果中排除SMITH。
    select ename 员工姓名, hiredate 雇佣日期
      from emp
     where deptno = (select deptno from emp where ename = 'SMITH')
       and ename <> 'SMITH';

--  9.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。
    select empno 员工编号, ename 姓名
      from emp
     where sal > (select avg(sal) from emp);

--  10.写一个查询显示其上级领导是King的员工姓名、工资。
    select e.ename 员工姓名, e.sal 工资
      from emp e join emp mgr
        on (e.mgr = mgr.empno)
     where mgr.ename = 'KING';

--  11.显示所有工作在RESEARCH部门的员工姓名，职位。
    select e.ename 员工姓名, e.job 职位
      from emp e, dept d
     where e.deptno = d.deptno
       and d.dname = 'RESEARCH';

--  12.查询每个部门的部门编号、平均工资，要求部门的平均工资高于部门10的平均工资。
    select d.deptno 部门编号, nvl(avg(e.sal), 0) 平均工资
      from emp e, dept d
     where e.deptno(+) = d.deptno 
     group by d.deptno
    having avg(e.sal) > (select avg(sal) from emp where deptno = 10);

--  13.查询大于自己部门平均工资的员工姓名，工资，所在部门平均工资，高于部门平均工资的额度。
    select e.ename 员工姓名,
           e.sal 工资,
           ed.avgsal 所在部门平均工资,
           (e.sal - ed.avgsal) 高与平均工资额度
      from emp e,
           (select deptno, avg(sal) avgsal from emp group by deptno) ed
     where e.deptno = ed.deptno
       and e.sal > ed.avgsal;

--  14.列出至少有一个雇员的所有部门
    select d.deptno 部门, count(e.ename) 部门人数
      from emp e, dept d
     where e.deptno(+) = d.deptno
     group by d.deptno
    having count(e.ename) >= 1;

--  15.列出薪金比"SMITH"多的所有雇员
    select ename 雇员姓名
      from emp
     where sal > (select sal from emp where ename = 'SMITH');

--  16.列出入职日期早于其直接上级的所有雇员
    select e.ename      雇员姓名,
           e.mgr        雇员经理,
           e.hiredate   雇员入职日期,
           mgr.hiredate 经理入职日期
      from emp e, emp mgr
     where e.mgr = mgr.empno(+)
       and e.hiredate < mgr.hiredate;

--  17.找员工姓名和直接上级的名字
    select e.ename 员工姓名, mgr.ename 上级名字
      from emp e, emp mgr
     where e.mgr = mgr.empno;

--  18.显示部门名称和人数
    select d.deptno 部门名称, count(e.ename) 部门人数
      from emp e, dept d
     where e.deptno(+) = d.deptno
     group by d.deptno;

--  19.显示每个部门的最高工资的员工
    select deptno 部门, ename 员工姓名
      from emp
     where sal in (select max(sal) from emp group by deptno);

--  20.显示出和员工号7369部门相同的员工姓名，工资
    select ename 员工姓名, sal 工资
      from emp
     where deptno = (select deptno from emp where empno = 7369)
       and empno <> 7369;

--  21.显示出和姓名中包含"W"的员工相同部门的员工姓名
    select ename 员工姓名
      from emp 
     where deptno = (select deptno from emp where ename like '%W%');

--  22.显示出工资大于平均工资的员工姓名，工资
    select ename 员工姓名, sal 工资
      from emp
     where sal > (select avg(sal) from emp);

--  23.显示出工资大于本部门平均工资的员工姓名，工资
    select e.ename 员工姓名, e.sal 工资,a.avgsal
      from emp e,
           (select deptno,avg(sal) avgsal from emp group by deptno) a
     where e.deptno = a.deptno
       and e.sal > a.avgsal;

--  24.显示每位经理管理员工的最低工资，及最低工资者的姓名
    select e.sal 最低工资, e.ename 姓名,e.mgr
      from emp e
     where (e.mgr,e.sal) in (select e.mgr, min(e.sal)
                               from emp e
                               group by e.mgr);

--  25.显示比工资最高的员工参加工作时间晚的员工姓名，参加工作时间
    select ename 员工姓名, hiredate 参加工作时间
      from emp
     where hiredate <
           (select hiredate
              from emp
             where sal = (select max(sal) from emp));

--  26.显示出平均工资最高的的部门平均工资及部门名称
    select avg(e.sal) 部门平均工资, d.dname 部门名称
      from emp e, dept d
     where e.deptno = d.deptno
     group by d.deptno,d.dname
    having avg(e.sal) = (select max(avg(sal)) from emp group by deptno);