嵌套查询(子查询)
一、不相关嵌套查询
1.查询工资比 Jones高的员工信息
SELECT *FROM emp
WHERE sal>(SELECT sal FROM emp
WHERE ename='Jones')
2.查询和雇员7369从事相同工作并且薪水大于雇员7876的雇员的姓名和工作
SELECT ename,job FROM emp
WHERE job=(SELECT job FROM emp WHERE empno='7369')AND
sal>(SELECT sal FROM emp WHERE empno='7876')
3.在emp表中,查询出既不是最高工资,也不是最低工资的员工信息
SELECT * FROM emp WHERE sal>(SELECT MIN(sal) from emp ) AND
sal<(SELECT MAX(sal) FROM emp)
4.列出与10号部门员工职务相同的其它部门员工信息
SELECT * FROM emp WHERE job in
(SELECT job FROM emp WHERE deptno = '10')
AND deptno != '10'
5.查询比任意一个经理(manager)员工资高但又不是从事经理工作的雇员信息
SELECT * FROM emp WHERE sal>(SELECT MIN(sal) FROM emp WHERE job='manager')
and job != 'manger'
6.查询比所有经理(manager)员工资低但又不是从事经理工作的雇员
SELECT * FROM emp WHERE sal<(SELECT MIN(sal) FROM emp WHERE job='manager')
and job != 'manger
’
- 查询在纽约工作的员工信息.
SELECT *FROM emp WHERE deptno = (SELECT DEPTNO from dept WHERE loc='NEW YORK')
- 列出薪金高于公司平均薪金的所有员工。
SELECT ename FROM emp WHERE sal>(SELECT AVG(sal) from emp )
- 查询不是销售部门(SALES)的员工信息。
SELECT *FROM emp WHERE deptno!=(SELECT dname from dept WHERE dname = 'SALES')
10.列出与“SCOTT”从事相同工作的其他所有员工。(排除自己)
select * from emp where job=(select job from emp where ename='SCOTT') and ename<>'SCOTT '
11.列出薪金不等于部门20中员工的薪金的所有其他部门员工的姓名和薪金。(any的用法,且排除自己)
select ename,sal from emp where sal<>any(select sal from emp where deptno<>20) and deptno<>20
12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。?
select ename,sal from emp where sal>all(select sal from emp where deptno=30)
13.列出薪金高于在部门30工作的任意一个员工的薪金的员工姓名和薪金。
select ename,sal from emp where sal>any(select sal from emp where deptno=30)
二、相关嵌套查询
- 查询工资是本部门最高工资的员工信息。
SELECT * FROM emp e1
WHERE sal=(SELECT MAX(sal)FROM emp e2 WHERE e2.deptno =e1.deptno
)
- 在emp表中,使用“关联子查询”检索工资大于同职位的平均工资的员工信息
SELECT * FROM emp e1
WHERE sal >= (SELECT AVG(sal) FROM emp e2 WHERE job=e1.job)
ORDER BY job
- 查询每个部门"工龄"最短的员工信息,结果显示部门名、员工姓名、雇佣时间。
SELECT dname,ename,hiredate FROM emp e1 INNER JOIN dept d
On e1.deptno = d.deptno
WHERE hiredate = (SELECT MAX(hiredate) FROM emp e2 WHERE e1.deptno=e2.deptno)
- Exists引导的相关子查询(难点)
查询在纽约工作的员工信息