Oracle_sql_(4)

33.查询在SALES部门工作的员工的姓名信息select ename from emp where deptno in (select deptno from dept where dname='SALES');34.查询工资高于公司平均工资的所有员工信息select e.* from emp e where sal > (select avg(sal) from emp);35.查询与SMITH员工从事相同工作的所有员工信息select * from emp where job = (select job from emp where ename='SMITH');36.列出工资等于30号部门中某个员工工资的所有员工的姓名和工资select ename,sal from emp where sal in (select sal from emp where deptno=30);37.查询工资高于30号部门中工作的所有员工的工资的员工姓名和工资select ename,sal from emp where sal > all(select sal from emp where deptno=30);38.查询每个部门中的员工数量、平均工资和平均工作年限select count(empno),avg(sal),avg(extract(year from current_date)-extract(year from hiredate)), deptno from emp group by deptno;39.查询从事同一种工作但不属于同一部门的员工信息select e.ename,e.empno,e.sal,e.deptno,m.ename,m.empno,m.sal from emp e left join emp m on e.mgr=m.empno;40.查询各个部门的详细信息以及部门人数、部门平均工资select a.*,b.* from dept a,(select deptno,count(*),avg(sal) from emp group by deptno) b where a.deptno=b.deptno;