郑国.txt

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

Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 
Connected as zg


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

JOB         AVG(SAL)
--------- ----------
ANALYST         3000
CLERK         1212.5
MANAGER   2758.33333
PRESIDENT       5000
SALESMAN        1400



2，查询出那种工作的工资最高
SQL> select job,sal from emp where sal=(select max(sal) from emp);


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



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

ENAME            SAL
---------- ---------
CLARK        2450.00


4，查询出部门编号为30的部门里面那种工作的平均工资最高
SQL> select job,b from (select avg(sal) as b,job from emp group by job having job in (select distinct job from emp where deptno=30)) a
  2  where b=(select max(avg(sal)) from emp group by job having job in (select distinct job from emp where deptno=30));

JOB                B
--------- ----------
MANAGER   2758.33333



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

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10


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

DEPTNO JOB       JOB         AVG(SAL)
------ --------- --------- ----------
    10 CLERK     CLERK         1212.5
    10 MANAGER   MANAGER   2758.33333
    10 PRESIDENT PRESIDENT       5000
    20 CLERK     CLERK         1212.5
    20 ANALYST   ANALYST         3000
    20 MANAGER   MANAGER   2758.33333
    30 CLERK     CLERK         1212.5
    30 MANAGER   MANAGER   2758.33333
    30 SALESMAN  SALESMAN        1400

9 rows selected

SQL>