Oracle例题（四）

备注：以下例题均基于Oracle的scott账户中的表

1、列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级

select e.ename 员工姓名, d.dname 部门名称, a.ename 领导, s.grade 工资等级
  from emp e, emp a, dept d, salgrade s
 where e.sal > (select avg(sal) from emp)
   and e.deptno = d.deptno
   and e.mgr = a.empno(+)
   and e.sal between s.losal and s.hisal;

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

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

3、列出薪金大于部门30中的任意员工的薪金的所有员工的姓名和薪金

select ename, sal
  from emp
 where sal > (select min(sal) from emp where deptno = 30);

4、列出薪金大于部门30中的全部员工的薪金的所有员工的姓名和薪金，部门名称

 select e.ename, e.sal, d.dname
   from emp e, dept d
  where e.sal > (select max(sal) from emp where deptno = 30)
    and e.deptno = d.deptno;

5、列出每个部门的员工数量，平均工资

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

6、列出每个部门的员工数量，平均工资和平均服务期限（月）

select count(empno), avg(sal), avg(sysdate - hiredate)/30
  from emp
 group by deptno;

7、列出各个工作的最低工资及从事工资最低工作的雇员的名称

select e.ename, a.asal
  from emp e, (select job, min(sal) asal from emp group by job) a
 where e.job = a.job
   and e.sal = a.asal;

8、求出部门名称带字符's'的部门员工，工资合计，部门人数

select e.ename, d.dname, c.sumSal, c. countEmp
  from emp  e,
       dept d, (select deptno, sum(sal) sumSal, count(empno) countEmp from emp group by deptno) c
 where e.deptno(+) = d.deptno
   and c.deptno(+) = e.deptno
   and dname like '%S%';

9、求出部门平均工资以及等级

select d.dname, a.avgSal, grade
  from dept d, (select deptno, avg(sal) avgSal from emp group by deptno) a, salgrade sg
 where a.avgSal between sg.losal and sg.hisal
   and d.deptno = a.deptno;

10、求平均工资等级最低的部门的名称

select dname
  from dept
  join (select deptno, grade
          from (select deptno, avg(sal) avgSal from emp group by deptno) a
          join salgrade on (a.avgSal between salgrade.losal and
                           salgrade.hisal)) b on dept.deptno = b.deptno
 where b.grade =
       (select min(grade)
          from (select deptno, avg(sal) avgSal from emp group by deptno) a
          join salgrade on (a.avgSal between salgrade.losal and
                           salgrade.hisal))

11、部门经理人中平均工资最低的部门名称

select dname
  from (select deptno, avg(sal) avgSal
          from emp
         where empno in (select mgr from emp)
         group by deptno) a
  join dept on a.deptno = dept.deptno
 where avgSal = (select min(avgSal)
                   from (select avg(sal) avgSal
                           from emp
                          where empno in (select mgr from emp)
                          group by deptno) a)

12、修改表的列属性

alter table emp modify sal varchar2(100);