咧出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名，部门人数。平均工资

1.select sal from emp where ename IN('SMITH，‘ALLEN’);

2.select e.empno,e,ename,e.sal

from emp e

where e.sal>ANY(

    select sal from emp where ename IN('SMITH，‘ALLEN’));

3.select e.empno,e,ename,e.sal

from emp e

where e.sal>ANY(

    select sal from emp where ename IN('SMITH，‘ALLEN’))

AND e.ename NOT IN('SMITH，‘ALLEN’);

4.找到领导信息

select e.empno,e,ename,e.sal,m.ename

from emp e，emp m

where e.sal>ANY(

    select sal from emp where ename IN('SMITH，‘ALLEN’))

AND e.ename NOT IN('SMITH，‘ALLEN’)

AND e.mgr=m.empno(+);

5.统计部门的信息，在from字句中完成

select e.empno,e,ename,e.sal,m.ename,temp.count,temp.avg,temp.max,temp.min,d.dname

from emp e，emp m，(

 select deptno dno,COUNT(empno) count,AVG(sal) avg,MAX(sal) max,MIn(sal) min

 from emp

 group by(deptno) temp,dept d

where e.sal>ANY(

    select sal from emp where ename IN('SMITH，‘ALLEN’))

AND e.ename NOT IN('SMITH，‘ALLEN’)

AND e.mgr=m.empno(+)

AND tept.dno=d.dedeptno

AND e.deptno=d.deptno;

SELECT deptno dno,COUNT(empno) count
    FROM emp
    GROUP BY deptno;

group by:防止重复

SELECT deptno dno FROM emp;