综合练习(一)

列出薪金高于部门 30 的所有员工薪金的员工姓名和薪金、部门名称、部门人数

列出与 ALLEN从事相同工作的所有员工及他们的部门名称、部门人数、领导姓名

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

列出薪金高于部门 30 的所有员工薪金的员工姓名和薪金、部门名称、部门人数

● 确定要使用的数据表

emp 表：员工姓名和薪金。

dept 表：部门名称。

emp 表：统计出部门人数。

● 确定已知的关联字段

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

第一步：找到部门 30 所有员工的薪金

SQL> select sal2  from emp3  where deptno=30;SAL
----------16001250125028501500950已选择 6 行。

第二步：以上查询中返回的是多行单列的数据，

那么此时就可以使用 3 种判断符判断：IN、ANY、ALL。

根据要求需要找到所有员工，因此使用“>ALL”。

SQL> select e.ename,e.sal2  from emp e3  where e.sal > all(4      select sal from emp where deptno=30);ENAME                       SAL
-------------------- ----------
JONES                      2975
FORD                       3000
KING                       5000

第三步：要找到部门的信息，自然在 from子句之后引入 dept 表，而后要消除笛卡尔积。用内连接

SQL> select e.ename,e.sal,d.dname2  from emp e,dept d3  where e.sal > all(4      select sal from emp where deptno=30)5      and e.deptno=d.deptno;ENAME                       SAL DNAME
-------------------- ---------- ----------------------------
JONES                      2975 RESEARCH
FORD                       3000 RESEARCH
KING                       5000 ACCOUNTING

第四步：需要统计出部门人数的信息

思考如下。

如果要进行部门的人数统计，那么一定要按照部门分组

在使用分组的时候，SELECT 子句只能够出现分组字段与统计函数

此时就出现了一个矛盾，因为 select 子句里面有其他字段，所以不可能直接使用 group by 分组，所以可以考虑利用子查询分组，即在 from子句之后使用子查询先进行分组统计，而后将临时表继续采用多表查询操作

select e.ename,e.sal,d.dname,temp.count
from emp e,dept d,(select deptno dno,count(empno) countfrom empgroup by deptno) temp
where e.sal > all(select sal from emp where deptno=30)and e.deptno=d.deptnoand d.deptno=temp.dno;

结果如下

SQL> select e.ename,e.sal,d.dname,temp.count2  from emp e,dept d,(3  select deptno dno,count(empno) count4      from emp5      group by deptno) temp6  where e.sal > all(7      select sal from emp where deptno=30)8      and e.deptno=d.deptno9      and d.deptno=temp.dno;ENAME                       SAL DNAME                             COUNT
-------------------- ---------- ---------------------------- ----------
FORD                       3000 RESEARCH                              3
JONES                      2975 RESEARCH                              3
KING                       5000 ACCOUNTING                            3

列出与 ALLEN从事相同工作的所有员工及他们的部门名称、部门人数、领导姓名

确定要使用的数据表

emp 表：员工信息

dept 表：部门名称

emp 表：领导信息

确定已知的关联字段

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

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

第一步：没有 ALLEN的工作就无法知道哪个员工满足条件，需要找到 ALLEN的工作

SQL> select job2  from emp3  where ename='ALLEN';JOB
------------------
SALESMAN

第二步：以上的查询返回的是单行单列，所以只能够在 where子句或者是 having子句中使用，根据现在的需求需在 where子句中使用，对所有的员工信息进行筛选

SQL> select e.empno,e.ename,e.job2  from emp e3  where job=(4  select job from emp where ename='ALLEN');EMPNO ENAME                JOB
---------- -------------------- ------------------7499 ALLEN                SALESMAN7521 WARD                 SALESMAN7654 MARTIN               SALESMAN7844 TURNER               SALESMAN

第三步：如果不需要重复信息，可以删除 ALLEN

SQL> select e.empno,e.ename,e.job2  from emp e3  where job=(4  select job from emp where ename='ALLEN')5  and e.ename<>'ALLEN';EMPNO ENAME                JOB
---------- -------------------- ------------------7521 WARD                 SALESMAN7654 MARTIN               SALESMAN7844 TURNER               SALESMAN

第四步：部门名称只需要加入 dept 表即可

SQL> select e.empno,e.ename,e.job,d.dname2  from emp e,dept d3  where job=(4  select job from emp where ename='ALLEN')5  and e.ename<>'ALLEN'6  and e.deptno=d.deptno;EMPNO ENAME                JOB                DNAME
---------- -------------------- ------------------ ----------------------------7521 WARD                 SALESMAN           SALES7654 MARTIN               SALESMAN           SALES7844 TURNER               SALESMAN           SALES

第五步：此时的查询不可能直接使用 group by 进行分组，所以需要使用子查询实现分组

SQL> select e.empno,e.ename,e.job,d.dname,temp.count2  from emp e,dept d,(3  select deptno dno,count(empno) count4  from emp5  group by deptno) temp6  where job=(7  select job from emp where ename='ALLEN')8  and e.ename<>'ALLEN'9  and e.deptno=d.deptno10  and d.deptno=temp.dno;EMPNO ENAME                JOB                DNAME                             COUNT
---------- -------------------- ------------------ ---------------------------- ----------7521 WARD                 SALESMAN           SALES                                 67654 MARTIN               SALESMAN           SALES                                 67844 TURNER               SALESMAN           SALES                                 6

第六步：找到对应的领导信息，直接使用自身关联

SQL> select e.empno,e.ename,e.job,d.dname,temp.count,m.ename2  from emp e,dept d,(3  select deptno dno,count(empno) count4  from emp5  group by deptno) temp,emp m6  where e.job=(7  select job from emp where ename='ALLEN')8  and e.ename<>'ALLEN'9  and e.deptno=d.deptno10  and d.deptno=temp.dno11  and e.mgr=m.empno;EMPNO ENAME                JOB                DNAME                             COUNT ENAME
---------- -------------------- ------------------ ---------------------------- ---------- --------------------7654 MARTIN               SALESMAN           SALES                                 6 BLAKE7521 WARD                 SALESMAN           SALES                                 6 BLAKE7844 TURNER               SALESMAN           SALES                                 6 BLAKE