综合练习
1.列出薪金高于部门30工作的所有员工的薪金的员工姓名和薪金、部门名称、部门人数。
确定要使用的数据表：
emp:姓名、薪金
dept:部门名称
emp:部门人数
确定已知关键字：雇员和部门
e.deptno = d.deptno;
①找出部门30工作的所有员工的薪金
select sal
from emp e
where e.deptno=30;
②如果返回的结果是多行单列，应该在where里面使用子查询语句，判断词有(in、any、all)
员工姓名和薪金
select e.ename,e.sal
from emp e
where sal>all(select sal
from emp e
where e.deptno=30);
③部门名称
select e.ename,e.sal,d.dname
from emp e,dept d
where sal>all(select sal
from emp e
where e.deptno=30) and e.deptno=d.deptno;
④部门人数
select deptno dno,count(empno)
from emp
group by deptno;
select e.ename,e.sal,d.dname,temp.count
from emp e,dept d,(
select deptno dno,count(empno) count
from emp
group by deptno
)temp
where sal>all(select sal
from emp e
where e.deptno=30)
and e.deptno=d.deptno
and temp.dno=d.deptno;
chaxun.png
2.列出与scott从事相同工作的所有员工及部门名称，部门人数，及领导姓名。
确定相关联的表：
emp表：员工信息;
dept表：部门名称；
emp表：统计部门人数；
emp表：领导姓名
相关联的字段：
雇员和部门：e.deptno=d.deptno
雇员和领导：emp.mgr = memp.empno
①列出从事scott工作的员工，此查询返回单行单列，一般用where或having条件
select job from emp where ename='SCOTT';
scott.png
②找到所有符合此要求的雇员信息
select e.ename,e.job,e.sal
from emp e
where e.job=(
select job
from emp
where ename='SCOTT'
);
③部门名称
select e.ename,e.job,e.sal,d.dname
from emp e,dept d
where e.job=(
select job
from emp
where ename='SCOTT'
and e.deptno=d.deptno);
④领导姓名
select e.ename,e.job,e.sal,d.dname,temp.count,
m.ename
from emp e,dept d,(
select deptno dno,count(empno) count
from emp
group by deptno)temp,emp m
where e.job=(
select job
from emp
where ename='SCOTT')
and e.deptno=d.deptno
and d.deptno=temp.dno
and e.mgr=m.empno;
⑤消除掉scott的用户
select e.ename,e.job,e.sal,d.dname,temp.count,
m.ename
from emp e,dept d,(
select deptno dno,count(empno) count
from emp
group by deptno)temp,emp m
where e.job=(
select job
from emp
where ename='SCOTT')
and e.deptno=d.deptno
and d.deptno=temp.dno
and e.mgr=m.empno
and e.ename<>'SCOTT';
scott.png
3.列出薪金比“SMITH”或“ALLEN”多的所有员工的编号，姓名，部门名称，其领导姓名，部门人数，平均工资、最高及最低工资。
确定相关表：
emp:员工的编号，姓名；
dept：部门名称；
emp：领导信息；
emp:统计部门数据
相关字段：
雇员和部门：e.deptno=d.deptno
雇员和领导：emp.mgr = memp.empno
①列出"SMITH"或''ALLEN''
select sal
from emp
where ename in ('SMITH','ALLEN');
②以上查询返回的是多行单列数据，应该在where字句中使用它。列出薪金比“SMITH”或“ALLEN”多的所有员工
select e.empno,e.ename,e.sal
from emp e
where e.sal>any(
select sal
from emp
where ename in('SMITH','ALLEN'))
and ename not in('SMITH','ALLEN');
③找到领导信息
select e.empno,e.ename,e.sal,m.ename
from emp e,emp m
where e.sal>any(
select sal
from emp
where ename in('SMITH','ALLEN'))
and ename not in('SMITH','ALLEN')
and e.mgr=m.empno;
④部门人数，平均工资、最高及最低工资。
select e.empno,e.ename,e.sal,m.ename,temp.count,
temp.avg,temp.max,temp.min,d.dname
from emp e,emp m,(
select deptno dno,count(empno)count,
avg(sal) avg,max(sal) max,min (sal) min
from emp
group by deptno) temp,dept d
where e.sal>any(
select sal
from emp
where
ename in('SMITH','ALLEN'))
and e.ename not in('SMITH','ALLEN')
and e.mgr=m.empno(+)
and temp.dno=d.deptno
and e.deptno=d.deptno;
avg.png
4.列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。
确定要使用的数据表：
emp:员工的编号、姓名
dept:部门名称、部门位置
emp:部门人数
emp:找到领导雇用日期，作为自身关联使用
确定已知的关联字段：
雇员和领导：emp.mgr = memp.empno
雇员和部门：emp.deptno = dept.deptno
①列出受雇日期早于其直接上级的所有员工的编号、姓名
select e.empno,e.ename
from emp e,emp m
where e.mgr = m.empno(+) and e.hiredate
< m.hiredate;
②部门名称、部门位置
select e.empno,e.ename,d.dname,d.loc
from emp e,emp m,dept d
where e.mgr = m.empno(+)
and e.hiredate < m.hiredate
and e.deptno = d.deptno;
③部门人数
select e.empno,e.ename,d.dname,d.loc,temp.count
from emp e,emp m,dept d,(
select deptno dno,count(empno) count
from emp
group by deptno)temp
where e.mgr = m.empno(+)
and e.hiredate < m.hiredate
and e.deptno = d.deptno
and d.deptno = temp.dno;
图片.png
