实例1:列出薪资高于在部门30工作的所有员工的薪资的员工姓名和薪资,部门名称,部门人数。
-emp 表:姓名,薪资,部门人数;
-dept 表:部门名称;
第一步:找出30部门的所有雇员的工资,返回多行单列;
select sal from emp where deptno=30;
第二步:返回多行单列,只能在where子句里面使用子查询(in,any, all),应该使用>all ,找到员工的姓名,薪资;
select e.ename, e.sal
from emp e
where sal>all(
select sal
from emp
where deptno=30 );
第三步:还需查找到部门信息,,在from子句之后引入dept表(一定存在关联字段或关联条件);
select e.ename, e.sal ,d.dname
from emp e, dept d
where sal>all(
select sal
from emp
where deptno=30 )
AND e.deptno=d.deptno ;
第四步:随后要统计部门人数
select deptno dno , count(empno) count
from emp
group by deptno;
第五步:以上查询返回的是多行多列的数据,按照使用原则应该在from子句中使用。
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
where deptno=30 )
AND e.deptno=d.deptno
AND temp.dno=d.deptno ;
实例2:列出与“SCOTT”从事相同工作的所有员工及部门名称,部门人数,领导姓名。
第一步:找到scott的工作,返回单行单列,一般用于where或having 上,此处没有统计需要,所以只在where子句上使用;
select job from emp where ename='SCOTT';
第二步:找到符合此要求的雇员信息;
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
from emp e, dept d,(
select deptno dno,count(empno) count
from emp
group by deptno) temp
where e.job=(
select job from emp where ename='SCOTT')
AND e.deptno=d.deptno
AND d.deptno=temp.dno;
第五步:查找出雇员对应的领导信息,使用emp,自身关联操作;
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';
实例3:列出所有“CLERK”(办事员)的姓名及其部门名称。部门人数,工资等级。
第一步:找到所有办事员的姓名;
select e.ename from emp e where e.job='CLERK';
第二步:找到部门名称;
select e.ename,d.dname
from emp e ,dept d
where e.job='CLERK'
AND e.edptno=d.deptno;
第三步:统计部门人数;
select e.ename,d.dname,temp.count
from emp e ,dept d,(
select deptnno dno,count(empno) count
from emp
group by deptno) temp
where e.job='CLERK'
AND e.edptno=d.deptno
AND d.deptno=temp.dno;
第四步:查询工资等级;
select e.ename,d.dname,temp.count,s.grade
from emp e ,dept d,(
select deptnno dno,count(empno) count
from emp
group by deptno) temp,salgrade s
where e.job='CLERK'
AND e.edptno=d.deptno
AND d.deptno=temp.dno
AND e.sal BETWEEN s.losal AND s.hisal;