Oracle练习题及答案

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

Select e.ename, d.dname from emp e join dept d on e.deptno = d.deptno where job = (Select job from emp where ename = 'SCOTT')

24、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金

第一步 获取部门是30的所有员工薪水

select sal from emp where deptno = 30

第二步 获取结果

Select ename, sal from emp where sal in (select sal from emp where deptno = 30)

25、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称

select ename, sal, dname from emp e join dept d on e.deptno = d.deptno where sal >  (select max (sal) maxSal from emp where deptno = 30)

26、列出在每个部门工作的员工数量,平均工资和平均服务期限

Select d.dname 部门名称, count(e.empno) 员工数量, round(avg(e.sal), 2) 平均薪水, round(avg(sysdate-hiredate)/365,0) 服务期限 from emp e, dept d where e.deptno = d.deptno group by d.dname

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

Select ename, dname, sal from emp e, dept d where e.deptno = d.deptno

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

Select d.*, (select count(e.empno) from emp e where e.deptno = d.deptno) 人数 from dept d

29、列出各种工作的最低工资及从事此工作的雇员姓名

30、列出各个部门的MANAGER(经理)的最低薪金

select deptno, min(sal) from (select deptno, sal, empno from emp where empno in (Select distinct mgr from emp where mgr is not null)) group by deptno

31、列出所有员工的年工资,按年薪从低到高排序

32、查出某个员工的上级主管，并要求出这些主管中的薪水超过3000

select ename 主管, sal 主管工资 from emp where empno in (Select distinct mgr from emp where mgr is not null) and sal > 3000

33、求出部门名称中,带'S'字符的部门员工的工资合计、部门人数

select deptno from dept where dname like '%S%'
select d.dname, sum(e.sal), count(e.empno) from emp e right join dept d on e.deptno = d.deptno group by d.dname having d.dname like '%S%'

34、给任职日期超过28年的员工加薪10%

Update emp set sal = sal*1.1 where (months_between(sysdate, hiredate)/12) > 28