本学习笔记由本人整理而成,转载请注明出处
———————————————————————————
####【子查询】
###子查询在where子句中
##查找和SCOTT同职位的员工
select a.ename,a.job from emp a where a.job=(select job from emp where name='SCOTT');
##查询薪水比整个机构平均薪水高的员工
select deptno,ename,sal from emp where sal> (select avg(sal) from emp);
##【<>等价于!=】查询出部门中有SALESMAN但职位不是salesman的员工信息
select empno,ename,job,sal,deptno from emp where deptno in (select deptno from emp where job='SALESMAN') and job <> 'SALESMAN';
##【exists】列出有员工的部门信息
select deptno,dname from dept d
where exists
(select *from emp e
where d.deptno=e.deptno);
###子查询在having子句中
##查询列出最低薪水高于部门30的最低薪水的部门信息
select deptno,min(sal)from emp group by deptno having min(sal)>(select min(sal) from emp where deptno=30);
###子查询在from子句中
##查询薪水比本部门平均薪水高的员工信息
select e.deptno,e.ename,e.sal from emp e,(select deptno,avg(sal) avgsal from emp group by deptno)x where e.deptno=x.deptno and e.sal>x.avgsal order by e.deptno;
###子查询在select部分
select e.ename,e.sal,
(select d.deptno from dept d where d.deptno=e.deptno)deptno
from emp e;
——————————————————————————
###【子查询综合示例】
##查询哪个部门的平均工资是最高的,列出部门编码、平均工资
select deptno,avg(sal)from emp group by deptno
having avg(sal)=(select max(avg(sal)) from emp group by deptno);
##列出各个部门中工资最高的员工信息:名字、部门号、工资
select ename,deptno,sal from emp
where (deptno,sal)in (select deptno,max(sal) sal from emp group by deptno);
##查询管理者是‘KING’的员工姓名和工资
select ename,sal from emp where mgr=(select empno from emp where ename='KING');
##查询部门所在地(loc)为“NEW YORK”的部门的员工的姓名(ename)、部门名称(dname)和岗位名称(job)
select e.ename,d.dname,e.job from emp e join dept d on e.deptno=d.deptno where d.loc='NEW YORK';
或:
select e.ename,d.dname,e.job from emp e join dept d on e.deptno=d.deptno
where e.deptno=(select deptno from dept where loc='NEW YORK');
##查询工资比公司平均工资高的所有员工的员工号(empno)、姓名(ename)、工资ÿ