Базы данных для чайников 2

Простые задания с решением на 5+. Oracle SQL 2 edition

-- 12 zad 2

create or replace trigger SUPER

before insert

on employees

for each row

begin

update employees m

set m.salary = m.salary * 1.01

where m.employee_id = (

select e.employee_id

from employees e

join departments d on d.manager_id = e.employee_id

where e.department_id = :new.department_id

);

end;

select e.first_name, d.department_id, d.department_name, e.salary, e.job_id

from employees e

join departments d on d.manager_id = e.employee_id;

insert into employees (last_name, employee_id, email, hire_date, job_id, department_id)

values(

'Mama',

370,

'EMAIL',

SYSDATE,

'AD_PRES',

90

);

desc employees;

select * from employees where department_id =90;

--12 zad 4

create view IT_EMPLOYEES_VIEW as

select e.first_name, d.department_name, e.employee_id, e.department_id

from employees e

join departments d on e.department_id = d.department_id

where d.department_name = 'IT';

drop view IT_EMPLOYEES_VIEW;

select * from IT_EMPLOYEES_VIEW;

create or replace trigger Banan

INSTEAD of delete

on IT_EMPLOYEES_VIEW

for each row

begin

update employees e

set department_id = (select d.department_id from departments d

where d.department_name = 'Accounting')

where e.employee_id = :old.employee_id;

end;

/

delete from IT_EMPLOYEES_VIEW

where employee_id = 103;

select e.first_name, e.employee_id, d.department_name from employees e

join departments d on e.department_id = d.department_id

where employee_id = 103;

--version 2a 4-5

create view DEPT_MNG_VIEW as

select *

from employees m

where m.employee_id IN (

select e.employee_id

from employees e

join departments d on d.manager_id = e.employee_id

where e.commission_pct is null);

drop view DEPT_MNG_VIEW;

select * from DEPT_MNG_VIEW;

create trigger TRIGGER_MNG_VIEW

instead of delete

on DEPT_MNG_VIEW

for each row

begin

update departments

set manager_id = null

where manager_id = :old.employee_id;

end;

/

drop trigger TRIGGER_MNG_VIEW;

delete from DEPT_MNG_VIEW

where employee_id = 200;

select d.department_name, d.manager_id from departments d

where department_name = 'Administration';

--version 2b zad 4-5

create or replace view COM20_VIEW as

select *

from employees m

where m.commission_pct > 0.2

and m.employee_id NOT IN(

select manager_id

from employees

where manager_id IS NOT NULL);

select * from COM20_VIEW;

create trigger TRIGGER_COM20_VIEW

instead of delete

on COM20_VIEW

for each row

begin

update employees

set commission_pct = commission_pct * 0.8

where employee_id = :old.employee_id;

end;

/

delete from COM20_VIEW

where employee_id = 158;

select first_name, commission_pct from employees

where employee_id = 158;

--version 2c 4-5

create view COM30_VIEW as

select *

from employees

where commission_pct > 0.3 and salary > 7000;

select * from COM30_VIEW;

create or replace trigger TRIGGER_COM30_VIEW

instead of delete

on COM30_VIEW

for each row

begin

update employees

set salary = (

select MIN(salary)

from employees

where job_id = :old.job_id)

where employee_id = :old.employee_id;

end;

/

delete from COM30_VIEW

where employee_id = 156;

select salary, employee_id, first_name, last_name, job_id

from employees

where employee_id = 156;

select MIN(salary)

from employees

where job_id = 'SA_REP';

--12 zad 3

create or replace trigger LOKAL

before update on departments

for each row

declare

STRANA varchar(2);

begin

select country_id into STRANA

from locations

where location_id = :new.location_id;

if STRANA = 'DE'

then

update employees

set email = email || '.DE'

WHERE department_id = :new.department_id;

end if;

if STRANA = 'UK'

then

update employees

set last_name = 'Bond'

WHERE department_id = :new.department_id

and first_name = 'James';

end if;

end;

/

update departments

set location_id = 2700

where department_name = 'Accounting';

select e.last_name, e.email from employees e

join departments d on d.department_id = e.department_id

where d.department_name = 'Accounting';

update departments

set location_id = 2500

where department_name = 'Shipping';

select e.last_name, e.first_name, e.email from employees e

join departments d on d.department_id = e.department_id

where d.department_name = 'Shipping' and e.first_name = 'James';

drop trigger LOKAL;

select * from locations;

select * from employees;