-- 2018/07/13 080-11:30 实训第4天上午
-- 子查询 可以嵌套 WHERE子句 HAVING子句 FROM子句
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
-- 查询工资比Jones工资高的员工信息
SELECT *
FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename = 'JONES');
-- 查询工资最低的员工姓名
SELECT ename
FROM emp
WHERE sal = (SELECT MIN (sal) FROM emp);
-- 单行子查询
-- 显示和雇员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);
-- 查询工资最低的员工姓名,岗位及工资
SELECT ename, job, sal
FROM emp
WHERE sal =
(SELECT MIN(sal)
FROM emp);
-- 查询部门最低工资比20部门最低工资高的部门编号及最低工资
SELECT deptno, MIN(sal)
FROM emp
GROUP BY deptno
HAVING MIN(sal) >
(SELECT MIN(sal)
FROM emp
WHERE deptno = 20);
-- 查询哪个部门的员工人数 高于各部门平均人数
SELECT deptno, COUNT(empno)
FROM emp
GROUP BY deptno
HAVING COUNT(empno) >
(SELECT AVG(COUNT(empno))
FROM emp
GROUP BY deptno);
-- 1-1 查询入职日期最早的员工姓名,入职日期
SELECT ename, hiredate
FROM emp
WHERE hiredate = (SELECT MIN (hiredate) FROM emp);
-- 1-2 查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名,工资,部门名称
SELECT ename, sal, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno(+)
AND loc = 'CHICAGO'
AND sal > (SELECT sal FROM emp WHERE ename = 'SMITH');
-- 1-3 查询入职日期比20部门入职日期最早的员工还要早的员工姓名,入职日期
SELECT ename, hiredate
FROM emp
WHERE hiredate < (SELECT MIN (hiredate) FROM emp WHERE deptno = 20);
-- 1-4 查询部门人数大于所有部门平均人数的的部门编号,部门名称,部门人数
SELECT d.deptno, dname, COUNT (d.deptno)
FROM emp e, dept d
WHERE e.deptno = d.deptno(+)
GROUP BY d.deptno, dname
HAVING COUNT (d.deptno) > (SELECT AVG (COUNT (deptno)) FROM emp GROUP BY deptno);
-- 多行子查询
-- 和多行子查询进行比较时,需要使用多行操作符,多行操作符包括:
-- IN
-- ANY
-- ALL
-- IN使用
-- 查询各部门最低工资员工编号,姓名,工资
SELECT empno, ename, sal
FROM emp
WHERE sal IN -- 不能用 =
(SELECT MIN(sal)
FROM emp
GROUP BY deptno);
-- 查询是经理的员工姓名,工资
SELECT ename, sal
FROM emp
WHERE empno IN (SELECT DISTINCT mgr
FROM emp);
-- ANY的使用 与子查询任意一个比较,有一个满足条件即可
-- < ANY 小于最大值
-- > ANY 大于最小值
-- = ANY 相当于IN
-- 查询是经理的员工姓名,工资
SELECT ename, sal
FROM emp
WHERE empno = ANY (SELECT mgr
FROM emp);
-- 查询部门编号不为10,且工资比10部门任意一名员工工资高的员工编号,姓名,职位,工资
SELECT empno, ename, job, sal
FROM emp
WHERE sal > ANY (SELECT sal
FROM emp
WHERE deptno = 10)
AND deptno <> 10;
-- ALL的使用
-- 表示和子查询的所有行结果进行比较,每一行必须都满足条件
-- < ALL 小于最小值
-- > ALL 大于最大值
-- = ALL 等于所有值,通常无意义
-- 查询部门编号不为10,且工资比10部门所有员工工资高的员工编号,姓名,职位,工资
SELECT empno, ename,job, sal
FROM emp
WHERE sal > ALL (SELECT sal
FROM emp
WHERE deptno= 10)
AND deptno <> 10;
-- 查询部门编号不为10,且工资比10部门所有员工工资低的员工编号,姓名,职位,工资
SELECT empno, ename,job, sal
FROM emp
WHERE sal < ALL (SELECT sal
FROM emp
WHERE deptno= 10)
AND deptno <> 10;
-- 查询部门编号不为10,且工资和10部门所有员工工资相等的员工编号,姓名,职位,工资
SELECT empno, ename,job, sal
FROM emp
WHERE sal = ALL (SELECT sal
FROM emp
WHERE deptno= 10)
AND deptno <> 10;
-- 2-1 查询入职日期比10部门任意一个员工晚的员工姓名、入职日期,不包括10部门员工
SELECT ename, hiredate
FROM emp
WHERE hiredate > ANY (SELECT hiredate FROM emp WHERE deptno = 10)
AND deptno != 10;
-- 2-2 查询入职日期比10部门所有员工晚的员工姓名、入职日期,不包括10部门员工
SELECT ename, hiredate
FROM emp
WHERE hiredate > ALL (SELECT hiredate FROM emp WHERE deptno = 10)
AND deptno != 10;
-- 2-3 查询职位和10部门任意一个员工职位相同的员工姓名,职位,不包括10部门员工
SELECT ename, job
FROM emp
WHERE job = ANY (SELECT job FROM emp WHERE deptno = 10)
AND deptno != 10;
-- 多列子查询 通常用IN操作符
-- 查询出和1981年入职的任意一个员工的部门和职位完全相同员工姓名、部门、职位、入职日期,不包括1981年入职员工
SELECT ename, deptno, job, hiredate
FROM emp
WHERE (deptno, job) IN
(SELECT deptno,job -- 与上面一一对应
FROM emp
WHERE to_char(hiredate,'YYYY')='1981')
AND to_char(hiredate,'YYYY')<>'1981';
-- 查询出和1981年入职的任意一个员工的部门或职位相同员工姓名、部门、职位、入职日期,不包括1981年入职员工
SELECT ename, deptno, job, hiredate
FROM emp
WHERE (deptno IN (SELECT deptno
FROM emp
WHERE to_char(hiredate,'YYYY')='1981')
OR job IN (SELECT job
FROM emp
WHERE to_char(hiredate,'YYYY')='1981'))
AND to_char(hiredate,'YYYY')<>'1981';
-- 3-1 查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名,职位,不包括10部门员工
SELECT ename, job
FROM emp
WHERE (mgr, job) IN
(SELECT mgr,job
FROM emp
WHERE deptno = 10 AND mgr IS NOT NULL)
AND deptno != 10;
-- 3-2 查询职位及经理和10部门任意一个员工职位或经理相同的员工姓名,职位,不包括10部门员工
SELECT ename, job
FROM emp
WHERE (mgr IN
(SELECT mgr
FROM emp
WHERE deptno = 10 AND mgr IS NOT NULL)
OR job IN
(SELECT job
FROM emp
WHERE deptno = 10))
AND deptno != 10;
-- 子查询中的空值
-- 查询不是经理的员工姓名
SELECT ename
FROM emp
WHERE empno NOT IN
(SELECT mgr
FROM emp
WHERE mgr IS NOT NULL);
-- 在FROM子句中使用子查询
-- 查询比自己部门平均工资高的员工姓名,工资,部门编号,部门平均工资
SELECT a.ename, a.sal, a.deptno, b.salavg
FROM emp a, (SELECT deptno, avg(sal) salavg
FROM emp
GROUP BY deptno) b
WHERE a.deptno = b.deptno
AND a.sal > b.salavg;
-- 4-1 查询比自己职位平均工资高的员工姓名、职位,部门名称,职位平均工资
SELECT ename, e.job, dname, salavg
FROM emp e, dept d, (SELECT job, AVG (sal) salavg FROM emp GROUP BY job) j
WHERE e.deptno = d.deptno(+)
AND e.job = j.job
AND e.sal > j.salavg;
-- 4-2 查询职位和经理同员工SCOTT或BLAKE完全相同的员工姓名、职位,不包括SCOOT和BLAKE本人
SELECT ename, job
FROM emp
WHERE (job, mgr) IN (SELECT job, mgr FROM emp WHERE ename IN ('SCOTT', 'BLAKE'))
AND ename NOT IN ('SCOTT', 'BLAKE');
-- 4-3 查询不是经理的员工姓名
SELECT ename
FROM emp
WHERE empno NOT IN (SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL);
-- ROWNUM 伪列 表示结果集的顺序号,没有存储在表中
SELECT rownum,empno, ename, job
FROM emp
ORDER BY empno; -- 观察不管是否有这句,ROWNUM依然是有序的
-- TOP-N查询 表中按照某个列排序,输出最大或最小的N条记录功能
SELECT [列名], ROWNUM
FROM (SELECT [列名]
FROM 表名
ORDER BY Top-N操作的列 ASC|DESC)
WHERE ROWNUM <= N;
-- 5-1 查询入职日期最早的前5名员工姓名,入职日期
SELECT ename, hiredate
FROM emp
WHERE ROWNUM <= 5
ORDER BY hiredate;
-- 5-2 查询工作在CHICAGO并且入职日期最早的前2名员工姓名,入职日期
SELECT ename, hiredate
FROM emp, dept
WHERE emp.deptno = dept.deptno(+)
AND loc = 'CHICAGO'
AND ROWNUM <= 2
ORDER BY hiredate;
-- 分页查询暂不了解
-- 课后练习
-- 1 查询工资高于编号为7782的员工工资,并且和7369号员工从事相同工作的员工的编号、姓名及工资
SELECT empno, ename, sal
FROM emp
WHERE sal > (SELECT sal FROM emp WHERE empno = 7782)
AND job = (SELECT job FROM emp WHERE empno = 7369);
-- 结果为空表示没有这样的人
-- 2 查询工资最高的员工姓名和工资
SELECT ename, sal
FROM emp
WHERE sal = (SELECT MAX (sal) FROM emp);
-- 3 查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资
SELECT emp.deptno, dname, MIN (sal)
FROM emp, dept
WHERE emp.deptno = dept.deptno(+)
HAVING MIN (sal) > (SELECT MIN (sal) FROM emp WHERE deptno = 10)
GROUP BY emp.deptno, dname;
-- 10部门最低工资 1300,20部门 800,30部门 950,所以查询无结果
-- 4 查询员工工资为其部门最低工资的员工的编号和姓名及工资
SELECT empno, ename, sal
FROM emp
WHERE (deptno, sal) IN (SELECT deptno, MIN (sal) FROM emp GROUP BY deptno);
-- 5 显示经理是KING的员工姓名,工资
SELECT ename, sal
FROM emp
WHERE mgr = (SELECT empno FROM emp WHERE ename = 'KING');
-- 6 显示比员工SMITH参加工作时间晚的员工姓名,工资,参加工作时间
SELECT ename, sal, hiredate
FROM emp
WHERE hiredate > (SELECT hiredate FROM emp WHERE ename = 'SMITH');
-- 7 使用子查询的方式查询哪些职员在NEW YORK工作
SELECT ename
FROM emp
WHERE deptno IN (SELECT deptno FROM dept WHERE loc = 'NEW YORK');
-- 这里应该用 IN,因为不知道有哪些部门在 NEW YORK。
-- 8 写一个查询显示和员工SMITH工作在同一个部门的员工姓名,雇用日期,查询结果中排除SMITH
SELECT ename, hiredate
FROM emp
WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SMITH')
AND ename != 'SMITH';
-- 9 写一个查询显示其工资比全体职员平均工资高的员工编号、姓名
SELECT empno, ename
FROM emp
WHERE sal > (SELECT AVG (sal) FROM emp);
-- 10 写一个查询显示其上级领导是King的员工姓名、工资
SELECT ename, sal
FROM emp
WHERE mgr = (SELECT empno FROM emp WHERE ename = 'KING');
-- 11 显示所有工作在RESEARCH部门的员工姓名,职位
SELECT ename, job
FROM emp
WHERE deptno = (SELECT deptno FROM dept WHERE dname = 'RESEARCH');
-- 12 查询每个部门的部门编号、平均工资,要求部门的平均工资高于部门20的平均工资
SELECT deptno, AVG (sal)
FROM emp
HAVING AVG (sal) > (SELECT AVG (sal) FROM emp WHERE deptno = 20)
GROUP BY deptno;
-- 13 查询大于自己部门平均工资的员工姓名,工资,所在部门平均工资,高于部门平均工资的额度
SELECT ename, sal, ROUND (da.salavg) 部门平均工资, ROUND (sal - da.salavg) 差额
FROM emp, (SELECT deptno, AVG (sal) salavg FROM emp GROUP BY deptno) da
WHERE emp.deptno = da.deptno(+)
AND emp.sal > da.salavg;
-- 14 列出至少有一个雇员的所有部门
SELECT deptno
FROM emp
GROUP BY deptno
HAVING COUNT (empno) > 0;
-- 15 列出薪金比"SMITH"多的所有雇员
SELECT *
FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename = 'SMITH');
-- SMITH 800
-- 16 列出入职日期早于其直接上级的所有雇员
SELECT w.ename, w.hiredate, w.mgr, m.empno, m.ename, m.hiredate
FROM emp w, emp m
WHERE w.mgr = m.empno(+)
AND w.hiredate < m.hiredate;
-- 17 找员工姓名和直接上级的名字
SELECT w.ename, w.mgr, m.empno, m.ename
FROM emp w, emp m
WHERE w.mgr = m.empno(+);
-- 18 显示部门名称和人数
SELECT dname, COUNT (empno)
FROM emp e, dept d
WHERE e.deptno = d.deptno(+)
GROUP BY dname;
-- 19 显示每个部门的最高工资的员工
SELECT *
FROM emp
WHERE (deptno, sal) IN (SELECT deptno, MAX (sal) FROM emp GROUP BY deptno);
-- 20部门有两个相等的最高工资
-- 20 显示出和员工号7369部门相同的员工姓名,工资
SELECT ename, sal
FROM emp
WHERE deptno = (SELECT deptno FROM emp WHERE empno = 7369);
-- 7369员工在20部门
-- 21 显示出和姓名中包含"W"的员工相同部门的员工姓名
SELECT ename
FROM emp
WHERE deptno IN (SELECT deptno FROM emp WHERE ename LIKE '%W%');
-- 员工WARD在30部门
-- 22 显示出工资大于平均工资的员工姓名,工资
SELECT ename, sal
FROM emp
WHERE sal > (SELECT AVG (sal) FROM emp);
-- AVG (sal) = 2073
-- 23 显示出工资大于本部门平均工资的员工姓名,工资
SELECT ename, sal, salavg
FROM emp e, (SELECT deptno, AVG (sal) salavg FROM emp GROUP BY deptno) ds
WHERE e.deptno = ds.deptno(+)
AND sal > salavg;
-- 24 显示每位经理管理员工的最低工资,及最低工资者的姓名
SELECT sal, ename
FROM emp
WHERE (mgr, sal) IN (SELECT mgr, MIN (sal) FROM emp GROUP BY mgr);
-- 有一个没有经理
-- 25 显示比工资最高的员工参加工作时间晚的员工姓名,参加工作时间
SELECT ename, hiredate
FROM emp
WHERE hiredate >
(SELECT hiredate FROM emp WHERE sal = (SELECT MAX (sal) FROM emp)); -- 比最高工资应该入职晚
-- 26 显示出平均工资最高的的部门平均工资及部门名称
SELECT AVG (sal), dname
FROM emp e, dept d
WHERE e.deptno = d.deptno(+)
GROUP BY dname
HAVING AVG (sal) = (SELECT MAX (AVG (sal)) FROM emp GROUP BY deptno);
-- END
暑假实训-6_Oracle数据库-6_子查询
最新推荐文章于 2024-05-23 17:58:21 发布