Sql语句经典难题

1.找出工资最高的员工。

2.部门中工资最高的人的名单
3.求部门的平均工资的等级
4.不用组函数求工资最高的人的信息。

5.部门工资平均的等级。

6.找出那些人不是经理
7.找出员工和他的领导
8.部门平均工资最高的部门的编号。

9.部门平均工资最高的部门的名称
10.比员工最高工资还高的经理
--select * from emp where sal =(select max(sal) from emp)
--select * from emp where sal in( select deptno,max(sal) from emp group by deptno)
/*
select * from emp e join
(select deptno,max(sal) maxsal from emp group by deptno)t
on (e.deptno=t.deptno and e.sal=t.maxsal)
*/
/*
select t.deptno ,s.grade from salgrade s join
(select deptno,avg(sal) avgsal from emp group by deptno) t
on ( t.avgsal between s.losal and s.hisal )
*/
select deptno,avg(grade) from
(
select e.deptno ,s.grade from emp e
join salgrade s on (e.sal between s.losal and s.hisal)
)
group by deptno
*/
--select * from emp where empno not in (select mgr from emp where mgr is not null)
--select w.ename , m.ename from emp w join emp m on (w.mgr=m.empno)
/*
select dname from dept where deptno=(
select deptno from (select deptno,avg(sal) avgsal from emp group by deptno) where avgsal=
(
/*
select max(avgsal) from
(
select deptno,avg(sal) avgsal from emp group by deptno
)
)
---select max(avg(sal)) from emp group by deptno
)
select * from (
select * from emp where empno in (select mgr from emp)
) where sal >(
select max(sal) from
select * from emp where empno not in (select mgr from emp where mgr is not null)
)
)
*/
select * from emp where sal not in
(
select e.sal from emp e join
emp e1
on(e.sal<e1.sal)
)。