思考題
查詢工資比Jones工資高的員工信息颂翼？
select * from emp
where sal > (select sal from emp where ename in 'JONES');
查詢工資最低的員工姓名圾笨？
select ename from emp
where sal = (select min(sal) from emp);
練習(xí)一
- 查詢?nèi)肼毴掌谧钤绲膯T工姓名天吓，入職日期
select ename,hiredate
from emp
where hiredate = (select min(hiredate) from emp);
- 查詢工資比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 in 'SMITH')
and dept.loc in 'CHICAGO';
- 查詢?nèi)肼毴掌诒?0部門入職日期最早的員工還要早的員工姓名车遂，入職日期
一種解法：
select ename,hiredate
from emp
where hiredate <
(select min(hiredate) from emp group by deptno having deptno in 20);
另一種解法：
select ename,hiredate
from emp
where hiredate <
(select min(hiredate) from emp where deptno in 20);
- 查詢部門人數(shù)大于所有部門平均人數(shù)的的部門編號(hào)，部門名稱舶担，部門人數(shù)
select dept.deptno,dept.dname,count(emp.deptno)
from emp join dept
on emp.deptno = dept.deptno
group by dept.deptno,dept.dname
having count(emp.deptno) >
(select avg(count(deptno)) from emp group by deptno);
練習(xí)二
- 查詢?nèi)肼毴掌诒?0部門任意一個(gè)員工晚的員工姓名坡疼、入職日期，不包括10部門員工
select ename,hiredate
from emp
where hiredate >
any (select hiredate from emp where deptno in 10)
and deptno <> 10;
- 查詢?nèi)肼毴掌诒?0部門所有員工晚的員工姓名衣陶、入職日期柄瑰，不包括10部門員工
select ename,hiredate
from emp
where hiredate >
all (select hiredate from emp where deptno in 10)
and deptno <> 10;
- 查詢職位和10部門任意一個(gè)員工職位相同的員工姓名，職位剪况，不包括10部門員工
select ename,job
from emp
where job =
any (select job from emp where deptno in 10)
and deptno <> 10;
練習(xí)三
- 查詢職位及經(jīng)理和10部門任意一個(gè)員工職位及經(jīng)理相同的員工姓名教沾，職位，不包括10部門員工
select ename,job
from emp
where (job,mgr) in
(select job,mgr from emp where deptno in 10)
and deptno not in 10;
- 查詢職位及經(jīng)理和10部門任意一個(gè)員工職位或經(jīng)理相同的員工姓名译断，職位授翻，不包括10部門員工
select ename,job
from emp
where job in
(select job from emp where deptno in 10)
or mgr in
(select mgr from emp where deptno in 10)
and deptno not in 10;
練習(xí)四
- 查詢比自己職位平均工資高的員工姓名、職位，部門名稱堪唐，職位平均工資
此題我不熟練巡语，建議重復(fù)練習(xí)
select ename,emp.job,dname,avgSal
from emp,
(select job,avg(sal) avgSal from emp group by job) tabjob,
dept
where emp.job = tabjob.job
and emp.deptno = dept.deptno
and avgSal < sal;
- 查詢職位和經(jīng)理同員工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');
- 查詢不是經(jīng)理的員工姓名。
select ename
from emp
where empno not in
(select nvl(mgr,0) from emp);
此處要特別注意只要 (select mgr from emp) 語句返回只要有一條空則
則會(huì)導(dǎo)致主查詢沒有記錄返回合陵。這是因?yàn)樗械臈l件和空值比較結(jié)果都是空值枢赔。因此無論
什么時(shí)候只要空值有可能成為子查詢結(jié)果集合中的一部分，就不能使用NOT IN 運(yùn)算符
切記拥知！切記Ｌぐ荨！否則最終會(huì)害死自己和同事還有公司的＞偈Ｖ此怼？铡；Ы摹！峦嗤！
練習(xí)五
- 查詢?nèi)肼毴掌谧钤绲那?名員工姓名蕊唐，入職日期。
select ename,hiredate
from (select * from emp order by hiredate asc)
where rownum <= 5;
- 查詢工作在CHICAGO并且入職日期最早的前2名員工姓名烁设，入職日期替梨。
select ename,hiredate
from (select ename,hiredate
from emp,dept
where emp.deptno = dept.deptno
and loc in 'CHICAGO'
order by hiredate asc)
where rownum <= 2;
第二種解法：
select emp.ename,emp.hiredate
from emp join dept
on emp.deptno = dept.deptno
where dept.loc in 'CHICAGO' and rownum <= 2
order by hiredate;
練習(xí)六
- 按照每頁顯示5條記錄，分別查詢第1頁装黑，第2頁副瀑，第3頁信息，要求顯示員工姓名恋谭、入職日期糠睡、部門名稱。
第一頁
select ename,hiredate,dname
from (select ename,hiredate,dname,rownum rn
from emp join dept
on emp.deptno = dept.deptno where rownum < 6 )
where rn > 0;
第二頁
select ename,hiredate,dname
from (select ename,hiredate,dname,rownum rn
from emp join dept
on emp.deptno = dept.deptno where rownum < 11 )
where rn > 5;
第三頁
select ename,hiredate,dname
from (select ename,hiredate,dname,rownum rn
from emp join dept
on emp.deptno = dept.deptno where rownum < 16 )
where rn > 10;
練習(xí)七
- 按照每頁顯示5條記錄疚颊，分別查詢工資最高的第1頁狈孔，第2頁，第3頁信息材义，要求顯示員工姓名均抽、入職日期、部門名稱其掂、工資油挥。
-- 第一步：將其所有的結(jié)果查出
select ename,hiredate,dname,sal
from emp,dept
where emp.deptno = dept.deptno
order by sal desc;
-- 第二步：將第一步的結(jié)果作為一個(gè)表再次進(jìn)行查詢,
--將將偽列重命名為一個(gè)列，此表可以作為制最大數(shù)值的表
select rownum rn tab_allDate.* from
(select ename,hiredate,dname,sal
from emp,dept
where emp.deptno = dept.deptno
order by sal desc) tab_allDate;
-- 比如此處可以添一句where rownum <= maxNum
-- 而若想分頁則可以將以上的集合再判斷 rn > minNum
-- 第三步：以上結(jié)果再次作業(yè)一個(gè)表進(jìn)行分頁
-- 第一頁：0~5
select * from
(select rownum rn,tab_allDate.* from
(select ename,hiredate,dname,sal
from emp,dept
where emp.deptno = dept.deptno
order by sal desc) tab_allDate
where rownum <= 5)
where rn > 0;
--第二頁：5~10
select * from
(select rownum rn,tab_allDate.* from
(select ename,hiredate,dname,sal
from emp,dept
where emp.deptno = dept.deptno
order by sal desc) tab_allDate
where rownum <= 10)
where rn > 5;
-- 第三頁: 11~15
select * from
(select rownum rn,tab_allDate.* from
(select ename,hiredate,dname,sal
from emp,dept
where emp.deptno = dept.deptno
order by sal desc) tab_allDate
where rownum <= 15)
where rn > 10;
課后作業(yè)
- 查詢工資高于編號(hào)為7782的員工工資，并且和7369號(hào)員工從事相同工作的員工的編號(hào)喘漏、姓名及工資护蝶。
select empno,ename,sal,job
from emp
where sal > (select sal from emp where empno in 7782)
and job in (select job from emp where empno in 7369);
- 查詢工資最高的員工姓名和工資。
select ename,sal
from emp
where sal in (select max(sal) from emp);
- 查詢部門最低工資高于10號(hào)部門最低工資的部門的編號(hào)翩迈、名稱及部門最低工資持灰。
--第一步：查詢部門最低工資
select emp.deptno,dname,min(sal) from emp,dept
where emp.deptno = dept.deptno;
--第二步：查詢10號(hào)部門的最低工資
select min(sal) from emp where deptno in 10;
--第三步：解題
--第一種 解題方式：
select emp.deptno,dname,min(sal) from emp,dept
where emp.deptno = dept.deptno
group by emp.deptno,dname --group by是所有列必須出現(xiàn)在后邊
having min(sal) > (select min(sal) from emp where deptno in 10);
--第二種 解題方式：
select tab_minSal_gb_deptno.deptno,dname,tab_minSal_gb_deptno.minSal_by_deptno
from (select min(sal) minSal_by_deptno,deptno from emp group by deptno) tab_minSal_gb_deptno,
(select min(sal) minSal_deptno10 from emp where deptno in 10),dept
where tab_minSal_gb_deptno.deptno = dept.deptno
and minSal_by_deptno > minSal_deptno10;
- 查詢員工工資為其部門最低工資的員工的編號(hào)和姓名及工資。
--第一步：先查部門的最低工資
select deptno,min(sal) from emp group by deptno;
--第二步：將上面的結(jié)果集作為一個(gè)表负饲，進(jìn)行多表查詢
select empno,ename,sal
from emp,(select deptno,min(sal) minSal_gb_deptno from emp group by deptno) tab_minSal_gb_deptno
where emp.deptno = tab_minSal_gb_deptno.deptno
and sal = minSal_gb_deptno;
- 顯示經(jīng)理是KING的員工姓名堤魁，工資。
--第一步：先查出KING的員工編號(hào)
select empno from emp where ename in 'KING';
--第二步：解題
select ename,sal
from emp
where mgr in (select empno from emp where ename in 'KING')
- 顯示比員工SMITH參加工作時(shí)間晚的員工姓名返十，工資妥泉，參加工作時(shí)間。
--第一步：先查出SMITH參加工作的時(shí)間
select hiredate from emp where ename in 'SMITH';
--第二步：解題
select ename,sal,hiredate
from emp
where hiredate > (select hiredate from emp where ename in 'SMITH');
- 使用子查詢的方式查詢哪些職員在NEW YORK工作洞坑。
select * from emp,dept
where emp.deptno in dept.deptno
and loc in 'NEW YORK';
- 寫一個(gè)查詢顯示和員工SMITH工作在同一個(gè)部門的員工姓名盲链，雇用日期，查詢結(jié)果中排除SMITH迟杂。
select ename,hiredate
from emp,dept
where emp.deptno in dept.deptno
and emp.deptno in (select deptno from emp where ename in 'SMITH')
and ename not in 'SMITH';
- 寫一個(gè)查詢顯示其工資比全體職員平均工資高的員工編號(hào)刽沾、姓名。
select empno,ename
from emp
where sal > (select avg(sal) from emp);
- 寫一個(gè)查詢顯示其上級(jí)領(lǐng)導(dǎo)是King的員工姓名排拷、工資侧漓。
select ename,sal
from emp
where mgr in (select empno from emp where ename in 'KING');
- 顯示所有工作在RESEARCH部門的員工姓名，職位监氢。
select ename,job
from emp,dept
where emp.deptno in dept.deptno
and dname in 'RESEARCH';
- 查詢每個(gè)部門的部門編號(hào)布蔗、平均工資，要求部門的平均工資高于部門20的平均工資浪腐。
--第一步：查詢第個(gè)部門的部門編號(hào)纵揍，平均工資
select deptno,avg(sal) avgSal_gb_deptno
from emp
group by deptno;
--第二步：查詢部門20的平均工資
select deptno,avg(sal) avgSal_gb_deptno_20
from emp
group by deptno
having deptno in 20;
--第三步：解題
select deptno,avg(sal)
from emp
group by deptno
having avg(sal) >
(select avg(sal)
from emp
group by deptno
having deptno in 20);
- 查詢大于自己部門平均工資的員工姓名，工資议街，所在部門平均工資泽谨，高于部門平均工資的額度。
--第一步：查詢自己部門的平均工資
select deptno,avg(sal) from emp group by deptno;
--第二步：將以上結(jié)果作為一個(gè)表進(jìn)行多表查詢
select ename,sal,avgSal_gb_deptno,(sal - avgSal_gb_deptno) 高于平均工資的差值
from emp,
(select deptno,avg(sal) avgSal_gb_deptno from emp group by deptno) tab_gb_deptno
where emp.deptno in tab_gb_deptno.deptno
and sal > avgSal_gb_deptno;
- 列出至少有一個(gè)雇員的所有部門
select deptno,count(1)
from emp
group by deptno
having count(*) > 0;
- 列出薪金比"SMITH"多的所有雇員
select * from emp
where sal > (select sal from emp where ename in 'SMITH');
- 列出入職日期早于其直接上級(jí)的所有雇員
select worker.ename,worker.hiredate
from emp worker,emp manager
where worker.mgr in manager.empno
and worker.hiredate < manager.hiredate;
- 找員工姓名和直接上級(jí)的名字
select worker.ename 員工姓名,manager.ename 直接上級(jí)姓名
from emp worker,emp manager
where worker.mgr in manager.empno(+);
- 顯示部門名稱和人數(shù)
--第一步：先查出部門人數(shù)
select count(*) countNum,deptno
from emp
group by deptno;
--第二步：以上面的查詢結(jié)果作一個(gè)表傍睹，多表查詢
select dname,countNum
from dept,(
select count(*) countNum,deptno
from emp
group by deptno) tab_countNum_gb_deptno
where dept.deptno in tab_countNum_gb_deptno.deptno(+);
- 顯示每個(gè)部門的最高工資的員工
select ename,sal,deptno
from emp
where sal in
(select max(sal) from emp group by deptno);
- 顯示出和員工號(hào)7369部門相同的員工姓名隔盛，工資
select ename,sal
from emp
where deptno in (select deptno from emp where empno in 7369);
- 顯示出和姓名中包含"W"的員工相同部門的員工姓名
--第一步：先查出姓名中包含"W"的員工部門
select deptno from emp where ename like '%W%';
--第二步：解題
select ename
from emp
where deptno in
(select deptno from emp where ename like '%W%')
- 顯示出工資大于平均工資的員工姓名，工資
select ename,sal
from emp
where sal > (select avg(sal) from emp);
- 顯示出工資大于本部門平均工資的員工姓名拾稳，工資
--第一步：查詢本部門的平均工資
select avg(sal),deptno from emp group by deptno;
--第二步：解題
select ename,sal
from emp,(select avg(sal) avgSal_gb_deptno,deptno from emp group by deptno) tab_avgSal_gb_deptno
where emp.deptno in tab_avgSal_gb_deptno.deptno
and sal > avgSal_gb_deptno;
- 顯示每位經(jīng)理管理員工的最低工資吮炕，及最低工資者的姓名
--第一步：先查出每位經(jīng)理管理的員工的最低工資
select min(sal),mgr from emp where mgr is not null group by mgr;
--第二步：解題
select sal,ename
from emp,(select min(sal) minSal,mgr from emp where mgr is not null group by mgr) tab_minSal_gb_mgr
where emp.mgr in tab_minSal_gb_mgr.mgr
and sal in minSal;
- 顯示比工資最高的員工參加工作時(shí)間晚的員工姓名，參加工作時(shí)間
select ename,hiredate
from emp
where hiredate > (select hiredate from emp where sal in (select max(sal) from emp));
- 顯示出平均工資最高的的部門平均工資及部門名稱
--第一步：先查出部門平均工資及名稱
select avg(sal) avgSal,deptno from emp group by deptno;
--第二步：解題
--方法一
select * from (select dname,avg(sal) avgsal from emp e,dept d where e.deptno = d.deptno group by dname)
where avgsal = (select max(avg(sal)) from emp group by deptno);
--方法二
select dname,avg(sal) 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);
分割線
博主為咯學(xué)編程：父母不同意學(xué)編程访得，現(xiàn)已斷絕關(guān)系;戀人不同意學(xué)編程龙亲，現(xiàn)已分手;親戚不同意學(xué)編程陕凹，現(xiàn)已斷絕來往;老板不同意學(xué)編程,現(xiàn)已失業(yè)三十年。鳄炉。杜耙。。拂盯。佑女。如果此博文有幫到你歡迎打賞，金額不限谈竿。团驱。。
