oracle homework2

Oracle作业

课堂练习：

1、要求查出员工的编号，员工的姓名，部门的编号，部门

名称和部门位置

selecte.empno,e.ename,d.deptno,d.dname,d.loc from empe,dept d where e.deptno=d.deptno ;

2、要求查出每个员工的姓名，工作，雇员的直接上级领导

的姓名

selecte.ename,e.job,m.ename from empe,emp m where

e.mgr=m.empno;

3、要求查出每个员工的姓名，工作，雇员的直接上级领导

的姓名及员工所在部门名称

selecte.ename,e.job,m.ename,d.dname from empe,empm,dept d where e.mgr =m.empno and

e.deptno=d.deptno;

4、要求查询出每个员工的姓名、工资、部门名称、工资在

公司的等级和领导的姓名及领导的工资在公司的等级

select

e.ename,e.sal,d.dname,es.grade,m.ename,ms.grade from

empe,empm,salgradees,salgradems,dept d where

e.mgr=m.empno and e.sal between es.losal and es.hisal

and e.deptno=d.deptno and m.sal between ms.losal and ms.hisal;

5、工资等级按照第一等级，第二等级的形式输出，使用

DECODE函数

select e.ename,e.sal,d.dname,decode(es.grade,5,'第一等

工资'，4，'第二等工资',3,'第三等工资',2,'第四等工资'，1,'第五等工资'),m.ename,m.sal,decode(ms.grade,5,'第一

等工资'，4，'第二等工资',3,'第三等工资',2,'第四等工资

'，1,'第五等工资') from

empe,empm,salgradees,salgradems,dept d where

e.mgr=m.empno and e.sal between es.losal and es.hisal

and e.deptno=d.deptno and m.sal between ms.losal and ms.hisal;

6、查询出员工的编号，姓名及领导的编号及姓名

selecte.empno,e.ename,m.ename,m.mgr from empe,emp m where e.mgr=m.empno(+);

分组统计

1、求出每个部门的员工数量，应该按照部门编号分组

selectdeptno,count(*) from emp group by deptno;

2、求每个部门的平均工资

selectdeptno,avg(sal) from emp group by deptno;

3、要求按部门分组，并显示部门的名称，及每个部门的员

工数。

selectd.dname,count(e.empno) from empe,dept d where

e.deptno(+)=d.deptno group by d.dname;

4、要求显示出平均工资大于2000的部门编号和平均工资

selectdeptno,avg(sal) from emp group by deptno having avg(sal)>2000;

5、显示非销售人员工作名称以及从事同一工作的员工的

工资总和，并且要满足从事同一工作的员工工资总和大于5000，输出结果按工资降序排列。

selectjob,sum(sal) from emp where job <>'SALESMAN'group by job having sum(sal)>5000 order by sum(sal) desc;

6、求出平均工资最高的部门工资

select max(deptno),max(avg(sal)) from emp group by deptno;

作业部分；

1、查询至少有1个员工的部门信息

select d.*,ed.count from dept d,(select deptno,count(empno) count from emp group by deptno having count(empno)>1) ed where d.deptno=ed.deptno;

2、列出所有工资比SMITH高的所有员工

select * from emp where sal>(select sal from emp where ename ='SMITH');

3、列出所有员工的姓名和直接上级的姓名

selecte.ename,m.ename from empe,emp m where

e.mgr=m.empno(+);

4、列出受雇比直接上级要早的员工的编号，姓名，和所在

部门名称

selecte.empno,e.ename,m.ename,d.dname from empe,empm,dept d where e.mgr=m.empno and

e.hiredate

5、分组列出部门名称和这些部门的员工信息，同时列出没

有员工的部门

select d.dname,d.deptno,e.empno,e.ename from emp

e ,dept d where e.deptno(+)=d.deptno;

6、列出所有办事员的姓名及部门名称，部门的人数

selecte.ename,d.dname, ed.cu from empe,dept d,(select deptno,count(empno) cu from emp e group by deptno) ed where job='CLERK' and e.deptno=d.deptno and ed.deptno=e.deptno;

7、列出工资低于1500的各种工作及从事此工作的员工数

selectjob,count(*) from emp e group by job having min(sal)<1500;

8、列出销售部员工的姓名

selectename from emp e where e.job ='SALESMAN';

9、列出工资高于公司平均工资的所有员工的姓名，所在部

门，上级领导及工资的等级

select e.ename,dname,m.ename,grade from empe,empm,deptd,salgrade s where e.mgr=m.empno and

e.deptno=d.deptno and e.sal>(select avg(sal) from emp)

and m.sal between s.losal and s.hisal;

10、列出与SCOTT从事相同工作的所有员工及部门名称

select e.empno,e.ename,e.job,e.sal,d.dname from empe,dept d where e.deptno=d.deptno and job = (select job from emp where ename ='SCOTT') and ename !='SCOTT';

11、列出每个部门的员工数，平均工资和平均服务期限

select

deptno ,count(*),round(avg(sal+nvl(comm ,0)))avgsal,roun d(avg(sysdate-hiredate))avgday from emp group by deptno;

12、列出所有员工的姓名，部门名称和工资

selecte.ename,d.dname,(sal+nvl(comm,0)) as sum from empe,dept d where e.deptno=d.deptno;

13、列出所有部门的详细信息和人数

select d.*,(select count(*)from emp where deptno =d.deptno)as sum from dept d;

14、列出每种工作的最低工资和员工姓名

select * from emp where sal in (select MIN(sal) from emp group by job);

15、列出每个部门的经理的最低工资

select MIN(sal) ,deptno from emp where job='MANAGER' group by deptno;

16、列出所有员工的年薪，从低到高排序

selectename,(sal+nvl(comm,0))*12 income from emp order by income;

17、给服务年限超过10年的员工加薪10%

updateemp set sal=sal+sal*0.1 where

(months_between(sysdate,hiredate)/12)>10;