17/12/6 子查詢

單行子查詢

• 括號(hào)內(nèi)的查詢叫做子查詢伪窖，也叫內(nèi)部查詢视事，先于主查詢的執(zhí)行绽昏。
• 子查詢可以嵌入1.where 2.having 3.from子句中
  練習(xí)1：

1.查詢?nèi)肼毴掌谧钤绲膯T工姓名脑融，入職日期虏劲。

select ename, hiredate
from emp
where hiredate = (SELECT min(hiredate)
                  from EMP)

2.查詢工資比SMITH工資高并且工作地點(diǎn)在CHICAGO的員工姓名阅虫，工資演闭，部門名稱。

select ename, sal, dept.dname 
from emp
join dept on emp.deptno = dept.deptno
where sal > (select sal
             from emp
             where ename = 'SMITH')
and dept.loc = 'CHICAGO'

3.查詢?nèi)肼毴掌诒?0部門入職日期最早的員工還要早的員工姓名颓帝，入職日期米碰。

select ename, hiredate
from emp
where hiredate < (select min(hiredate)
                  from emp
                  where deptno = 20)

4.查詢部門人數(shù)大于所有部門平均人數(shù)的部門編號(hào)，部門名稱购城，部門人數(shù)吕座。

select emp.deptno, dname, count(*)
from emp
join dept on dept.deptno = emp.deptno
group by emp.deptno, dname
HAVING count(*) > (select avg(count(*))
                           from emp
                           group by emp.deptno)

多行子查詢

-子查詢返回的條數(shù)，可以是一套或多條
-和多行子查詢進(jìn)行比較時(shí)瘪板，需要使用多行操作符.
in:
any: 表示和子查詢的任意一行結(jié)果進(jìn)行比較吴趴，有一個(gè)滿足條件即可。
<li>< any: 表示小于子查詢結(jié)果集中的任意一個(gè)侮攀，即小于最大值就可以锣枝。
<li>> any: 反之厢拭，大于最小值即可。
<li>= any: 相當(dāng)于IN撇叁。
all: 表示和子查詢的所有行記過進(jìn)行比較供鸠，每一行必須滿足條件。
<li>< all: 表示小于子查詢結(jié)果集中的所有行陨闹，即小于最小值楞捂。
<li>>all: 反之，大于最大值趋厉。
<li>=all: 無意義寨闹。

練習(xí)2
1.查詢?nèi)肼毴掌诒?0部門任意一個(gè)員工晚的員工姓名、入職日期觅廓，不包括10部門員工鼻忠。

select ename, hiredate
from emp
where hiredate >any (select hiredate
                     from emp
                     where deptno=10)
and deptno <> 10                     

2.查詢?nèi)肼毴掌诒?0部門任意一個(gè)員工晚的員工姓名涵但、入職日期杈绸，不包括10部門的員工。

select ename, hiredate
from emp
where hiredate >all (select hiredate
                     from emp
                     where deptno=10)
and deptno <> 10                     

3.查詢職位和10部門任意一個(gè)員工職位相同的員工姓名矮瘟，職位瞳脓，不包括10部門員工。

select ename, job
from emp
where job in (select job
              from emp
              where deptno=10)
and deptno <> 10      

多列子查詢

-之前講的子查詢都是在一個(gè)條件表達(dá)式內(nèi)和子查詢的一個(gè)列進(jìn)行比較澈侠，多列子查詢可以在一個(gè)條件表達(dá)式內(nèi)同時(shí)和子查詢的多個(gè)列進(jìn)行比較劫侧。
-多列子查詢通常用IN操作符完成。

練習(xí)3
1.查詢職位及經(jīng)理和10部門任意一個(gè)員工職位及經(jīng)理相同的員工姓名哨啃，職位烧栋，不包括10部門員工。

select ename, job
from emp
where (job, mgr) in (select job,mgr
                     from emp
                     where deptno=10)
and deptno <> 10                     

2.查詢職位及經(jīng)理和10部門任意一個(gè)員工職位或經(jīng)理相同的員工姓名拳球，職位审姓，不包括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

子查詢中的空值

無論什么時(shí)候只要空值有可能成為子查詢結(jié)果的一部分祝峻，就不能使用NOT IN運(yùn)算符魔吐。

在from子句中使用子查詢

查詢比自己部門平均工資高的員工姓名，工資莱找，部門編號(hào)酬姆，部門平均工資。

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             

練習(xí)4：
1.查詢比自己職位平均工資高的員工姓名奥溺、職位辞色，部門名稱，職位平均工資浮定。

select a.ename, a.job, dept.deptno, b.salavg
from emp a, dept, (select job, avg(sal) salavg
             from emp 
             group by job) b
where a.job = b.job and a.DEPTNO = DEPT.DEPTNO
and a.sal > b.salavg

2.查詢職位和經(jīng)理同員工SCOTT或BLACK完全相同的員工姓名相满、職位诱篷、不包括SCOTT和BLAKE本人。
方法1：多列子查詢

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')               

方法2：建立一個(gè)與'SCOTT' 雳灵，'BLAKE'的職位和經(jīng)理的臨時(shí)表棕所，然后與emp建立連接，過濾掉與SCOTT 與 BLAKE 職位經(jīng)理不同的行悯辙。

select ename,EMP.job
from EMP
join 
(select job,mgr
from emp 
where ename in ('SCOTT','BLAKE')) tempTab 
on EMP.job = TEMPTAB.job and EMP.MGR = TEMPTAB.mgr
where ename not in ('SCOTT','BLAKE') 

ROWNUM(偽列)

TOP-N查詢

練習(xí)5
1.查詢?nèi)肼毴掌谧钤绲那?名員工姓名琳省，入職日期

SELECT ROWNUM, ename, hiredate
FROM(SELECT ename,hiredate
from emp
ORDER BY hiredate) demptab
where ROWNUM < =2 

2.查詢工作在CHICAGO并且入職日期最早的前兩名員工姓名，入職日期躲撰。

SELECT ROWNUM, ename, hiredate
FROM(SELECT ename,hiredate,deptno
from emp
ORDER BY hiredate) demptab
join dept on dept.deptno = DEMPTAB.DEPTNO
where ROWNUM < =2 and dept.loc = 'CHICAGO'

分頁(yè)

練習(xí)7
1.按照每頁(yè)顯示5條記錄针贬，分別查詢工資最高的第一頁(yè)，第二頁(yè)拢蛋，第三頁(yè)信息桦他，要求顯示員工姓名、入職日期谆棱、部門名稱快压、工資。
方法1：
第一頁(yè)

select *
from (select rownum rn, *
       from emp join dept on emp.deptno= dept.deptno
       order by sal desc) b
where rn>0 and rn <=5 

方法2：

select tempTab2.ENAME,tempTab2.HIREDATE,DEPT.DNAME,TEMPTAB2.sal
from 
(select rownum rn, tempTab.* from (select * from emp order by sal desc) tempTab where rownum <= 15) tempTab2
join dept on DEPT.deptno = tempTab2.deptno
where tempTab2.rn > 10

第二頁(yè)垃瞧，第三頁(yè)蔫劣。。个从。

課后作業(yè)
1.查詢工資高于編號(hào)為7782的員工工資脉幢，并且和7369號(hào)員工從事相同工作的員工的編號(hào)、姓名及工資嗦锐。

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號(hào)部門最低工資的部門的編號(hào)、名稱及部門最低工資奕污。

select EMP.deptno, DEPT.dname, min(sal)
from emp
join dept on emp.deptno = dept.deptno
group by EMP.deptno, dept.dname
having min(sal) > (select min(sal)
                   from emp 
                   where deptno=10)

4.查詢員工工資為其部門最低工資的員工的編號(hào)和姓名及工資萎羔。

select empno, ename, sal
from emp
join dept on emp.deptno = dept.deptno
where sal in (select min(sal)
             from emp
             group by deptno )

5.顯示經(jīng)理是KING的員工姓名，工資菊值。

select a.ename, a.sal
from emp a
join emp b on a.mgr = b.empno
where b.ename = 'KING'

6.顯示比員工SMITH參加工作時(shí)間晚的員工姓名外驱，工資，參加工作時(shí)間腻窒。

select ename, sal, hiredate
from emp
where hiredate > (select hiredate
                  from emp
                  where ename = 'SMITH')

7.使用子查詢的方式查詢那些職員在NEW YORK工作昵宇。

select ename
from emp
join dept on emp.deptno = dept.deptno
where loc = 'NEW YORK'

8.寫一個(gè)查詢顯示和員工SMITH工作在同一個(gè)部門的員工姓名，雇傭日期儿子，查詢結(jié)果中排除SMITH瓦哎。

select ename, hiredate
from emp
where deptno = (select deptno
                from emp
                where ename='SMITH') 
                and  ename <> 'SMITH'

9.寫一個(gè)查詢顯示其工資比全體職員平均工資高的員工編號(hào)、姓名。

select empno, ename
from emp
where sal > (select avg(sal)
             from emp)

10.寫一個(gè)查詢顯示其上級(jí)領(lǐng)導(dǎo)是King的員工姓名蒋譬、工資割岛。

select a.ename, a.sal
from emp a
join emp b on a.mgr = b.empno
where a.mgr = (select empno
               from emp
               where ename = 'KING')

11.顯示所有工作在RESEARCH部門的員工姓名，職位犯助。

select ename, job
from emp
join dept on emp.deptno = dept.deptno
where dept.dname = 'RESEARCH'

12.查詢每個(gè)部門的部門編號(hào)癣漆、平均工資，要求部門的平均工資高于部門20的平均工資剂买。

select deptno, avg(sal) 
from emp
group by deptno
where avg(sal) > (select avg(sal)
                  from emp
                  group by deptno)

13.查詢大于自己部門平均工資的員工姓名惠爽，工資，所在 部門平均工資瞬哼，高于部門平均工資的額度婚肆。

select e.ename, e.sal, b.avgsal, e.sal-b.avgsal  
from emp e
join(select deptno,avg(sal) avgsal
from emp
group by deptno) b on e.deptno = b.deptno
where e.sal>b.avgsal

注：b.avg(sal) 必須得起別名。
14.列出至少有一個(gè)雇員的所有部門坐慰。

select deptno, count(*)
from emp
group by deptno

15.列出薪金比"SMITH"多的所有雇員.

select ename
from emp
where sal > (select sal
             from emp
             WHERE ename = 'SMITH')

16.列出入職日期早于其直接上級(jí)的所有雇員.

select
from emp woker
join emp manager on woker.mgr = manager

17.找員工姓名和直接上級(jí)的名字较性。

select woker.ename , manager.ename
from emp woker
join emp manager on woker.mgr = manager.empno

18.顯示部門名稱和人數(shù)

select dname, nvl(count(empno),0)
from emp
right join dept on emp.deptno = dept.deptno
group by dname

19.顯示每個(gè)部門的最高工資的員工
方法1：

SELECT * 
FROM EMP 
WHERE (DEPTNO,SAL) IN (SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO)

方法2：

select *
from emp a
join(select deptno, max(sal) maxsal
from emp 
group by deptno) b on a.deptno = b.deptno and a.sal = b.maxsal   

20.顯示出和員工號(hào)7369部門相同的員工姓名，工資

select ename, sal
from emp
where deptno = (select deptno 
                from emp
                where empno = 7369) and empno <> 7369

21.顯示出和姓名中包含"W"的員工相同部門的員工姓名

select ename
from emp
where deptno = (select deptno
                from emp
                where ename like '%W%' )

22.顯示出工資大于平均工資的員工姓名结胀，工資

select ename ,sal
from emp
where sal > (select avg(sal)
             from emp  )

23.顯示出工資大于本部門平均工資的員工姓名 工資

select ename, sal
from emp a
join(select DEPTNO, avg(sal) avgsal
     from emp
     group by deptno) b
on a.deptno = b.deptno
where a.sal > b.avgsal

24.顯示每位經(jīng)理管理員工的最低工資赞咙，及最低工資者的姓名。
方法1：

select EMP.ename,EMP.sal
 from EMP
 join (select EMP.mgr, min(sal) minsal
 from EMP
 GROUP BY EMP.mgr) tempTab on EMP.mgr = tempTab.mgr and EMP.sal = tempTab.minsal

方法2：

select sal, ename
from emp
where (mgr, sal) in (select mgr, min(sal)
                    from emp
                    group by mgr)

25.顯示比工資最高的員工參加工作時(shí)間晚的員工姓名把跨，參加
工作時(shí)間

select ename, HIREDATE
from emp
where  hiredate >(select hiredate
                   from emp
                   where sal =(select max(sal) from emp))              

26.顯示出平均工資最高的的部門平均工資及部門名稱

SELECT dname, avgsal
from dept d
join(select deptno, avg(sal) avgsal
from emp 
GROUP BY deptno) temptab on d.deptno = TEMPTAB.DEPTNO
where avgsal = (select max(avg(sal))
                 from emp
                 group by deptno)