SQL1999学习笔记

SQL1999学习

 

​select ename,sal form 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)) t group by deptno

 

select ename from emp where empno in(select distinct 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 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))