李建国.txt

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

1.查询出每种工作的平均工资
SQL> select job,avg(sal)from emp group by job;

JOB         AVG(SAL)
--------- ----------
ANALYST         3000
CLERK         1037.5
MANAGER   3058.33333
PRESIDENT       5000
SALESMAN        1400


2.查询出那种工作的工资最高
SQL> 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);

JOB          AVG_SAL
--------- ----------
PRESIDENT       5000


3.查询出工资最底的经理的名字
SQL> select ename from emp where sal=(select min(sal) from emp where job='MANAGER' );

ENAME
----------
JONES



4.查询出部门编号为30的部门里面那种工作的平均工资最高
SQL> select job from (select max(sal)as 最高工资 ,job from (select * from emp where deptno=30) group by job)where 最高工资=all(select max(sal) from emp where deptno=30 );

JOB
---------
MANAGER




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

ENAME
----------
BLAKE
CLARK


6.统计出各个部门的各个工作岗位的平均工资
SQL> select deptno,job,avg(sal) 平均工资 from emp group by deptno ,job;

DEPTNO JOB         平均工资
------ --------- ----------
    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