oracle考题
ORACLE 测试
一、完成下列SQL语句
1．查询员工姓名（last_name或first_name），hire_date , department_id
满足以下条件：雇用时间在1997年之后，department_id 为80 或 90 或110, commission_pct不为空
select last_name, hire_date, department_id
from employees
where hire_date > to_date('1997-12-31', 'yyyy-mm-dd') and department_id in (80,90,110) and commission_pct is not null
2．查询员工所在的部门名称、部门地址、姓名、工作、工资其中员工所在部门的部门名称为’Executive’
select department_name, city, last_name, job_id, salary from employees e join departments d on e.department_id = d.department_id join locations l on d.location_id = l.location_id where department_name = 'Executive'
3．查询公司各员工工作的年数，工作的月份数（sysdate-hire_date为工作的天数），并按工作年数的降序排序。
select months_between(sysdate, hire_date) / 12 hire_years, months_between(sysdate, hire_Date) hire_months from employees order by hire_years desc
4．查询员工号、姓名、雇用时间、工资其中员工的管理者为’De Haan’
select employee_id, last_name, hire_date, salary
from employees
where manager_id in (
select employee_id
from employees
where last_name = 'De Haan'
)
5．将公司中工资小于自己所在部门平均工资的员工工资更新为原工资的1.1倍
update employees e set salary = salary * 1.1
where salary < (
select avg(salary)
from employees
where department_id = e.department_id
)
6．查询在90或60号部门工作的员工的last_name, job_id
select last_name, job_id from employees where department_id in (90,60)
7．查询员工姓名、工资和税金，其中税金的换算关系为
Salary tax _rate
0— 2999 salary*0.10
3000—5999 salary*0.15
6000 — 8999 salary*0.20
9000 — 11999 salary*0.30
〉12000 salary*0.40
select last_name, salary, salary * decode(trunc(salary / 3000), 0, 0.1,
1, 0.15,
2, 0.2,
3, 0.3,
0.4) tax_rate
from employees
二、完成下列存储过程
创建存储过程updatesalary(deptid number)
其中参数deptid为部门id ， 根据输入的部门id输出出该部门各员工的雇佣时间和工资，并根据以下条件对工资进行更新
hire_date salary
hire_date < 1995 salary = salary*1.2
hire_date >=1995 and hire_date <= 1998 salary = salary*1.15
hire_date > 1998 and hire_date <= 2001 salary = salary *1.10
hire_date > 2001 salary = salary * 1.05
create or replace procedure updatesalary(deptid number)
is
cursor sal_cur is select hire_date, salary from employees where department_id=deptid for update;
v_temp number(3,2);
begin
for sal_rec in sal_cur loop
if sal_rec.hire_date < to_date('1995-1-1','yyyy-mm-dd') then
v_temp := 1.2;
elsif sal_rec.hire_date < to_date('1999-1-1','yyyy-mm-dd') then
v_temp := 1.15;
elsif sal_rec.hire_date < to_date('2002-1-1','yyyy-mm-dd') then
v_temp := 1.1;
else
v_temp := 1.05;
end if;
update employees set salary = salary * v_temp where current of sal_cur;
end loop;
end;
三、完成下列函数
创建函数 Deptcount (p_deptno number default 10 ，p_emp_count out number) return number
根据输入参数p_deptno（部门id）求出该部门的部门人数(p_emp_count)
如果函数正常执行 则函数返回值为0；
如果该部门人数超过100 则函数返回值为1；
如果出现其他错误 则函数返回值为2
create or replace function Deptcount(p_deptno number default 10, p_emp_count out number)
return number
is
too_many_emps exception;
begin
select count(employee_id) into p_emp_count
from employees
where department_id = p_deptno;
if p_emp_count > 100 then
raise too_many_emps;
end if;
return 0;
exception
when too_many_emps then return 1;
when others then return 2;
end;
四、创建触发器 dmltrigger 在对表 employees 进行 insert , update 或 delete 操作前触发，
如果是insert操作， 则将插入的数据存入empback表中
如果是update操作， 则将更新前的数据存入empback表中
如果是delete操作 ，则将删除的数据存入empback表中
注意: 1. 可以利用 INSERTING, UPDATING, DELETING 识别实际触发触发器的语句类型
如: if inserting then ...
2. 通过查看 user_users 表可以得到当前登录 oracle 数据库用户的详细信息
以下是empback表的结构
CREATE TABLE empback
(userid VARCHAR2(20) --执行操作者的用户名
, Dmldate date DEFAULT SYSDATE -- 操作时间
, employee_id number(6) -- 员工id
, first_name VARCHAR2(20) --名
, last_name VARCHAR2(25) --姓
, email VARCHAR2(25) --电子邮件
, phone_number VARCHAR2(20) --电话号码
, hire_date DATE --雇用时间
, job_id VARCHAR2(10) -- 工作id
, salary number(8,2) --工资
, commission_pct number(2,2) -- 奖金
, manager_id number(6) --雇员的管理者的员工id
, department_id number(4) -- 部门id
)
create or replace trigger dmltrigger
before insert or update or delete
on employees
for each row
begin
if INSERTING then
insert into empback values((select username from user_users), sysdate, :new.employee_id, :new.first_name,:new.last_name,:new.email,:new.phone_number,
:new.hire_date, :new.job_id, :new.salary, :new.commission_pct, :new.manager_id, :new.department_id );
elsif UPDATING or DELETING then
insert into empback values((select username from user_users), sysdate, :old.employee_id, :old.first_name,:old.last_name,:old.email,:old.phone_number,
:old.hire_date, :old.job_id, :old.salary, :old.commission_pct, :old.manager_id, :old.department_id );
end if;
end;
------------------------------------------------------------------------------------------------------------------------------------------------------------
create or replace trigger dmltrigger
before insert or update or delete
on employees
for each row
declare
v_username user_users.username%type;
begin
select username into v_username from user_users;
if INSERTING then
insert into empback values(v_username, sysdate, :new.employee_id, :new.first_name,:new.last_name,:new.email,:new.phone_number,
:new.hire_date, :new.job_id, :new.salary, :new.commission_pct, :new.manager_id, :new.department_id );
elsif UPDATING or DELETING then
insert into empback values(v_username, sysdate, :old.employee_id, :old.first_name,:old.last_name,:old.email,:old.phone_number,
:old.hire_date, :old.job_id, :old.salary, :old.commission_pct, :old.manager_id, :old.department_id );
end if;
end;
