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;