朱志明.txt

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

以下练习以emp,dept为准进行查询：
1，查询出每种工作的平均工资
  QL> select distinct job,avg(sal)
  2  from emp
  3  group by job
  4  order by job;

JOB         AVG(SAL)
--------- ----------
ANALYST         3000
CLERK         1037.5
MANAGER   3058.33333
PRESIDENT       5000
SALESMAN        1400
clerk     
2，查询出那种工作的工资最高

  SQL> select distinct job,sal
  2  from emp
  3  where sal=(select max(sal) from emp);

JOB             SAL
--------- ---------
PRESIDENT   5000.00


3，查询出工资最底的经理的名字

   select deptno,ename,job,(select min(sal) from emp b where b.deptno = a.deptno) minsal
   from emp a
   where job='MANAGER'
   order by deptno;

DEPTNO ENAME      JOB           MINSAL
------ ---------- --------- ----------
    10 CLARK      MANAGER         1300
    20 JONES      MANAGER          800
    30 BLAKE      MANAGER          950
   


4，查询出部门编号为30的部门里面那种工作的平均工资最高

   select job,sal from emp 
  where deptno=30 group by job,sal 
  having sal= (select max(sal) from emp where sal in (select avg(sal) from emp where deptno=30 
  group by job));

JOB             SAL
--------- ---------
MANAGER     3000.00

5，查询出名字中带K的经理
   
  select ename ,job from emp where  job='MANAGER' and ename like '%K%';

ENAME      JOB
---------- ---------
BLAKE      MANAGER
CLARK      MANAGER    
  

6，统计出各个部门的各个工作岗位的平均工资
      
     select distinct deptno 部门编号,job 工作岗位,avg(sal) 平均工资
  2  from emp
  3  group by deptno,job;

部门编号 工作岗位    平均工资
-------- --------- ----------
         clerk     
      10 CLERK           1300
      10 MANAGER         3200
      10 PRESIDENT       5000
      20 CLERK            950
      20 ANALYST         3000
      20 MANAGER         2975
      30 CLERK            950
      30 MANAGER         3000
      30 SALESMAN        1400