Oracle 练习题P256

--根据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;