Oracle练习题

写一个PL/SQL程序，输出部门平均工资最高的部门名。

查询平均工资最高的部门名：

select a.dname, b.s
  from dept a,
       (select *
          from (select avg(sal) s, deptno
                  from emp
                 group by deptno
                 order by avg(sal) desc)
         where rownum = 1) b
 where a.deptno = b.deptno;

PL/SQL:

declare
 v_dname dept.dname%type;
  begin
  select dname
  into v_dname
  from
  (select dname,avg(sal)
  from emp,dept
 where emp.deptno=dept.deptno
  group by dname
  order by avg(sal) desc)
  where rownum=1;
  dbms_output.put_line(v_dname);
  end;
  /

写一个PL/SQL程序，输出emp表的第2条记录员工的姓名、月收入、工龄。

select ename,sal+nvl(comm,0),(sysdate-hiredate)/365 from ( select rownum r,emp.* from emp)
where r=2;

PL/SQL

declare
v_ename emp.ename%type;
v_sal emp.sal%type;
v_hiredate number;
begin
select ename,sal+nvl(comm,0),to_number((sysdate-hiredate)/365) into v_ename,v_sal,v_hiredate 
from ( select rownum r,emp.* from emp) 
where r=2;
dbms_output.put_line(v_ename||v_sal||v_hiredate );
end;

编写一段PL/SQL程序，接收一个职工的姓名，然后显示出包括他的部门名称的信息

declare 
  v_ename emp.ename%type;
  v_dname dept.dname%type;
begin
  v_ename:='&v_ename';
  select dname into v_dname from emp,dept where ename=v_ename and emp.deptno=dept.deptno;
 dbms_output.put_line(v_dname);
end;

编写一段PL/SQL程序，显示出dept表中部门号码等于10的所有信息。

declare 
  v_deptno dept.deptno%type;
  v_dname dept.dname%type;
  v_loc dept.loc%type;
begin
  v_deptno:=10;
  select deptno,dname,loc into v_deptno,v_dname,v_loc from dept where deptno=v_deptno and deptno=10;
  dbms_output.put_line(v_deptno||v_dname||v_loc);
end;