章猛.txt

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

以下练习以emp,dept为准进行查询：
1，查询出每种工作的平均工资
select job,avg(sal) from emp group by job;
2，查询出那种工作的工资最高
select job,sal from emp where sal >= all(select sal from emp);
3，查询出工资最底的经理的名字
select ename,sal from emp where sal <= all(select sal from emp where job='MANAGER') and job='MANAGER';
4，查询出部门编号为30的部门里面那种工作的平均工资最高
select job, avg(sal) from emp where deptno=30 group by job having(avg(sal)>=all(select avg(sal) from emp where deptno=30 group by job));
5，查询出名字中带K的经理
 select * from emp where ename like '%K%' and job='MANAGER';
6，统计出各个部门的各个工作岗位的平均工资
select deptno,job,avg(sal) 平均工资 from emp group by deptno,job;





























declare
 type z_m is table of emp.ename%type index by binary_integer;
 zm z_m;
 itm number;
 begin
   /*<<andy>>
   declare
    type xiec is record(
       en emp.ename%type,
       sal emp.sal%type);
    em xiec;
   begin*/
    select ename into zm from emp;
    itm :=-50;
    for itm in -50..30 loop
     dbms_output.put_line(zm(itm));
     end loop;
     end;
    /*select ename,sal into em.en,em.sal from emp where ezm := &&adcv;
 dbms_output.put_line(zm);
  end;*/