Oracle_sql_(5)

41.查询各种工作的最低工资select job,min(sal) from emp group by job;42.查询各个部门中的不同工种的最高工资select deptno,job,max(sal) from emp group by deptno,job;43.查询10号部门员工以及领导的信息select a.* from emp a,emp b where a.deptno=10 and a.mgr=b.empno;44.查询各个部门的人数及平均工资select deptno,count(empno),avg(sal) from emp group by deptno;下面的参考答案是错误的，统计人数，不是累加，不能用sum,应改为countselect deptno,sum(empno),avg(sal) from scott.emp group by deptno;45.查询工资为某个部门平均工资的员工信息select * from emp where sal in (select avg(sal) from emp group by deptno);注意不能使用等于号,使用等于号要加anyselect * from emp where sal = any(select avg(sal) from emp group by deptno);46.查询工资高于本部门平均工资的员工的信息select a.* from emp a where sal > (select avg(sal) from emp b where a.deptno=b.deptno);47.查询工资高于本部门平均工资的员工的信息及其部门的平均工资select a.*,(select avg(sal)from emp where deptno=a.deptno) from emp a where sal>(select avg(sal) from emp where deptno=a.deptno);48.查询工资高于20号部门某个员工工资的员工的信息select * from emp where sal > any(select sal from emp where deptno=20);49.统计各个工种的人数与平均工资select job,count(*),avg(sal)from emp group by job;50.统计每个部门中各个工种的人数与平均工资select deptno,job,count(*),avg(sal) from emp group by deptno,job;