Oracle数据库复杂查询

第一题：列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。
select * from dept;

select d.deptno,d.dname,count(empno),avg(sal),min(sal),max(sal)
from emp e,dept d
where d.deptno=e.deptno
group by d.deptno,d.dname
having count(empno)>1;

group by 子句：

在前面的操作中，都是对表中的每一行数据进行单独的操作。

在有些情况下，需要把一个表中的行分为多个组，然后将这个作为一个整体，获得改组的一些信息，

例如获取部门编号为10的员工人数，

where：

select deptno,count(empno)
from emp
where deptno=10
group by deptno

having：

select deptno,count(empno)
from emp
having deptno=10
group by deptno

注意：having 和group by的位置交换后也是能执行出来的。

或者某个部门的员工的平均工资等，就需要使用group by子句对表中的数据进行分组。

select deptno,avg(sal)
from emp
group by deptno
having deptno=10

注意：

使用group by子句，可以根据表中的某一列或某几列对表中的数据进行分组，多个列之间使用逗号隔开。如果根据多个列进行分组，oracle会首先根据第一列进行分组，然后在分出来的组中在按照第二列进行分组。

SQL> select deptno as “部门编号”,count(*) as “员工人数”,job as “工作”
2 from emp
3 group by deptno,job
4 order by deptno
5 ;
部门编号 员工人数 工作

---

  10          1 CLERK
  10          1 MANAGER
  10          1 PRESIDENT
  20          2 ANALYST
  20          2 CLERK
  20          1 MANAGER
  30          1 CLERK
  30          1 MANAGER
  30          4 SALESMAN

9 rows selected

having子句：

1、having子句通常与group by 子句一起使用，在完成对分组结果的统计后，

可以使用having子句对分组的结果进行一步筛选。

2、如果在select语句中使用group by 子句，那么having子句将应用股group by子句创建的组；

 如果制定了where子句，而没有指定group by子句，那么having子句将应用于where子句的输出,并且这个输出被看做是一个组;

3、如果在select语句中既没指定where子句，也没有制定group by子句，那么having子句将应用from子句的输出，并且将这个输出看做一个组。

理解having子句的最好的方法就是记住select语句中的子句的处理次序：

1、where子句只能接受from子句输出的数据；

2、havi–例7.13在前面例7、11的语句中，添加having祖居，指定调教为员工人数大于3如下：
select deptno as “部门编号”,count(*) as “员工人数”
from emp
group by deptno
having count(*)>3ng子句则可以接受来自group by，where，from子句输出的数据。

提示：

如果不使用group by子句，那么having子句的功能与where子句一样，都是定义搜索条件，但是having子句的搜索条件与组有关，而不是与单个的行有关。

第二题： 列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名。

–第一步：找出“SMITH”或“ALLEN”的工资
select ename,sal
from emp
where ename in(‘SMITH’,’ALLEN’);
select ename,sal
from emp
where ename=’SMITH’ or ename=’ALLEN’;
–第二步：以上的查询返回的多行单列的记录，按照子查询的要求在WHERE子句中写合适，
–所以这个时候将上面的查询作为一个子查询出现，继续查询符合此要求的员工的编号、姓名。
select e.empno,e.ename,sal
from emp e
where sal> any( select sal from emp where ename=’SMITH’ or ename=’ALLEN’ )
–any:大于最小值
–all大于最大值

select * from emp where sal>1600
–第三步：查询出部门的名称，引入部门表，同时增加消除笛卡尔积的条件
select e.empno,e.ename,sal,d.dname
from emp e,dept d
where sal> any( select sal from emp where ename=’SMITH’ or ename=’ALLEN’ ) and e.deptno=e.deptno
–第四步：领导的信息需要emp表自身关联
select e.empno,e.ename,e.sal,d.dname,m.ename
from emp e,dept d,emp m
where e.sal>any(select sal from emp where ename=’SMITH’ or ename=’ALLEN’) and e.deptno=e.deptno and
e.mgr=m.empno

any：处理select返回的多个值

–例8.7对scott用户的emp表进行操作，获得工资大于任意一个部门的平均工资的员工信息，如下：

–第一步：获取每一个部门的平均工资

select deptno,avg(sal)
from emp
group by deptno

–第二步：用any处理上个查询出来的多个平均值

select *
from emp
where sal>any(select avg(sal)
from emp
group by deptno)

–例8.8对scott用户的emp表进行操作，获得工资大于所用部门的平均工资的员工
select *
from emp
where sal>all(select avg(sal)
from emp
group by deptno)

–获取员工和员工老板的名字
select e.empno,e.ename||’ 的老板是 ‘||m.ename
from emp e,emp m
where
e.mgr=m.empno

select empno
from emp
where ename=’JONES’

select empno
from emp
where ename=’KING’

select * from emp;

仔细观察这个表：

第三题：列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列。
select e.empno,e.ename,m.deptno,m.ename,12*(m.sal+nvl(m.comm,0))
–员工，老板
from emp e,emp m
–员工的老板的编号=老板的编号
where e.mgr=m.empno(+)
–KING这个员工没老板
order by 12*(m.sal) desc;

SELECT e.empno,e.ename,m.empno,m.ename,(m.sal+NVL(m.comm,0))*12 income
FROM emp e,emp m
WHERE e.mgr=m.empno(+)
ORDER BY income DESC;

select * from emp;

–左连接：是在检索结果中除了显示满足连接条件的行外，还显示JOIN关键字左侧表中所有满足检索条件的行。
–例8.26使用做外链接，检索emp表和salgrade表，获取员工的工资等级。
–为了观察做外链接的执行效果，首先使用insert语句向emp 表中添加一些记录，其中sal列的值需要小于700或者
–大于9999，也就是不在工资的等级范围内。
insert into emp values(7937,’Candy’,null,null,null,500,null,null);
select * from emp;
–下面使用左连接
select e.empno,e.ename,e.sal,d.grade
from emp e,salgrade d
where e.sal between d.losal and d.hisal(+)

select empno,sal from emp;

–使用右外连接，检索emp表和dept表中所包含的部门编号
select distinct e.deptno as “emp表”,d.deptno as “deptno表”
from emp e,dept d
where e.deptno(+)=d.deptno

dept表：

emp表：

注意：dept表中定义全部的部门号，当一个部门有人了才记录到emp表中。

想显示全部的dept，就在缺失的表上加上+号。

–3、 列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列。
select e.empno,e.ename,m.deptno,m.ename,12*(m.sal+nvl(m.comm,0))
–员工，老板
from emp e,emp m
–员工的老板的编号=老板的编号
where e.mgr=m.empno(+)–老板
–KING这个员工没老板
order by 12*(m.sal) desc;
–想显示所有的员工，必须在缺失的一个表上加+号
–KING 的e.mgr为空 where e.mgr=m.empno(+)–老板

第四题：列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。

–第一步：列出受雇日期早于其直接上级的所有员工的编号、姓名 —— 自身关联emp表。
select e.empno,e.ename,m.ename
from emp e,emp m
where e.mgr=m.empno(+) and e.hiredate