黑马作文650字怎么写？( 二 )

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)) -------------- --求比普通员工的最高薪水还要高的经理人名称 --1,求所有经理的编号
create or replace view v1 as select distinct mgr from emp where mgr is not null select * from v1 --2,普通员工的最高薪水
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 distinct
mgr 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名雇员(重点掌握) --这种没法实现,oracle下rownum只能使用 >= 等比较操作符 --注意里面的rownum和外面的rownum的区别，外面要想访问里面的rownum，必须取得一个别名。
select * from (select ename,sal from emp order by sal desc ) t where rownum>=5 and rownum--所以再套一层select select * from (select t.*,rownum r from (select ename,sal from emp order by sal desc ) t ) where r>=5 and r--还有一种排序方式 select * from (select * from emp order by sal desc)where rownum--2,取前5行
--- --求每个部门中薪水最高的前两名雇员
--1,每个员工的姓名，部门，工资,按部门和工资(倒序)排列
select ename,deptno,sal from emp order by deptno,sal desc --2,套一层,加上个r select ename,deptno,sal,rownum r from (select ename,deptno,sal from emp order by deptno,sal desc) t --3,创建试图
create or replace view v1 as select ename,deptno,sal,rownum r from (select ename,deptno,sal from emp order by deptno,sal desc) t --观察一下
select * from v1
--每个部门中,薪水最高的第一行,并创建试图 create or replace view v2 as select deptno,min(r) min_r from v1 group by deptno
--两个view跨表连接,大于薪水最高的行数,小于最高的行数+1,并且部门编号要匹配 select ename from v1 join v2 on ( v1.deptno = v2.deptno and v1.r >=v2.min_r and v1.r----------------- --面试题: 比较效率
select * from emp where deptno = 10 and ename like \\'%A%\\';select * from emp where ename like \\'%A%\\' and deptno = 10;
-------------------------- --使用union、minus
--使用union、minus可以用来实现结果集的合并和去除（可以理解为加和减），例如： select * from emp where deptno=10 union select * from emp where deptno=20;