黄松平.txt

来自「关于oracle和sql的书籍和ppt教程,非常好,本人珍藏品」· 文本 代码 · 共 52 行

1
select job , avg(sal)平均工资 from emp group by job;

JOB         平均工资
--------- ----------
ANALYST         3000
CLERK         1037.5
MANAGER   3058.33333
PRESIDENT       5000
SALESMAN        1400
2
select job 工资最高的职位 from emp where sal=(select max(sal) from emp);

工资最高的职位
--------------
PRESIDENT
3
select ename 工资最低经理名字,sal from emp where job='MANAGER' and  sal=(select min(sal) from emp where job='MANAGER') ;

工资最低经理名字       SAL
---------------- ---------
JONES              2975.00

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

JOB
---------
clerk
5
select ename from emp where ename like '%K%' and job='MANAGER';

ENAME
----------
BLAKE
CLARK
6
select deptno,(select dname from dept where deptno=emp.deptno ) 部门名字,job,avg(sal) from emp group by deptno,job;

DEPTNO 部门名字       JOB         AVG(SAL)
------ -------------- --------- ----------
                      CLERK           2975
    10 ACCOUNTING     CLERK           1300
    10 ACCOUNTING     MANAGER         3200
    10 ACCOUNTING     PRESIDENT       5000
    20 RESEARCH       CLERK            950
    20 RESEARCH       ANALYST         3000
    20 RESEARCH       MANAGER         2975
    30 SALES          CLERK            950
    30 SALES          MANAGER         3000
    30 SALES          SALESMAN        1400