6.查询各个部门的名称和员工人数。
SELECT e.c,d.dname
FROM（
SELECT count(*) c, deptno FROM emp GROUP BY deptno) e,
dept d
WHERE e.deptno =d.deptno
7.查询各个职位员工工资大于平均工资(平均工资包括所有员工)的人数和员工职位。
SETECT job, count(empno) FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp)
GROUP BY job;
8.查询工资最高的3名员工信息。
SELECT *
FROM (SELECT * FROM emp order by sal desc)
WHERE rownum <=3:
9.查询各部门工资最高的员工信息。
SELECT *
FROM emp e
WHERE e.sal= (SELECT MAX(sal)
FROM emp WHERE deptno =e.deptno)；
10.查询每个部门工资最高的前两名员工。
SELECT *
FROM emp e
WHERE
(SELECT count(*)
FROM emp
WHERE sal > e.sal AND e.deptro = deptno) <2
11.查询出有3个以上下属的员工信息。
SELECT *
FROM emp e
WHERE
(SELECT count(*) FROM emp WHERE e.empno = mgr) >2；
