杨勇.txt

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

以下练习以emp,dept为准进行查询：
1，查询出每种工作的平均工资select job,avg(sal) from emp group by job;
2，查询出那种工作的工资最高
select JOB,AVG_SAL from (select JOB,avg(SAL) AVG_SAL from emp group by JOB) 
where AVG_SAL=(select max(avg(SAL)) from emp group by JOB);
select job,sal from emp where sal>=all(select sal from emp);
3，查询出工资最底的经理的名字
select ename,sal from emp where job='MANAGER' and sal<=all(select sal from emp where job='MANAGER');
4，查询出部门编号为30的部门里面那种工作的平均工资最高 
select distinct job,avg(sal) from emp
where deptno = 30
group by job having avg(sal) > = all(select avg(sal) from emp where deptno=30 group by job);
5，查询出名字中带K的经理select ename from emp where ename like '%K%'and job = 'MANAGER';
6，统计出各个部门的各个工作岗位的平均工资select deptno, job,avg(sal)from emp group by deptno, job;