---求平均工资SELECT AVG(sal) FROM emp;-----------大于平均工资SELECT e.ename,e.job,e.salFROM emp eWHERE e.sal>(SELECT AVG(sal) FROM emp)----------e领导编号=m雇员编号--emp雇员表，dept部门表SELECT e.ename 雇员姓名,e.job 雇员职位,e.sal 雇员工资,m.ename 领导姓名,m.job 领导职位,d.dname 部门名称FROM emp e,emp m,dept dWHERE e.sal>(SELECT AVG(sal) FROM emp)AND e.mgr=m.empno(+)AND e.deptno=d.deptno-----------加FROM子查询语句,统计人数SELECT e.ename 雇员姓名,e.job 雇员职位,e.sal 雇员工资,m.ename 领导姓名,m.job 领导职位,d.dname 部门名称,dtemp.count 部门人数FROM emp e,emp m,dept d,(SELECT deptno dno,COUNT(empno) countFROM empGROUP BY deptno) dtempWHERE e.sal>(SELECT AVG(sal) FROM emp)AND e.mgr=m.empno(+)AND e.deptno=d.deptnoAND dtemp.dno(+)=d.deptno----加个工资表。工资等级SELECT e.ename 雇员姓名,e.job 雇员职位,e.sal 雇员工资,m.ename 领导姓名,m.job 领导职位,d.dname 部门名称,dtemp.count 部门人数,s.grade 工资等级FROM emp e,emp m,dept d,(SELECT deptno dno,COUNT(empno) countFROM empGROUP BY deptno) dtemp,salgrade sWHERE e.sal>(SELECT AVG(sal) FROM emp)AND e.mgr=m.empno(+)AND e.deptno=d.deptnoAND dtemp.dno(+)=d.deptnoAND e.sal BETWEEN s.losal AND s.hisal--61m-----使用此工资等级的人数SELECT s1.grade sg,COUNT(e1.empno) countFROM emp e1,salgrade s1WHERE e1.sal BETWEEN s1.losal AND s1.hisalGROUP BY s1.grade--第七部--嵌入工资等级的人数,以s1.grade为分组----SELECT s1.grade sg,COUNT(e1.empno) countFROM emp e1,salgrade s1WHERE e1.sal BETWEEN s1.losal AND s1.hisalGROUP BY s1.grade;----完成体SELECT e.ename 雇员姓名,e.job 雇员职位,e.sal 雇员工资,m.ename 领导姓名,m.job 领导职位,d.dname 部门名称,dtemp.count 部门人数,s.grade 工资等级,stemp.count 等级人数--m领导,FROM emp e,emp m,dept d,(SELECT deptno dno,COUNT(empno) countFROM empGROUP BY deptno) dtemp,--dtemp 部门人数salgrade s,(SELECT s1.grade sg,COUNT(e1.empno) countFROM emp e1,salgrade s1WHERE e1.sal BETWEEN s1.losal AND s1.hisalGROUP BY s1.grade) stemp--stemp 工资等级人数WHERE e.sal>(SELECT AVG(sal) FROM emp)AND e.mgr=m.empno(+)AND e.deptno=d.deptnoAND dtemp.dno(+)=d.deptnoAND e.sal BETWEEN s.losal AND s.hisalAND s.grade=stemp.sg;