1.找出工资高于SMITH的员工姓名,工资
1)查询工资高于XXX的员工姓名和薪资
SELECT ENAME,SAL FROM EMP WHERE SAL > XXX;
2)查询SMITH的薪资XXX
SELECT SAL FROM EMP WHERE ENAME = 'SMITH';
3) 拼接
SELECT ENAME,SAL FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'SMITH');
4)转相关子查询
SELECT ENAME,SAL FROM EMP A WHERE EXISTS (SELECT SAL FROM EMP B WHERE ENAME = 'SMITH' AND A.SAL>B.SAL);
2.查询工资比20号部门所有人工资都高的员工信息
1)查询工资高于XXX的员工信息
SELECT * FROM EMP WHERE SAL > XXX;
2)查询20部门的最高薪资XXX
SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 20;
3) 拼接
SELECT * FROM EMP WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 20);
4) 转相关子查询
SELECT *
FROM EMP A
WHERE EXISTS(SELECT MAX(SAL)
FROM EMP B
WHERE DEPTNO =20
GROUP BY 1
HAVING A.SAL > MAX(B.SAL));
3.查询工资大于10号部门平均工资的员工信息
1)查询工资大于XXX的员工信息
SELECT * FROM EMP WHERE SAL > XXX;
2)查询10号部门的平均薪资
SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 10;
3)拼接
SELECT * FROM EMP WHERE SAL > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 10);
4) 转相关子查询
SELECT *
FROM EMP A
WHERE EXISTS (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 10 HAVING A.SAL >AVG(SAL));
--要么加GROUP BY 常量 要么就不要加 GROUP BY 否则影响效率
4.查询哪个部门的平均薪资高于30号部门平均薪资,显示出其部门编号和人数
1)查询平均薪资高于XXX的部门编号和人数
SELECT DEPTNO,COUNT(1)
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL) > XXX;
2)查询30号部门的平均薪资XXX
SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 30;
3) 拼接
SELECT DEPTNO,COUNT(1)
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL) > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 30);
4) 转相关子查询
SELECT DEPTNO,COUNT(1)
FROM EMP T
GROUP BY DEPTNO
HAVING EXISTS (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 30 HAVING AVG(T.SAL) > AVG(SAL));
5.查询最低工资比20部门最低工资要高的部门,显示其部门编号及部门内的最低工资
1)查询最低工资高于XXX的部门编号及最低工资
SELECT DEPTNO,MIN(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING MIN(SAL) > XXX;
2)查询20号部门的最低工资XXX
SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 20;
3) 拼接
SELECT DEPTNO,MIN(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING MIN(SAL) > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 20);
4) 转相关子查询
SELECT DEPTNO,MIN(SAL)
FROM EMP T
GROUP BY DEPTNO
HAVING EXISTS (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 20 HAVING MIN(T.SAL) > MIN(SAL));
6.查询部门名称中带有字母R的部门下所有员工信息
1) 查询部门XXX下的所有员工
SELECT * FROM EMP WHERE DEPTNO = XXX;
2)查询名称中带有R的部门编号
SELECT DEPTNO FROM DEPT WHERE DNAME LIKE '%R%'; --有两个值 两个部门
3) 拼接
SELECT * FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE DNAME LIKE '%R%');
4) 转相关子查询
SELECT *
FROM EMP T
WHERE EXISTS (SELECT DEPTNO FROM DEPT WHERE DNAME LIKE '%R%' AND T.DEPTNO = DEPTNO);
7.查询比三个部门平均工资都高的员工,显示其员工编号,员工姓名,岗位和薪资
1)查询工资大于XXX的员工,查询其员工编号、姓名、岗位和薪资
SELECT EMPNO,ENAME,JOB,SAL
FROM EMP
WHERE SAL > XXX;
2) 查询各部门平均薪资中的最高值XXX
SELECT MAX(A)
FROM TB;
3) 查询各个部门的平均薪资 --数据源
SELECT DEPTNO,AVG(SAL) A FROM EMP GROUP BY DEPTNO;
4) 拼接
SELECT EMPNO,ENAME,JOB,SAL
FROM EMP
WHERE SAL > (SELECT MAX(A) --平均薪资中找到最高的那一个
FROM (SELECT DEPTNO,AVG(SAL) A FROM EMP GROUP BY DEPTNO));
SELECT EMPNO,ENAME,JOB,SAL
FROM EMP
WHERE SAL > ALL(SELECT AVG(SAL) A FROM EMP GROUP BY DEPTNO); --不能转相关子查询
5) 转相关子查询
SELECT EMPNO,ENAME,JOB,SAL
FROM EMP
WHERE EXISTS (SELECT MAX(A)
FROM (SELECT DEPTNO,AVG(SAL) A FROM EMP GROUP BY DEPTNO)
HAVING SAL > MAX(A)); --里面没有SAL字段,找不到往外面找,直接运行就可以。
--因为子查询中数据源内没有SAL字段,所以SAL前可以不加归属说明
8.查询工资小于1000的员工所在的部门的部门名称和工作地点
1)查询部门编号是XXX的部门名称和工作地点
SELECT DNAME,LOC
FROM DEPT
WHERE DEPTNO = XXX;
2)查询工资小于1000的员工所在的部门编号
SELECT DEPTNO FROM EMP WHERE SAL < 1000;
3) 拼接
SELECT DNAME,LOC
FROM DEPT
WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE SAL < 1000);
4) 转相关子查询
SELECT DNAME,LOC
FROM DEPT T
WHERE EXISTS (SELECT DEPTNO FROM EMP WHERE SAL < 1000 AND T.DEPTNO = DEPTNO);
9.查询比工资最高的员工入职时间晚的员工,显示其姓名,入职时间
1)查询入职时间晚于XXX的员工姓名和入职时间
SELECT ENAME ,HIREDATE
FROM EMP
WHERE HIREDATE > XXX;
2)查询工资是YYY的员工的入职时间XXX
SELECT HIREDATE XXX FROM EMP WHERE SAL = YYY;
3) 查询最高工资 YYY
SELECT MAX(SAL) YYY FROM EMP;
4) 拼接
SELECT * FROM EMP;
SELECT ENAME ,HIREDATE
FROM EMP
WHERE HIREDATE > (SELECT HIREDATE XXX FROM EMP WHERE SAL = (SELECT MAX(SAL) YYY FROM EMP));
5) 转相关子查询
两个都转:
SELECT ENAME ,HIREDATE
FROM EMP A
WHERE EXISTS (SELECT HIREDATE XXX
FROM EMP B
WHERE EXISTS (SELECT MAX(SAL) YYY FROM EMP C HAVING B.SAL = MAX(C.SAL))
AND A.HIREDATE >B.HIREDATE
);
转里面:
SELECT ENAME ,HIREDATE
FROM EMP A
WHERE HIREDATE > (SELECT HIREDATE XXX
FROM EMP B
WHERE EXISTS(SELECT MAX(SAL) YYY FROM EMP C HAVING B.SAL = MAX(C.SAL)));
转外面:
SELECT ENAME ,HIREDATE
FROM EMP A
WHERE EXISTS (SELECT HIREDATE XXX
FROM EMP B
WHERE SAL = (SELECT MAX(SAL) YYY FROM EMP C)
AND A.HIREDATE >B.HIREDATE
);
--多个子查询嵌套的情况下,可以选择只将其中一部分子查询转成相关子查询。 书写形式不同,效率不同。
10.求入职时间相同的员工
1) 查询入职时间是XXX的员工
SELECT * FROM EMP WHERE HIREDATE = XXX;
2) 查询相同的入职时间XXX
SELECT HIREDATE
FROM EMP
GROUP BY HIREDATE -- 分组 看看那个组下面的人数是不是多于2个人
HAVING COUNT(1) >=2; --说明有相同入职时间的人
3) 拼接
SELECT *
FROM EMP
WHERE HIREDATE IN (SELECT HIREDATE -- 不能确定1个时间 用IN
FROM EMP
GROUP BY HIREDATE
HAVING COUNT(1) >=2);
4)转相关子查询
SELECT *
FROM EMP T
WHERE EXISTS (SELECT HIREDATE -- 不能确定1个时间 用IN
FROM EMP
WHERE T.HIREDATE = HIREDATE
GROUP BY HIREDATE
HAVING COUNT(1) >=2);
--直接写相关
SELECT *
FROM EMP T1
WHERE EXISTS(SELECT *
FROM EMP T2
WHERE T1.HIREDATE = T2.HIREDATE
AND T1.EMPNO <> T2.EMPNO); --不想和自己比较,去掉和自己比较,和其他人比较