黑马程序员c语言教程：Oracle指令(5)_黑马程序员c语言教程

黑马程序员c语言教程：Oracle指令(5)由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“黑马程序员c语言教程”。

---------------------------不准用组函数(即MAX()),求薪水的最高值(面试题)--第一种解决办法:--1,先把所有薪水按照倒序排列--2,再取第一行 select * from(select sal from emp order by sal desc)t where rownum=1

--第二种解决办法:--1,先跨表查询自己,先求出的结果中,e1.sal不可能出现最大数--2,然后再not in select e2.sal from emp e1,emp e2 where e1.sal>e2.sal select sal from emp where sal not in(select e2.sal from emp e1,emp e2 where e1.sal>e2.sal)

-----求平均薪水最高的部门的部门编号--第一种解决办法:--1,先求出每个部门的平均薪水, select deptno,avg(sal)avg_sal from emp group by deptno--2,再求每个部门的平均薪水的最高值, select max(avg_sal)from(***1111111111)--3,最后再求第一步结果中avg_sal = 最高薪水的记录.select deptno from(111111111111)where avg_sal =(22222222)

select deptno from(select deptno,avg(sal)avg_sal from emp group by deptno)where avg_sal =(select max(avg_sal)from(select deptno,avg(sal)avg_sal from emp group by deptno))

--没法考虑并列第一的情况 select deptno from(select deptno,avg(sal)avg_sal from emp group by deptno order by avg(sal)desc)

where rownum

--第二种解决办法:--1,将上面的第一步第二步合并,先求最高平均薪水,用max(avg(sal))的办法--不能写成select deptno,max(avg(sal))from emp group by deptno select max(avg(sal))from emp group by deptno--2,求出每个部门的平均薪水

select deptno,avg(sal)avg_sal from emp group by deptno--3,最后再求第二步结果中(即每个部门的平均薪水),avg_sal =(第一步结果)的记录.即avg_sal =最高薪水的记录.select deptno from(select deptno,avg(sal)avg_sal from emp group by deptno)where avg_sal =(select max(avg(sal))from emp group by deptno)

--第三种解决办法:--1,先求出每个部门的平均薪水, select avg(sal)avg_sal from emp group by deptno--2,求最高平均薪水,用max(avg(sal))的办法

select max(avg(sal))from emp group by deptno--3,再使用having语句, avg(sal)= 第二步的结果 注意：为组函数起的别名在having中不能用

select deptno from emp group by deptno having avg(sal)=(select max(avg(sal))from emp group by deptno)

-----求平均薪水最高的部门的部门名称--1,部门平均最高薪水

--2,得到部门编号列表,注意用group by deptno--3,再应用having子句, having avg(sal)=(第一步的结果)--4,得到平均最高薪水的那个部门的编号--5,再得到部门名称

select dname from dept where deptno in(select deptno from(select deptno,avg(sal)avg_sal from emp group by deptno)

where avg_sal =(select max(avg_sal)from(select deptno,avg(sal)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

--第二步：最低的等级值

select min(grade)from(***1111111111)

--第三步：等于最低值的部门编号------------有错误,应该是grade= select deptno from(111111111111)where grade =(22222222222222)--第四步：求名称

select dname from dept where deptno in(33333333333)select dname from dept where deptno in（select deptno from(select * from(select deptno, avg(sal)avg_sal from emp group by deptno)t join salgrade on avg_sal between losal and hisal)where grade =(select min(grade)from(select * from(select deptno, avg(sal)avg_sal from emp group by deptno)t join salgrade on avg_sal between losal and hisal)))

--也可以用视图的方式来解决--conn sys/bjsxt as sysdba--grant create table, create view, create sequence to scott--根据第一步的结果,建立一个view

create or replace view v1 as--必须明确定义列

select deptno, avg_sal, grade from(select deptno,avg(sal)avg_sal from emp group by deptno)t join salgrade on avg_sal between losal and hisal

--查看一下

select * from v1--查询一下

--带入view

select dname from dept where deptno in(select deptno from(v1)where grade =(select min(grade)from v1))