Oracle_sql_(6)

51.查询部门人数大于5的部门的员工的信息
select * from emp where deptno = any(select deptno from emp group by deptno having count(*)>5);
select * from emp where deptno in (select deptno from emp group by deptno having count(*)>5); 

52.查询工资、奖金与10号部门某员工工资、奖金都相同的员工的信息
select sal,comm from emp where (sal,comm)=any(select sal,comm from emp where deptno=10);

53.查询所有员工工资都大于2000的部门的信息
select * from dept where deptno not in (select deptno from emp where sal<2000);

54.查询所有员工工资都大于2000的部门的信息及其员工信息
select a.*,b.*  from emp a ,dept b where a.deptno=b.deptno  and  a.deptno  not in 
(select deptno from emp where sal<2000);

55.查询所有员工工资都在2000~3000之间的部门的信息
select * from dept where deptno not in (select deptno from emp where sal between 2000 and 3000);
select * from dept where deptno not in (select deptno from emp where sal<2000 or sal> 3000);

56.查询所有工资都在2000~3000之间的员工所在部门的员工信息
select * from emp where deptno in (select distinct deptno from emp where sal between 2000 and 3000);

57.查询每个员工的领导所在部门的信息
select d.* from dept d, emp e,emp m where d.deptno=m.deptno and e.mgr=m.empno;

58.查询人数最多的部门信息
select * from dept where deptno in
(select deptno from emp group by deptno having count(*) >= all(select max(count(*)) from emp group by deptno));

59.查询30号部门中工资排序前3名的员工信息(Oracle分页查询,用到rownum)
我的思路和方法：
1.部门按工资降序：
select e.*,rownum from emp e order by sal desc
2.取前三：
select t1.*,rownum r from (select e.*,rownum from emp e order by sal desc )t1 where rownum <= 3
3.加上条件30号部门：
select t1.*,rownum r from (select e.*,rownum from emp e order by sal desc )t1 where rownum <= 3 and deptno=30;
-------------------------------------------------------------------------------------------------------------------
如果改为排序3到6名的员工的信息则会加一层嵌套，即三层嵌套
4.分页3-6：
select t2.* from (select t1.*,rownum r from (select e.*,rownum from emp e order by sal desc )t1
where rownum <= 6 and deptno=30)t2 where t2.r >=3;
-------------------------------------------------------------------------------------------------------------------
参考答案：
select a.*,rownum from(select * from emp order by sal desc)a where rownum<=3 and deptno=30 order by sal desc;

60.查询所有员工中工资排在5~10名之间的员工信息(如果含5或10则查到6条记录)
select t2.* from (select t1.*,rownum r from (select e.*,rownum from emp e order by sal desc )t1
where rownum <= 10 )t2 where t2.r >=5;

61.查询SMITH员工及所有其直接、间接下属员工的信息
select * from emp a where  (a.mgr=(select empno from emp where ename='SMITH'))
or (a.empno=(select mgr from emp where ename='SMITH'))  
or (a.empno=(select empno from emp where ename='SMITH'))
or (a.empno=(select mgr from emp where empno=(select mgr from emp where ename='SMITH')));

62.查询SOCTT员工及其直接、间接上级员工的信息
select * from emp a where a.empno=(select empno from scott.emp where ename='SCOTT')
or a.mgr=(select empno from emp where ename='SCOTT')
or a.mgr=(select empno from emp where mgr=(select empno from emp where ename='SCOTT' ));

63.以树状结构查询所有员工与领导之间的层次关系
select lpad(' ', 5 * level - 1) || empno EMPNO,
       lpad(' ', 5 * level - 1) || ename ENAME
  from scott.emp
 start with empno = 7839
connect by prior empno = mgr;

64.向emp表中插入一条记录，员工号为1357，员工名字为orcle，工资为2050元，部门10号，入职日期为2002年5月10日
insert into emp(empno,ename,sal,deptno,hiredate) values(1357,'oracle',2050,20,to_date('2002-6-10','YYYY-MM-DD'));

65.向emp表中插入一条记录，员工名字为FAN，员工号为8000，其他信息与SMITH员工的信息相同
insert into emp(ename,empno,job,mgr,hiredate,sal,comm,deptno) values
('FAN',8000,'CLERK',7902,to_date('1980-12-17','yyyy-mm-dd'),800,null,20);

66.将各部门员工的工资修改为该员工所在部门平均工资加1000
update emp  e  set sal = 1000 + (select avg(sal) from emp where deptno=e.deptno);

67.查询工资、奖金与10号部门某员工工资、奖金都相同的员工的信息
select sal,comm from emp where (sal,comm) = any(select sal,comm from emp where deptno=10);