605练习与答案.txt

关于oracle和sql的书籍和ppt教程,非常好,本人珍藏品

以下练习以emp,dept为准进行查询：
1，查询出每种工作的平均工资
2，查询出那种工作的工资最高
3，查询出工资最底的经理的名字
4，查询出部门编号为30的部门里面那种工作的平均工资最高
5，查询出名字中带K的经理
6，统计出各个部门的各个工作岗位的平均工资










1、select job,avg(sal) 人均工资 from emp group by job;
2、select job 高薪工种 from (select job,avg(sal) sa from emp group by job) where sa=(select max(s) from (select job,avg(sal) s from emp group by job));
3、select ename from (select job,ename,sal from emp group by job,ename,sal having job = 'MANAGER') where sal=(select min(sal) from (select job,ename,sal from emp group by job,ename,sal having job = 'MANAGER'));
4、select deptno,job,s from (select deptno,job,avg(sal) s from emp group by deptno,job having deptno=30) where s=(select min(d) from (select deptno,job,avg(sal) d from emp group by deptno,job having deptno=30));
5、select job,ename from emp group by job,ename having job = 'MANAGER' and ename like'%K%';
6、select deptno,job,avg(sal) 平均工资 from emp group by deptno,job;



1 select job,avg(sal) from emp
    group by job;

2 select job,sal from emp
  where sal=(select max(sal) from emp);

3 select ename from emp
    where sal=(select min(sal) from emp where job ='MANAGER') and job='MANAGER';

4 select deptno,job,avg(sal) from emp
  group by deptno,job
  having deptno=30 and avg(sal)>= (select avg(sal) from emp);

5 select ename,job from emp
 where ename like '%K%' and job='MANAGER';

6 select deptno,job,avg(sal) from emp
group by cube (deptno,job);