今天做了2道面试题

有如下2个表
表1：emp
drop table emp;
create table emp
( emp_no NUMBER(10)
, emp_name VARCHAR2(30)
, dept_no NUMBER(4)
, begindate DATE
, sal NUMBER(7)) ;

表2：dept
drop tabel dept
create table dept
( dept_no NUMBER(4)
,dept_name varchar2(30) ) ;


insert into emp values (11001, 'jun002' ,1, date '2005-09-01' ,10000);
insert into emp values (11002, 'xinsheng001' ,2, date '2005-09-01' ,10000);
insert into emp values (11003, 'xiaoqian001' ,3, date '2005-09-01' ,10000);
insert into emp values (11004, 'xiaohua001' ,4, date '2005-09-01' ,10000);
insert into emp values (11005, 'shunzi' ,20, date '2005-09-01' ,10000);
insert into emp values (11006, 'jun001' ,1, date '2005-09-01' ,10000);
insert into emp values (11007, 'xinsheng002' ,2, date '2005-09-01' ,10000);
insert into emp values (11008, 'xiaoqian002' ,20, date '2005-09-01' ,10000);
insert into emp values (11009, 'xiaohua004' ,6, date '2005-09-01' ,10000);
insert into emp values (11010, 'jun003' ,1, date '2005-09-01' ,10000);
insert into emp values (11011, 'jun004' ,1, date '2005-09-01' ,10000);
insert into emp values (11012, 'xinsheng003' ,2, date '2005-09-01' ,10000);
insert into emp values (11013, 'xinsheng004' ,2, date '2005-09-01' ,10000);
insert into emp values (11014, 'xinsheng005' ,2, date '2005-09-01' ,10000);
insert into emp values (11015, 'xinsheng006' ,2, date '2005-09-01' ,10000);
insert into emp values (11016, 'xiaohua002' ,4, date '2005-09-01' ,10000);
insert into emp values (11017, 'xiaohua003' ,4, date '2005-09-01' ,10000);
insert into emp values (11018, 'xialli001' ,20, date '2005-09-01' ,10000);

insert into dept values (1, 'TEST');
insert into dept values (2, 'CD');
insert into dept values (3, 'UT');
insert into dept values (4, 'ITA');
insert into dept values (5, 'ITB');
insert into dept values (6, 'PO');
insert into dept values (7, 'AR');
insert into dept values (8, 'AP');
insert into dept values (9, 'IF');
insert into dept values (10, 'MT');
insert into dept values (20, 'OF');


1.根据表emp、dept，检索员工号、员工名称和每个部门的员工数（员工数目在3人以下的不予检索）
SELECT E.EMP_NO,E.EMP_NAME,E.DEPT_NO,COUNT(E.EMP_NO)
FROM EMP E,EMP M
WHERE E.DEPT_NO = M.DEPT_NO
GROUP BY E.EMP_NO,E.EMP_NAME,E.DEPT_NO
HAVING COUNT(E.EMP_NO) >= 3;
2.写一个块，实现部门号为20的员工的工资提高20%。要求：必须定义个游标和使用LOOP
declare
cursor cur_get_emp_info
is
select
sal
from
emp
where
dept_no = 20
;
begin
for rec_get_emp_info in cur_get_emp_info loop
exit when cur_get_emp_info%notfound;
update emp
set sal = rec_get_emp_info.sal*0.2+rec_get_emp_info.sal;
end loop;
end;
/

###### 欢迎指正 ####################