第7章：子查询

于 2020-01-10 19:28:35 发布
阅读量1.5k 收藏 6
点赞数 2
分类专栏： oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_43663024/article/details/103929274
版权

ALL:表示大于子查询结果集中的所有行，即大于最大值。
查询部门编号不为10，且工资比10部门所有员工工资高的员工编号，姓名，职位，工资。
select empno,ename,job,sal
from emp
where sal>all(select sal from emp where deptno=10)
and deptno<>10;

= ALL :表示等于子查询结果集中的所有行,即等于所有值，通常无意义。
查询部门编号不为10，且工资和10部门所有员工工资相等的员工编号，姓名，职位，工资。
select empno,ename,job,sal
from emp
where sal=all(select sal from emp where deptno=10)
and deptno<>10;

---------------------练习1 单行子查询------------------------------------------------------

1.查询入职日期最早的员工姓名，入职日期
select ename,hiredate
from emp
where hiredate=(select min(hiredate) from emp )

2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称
select e.ename 员工姓名,e.sal 工资,d.loc 工作地点 ,d.dname 部门名称
from emp e,dept d
where e.deptno=d.deptno and sal>(select sal from emp where ename=‘SMITH’)
and d.loc=‘CHICAGO’;
3.查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期
select ename 员工姓名,hiredate 入职日期
4.查询部门人数大于所有部门平均人数的的部门编号，部门名称，部门人数

---------------------练习2 多行子查询------------------------------------------------------
---------ANY：表示和子查询的任意一行结果进行比较，有一个满足条件即可。--------------
< ANY：表示小于子查询结果集中的任意一个，即小于最大值就可以。

ANY：表示大于子查询结果集中的任意一个，即大于最小值就可以。

= ANY：表示等于子查询结果中的任意一个，即等于谁都可以，相当于IN。

1.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工
select ename,hiredate
from emp
where hiredate>any(select max(hiredate)from emp where deptno=10)
and deptno<>10;
2.查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工
select ename,hiredate
from emp
where hiredate>all(select hiredate from emp where deptno=10)
and deptno<>10;
3.查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工
select ename,job
from emp
where job=any(select job from emp where deptno=10 )
and deptno<>10;
---------------------练习3 多列子查询------------------------------------------------------
1.查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名，职位，不包括10部门员工
select ename,job
from emp
where (job,mgr)in(select job,mgr from emp where deptno=10)
and deptno<>10

2.查询职位及经理和10部门任意一个员工职位或经理相同的员工姓名，职位，不包括10部门员工
select ename,job
from emp
where (job in(select job from emp where deptno=10)
or
mgr in(select mgr from emp where deptno=10))
and deptno<>10

---------------------练习4 在 FROM 子句中使用子查询------------------------------------------------------
1.查询比自己职位平均工资高的员工姓名、职位，部门名称，职位平均工资
select e.,t.
from
(select e.ename,e.job,d.deptno,e.sal
from emp e,dept d
where e.deptno=d.deptno)e,
(select deptno, avg(sal) from emp group by deptno)t
where e.deptno=t.deptno and e.sal>all(select avg(sal) from emp )

2.查询职位和经理同员工SCOTT或BLAKE完全相同的员工姓名、职位，不包括SCOOT和BLAKE本人。
select ename,job
from emp
where ((job,mgr)in(select job,mgr from emp where ename=‘SCOTT’)
or
(job,mgr)in(select job,mgr from emp where ename=‘BLAKE’))
and ename<>‘SCOTT’ and ename<>‘BLAKE’

3.查询不是经理的员工姓名。
select ename
from emp
where empno not in(select mgr from emp where mgr is not null)

---------------------练习5 rownum------------------------------------------------------
—ROWNUM是一个伪列，伪列是使用上类似于表中的列，而实际并没有存储在表中的特殊列；
–ROWNUM的功能是在每次查询时，返回结果集的顺序号，这个顺序号是在记录输出时才一步一步产生的，第一行显示为1，第二行为2，以此类推。

1.查询入职日期最早的前5名员工姓名，入职日期。
select e.*,rownum
from
(select ename,hiredate
from emp)e
where rownum<6;

2.查询工作在CHICAGO并且入职日期最早的前2名员工姓名，入职日期。
select e1.*,rownum rn
from
(select ename,hiredate
from emp e,dept d
where e.deptno=d.deptno and d.loc=‘CHICAGO’)e1
where rownum<3;

---------------------练习6 分页------------------------------------------------------
分页查询 在Oracle中，利用ROWNUM的特性，可以实现数据库端的分页查询，查询语法为：1.当未指定需要按照某列排序，语法为：
SELECT b.*
FROM (SELECT ROWNUM rn,[列名1,列名2,…列名n]
FROM 表名1,[表名2,…表名n]
WHERE [条件表达式 AND ] ROWNUM <=目标页数每页记录数) b
WHERE rn > (目标页数-1)每页记录数
2.当指定需要按照某列排序时，语法为：
SELECT *
FROM (SELECT ROWNUM rn, b.
FROM (SELECT 列名1 [,列名2,…列名n]
FROM 表名1,[表名2,…表名n]
[WHERE 子句]
ORDER BY 要排序的列 ASC|DESC ) b
WHERE ROWNUM <=目标页数每页记录数
)
WHERE rn > (目标页数-1)*每页记录数 ;

1.按照每页显示5条记录，分别查询第1页，第2页，第3页信息，要求显示员工姓名、入职日期、部门名称。
第1页
select e1.*
from
(select e.ename,e.hiredate,d.dname,rownum rn
from emp e,dept d
where e.deptno=d.deptno and rownum<=15)e1
where rn>=(1-1)5
第2页
select e1.
from
(select e.ename,e.hiredate,d.dname,rownum rn
from emp e,dept d
where e.deptno=d.deptno and rownum<=25)e1
where rn>=(2-1)5
第3页
select e1.
from
(select e.ename,e.hiredate,d.dname,rownum rn
from emp e,dept d
where e.deptno=d.deptno and rownum<=3*5)e1
where rn>=(3-1)*5

---------------------练习7 分页------------------------------------------------------
1.按照每页显示5条记录，分别查询工资最高的第1页，第2页，第3页信息，要求显示员工姓名、入职日期、部门名称、工资。
第1页
select *
from
(select e1.,rownum rn
from
(select e.ename,e.hiredate,d.dname,e.sal
from emp e,dept d
where e.deptno=d.deptno
order by e.sal desc) e1 -----这里为什么不在这张表上长上rownum,因为order by 是最后执行的，而rownum是在查询结果出现后才出来的，在这里长上rownum order by会打乱rownum顺序
where rownum<=15 )
where rn>=(1-1)*5;

第2页
select *
from
(select e1.,rownum rn
from
(select e.ename,e.hiredate,d.dname,e.sal
from emp e,dept d
where e.deptno=d.deptno
order by e.sal desc) e1 -----这里为什么不在这张表上长上rownum,因为order by 是最后执行的，而rownum是在查询结果出现后才出来的，在这里长上rownum order by会打乱rownum顺序
where rownum<=25 )
where rn>(2-1)*5;

第3页
select *
from
(select e1.,rownum rn
from
(select e.ename,e.hiredate,d.dname,e.sal
from emp e,dept d
where e.deptno=d.deptno
order by e.sal desc) e1 -----这里为什么不在这张表上长上rownum,因为order by 是最后执行的，而rownum是在查询结果出现后才出来的，在这里长上rownum order by会打乱rownum顺序
where rownum<=35 )
where rn>(3-1)*5;

---------------------课后作业------------------------------------------------------
1.查询工资高于编号为7782的员工工资，并且和7566号员工从事相同工作的员工的编号、姓名及工资。
select *from emp;
select empno,ename,sal
from emp
where sal>(select sal from emp where empno=7782) and job=(select job from emp where empno=7566);

2.查询工资最高的员工姓名和工资。
select ename,sal
from emp
where sal=(select max(sal)from emp);
3.查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。
select d.deptno,d.dname,min(sal)
from emp e ,dept d
where e.deptno=d.deptno
group by d.deptno,d.dname
having min(sal)>(select min(sal) from emp where deptno=10);

4.查询员工工资为其部门最低工资的员工的编号和姓名及工资。

select empno,ename,sal
from emp
where sal=(select min(sal)from emp);

5.显示经理是KING的员工姓名，工资。

select e.ename,e.sal
from emp e,emp b
where e.mgr=b.empno and b.ename=‘KING’

6.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。
select ename,sal,hiredate
from emp
where hiredate>all(select hiredate from emp where ename=‘SMITH’)
7.使用子查询的方式查询哪些职员在NEW YORK工作。
法一：自身连接方式
select ename
from emp e,dept d
where e.deptno=d.deptno and d.loc=‘NEW YORK’
法一：子查询方式
select ename
from emp e,dept d
where e.deptno=d.deptno and d.loc in(select loc from dept where loc=‘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,sal
from emp
where sal>all(select avg(sum(sal)) from emp group by ename )
10.写一个查询显示其上级领导是KING的员工姓名、工资。

select e.ename,e.sal
from emp e,emp b
where e.mgr=b.empno and b.ename=‘KING’;

11.显示所有工作在RESEARCH部门的员工姓名，职位。
select e.ename,job
from emp e,dept d
where e.deptno=d.deptno and d.dname=‘RESEARCH’;
12.查询每个部门的部门编号、平均工资，要求部门的平均工资高于部门20的平均工资。
select deptno,avg(sal)
from emp
group by deptno
having avg(sal)>(select avg(sal)from emp where deptno=20)
13.查询大于自己部门平均工资的员工姓名，工资，所在部门平均工资，高于部门平均工资的额度。

select e.ename,e.sal,s.*
from emp e,
(select deptno,avg(sal)avg_sal from emp group by deptno )s
where e.deptno=s.deptno and e.sal>s.avg_sal;

  1. 列出至少有一个雇员的所有部门
    select deptno,count()
    from emp
    group by deptno
    having count(    )>=1;

  2. 列出薪金比"SMITH"多的所有雇员
    select sal,ename
    from emp
    where sal>(select sal from emp where ename=‘SMITH’);

  3. 列出入职日期早于其直接上级的所有雇员
    select e.ename,e.hiredate
    from emp e,emp b
    where e.mgr=b.empno and e.hiredate<b.hiredate

  4. 找员工姓名和直接上级的名字
    select e.ename,b.ename
    from emp e,emp b
    where e.mgr=b.empno

  5. 显示部门名称和人数
    select d.dname,count(*)
    from dept d,emp e
    where d.deptno=e.deptno
    group by d.dname

  6. 显示每个部门的最高工资的员工
    select e.deptno,e.ename,e1.max_sal
    from emp e,
    (select deptno,max(sal)max_sal from emp group by deptno)e1
    where e.deptno=e1.deptno and e.sal=e1.max_sal

  7. 显示出和员工号7369部门相同的员工姓名，工资
    select ename,sal
    from emp
    where deptno =(select deptno from emp where empno=7369 )

  8. 显示出和姓名中包含"W"的员工相同部门的员工姓名
    select ename
    from emp
    where (deptno,ename)in (select deptno,ename from emp where ename like’%W%’);

  9. 显示出工资大于平均工资的员工姓名，工资
    select ename,sal
    from emp
    where sal>(select avg(sal)from emp)

  10. 显示出工资大于本部门平均工资的员工姓名，工资
    select e.ename,e.sal,e1.*
    from emp e,
    (select deptno, avg(sal)avg_sal from emp group by deptno)e1
    where e.deptno=e1.deptno and e.sal>e1.avg_sal

  11. 显示每位经理管理员工的最低工资，及最低工资者的姓名
    select e.ename,min(e.sal)
    from emp e,emp b
    where e.empno=b.mgr
    group by e.ename

  12. 显示比工资最高的员工参加工作时间晚的员工姓名，参加工作时间
    select ename,hiredate
    from emp
    where hiredate>(select hiredate from emp where sal=(select max(sal)from emp ) )

  13. 显示出平均工资最高的的部门平均工资及部门名称
    select d.dname, avg(sal)
    from emp e, dept d
    where e.deptno=d.deptno
    group by d.dname
    having avg(sal)=(select max(avg(sal)) from emp group by deptno )

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值