Oracle 11g 第六章知识点总结——子查询

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题：