1. 请提取出所有员工的姓名及其所属领导的姓名。
select emp1.first_name||' '||emp1.last_name worker_name,
emp2.first_name||' '||emp2.last_name manager_name
from employees emp1 join employees emp2
on (emp1.manager_id=emp2.employee_id);
2.请给出薪金低于职务ID为IT_PROG的任何雇员的薪金且职务不是IT_PROG 的雇员。
select employees_id,last_name,salary,job_id
from employees
where salary<all(select salary
from employees
where job_id='IT_PROG')
and job_id<>'IT_PROG';
3.请给出没有分配部门的员工名字以及没有雇员的部门名称。
select e.last_name, d.department_name
from employees e, departments d
where e.department_id=d.department_id(+)
and d.department_name is null
union all
select e.last_name, d.department_name
from employees e, departments d
where e.department_id(+)=d.department_id
and e.last_name is null;
select e.last_name, d.department_name
from employees e full outer join departments d
on (e.department_id=d.department_id)
where d.department_id is null or e.last_name is null;
4.请给出没有下属的所有雇员的员工编号及姓名。
select emp2.first_name||' '||emp2.last_name manager,
emp2.employee_id
from employees emp1,employees emp2
where emp1.manager_id(+)=emp2.employee_id
and emp1.employee_id is null;
5.创建一个视图,要求能够显示全体雇员的详细信息,包括雇员姓名、职务ID、主管经理ID、任职部门ID、部门所在位置、薪水、佣金以及职务。
create view info_view as
select first_name || ' ' || last_name name,
e.job_id, e.manager_id, d.department_id, l.city, e.salary,
e.commission_pct, j.job_title
from employees e, departments d, locations l, jobs j
where (e.department_id=d.department_id)
and (d.location_id=l.location_id) and (e.job_id=j.job_id);
6.请对新入职员工Doug Smith建立人事档案,其职务是销售代表,底薪为2500, 佣金为0.15,emial为'56889045@outlok.com' 工作地点在London。
insert into employees
(employee_id,
first_name,
last_name,
email,
hire_date,
job_id,
salary,
commission_pct,
department_id)
values
(emp_seq.nextval,
'Doug',
'Smith',
'56889045@outlok.com',
sysdate,
'SA_REP',
2500,
0.15,
(select d.department_id
from departments d
where d.location_id in
(select location_id from locations where city = 'London')));
7.编写一个匿名块,把所有员工的姓名和薪水提取到一个RECORD变量中输出,要求使用游标。
declare
cursor emp_cursor is
select first_name||last_name,salary from employees;
type emp_record_type is record(v_name employees.last_name%TYPE,
v_salary employees.salary%TYPE);
emp_record emp_record_type;
begin
open emp_cursor;
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%NOTFOUND;
dbms_output.put_line(emp_record.v_name||' '||emp_record.v_salary);
end loop;
close emp_cursor;
end;
8.编写一个匿名块,要求提取员工编号为111的职位ID,要求定义异常,
(1)如果没有数据,输出文字提示:没有该员工的信息;
(2)该员工的职位ID不为AD_VP,输出文字提示:该员工职位已经变更。
declare
info_extract_error exception;
v_job_id employees.job_id%TYPE;
begin
select job_id into v_job_id
from employees
where employee_id = 111;
if v_job_id <> 'AD_VP' then
raise info_extract_error;
end if;
exception
when NO_DATA_FOUND then
dbms_output.put_line('没有该员工的信息');
when info_extract_error then
dbms_output.put_line('该员工职位已经变更');
end;