数据库开发面试题-01

1、搜索出平均工资大于该组平均工资的员工姓名和工资（表来自oracle自带的scott中的emp和deptno表）

法一：简单sql

法二：使用游标写成函数

 

思路：

1、整合为一张大表，里面有emp的全部信息+这个人所在组的平均工资（如下图）

2、再从这个表中找到sal>v_sal的人名

 

select e.ename,e.sal

from emp e,(       select avg(sal) v_sal,deptno

from emp

group by deptno) s

where e.deptno=s.deptno and e.sal > s.v_sal

/

 

 

 

select e1.ename, e1.sal
from emp e1
where e1.sal < (select avg(e2.sal)
                   from emp e2
                   where e2.deptno = e1.deptno)

 

 

 

？？上面哪种效率更高

 

 

用游标写成函数

Create or replace  procedure pro_avg_sal

as

cursor c1 is select avg(sal) s,deptno from emp group by deptno;	--游标c1记录每个部门相应的平均工资
cursor c2 (v_deptno number) is select * from emp where deptno=v_deptno;	--游标c2记录emp一行的数据

begin

for i in c1 loop

dbms_output.put_line(chr(10)||i.deptno||'组的平均工资为：'||i.s);

for j in c2(i.deptno) loop

if ( j.sal > i.s ) then

如果某员工的工资高于其所在部门的平均工资，则打印出这名员工相应信息

dbms_output.put_line(j.ename||'  '||j.sal||'  '||'j.deptno');

end if;

end loop;

end loop;

end;

/

SQL> /

 

过程已创建。

 

SQL> exec pro_avg_sal

 

30组的平均工资为：1843.416666666666666666666666666666666667

ALLEN  1936  j.deptno

BLAKE  3135  j.deptno

 

20组的平均工资为：2513.5

JONES  3272.5  j.deptno

FORD  3300  j.deptno

 

10组的平均工资为：3256

KING  5500  j.deptno

 

PL/SQL 过程已成功完成。

 

再思考：这里不涉及update等DML操作，所以不用考虑锁的问题

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26171028/viewspace-709440/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26171028/viewspace-709440/