---求部门中哪些人的薪水最高
select ename,sal from emp
join(select max(sal) max_sal, deptno from emp group by deptno)t
on (emp.sal=t.max_sal and emp.deptno=t.deptno)
---求部门的平均薪水的等级
select deptno,avg_sal,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,avg(grade) from
(select deptno,ename,grade from emp join salgrade s
on (emp.sal between s.losal and s.hisal))
group by deptno
----求哪些人是经理人
select ename from emp where empno in (select mgr from emp)
---求不准用组函数,求薪水的最高值(面试题)
select distinct sal from emp where sal not in
(select distinct e1.sal from emp e1 join emp e2 on(e1.sal<e2.sal))
---求平均薪水最高的部门的部门编号
select deptno,avg_sal 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 dname from dept where deptno=
(
select deptno from
本内容试读结束,登录后可阅读更多
下载后可阅读完整内容,剩余4页未读,立即下载