备注：以下例题均基于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);
