--根据Oracle数据库scott模式下的emp表和dept表，完成下列操作。
--（1）查询20号部门的所有员工信息
select * from emp where deptno = 20;
--（2）查询所有工种为CLERK的员工的员工号、员工名和部门号
select empno,ename,deptno from emp where job = 'CLERK';
--（3）查询奖金（COMM）高于工资（SAL）的员工信息
select * from emp where nvl(comm,0) > sal;
--（4）查询奖金高于工资的20%的员工信息
select * from emp where nvl(comm,0) > (sal * 0.2);
--（5）查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息
select * from emp where (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'CLERK');
--（6）查询所有工种不是MANAGER和CLERK，且工资大于或等于2000的员工的详细信息
select * from emp where job not in ('MANAGER','CLERK') and sal >= 2000;
--（7）查询有奖金的员工的不同工种
select distinct job from emp where nvl(comm,0) <> 0;
--（8）查询所有员工工资与奖金的和
select ename,sal,nvl(comm,0),(sal+nvl(comm,0)) from emp;
--（9）查询没有奖金或奖金低于100的员工信息
select * from emp where nvl(comm,0) < 100;
--（10）查询各月倒数第二天入职的员工信息
select * from emp where hiredate = last_day(hiredate) - 2;
--（11）查询工龄大于或等于10年的员工信息
select * from emp where months_between(sysdate,hiredate) > (10 * 12);
--（12）查询员工信息，要求以首字母大写的方式显示所有员工的姓名
select empno,initcap(ename),job,mgr,hiredate,sal,comm,deptno from emp;
--（13）查询员工名正好为6个字母的员工的信息
select * from emp where length(ename) = 6;
--（14）查询员工名字中不包含字母“S”的员工
select * from emp where instr(ename,'S') > 0;
--（15）查询员工姓名的第2个字母为“M”的员工信息
select * from emp where instr(ename,'M') = 2;
--（16）查询所有员工姓名的前3个字符
select substr(ename,1,3) from emp;
--（17）查询所有员工的姓名，如果包含字母“s”，则用“S”替换
select replace(ename,'S','s') from emp;
--（18）查询员工的姓名和入职日期，并按入职日期从先到后进行排序
select ename,hiredate from emp order by hiredate asc;
--（19）显示所有员工的姓名、工种、工资和奖金，按工种降序排序，若工种相同则按工资升序排序
select ename,job,sal,comm from emp order by job desc, sal asc;
--（20）显示所有员工姓名、入职的年份和月份，按入职日期所在的月份排序，若月份相同则按入职的年份排序
select ename,to_char(hiredate,'yyyy') hiredate_year,to_char(hiredate,'mm')hiredate_month
from emp order by hiredate_year,hiredate_month;
--（21）查询在2月份入职的所有员工信息
select * from emp where to_char(hiredate,'mm') = '02';
--（22）查询至少有一个员工的部门信息
select d.deptno,d.dname,d.loc,e.num from dept d
join (select deptno,count(empno) num from emp group by deptno) e
on d.deptno = e.deptno where num > 1;
--（23）查询工资比SMITH员工工资高的所有员工信息
select * from emp where sal > (select sal from emp where ename = 'SMITH');
--（24）查询所有员工的姓名及其直接上级的姓名
select e1.ename name,e2.ename mgr from emp e1
join emp e2 on e1.mgr = e2.empno;
--（25）查询入职日期早于其直接上级领导的所有员工信息
select * from emp where empno in
(select e1.empno from emp e1 join emp e2 on e1.mgr = e2.empno where e1.hiredate < e2.hiredate);
--（26）查询所有部门及其员工信息，包括那些没有员工的部门
select * from dept d left join (select * from emp) e on d.deptno = e.deptno;
--（27）查询所有员工及其部门信息，包括那些还不属于任何部门的员工
select * from dept d right join (select * from emp) e on d.deptno = e.deptno;
--（28）查询所有工种为CLERK的员工的姓名及其部门名称
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
--（29）查询最低工资大于2500的各种工作
select job,min(sal) min_sal from emp group by job having min(sal) > 2500;
--（30）查询平均工资低于2000旳部门及其员工信息
select * from dept d join emp e on d.deptno = e.deptno where d.deptno =
(select deptno from emp group by deptno having avg(sal) < 2000);
--（31）查询在SALES部门工作的员工的姓名信息
select ename from dept d join emp e on d.deptno = e.deptno where d.dname = 'SALES';
--（32）查询工资高于公司平均工资的所有员工信息
select * from emp where sal > (select avg(sal) from emp);
--（33）查询与SMITH员工从事相同工作的所有员工信息
select * from emp where job = (select job from emp where ename = 'SMITH');
--（34）列出工资等于30号部门中某个员工工资的所有员工的姓名和工资
select ename,sal from emp where sal in (select sal from emp where deptno = 30);
--（35）查询工资高于30号部门中工作的所有员工的工资的员工姓名和工资
select ename,sal from emp where sal > (select max(sal) from emp group by deptno having deptno = 30);
--（36）查询每个部门中的员工数量、平均工资和平均工作年限
select count(*) num,avg(sal) avg_sal,avg(months_between(sysdate,hiredate))/12 age from emp group by deptno;
--（37）查询各个部门的详细信息以及部门人数、部门平均工资
select d.deptno,d.dname,d.loc,a.num,a.avg_sal from dept d
join (select deptno,count(*) num,avg(sal) avg_sal,avg(months_between(sysdate,hiredate))/12 age from emp group by deptno) a
on d.deptno = a.deptno;
--（38）查询各个部门中不同工种的最高工资
select deptno,job,max(sal) from emp group by deptno,job order by deptno,job;
--（39）查询10号部门员工及其领导的信息
select * from emp where deptno = 10 or mgr in (select empno from emp where deptno = 10);
--（40）查询工资为某个部门的平均工资的员工信息
select * from emp where sal in (select avg(sal) from emp group by deptno);
--（41）查询工资高于本部门平均工资的员工的信息
select e1.* from emp e1
join (select deptno,avg(sal) avg_sal from emp group by deptno) e2 on e1.deptno = e2.deptno
where e1.sal > e2.avg_sal;
--（42）查询工资高于本部门平均工资的员工的信息及其部门的平均工资
select e1.*,e2.dept_avg_sal from emp e1
join (select deptno,avg(sal) dept_avg_sal from emp group by deptno) e2 on e1.deptno = e2.deptno
where e1.sal > e2.dept_avg_sal;
--（43）查询工资高于20号部门某个员工工资的员工的信息
select * from emp where sal > any(select sal from emp where deptno = 20) order by empno;
select * from emp where sal > (select min(sal) from emp where deptno = 20) order by empno;
--（44）统计各个工种的员工人数与平均工资
select job,count(*),avg(sal) from emp group by job;
--（45）统计每个部门中各工种的人数与平均工资
select deptno,job,count(*),avg(sal) from emp group by deptno,job order by deptno,job;
--（46）查询工资、奖金与10号部门某员工工资、奖金都相同的员工信息
select * from emp where (sal,nvl(comm,0)) in (select sal,nvl(comm,0) from emp where deptno = 10);
--（47）查询部门人数大于5的部门的员工信息
select * from emp where deptno in
(select deptno from emp group by deptno having count(*) > 5);
--（48）查询所有员工工资都大于2000的部门的信息
select d.*,a.avg_sal from dept d
join (select deptno,avg(sal) avg_sal from emp group by deptno) a on d.deptno = a.deptno where avg_sal > 2000;
--（49）查询所有员工工资都大于2000的部门的信息及员工信息
select d.*,a.avg_sal,e.* from dept d
join (select deptno,avg(sal) avg_sal from emp group by deptno) a on d.deptno = a.deptno
join emp e on d.deptno = e.deptno
where avg_sal > 2000 order by d.deptno;
--（50）查询所有员工工资都在2000~3000之间的部门的信息
select d.* from dept d
join (select deptno,min(sal) min_sal,max(sal) max_sal from emp group by deptno) a on d.deptno = a.deptno
where min_sal > 500 and max_sal < 3000;
--（51）查询所有工资在2000~3000之间的员工所在部门的员工信息
select * from emp where deptno in
(select distinct deptno from emp where sal between 2000 and 3000);
--（52）查询人数最多的部门信息
select * from
(select d.*,a.num from dept d
join (select deptno,count(*) num from emp group by deptno) a on d.deptno = a.deptno order by num desc)
where rownum <= 1;
--（53）查询30号部门中工资排序前3名的员工信息
select * from
(select * from emp where deptno = 30 order by sal desc)
where rownum <=3;
--（54）查询所有员工中工资排序在5~10名之间的员工信息
select * from (select rownum n,e.* from (select * from emp order by sal desc) e) where n between 5 and 10;
--（55）向emp表中插入一条记录，员工号为1357，员工名字为oracle，工资为2050，部门号为20，入职日期为2002年5月10日
create table emp2 as select * from emp;
insert into emp2(empno,ename,sal,deptno,hiredate) values(1357,'oracle',2050,20,to_date('2002-5-10','yyyy-mm-dd'));
select * from emp2;
--（56）向emp表中插入一条记录，员工名为FAN，员工号为8000，其他信息与SMITH员工的信息相同
insert into emp2 select 8000,'FAN',job,mgr,hiredate,sal,comm,deptno from emp2 where ename='SMITH';
select * from emp2;
--（57）将各部门员工的工资修改为该员工所在部门平均工资加1000
select deptno,avg(sal) from emp2 group by deptno order by deptno;
update emp2 e1 set sal = (select avg(sal) + 1000 from emp2 e2 group by deptno having e1.deptno = e2.deptno);
select deptno,avg(sal) from emp2 group by deptno order by deptno;