【正文】 from emp)有哪些人的薪水是在整個雇員的平均薪水之上的select ename,sal from emp where sal (select avg(sal)from emp)雇員中哪些人是經(jīng)理人1,首先查詢mgr中有哪些號碼2,再看有哪些人員的號碼在此出現(xiàn)select distinct mgr from emp where mgr is not null order by mgrselect ename from emp where empno in(select distinct mgr from emp where mgr is not null)where in 中不讓寫orderby select ename from emp where empno in(select distinct mgr from emp where mgr is not null order by mgr)在From子句中使用子查詢部門平均薪水的等級1,首先將每個部門的平均薪水求出來2,然后把結果當成一張表，再用這張結果表和salgrade表做連接，以此求得薪水等級select deptno,avg(sal)from emp group by deptno select * from(select deptno,avg(sal)avg_sal from emp group by deptno)t join salgrade on avg_sal between losal and hisal。oracle下rownum只能使用 = 等比較操作符，select rownum,emp.* from emp where rownum(錯誤的寫法)例如，當我們要求薪水最高的前5個人時，最直接的想法可以這樣寫： select * from emp where rownumselect * from(select * from emp order by sal desc)t where rownum第五篇：黑馬程序員c語言教程：Oracle指令(6)為什么in的后面不能order by ？求部門經(jīng)理人中平均薪水最低的部門名稱(思考題)第一步,求部門經(jīng)理的雇員編號select distinct mgr from emp where mgr is not null 第二步，按部門統(tǒng)計，求部門經(jīng)理的平均薪水select deptno,avg(sal)avg_sal from emp where empno in(select distinct mgr from emp where mgr is not null)group by deptno 第三步，求最低值select min(avg(sal))from emp where empno in(select distinct mgr from emp where mgr is not null)group by deptno 第四步，求部門經(jīng)理人中平均薪水最低的部門名稱select deptno from(2222222222222)where avg_sal =(***333333333)select dname from dept where deptno in(select deptno from(select deptno,avg(sal)avg_sal from emp where empno in(select distinct mgr from emp where mgr is not null)group by deptno)where avg_sal =(select min(avg(sal))from emp where empno in(select distinct mgr from emp where mgr is not null)group by deptno))求比普通員工的最高薪水還要高的經(jīng)理人名稱1,求所有經(jīng)理的編號create or replace view v1 as select distinct mgr from emp where mgr is not null select * from v12,普通員工的最高薪水select max(sal)from emp where empno not in(select distinct mgr from emp where mgr is not null)3，select ename from emp where empno in(select * from v1)and sal (select max(sal)from emp where empno not in(select distinctmgr from emp where mgr is not null))即:select ename from emp where empno in(select distinct mgr from emp where mgr is not null)and sal (select max(sal)from emp where empno not in(select distinct mgr from emp where mgr is not null))求薪水最高的前5名雇員1,先觀察一下2,看看rownum的作用3,不是我們想要的結果select ename,sal from emp where rownum求薪水最高的第6到第10名雇員(重點掌握)這種沒法實現(xiàn),oracle下rownum只能使用 = 等比較操作符注意里面的rownum和外面的rownum的區(qū)別，外面要想訪問里面的rownum，必須取得一個別名。%A%39。%A%39。使用union、minus使用union、minus可以用來實現(xiàn)結果集的合并和去除（可以理解為加和減），例如： select * from emp where deptno=10 union select * from emp where deptno=20。相當于select * from emp where deptno in(10,20)and sal=1500求分段顯示薪水的個數(shù)如：scale total 5000 8select 39。=801或者顯示成為注意：使用between..and..的時候，包含了最大和