Oracle培训(二十九)——Oracle 11g 第六章知识点总结——子查询
知识点预览
子查询
子查询
1.使用子查询解决问题
谁的工资比 Abel 高?
2.子查询语法
a)子查询 (内查询) 在主查询之前一次执行完成。
b)子查询的结果被主查询使用 (外查询)。
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM
table);
3.子查询
SELECT last_name
FROM employees
WHERE salary >
(SELECT salary
FROM employees
WHERE last_name = 'Abel');
4.注意事项
a)子查询要包含在括号内。
b)将子查询放在比较条件的右侧。
c)除非进行Top-N 分析,否则不要在子查询中使用ORDERBY 子句。
d)单行操作符对应单行子查询,多行操作符对应多行子查询。
5.子查询类型
6.单行子查询
a)只返回一行。
b)使用单行比较操作符。
7.执行单行子查询
SELECTlast_name, job_id, salary
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141)
AND salary >
(SELECT salary
FROM employees
WHERE employee_id = 143);
8.在子查询中使用组函数
SELECTlast_name, job_id, salary
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees);
9.子查询中的 HAVING 子句
a)首先执行子查询。
b)向主查询中的HAVING 子句返回结果。
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
(SELECTMIN(salary)
FROM employees
WHERE department_id = 50);
10.非法使用子查询
SELECT employee_id,last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
11.子查询中的空值问题
SELECTlast_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = 'Haas');
12.多行子查询
a)返回多行。
b)使用多行比较操作符。
13.在多行子查询中使用 ANY 操作符
SELECTemployee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
14.在多行子查询中使用 ALL 操作符
SELECTemployee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
15.子查询中的空值问题
SELECTemp.last_name
FROM employees emp
WHERE emp.employee_id NOT IN/EXISTS
(SELECTmgr.manager_id
FROM employees mgr);
no rows selected
16.示例
--查询出比MARTIN工资高的所有人
--分析:
--a.查询出MARTIN的工资
selectsal from emp where ename = 'MARTIN';
--b.查询出比他高的所有人
selectename,sal
fromemp
wheresal >
--子查询要包含在括号内
(
select sal
from emp
where ename = 'MARTIN'
);
--<ALL --小于最小的
--<ANY --小于最大的
-->ALL --大于最大的
-->ANY --大于最小的
--=ANY --等价于in
--=ALL --报错
--多行子查询
--<any小于最大的 =any / in
--<all 小于最小的
--1. 列出至少有一个员工的所有部门
select count(*) ,sum(sal),deptno
from emp
having count(*)>=1
group by deptno
--2.列出薪金比"SMITH"高的所有员工
--4.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称。
select e.empno,e.ename,d.dname
from
emp e, emp m,
(
selectdeptno,dname
fromdept
) d
where e.hiredate < m.hiredate
and e.mgr = m.empno
andd.deptno = e.deptno;
--5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。
select d.dname,e.ename,e.deptno
from emp e right outer join dept d
on e.deptno = d.deptno;
--6.列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数
selectename,e.deptno,d.dname,t.num
fromemp e,dept d , (
selectdeptno,count(*) num
fromemp
wherejob='CLERK'
groupby deptno
)t
where job='CLERK' and e.deptno=d.deptno andd.deptno=t.deptno;
selecte.ename,d.dname,t.num
fromemp e, dept d,
(
select deptno,count(*) num
from emp
where job = 'CLERK'
group by deptno
)t
where e.deptno = d.deptno and job = 'CLERK'and t.deptno = e.deptno;
--7.列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
select job, count(*)
from emp
having min(sal) > 1500
group by job;
--8.列出在部门"SALES"(销售部)工作的员工的姓名,假定不知道销售部门的部门编号。
select deptno from dept where dname ='SALES';
select e.ename from emp;
select e.ename,e.deptno
from emp e
where e.deptno =
(
selectdeptno
fromdept where
dname= 'SALES'
);
--9.列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,--工资等级。
selecte.ename,e.sal,d.dname,m.ename
fromemp e,dept d,emp m
wheree.sal>(
selectavg(sal) from emp
)and e.deptno=d.deptno and e.mgr=m.empno;
select e.ename,temp.dname,m.ename as "上级领导"
from
emp e, emp m,(
selectdeptno,dname
from
dept
) temp
where e.mgr = m.empno
and temp.deptno = e.deptno
and e.sal >
(select avg(sal)
from emp);
--10.列出与"SCOTT"从事相同工作的所有员工及部门名称
select e.ename,temp.dname
from emp e,
(
selectdeptno,dname
from
dept
)temp
where e.job =
(
selectjob
fromemp
whereename = 'SCOTT'
) and e.deptno = temp.deptno
and e.ename != 'SCOTT';
--11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金
select e.ename, e.sal
from emp e
where
e.sal = any
(
selectsal
fromemp
wheredeptno = 30
);
--12.列出薪金高于在部门30工作的所有员工的薪金的员工的姓名和薪金、部门名称。
select e.ename, e.sal, temp.dname
from emp e,
(
selectdeptno,dname
from
dept
) temp
where
e.sal > all
(
selectsal
fromemp
wheredeptno = 30
) and temp.deptno = e.deptno;
--13.列出在每个部门工作的员工数量、平均工资和平均服务期限。
select count(*),avg(sal),avg(round((sysdate -hiredate) / 365, 0)),deptno
from emp
group by deptno;
--14.列出所有员工的姓名、部门名称和工资
select e.ename,temp.dname,e.sal
from emp e,
(
selectdeptno,dname
from
dept
) temp
where temp.deptno = e.deptno;
--15.列出所有部门的详细信息和部门人数
selectd.*,t.c
fromdept d,(
selectdeptno,count(*) c
fromemp
groupby deptno
)t
whered.deptno=t.deptno;
select d.*,temp.c
from dept d,
(
select count(*) asc,deptno
from emp
group by deptno
)temp
where d.deptno = temp.deptno;
--优化
select dept.dname,tem.num
from dept
left join
(select deptno, count(*) num
from emp
group by(deptno)
)tem
on tem.deptno = dept.deptno;
--16.列出各种工作的最低工资及从事此工作(从事该职位且工资最低)的雇员姓名。
--1列出各种工作的最低工资
selecte.ename,e.sal,t.s,t.job
fromemp e,(
selectmin(sal) s,job
fromemp
groupby job
)t
wheree.sal=t.s;
--17.出各个部门的MANAGER(经理)的最低薪金。
selectmin(sal)
fromemp
wherejob = 'MANAGER'
group by deptno;
--18.列出所有员工的年工资,按年薪从低到高排序。
selectsal*12 s from emp order by s;
selectename,sal * 12 as "年薪"
fromemp
orderby sal;
selectename,sal * 12 as "年薪"
fromemp
orderby sal * 12;
--19.查处某个员工的上级主管,并要求出这些主管的薪水超过3000
selecte.ename,e.mgr,m.empno,m.ename
fromemp e, emp m
wheree.mgr = m.empno
andm.sal > 3000;
--20.求出部门名称中,带‘S’字符的部门员工的工资合计、部门人数
selectd1.dname,t.*
fromdept d1,(
selectcount(*),sum(e.sal),e.deptno
fromemp e,dept d
whered.dname like '%S%' and e.deptno=d.deptno
groupby e.deptno
)t
whered1.deptno=t.deptno;
--第一行:ORA-00937: 不是单组分组函数
select sum(e.sal), temp.c
from emp e,
(
selectcount(*) c
fromdept
groupby deptno
)temp
where e.deptno
in
(
selectdeptno
fromdept
wheredname like '%S%'
);
select sum(sal), count(*)
from emp
group by deptno
having deptno
in
(
selectdeptno
fromdept
wheredname like '%S%'
);
--21.给任职日期超过10年的人加薪10%
第9题:
第15题:
第16题:
第20题:
分享到:
相关推荐
Oracle经典教程3——子查询和常用函数
Oracle10g 培训——基础知识,适合初学者了解,
很好的oracle 10g培训资料——管理第一部分:结构组件
《Oracle11g从入门到精通》面向数据库管理人员和数据库开发人员,从实际角度出发,系统地介绍了数据库和Oracle的相关概念和原理、Oracle的数据库管理(如安装与启动,用户权限、备份与恢复等),以及Oracle的应用...
Oracle数据库管理员培训教材——10g版本,学习初学oracle,oracle进阶等有很好的帮助,就是好!
第6章 基本的SQL*Plus报表及命令 第7章 文本信息的收集与修改 第8章 正则表达式搜索 第9章 数值处理 第10章 日期:过去、现在及日期的差 第11章 转换函数与变换函数 第12章 分组函数 第13章 当一个查询依赖于另一个...
oracle内部培训资料——游标 实例代码,使用注意事项等
很好的oracle 10g培训资料,这是SQL的第二部分
oracle数据库双机热备安装——裸设备.doc oracle数据库双机热备安装——裸设备.doc oracle数据库双机热备安装——裸设备.doc
oracle11g 第一部分:SQL语言基础 第一章:关系型与非关系型数据库 第二章:SQL的基本函数 第三章:SQL的数据类型 第四章、WHERE子句中常用的运算符 第五章:分组函数 第六章:数据限定和排序 第七章:复杂...
第1章 oracle database 11g 体系结构 第2章 安装oracle database 11g 和创建数据库 第3章 升级到oracle database 11g 第4章 规划oracle 应用程序——方法、风险和标准 第ⅱ部分 sql 和sql*plus 第5章 sql...
共计5个视频 1 RMAN工具简介 2 备份与恢复概述 3 使用RMAN工具备份 4 使用RMAN工具部分恢复 5 使用RMAN工具完全恢复
Oracle工作总结——日志文件切换频率的调整 Oracle工作总结——日志文件切换频率的调整
很好的oracle 10g培训的资料,很精辟,这是SQL的第一部分
Oracle Database 11g 标准课程第六章 子查询与集合操作子查询概述