综合练习(二)

目录

列出薪金比 SMITH 或 ALLEN 多的所有员工的编号、姓名、部门名称、领导姓名、部门人数，以及所在部门的平均工资、最高和最低工资

补充

spool

---

Oracle从入门到总裁:https://blog.csdn.net/weixin_67859959/article/details/135209645

列出薪金比 SMITH 或 ALLEN 多的所有员工的编号、姓名、部门名称、领导姓名、部门人数，以及所在部门的平均工资、最高和最低工资

 确定要使用的数据表

emp 表：员工的编号、姓名

dept 表：部门名称

emp 表：领导姓名

emp 表：统计信息

确定已知的关联字段

员工与部门：emp.deptno=dept.deptno

员工与领导：emp.mgr=memp.empno

第一步：知道 SMITH 或 ALLEN 的薪金，这个查询返回多行单列（WHERE 中使用）。

SQL> select sal2  from emp3  where ename in('SMITH','ALLEN');SAL
----------8001600

第二步：应该比里面的任意一个多即可，但是要去掉这两个员工

由于是多行单列子查询，所以使用 >ANY 完成

SQL> select e.empno,e.ename,e.sal2  from emp e3  where e.sal>any(4  select sal5  from emp6      where ename in('SMITH','ALLEN'))7      and e.ename not in('SMITH','ALLEN');EMPNO ENAME                       SAL
---------- -------------------- ----------7839 KING                       50007902 FORD                       30007566 JONES                      29757698 BLAKE                      28507782 CLARK                      24507844 TURNER                     15007934 MILLER                     13007654 MARTIN                     12507521 WARD                       12507900 JAMES                       950已选择 10 行。

第三步：找到部门名称

SQL> select e.empno,e.ename,e.sal,d.dname2  from emp e,dept d3  where e.sal>any(4  select sal5  from emp6      where ename in('SMITH','ALLEN'))7      and e.ename not in('SMITH','ALLEN')8      and e.deptno=d.deptno;EMPNO ENAME                       SAL DNAME
---------- -------------------- ---------- ----------------------------7839 KING                       5000 ACCOUNTING7902 FORD                       3000 RESEARCH7566 JONES                      2975 RESEARCH7698 BLAKE                      2850 SALES7782 CLARK                      2450 ACCOUNTING7844 TURNER                     1500 SALES7934 MILLER                     1300 ACCOUNTING7521 WARD                       1250 SALES7654 MARTIN                     1250 SALES7900 JAMES                       950 SALES

第四步：找到领导信息

SQL> select e.empno,e.ename,e.sal,d.dname,m.ename2  from emp e,dept d,emp m3  where e.sal>any(4  select sal5  from emp6      where ename in('SMITH','ALLEN'))7      and e.ename not in('SMITH','ALLEN')8      and e.deptno=d.deptno9      and e.mgr=m.empno(+);EMPNO ENAME                       SAL DNAME                        ENAME
---------- -------------------- ---------- ---------------------------- --------------------7902 FORD                       3000 RESEARCH                     JONES7844 TURNER                     1500 SALES                        BLAKE7521 WARD                       1250 SALES                        BLAKE7654 MARTIN                     1250 SALES                        BLAKE7900 JAMES                       950 SALES                        BLAKE7934 MILLER                     1300 ACCOUNTING                   CLARK7566 JONES                      2975 RESEARCH                     KING7698 BLAKE                      2850 SALES                        KING7782 CLARK                      2450 ACCOUNTING                   KING7839 KING                       5000 ACCOUNTING已选择 10 行。

第五步：得到部门人数，以及部门平均工资、最高和最低工资

整个查询里面不能够直接使用GROUP BY，所以现在应该利用子查询实现统计操作

SQL> select e.empno,e.ename,e.sal,d.dname,m.ename,temp.count,temp.avg,temp.max,temp.min2  from emp e,dept d,emp m,(3  select deptno dno,count(empno) count,avg(sal) avg,max(sal) max,min(sal) min4      from emp5      group by deptno) temp6  where e.sal>any(7  select sal8  from emp9      where ename in('SMITH','ALLEN'))10      and e.ename not in('SMITH','ALLEN')11      and e.deptno=d.deptno12      and e.mgr=m.empno(+)13      and d.deptno=temp.dno;EMPNO ENAME                       SAL DNAME                        ENAME                     COUNT        AVG        MAX        MIN
---------- -------------------- ---------- ---------------------------- -------------------- ---------- ---------- ---------- ----------7902 FORD                       3000 RESEARCH                     JONES                         3 2258.33333       3000        8007844 TURNER                     1500 SALES                        BLAKE                         6 1566.66667       2850        9507654 MARTIN                     1250 SALES                        BLAKE                         6 1566.66667       2850        9507521 WARD                       1250 SALES                        BLAKE                         6 1566.66667       2850        9507900 JAMES                       950 SALES                        BLAKE                         6 1566.66667       2850        9507934 MILLER                     1300 ACCOUNTING                   CLARK                         3 2916.66667       5000       13007566 JONES                      2975 RESEARCH                     KING                          3 2258.33333       3000        8007698 BLAKE                      2850 SALES                        KING                          6 1566.66667       2850        9507782 CLARK                      2450 ACCOUNTING                   KING                          3 2916.66667       5000       13007839 KING                       5000 ACCOUNTING                                                 3 2916.66667       5000       1300已选择 10 行。

补充

spool

在数据库实际操作中，需要将查询结果输出到指定文件中，此时可以使用 spool 命令 

spool filename [create][replace][append]|OFF|OUT

其中

filename 为输出的文件名称，默认的扩展名为 LST，后面所跟参数作用如下

create 表示创建新的文件，是默认状态

replace 表示替代已经存在的文件

append 表示把查询内容附加到已经存在的文件中

OFF|OUT 表示关闭输出

例如:

下面代码将查询结果输出到 D 盘的 temp.ext 文件中 

SQL> spool d:\temp.txt
SQL> select empno,ename,job from emp;EMPNO ENAME                JOB
---------- -------------------- ------------------7369 SMITH                CLERK7499 ALLEN                SALESMAN7521 WARD                 SALESMAN7566 JONES                MANAGER7654 MARTIN               SALESMAN7698 BLAKE                MANAGER7782 CLARK                MANAGER7839 KING                 PRESIDENT7844 TURNER               SALESMAN7900 JAMES                CLERK7902 FORD                 ANALYSTEMPNO ENAME                JOB
---------- -------------------- ------------------7934 MILLER               CLERK8989 HELLO已选择 13 行。SQL> spool off

此时,我的D盘就有了这个文件,内容如下