oracle期末（二） .

oracle数据库管理与开发  孙风栋

部分答案

p255

(1)select * from hr.employees where department_id=100;

(2)select employee_id,first_name,department_id from hr.employees where job_id='SA_MAN';

(3)select employee_id,salary,salary*commission_pct,nvl2(commission_pct,salary*(1+commission_pct),salary) total
   from hr.employees;

(4)select * from hr.employees where salary*commission_pct>salary*0.2;

(5)select * from hr.employees where (job_id='AD_ASST' and department_id=40) or (job_id='SA_REP' and department_id=20);

(6)select * from hr.employees where job_id not in('Stock Manager','Purchasing Manager') and salary>=2000;

(7)select job_id,first_name from hr.employees where COMMISSION_PCT is not null group by job_id;

(8)select * from hr.employees where commission_pct is null or salary*commission_pct<100;

(9)select employee_id,department_id,job_id,first_name,last_name,hire_date from hr.employees
where last_day(hire_date)-hire_date=1;

(10)select * from hr.employees where months_between(sysdate,hire_date)/12>=10;

(11)select initcap(first_name),initcap(last_name) from hr.employees;

(12)select * from hr.employees where length(first_name)=6;

(13)select * from hr.employees where first_name not like '%S%';

(14)select * from hr.employees where first_name like '_M%';

(15)select substr(first_name,1,3),substr(last_name,1,3) from hr.employees;

(16)select replace(first_name,'s','S') from hr.employees;

(17)select first_name||last_name,hire_date from hr.employees order by hire_date;

(18)select first_name||last_name,job_id,salary,salary*commission_pct from hr.employees
order by job_id desc,salary;

(19)select first_name||last_name,to_char(hire_date,'yyyy') 年份,to_char(hire_date,'mm') 月份 from hr.employees
order by to_char(hire_date,'mm'),to_char(hire_date,'yyyy');

(20)select * from hr.employees where to_char(hire_date,'mm')=2;

(21)select employee_id,first_name||last_name,job_id,(trunc((sysdate-hire_date)/365)||'年'
||trunc(mod((sysdate-hire_date),365)/30)||'月'||trunc(mod(mod((sysdate-hire_date),365)/30))
||'日') days from hr.employees;

(22)select * from hr.departments where department_id in (select department_id from hr.employees group by department_id having count(employee_id)>1);

(23)select * from hr.employees
where salary>(select salary from hr.employees where employee_id=100);

(24)select first_name||last_name 员工姓名,(select first_name||last_name from hr.employees e2 where e1.manager_id=e2.employee_id) 上级姓名
from hr.employees e1;

(25)select first_name||last_name 员工姓名,hire_date 入职日期,(select first_name||last_name from hr.employees e2 where
e1.manager_id=e2.employee_id) 上级姓名,(select hire_date from hr.employees e2 where e1.manager_id=e2.employee_id) 入职日期
from hr.employees e1
where hire_date<(select hire_date from hr.employees e2 where e1.manager_id=e2.employee_id);

(26)select d.department_id 部门号,d.department_name 部门名称,l.state_province||l.city||l.street_address 部门所在地,e.first_name||e.last_name 部门领导
from hr.departments d,hr.locations l,hr.employees e
where d.location_id=l.location_id and d.manager_id=e.manager_id;

(27)select d.department_id 部门号,d.department_name 部门名,e.employee_id 员工号,e.first_name 员工名
from hr.departments d left outer join hr.employees e
on d.department_id=e.department_id;

(28)select e.employee_id 员工号,e.first_name 员工名,d.department_id 部门号,d.department_name 部门名
from hr.employees e left outer join hr.departments d
on d.department_id=e.department_id;

(29)select e.employee_id 员工号,e.first_name 员工名,d.department_name 部门名称,j.job_title 职位名称,e.salary 工资,
salary*commission_pct 奖金
from hr.employees e,hr.departments d,hr.jobs j
where e.department_id=d.department_id and e.job_id=j.job_id;

(30)select job_id,min(salary) from hr.employees group by job_id having min(salary)>5000;

(31)select d.department_id,d.department_name,e.employee_id,e.first_name from hr.departments d,hr.employees e
where d.department_id=e.department_id and
d.department_id in(select department_id from hr.employees e group by department_id having avg(salary)<6000);

(32)select first_name||last_name from hr.employees where department_id=(select department_id from hr.departments where department_name='Sales');

(33)select * from hr.employees
where salary>(select avg(salary) from hr.employees);

(34)select * from hr.employees
where job_id=(select job_id from hr.employees where employee_id='150');

(35)select first_name||last_name,salary
from hr.employees
where salary in(select salary from hr.employees where department_id=30) and department_id!=30;

(36)select first_name||last_name,salary
from hr.employees
where salary >all(select salary from hr.employees where department_id=30) and department_id!=30;

(37)select count(*),avg(salary),avg(sysdate-hire_date) from hr.employees group by department_id;

不用做(38)select * from hr.employees e1
where e1.job_id in(select distinct e2.job_id from hr.employees e2 where e2.department_id!=e1.department_id);

(39)select d.department_id,d.department_name,count(e.employee_id),avg(e.salary)
from hr.departments d left outer join hr.employees e on d.department_id=e.department_id
group by d.department_id,department_name;

(40)select job_id 工种,min(salary) from hr.employees e group by job_id;

(41)select department_name,job_id,max(salary)
from hr.departments d left join hr.employees e on d.department_id=e.department_id
group by department_name,job_id;
或
select department_id,job_id,max(salary) from hr.employees group by department_id,job_id;

(42)select d.department_name,count(e.employee_id),avg(salary)
from hr.departments d left join hr.employees e on d.department_id=e.department_id
group by d.department_name;
或
(select count(*),avg(salary) from hr.employees group by department_id;)

(43)select * from hr.employees where salary in(select avg(salary) from hr.employees group by department_id);

(44)select * from hr.employees e1
where salary >(select avg(salary) from hr.employees e2 where e1.department_id=e2.department_id);

(45)select *,(select avg(salary) from hr.employees e2 where e1.department_id=e2.department_id)
from hr.employees e1
where salary >(select avg(salary) from hr.employees e2 where e1.department_id=e2.department_id);

(46)select * from hr.employees where salary> any(select salary from hr.employees where department_id=50);

(47)select job_id,count(employee_id),avg(salary) from hr.employees
group by job_id;

(48)select d.department_id,e.job_id,count(employee_id),avg(salary)
from hr.departments d left join hr.employees e on d.department_id=e.department_id
group by d.department_id,e.job_id;
或
(select department_id,job_id,count(*),avg(salary) from hr.employees group by department_id,job_id;)

(49)select * from hr.employees e where salary+salary*commission_pct in
(select salary+salary*commission_pct from hr.employees e1
where e1.department_id=10 and e.department_id!=10 and e.salary=e1.salary and e.commission_pct=e1.commission_pct);
或
select * from hr.employees
where (salary,commission_pct)
in (select salary,commission_pct from hr.employees where department_id=10);

(50)select * from hr.employees
where department_id
in (select department_id from hr.employees group by department_id having count(*)>10);

(51)select * from hr.departments
where department_id
in(select department_id from hr.employees group by department_id having min(salary)>10000);

(52)select * from hr.departments left join hr.employees e on d.department_id=e.department_id
where e.department_id
in(select department_id from hr.employees e1 group by department_id having min(salary)>5000);

(53)select * from hr.departments
where department_id
in(select department_id from hr.employees group by department_id having min(salary)>4000 and max(salary)<8000);

(54)select * from hr.departments
where department_id
in(select department_id from hr.employees group by department_id having
count(*) >=all(select max(count(*)) from hr.employees group by department_id));

(55)select rownum,a.*
from (select * from hr.employees order by salary desc) a
where rownum<=3 and department_id=30 order by salary desc;

不做(56)select * from
(select t.*,rank()over (partition by n order by salary) r from
(select '' as n,tt.* from hr.employees tt)t)
where r>=5 and r<=10;
56,57,58不用做，56实训时会做，57，58涉及树状查询
(59)insert into hr.employees
select 1000,first_name,last_name,'example@neusoft.edu.cn',phone_number,
'10-8月-2002',job_id,salary,commission_pct,manager_id,department_id
from hr.employees where employee_id=160;

(60)update hr.employees e
set salary=1000+(select avg(salary) from hr.employees
where department_id=e.department_id);

 

p299

(1)
begin
 for v_emp in (select * from employees) loop
  dbms_output.put_line(v_emp.employee_id||'  '||v_emp.first_name||
                     ' '||v_emp.last_name||' '||v_emp.department_id);
 end loop;
end;

(2)

declare
  cursor c_emp is select * from employees e where salary>(
     select avg(salary) from employees
     where department_id=e.department_id);
begin
 for v_emp in c_emp loop
  dbms_output.put_line(v_emp.employee_id||'  '||v_emp.first_name||
                     ' '||v_emp.last_name||' '||v_emp.department_id);
 end loop;
end;
(3)
declare
  cursor c_emp is select e.employee_id eid,e.first_name efname,
  e.last_name elname,e.department_id edid,m.employee_id mid,
  m.first_name mfname,m.last_name mlname,m.department_id mdid
   from employees e left join employees m
   on e.manager_id=m.employee_id;
begin
 for v_emp in c_emp loop
  dbms_output.put_line(
  v_emp.eid  ||'  '||v_emp.efname||' '||v_emp.elname||' '||
  v_emp.edid ||'  '||v_emp.mid   ||' '||v_emp.mfname||' '||
  v_emp.mlname ||' '||v_emp.mdid);
 end loop;
end;
/
(4)
declare
  v_emp employees%rowtype;
begin
  select * into v_emp from employees where last_name='Smith';
  dbms_output.put_line(
     v_emp.employee_id||' '||v_emp.first_name||' '||
     v_emp.last_name||' '||v_emp.salary||' '||v_emp.department_id);
exception
  when no_data_found then
    insert into employees values    (2010,null,'Smith','smith@neusoft.edu.cn',null,
    to_date('2000-10-5','yyyy-mm-dd'),'AD_VP',7500,NULL,NULL,50);
  when too_many_rows then
  for v_emp in (select * from employees where last_name='Smith') loop
    dbms_output.put_line(
     v_emp.employee_id||' '||v_emp.first_name||' '||
     v_emp.last_name||' '||v_emp.salary||' '||v_emp.department_id);
  end loop;
end;
(5)
declare
  v_inc employees.salary%type;
begin
 for v_emp in (select * from employees) loop
  case
  when v_emp.job_id in('AD_PRES','AD_VP','AD_ASST') THEN v_inc:=1000;
  when v_emp.job_id in('FI_MGR','FI_ACCOUNT') THEN v_inc:=800;
  when v_emp.job_id in('AC_MGR','AC_ACCOUNT') THEN v_inc:=700;
  when v_emp.job_id in('SA_MAN','SA_REP') THEN v_inc:=600;
  when v_emp.job_id in('PU_MAN','PU_CLERK')THEN v_inc:=500;
when v_emp.job_id in('ST_MAN','ST_CLERK','SH_CLERK') THEN v_inc:=400;
  when v_emp.job_id in('IT_PROG','MK_MAN','MK_REP') THEN v_inc:=300;
  else v_inc:=200;
 end case;
  update employees set salary=salary+v_inc
   where employee_id=v_emp.employee_id;
 end loop; 
end;
(6)declare
  v_job_id  jobs.job_id%type;
  v_maxsal number;
  v_minsal number;
begin
  select job_id into v_job_id from employees where employee_id=201; 
  select min_salary,max_salary into v_minsal,v_maxsal from jobs
  where job_id=v_job_id;
  if 8000 between v_minsal and v_maxsal then
    update employees set salary=8000 where employee_id=201;
  end if;
exception
  when no_data_found then
    dbms_output.put_line('the employee doex not exist!');
end;

 

p332

 

(1)

create or replace procedure proc_show_sqlary(

  p_empno employees.employee_id%type)

as

  v_sal employees.salary%type;

begin

  select salary into v_sal from employees where employee_id=p_empno;

  dbms_output.put_line(v_sal);

exception

  when no_data_found then

   dbms_output.put_line('there is not such an employee!');

end;

 

(3)

create or replace procedure proc_job_age(

  p_empno employees.employee_id%type,

  p_age OUT number)

as

begin

  select round((sysdate-hire_date)/365,2) into p_age from employees

  where employee_id=p_empno;

exception

  when no_data_found then

  dbms_output.put_line('there is not such an employee!');

end;

 

(4)

create or replace procedure proc_show_emp(

  p_deptno employees.department_id%type)

as

begin

  for v_emp in(

         select * from (

         select * from employees where department_id=p_deptno and hire_date is not          null order by hire_date desc ) where rownum<=10 ) loop

    dbms_output.put_line(v_emp.employee_id||' '||v_emp.hire_date);

  end loop;

end;

/

(5)

create or replace function func_ret_sal(

  p_empno employees.employee_id%type)

return employees.salary%type

as

v_sal employees.salary%type;

begin

  select salary into v_sal from employees where employee_id=p_empno;

return v_sal;

exception

when no_data_found then

dbms_output.put_line('there id not such an employee!');

end;

/

(6)

create or replace function func_ret_avgsal(

  p_empno employees.deprtment_id%type)

return employees.salary%type

as

v_sal employees.salary%type;

begin

  select avg(salary) into v_sal from employees where employee_id=p_deptno;

return v_sal;

exception

when no_data_found then

dbms_output.put_line('there id not such an employee!');

end;

/

(7)

 

(10) create or replace trigger trg_emp

 before insert or update or delete

 on employees

 begin

    if to_char(sysdate,'hh24:mi') not between '08:00' and '17:00' then

   raise_application_error(-20000,'forbid operating');

 end if;

 end;

 

(11)create or replace trigger trg_dept_stat

after insert or update or delete

on employees

begin

   for v_dept in (select department_id,count(*) num,avg(salary) avgsal

         from employees group by department_id) loop

   dbms_output.put_line(v_dept.department_id||' '||v_dept.num||' '||v_dept.avgsal);

end loop;

end;