1 不许用组函数 求薪水的最高值:
可建表 select distinct (去重) e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal) ;
此表中包含e1 中除最高值外的所有值. 从e1中选择不在此表中的值即可 .
select distinct sal from emp where sal not in (select distinct (去重) e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal)) ;
2 求平均薪水最高的部门的部门编号
先求出每个部门的平均薪水, 部门编号 : select avg(sal)avg_sal ,deptno from emp group by deptno
求出此表中部门编号的最大值 : select max(avg_sal) from
(select avg(sal)avg_sal ,deptno from emp group by deptno)
上表中平均薪水值等于最大值的部门编号为:
select deptno, avg_sal from
(select avg(sal)avg_sal ,deptno from emp group by deptno)
where avg_sal =
(select max(avg_sal) from
(select avg(sal)avg_sal ,deptno from emp group by deptno) )
/
3 求平均薪水最高的部门的部门名称
得到以上部门编号后 , 直接在dept 表中查询名称:
select dname from dept where deptno =
(
select deptno from
(select avg(sal)avg_sal ,deptno from emp group by deptno)
where avg_sal =
(select max(avg_sal) from
(select avg(sal)avg_sal ,deptno from emp group by deptno)
)
)
4求平均薪水等级最高的部门的部门名称
步步为营:
首先求出每个部门的平均薪水等级:
select avg_sal,deptno,grade from
(select deptno,avg(sal)avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal)
在以上基础上求出最高等级
select max(grade) from
(
select avg_sal,deptno,grade from
(select deptno,avg(sal)avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal)
)
等级为最高等级的部门编号:
select deptno from
(
select avg_sal,deptno,grade from
(select deptno,avg(sal)avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal)
)
where grade =
(
select max(grade) from
(
select avg_sal,deptno,grade from
(select deptno,avg(sal)avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal)
)
)
最后从dept表中查找这些部门的名称:
select dname from dept where deptno in
(
select deptno from
(
select avg_sal,deptno,grade from
(select deptno,avg(sal)avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal)
)
where grade =
(
select max(grade) from
(
select avg_sal,deptno,grade from
(select deptno,avg(sal)avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal)
)
)
)
其实上述命令中 重复利用了
select avg_sal,deptno,grade from
(select deptno,avg(sal)avg_sal from emp group by deptno) t
join salgrade s on(t.avg_sal between s.losal and s.hisal)
这个表 . 为了简洁起见,可以将这个表建立然后引用:
首先获得建表权限:
conn sys/scott as sysdba;
创建这个表,起名为 v$_dept_avg_sal_info :
SQL> create view v$_dept_avg_sal_info as
2 select deptno,grade from
3 (select deptno,avg(sal)avg_sal from emp group by deptno) t
4 join salgrade s on (t.avg_sal between s.losal and s.hisal)
5 /
然后上述题目的答案可写为:
1 select dname from
2 v$_dept_avg_sal_info
3 join dept on(v$_dept_avg_sal_info.deptno=dept.deptno)
4 where grade =
5 (
6 select min(grade) from
7 v$_dept_avg_sal_info
8* )
SQL> /
组函数的嵌套:
求最高的平均薪水时, 其实不必先求出平均薪水再求其最高值, 而是存在以下简便 写法:
select max(avg(sal)) from emp group by deptno;