谢妮娜.txt

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

1，查询出每种工作的平均工资

SQL> select job,(select avg(sal) from emp b where b.job=a.job) avgsal
  2  from emp a
  3  group by job;

JOB           AVGSAL
--------- ----------
ANALYST         3000
CLERK         1037.5
MANAGER   2758.33333
PRESIDENT       5000
SALESMAN        1400




2，查询出那种工作的工资最高

SQL> SELECT JOB,SAL FROM EMP
  2  WHERE SAL=(SELECT MAX(SAL) FROM EMP);

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




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

SQL> select ename,job,sal from emp
  2  where job='MANAGER'
  3  and sal<=all(select sal from emp where job='MANAGER')
  4  ;

ENAME      JOB             SAL
---------- --------- ---------
JONES      MANAGER     2975.00




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

SQL> select job,avg(sal) from emp
  2  where deptno=30
  3  group by job
  4  having  avg(sal) >any (select avg(sal) from emp )
  5  ;

JOB         AVG(SAL)
--------- ----------
MANAGER         3000




5，查询出名字中带K的经理

SQL> select Ename,job from emp
  2  where ename like '%K%'
  3  and job='MANAGER'
  4  ;

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




6，统计出各个部门的各个工作岗位的平均工资

SQL> select job,deptno,avg(sal) from emp
  2  group by (deptno,job);

JOB       DEPTNO   AVG(SAL)
--------- ------ ----------
CLERK         10       1300
MANAGER       10       3200
PRESIDENT     10       5000
CLERK         20        950
ANALYST       20       3000
MANAGER       20       2975
CLERK         30        950
MANAGER       30       3000
SALESMAN      30       1400

9 rows selected