Oracle修炼路程--查询习题

第一题 ：查询所有员工的年工资、所在部门的名称，按年薪从低往高排序；

SQL> select (sal*12+nvl(comm,0)) 年工资,d.dname 部门名称 from emp e,dept d where e.deptno=d.deptno order by 年工资 asc;

    年工资 部门名称
---------- --------------
     14400 RESEARCH
     16200 SALES
     18000 RESEARCH
     20300 SALES
     20400 ACCOUNTING
     21200 OPERATIONS
     22800 SALES
     23400 RESEARCH
     24300 SALES
     40800 RESEARCH
     40800 RESEARCH
     48600 ACCOUNTING
     53400 SALES
     54900 RESEARCH
     84000 ACCOUNTING

已选择15行。

第二题 ：查询所有员工的编号、姓名，及其上级领导编号、姓名，显示结果按领导的年薪降序；

SQL> select e.empno 员工编号,e.ename 员工姓名,d.empno 领导编号,d.ename 领导姓名 from emp e，emp d where e.mgr=d.empno(+) order by d.sal*12+nvl(d.comm,
0) desc;

  员工编号 员工姓名     领导编号 领导姓名
---------- ---------- ---------- ----------
      7839 KING
      7782 CLARK            7839 KING
      7698 BLAKE            7839 KING
      7566 JONES            7839 KING
      7902 FORD             7566 JONES
      7788 SCOTT            7566 JONES
      7521 WARD             7698 BLAKE
      7654 MARTIN           7698 BLAKE
      7844 TURNER           7698 BLAKE
      7900 JAMES            7698 BLAKE
      7499 ALLEN            7698 BLAKE
      7934 MILLER           7782 CLARK
      7369 SMITH            7902 FORD
      7876 ADAMS            7788 SCOTT
      5741 QIN_JIALI        7654 MARTIN

已选择15行。

第三题 ：查询非销售人员：工作名称，以及从事同一工作员工的月工资之和，要求月工资之和大于5000，输出结果按月工资之和降序排列；

SQL> select job,sum(sal) from emp where job<> 'SALESMAN' group by job having sum(sal)>5000 order by sum(sal) desc;

JOB         SUM(SAL)
--------- ----------
MANAGER        13075
CLERK           7650
PRESIDENT       7000
ANALYST         6800

第四题 ：查询所有领取奖金和不领取奖金的员工人数、平均工资

SQL> select count(*),avg(sal) from emp where comm is not null and comm>0
  2  union
  3  select count(*),avg(sal) from emp where comm is null or comm=0;

  COUNT(*)   AVG(SAL)
---------- ----------
         4       1800
        11 3138.63636

本题涉及集合运算：
前提：各个集合的列数、类型必须一致。

union（并集）：返回各个查询的所有记录，不包括重复记录。
unionall（并集）：返回各个查询的所有记录，包括重复记录。
intersect（交集）：返回两个查询的共有的记录。
minus（补集）：返回包含在第一个查询中，但不包含在第二个查询中的记录。

–报表：查询总工资、各个部门的总工资、各个部门中各个工作的总工资；

各个部门中各个工作的总工资：

SQL> select deptno,job,sum(sal) from emp group by deptno,job;

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        20 CLERK           4600
        30 SALESMAN        5550
        20 MANAGER         4575
        30 CLERK           1350
        10 PRESIDENT       7000
        40 SALESMAN        1650
        30 MANAGER         4450
        10 CLERK           1700
        10 MANAGER         4050
        20 ANALYST         6800

已选择10行。

各个部门的总工资

SQL> select deptno,NULL,sum(sal) from emp group by deptno;

    DEPTNO N   SUM(SAL)
---------- - ----------
        30        11350
        20        15975
        40         1650
        10        12750

查询总工资：

SQL> select NULL,NULL,sum(sal) from emp;

N N   SUM(SAL)
- - ----------
         41725

求并集:

SQL> select deptno,job,sum(sal) from emp group by deptno,job
  2  union
  3  select deptno,NULL,sum(sal) from emp group by deptno
  4  union
  5  select NULL,NULL,sum(sal) from emp;

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 CLERK           1700
        10 MANAGER         4050
        10 PRESIDENT       7000
        10                12750
        20 ANALYST         6800
        20 CLERK           4600
        20 MANAGER         4575
        20                15975
        30 CLERK           1350
        30 MANAGER         4450
        30 SALESMAN        5550
        30                11350
        40 SALESMAN        1650
        40                 1650
                          41725

已选择15行。

增强group by:rollup()函数

用法：如果是group by rollup(A, B, C)
首先,对(A、B、C)进行group by
其次，对(A、B)进行group by
最后，对（A）进行GROUP BY

SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 CLERK           1700
        10 MANAGER         4050
        10 PRESIDENT       7000
        10                12750
        20 CLERK           4600
        20 ANALYST         6800
        20 MANAGER         4575
        20                15975
        30 CLERK           1350
        30 MANAGER         4450
        30 SALESMAN        5550
        30                11350
        40 SALESMAN        1650
        40                 1650
                          41725

已选择15行。

第五题 ：查询每种工作的最低工资，以及领取该工资的员工姓名。

SQL> select e.ename,d.minsal,e.job from emp e,(select min(sal) minsal,job from emp group by job) d where d.minsal=e.sal and d.job=e.job;

ENAME          MINSAL JOB
---------- ---------- ---------
SMITH            1200 CLERK
WARD             1650 SALESMAN
MARTIN           1650 SALESMAN
CLARK            4050 MANAGER
SCOTT            3400 ANALYST
KING             7000 PRESIDENT
FORD             3400 ANALYST

已选择7行。

第六题 ：查询出工资不超过2500的人数最多的部门名称；

SQL> select d.dname,d.deptno from dept d,emp e where d.deptno=e.deptno and e.sal<=2500 group by d.deptno,d.dname having count(*)=(select max(count(*))
 from emp where sal<=2500 group by deptno);

DNAME              DEPTNO
-------------- ----------
SALES                  30

–难度系数比较高；
第七题 ：查询出管理员工人数最多的人的名字和他管理的人的名字；

分析过程：

1 每个领导管理的人数：

SQL> select mgr,count(*) from emp group by mgr;

       MGR   COUNT(*)
---------- ----------
      7839          3
                    1
      7782          1
      7698          5
      7902          1
      7654          1
      7566          2
      7788          1

已选择8行。

2 所有领导中，管理人数最多是多少？

SQL> select max(cn) from (select mgr,count(*) cn from emp group by mgr);

   MAX(CN)
----------
         5

3 查询出管理人数最多的领导编号

SQL> select mgr from emp group by mgr having count(*)=(select max(cn) from (select mgr,count(*) cn from emp group by mgr));

       MGR
----------
      7698

4 根据领导编号，查询领导名字；

SQL> select d.ename,e.ename from emp e,emp d where e.deptno=d.deptno and d.empno=(select mgr from emp group by mgr having count(*)=(select max(cn) fro
m (select mgr,count(*) cn from emp group by mgr))) ;

ENAME      ENAME
---------- ----------
BLAKE      ALLEN
BLAKE      WARD
BLAKE      BLAKE
BLAKE      TURNER
BLAKE      JAMES

第八题： 统计各个年份的入职人数，以及总入职人数；

本题涉及到了decode函数：
格式：decode(条件,值1,翻译值1,值2,翻译值2,…值n,翻译值n,缺省值)；
该函数的含义如下：
IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
…
ELSIF 条件=值n THEN
RETURN(翻译值n)

ELSE
RETURN(缺省值)
END IF

SQL> select count(*) 总人数,sum(decode(to_char(hiredate,'yyyy'),1980,1,0)) "1980",sum(decode(to_char(hiredate,'yyyy'),1987,1,0)) "1987",
  2  sum(decode(to_char(hiredate,'yyyy'),1982,1,0)) "1982",sum(decode(to_char(hiredate,'yyyy'),1985,1,0)) "1985",
  3  sum(decode(to_char(hiredate,'yyyy'),1981,1,0)) "1981" from emp;

    总人数       1980       1987       1982       1985       1981
---------- ---------- ---------- ---------- ---------- ----------
        15          1          2          1          1         10