LV10 OBP
unknown
sql
3 years ago
11 kB
14
Indexable
--ZADATAK 1.
/*
Kreirajte vasu tabelu zaposlenih koja ce sadrzavati sljedece kolone: sifru
zaposlenog, naziv zaposlenog, naziv odjela, sifra posla, naziv posla, platu i dodatak
na platu. Potom modificirati vasu tabelu zaposlenih sa novom kolonom sifra koja ce
biti primarni kljuc. Potom kreirati triger koji ce onemoguciti promjene nad podacima
onih zaposlenih koji su poceli da rade od 1998 godine.
*/
CREATE TABLE zaposleni10 AS
SELECT e.employee_id sifra_zaposlenog,
e.first_name || ' ' || e.last_name naziv_zaposlenog,
d.department_name naziv_odjela, j
.job_id sifra_posla,
j.job_title naziv_posla,
e.salary plata,
e.commission_pct dodatak_na_platu
FROM employees e, departments d, jobs j
WHERE e.job_id = j.job_id AND e.department_id = d.department_id;
SELECT * FROM zaposleni10;
ALTER TABLE zaposleni10
ADD(id NUMBER);
CREATE SEQUENCE seq_zap10_id
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOMINVALUE
NOCYCLE
NOCACHE;
UPDATE zaposleni10
SET id = seq_zap10_id.NEXTVAL;
ALTER TABLE zaposleni10
ADD CONSTRAINT zap10_id_pk PRIMARY KEY(id);
ALTER TABLE zaposleni10
ADD(datum_zaposlenja DATE);
UPDATE zaposleni10 z
SET z.datum_zaposlenja = (SELECT e.hire_date
FROM employees e
WHERE e.employee_id = z.sifra_zaposlenog);
CREATE OR REPLACE TRIGGER trig_zap10_98
BEFORE UPDATE ON zaposleni10
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
IF To_Number(To_Char(:old.datum_zaposlenja, 'YYYY')) = 1998
THEN Raise_Application_Error(-20500, 'Nije moguce izvrsiti promjenu podataka onih zaposlenih koji su poceli da rade 1998. godine!');
END IF;
END;
/
--Provjera
INSERT INTO zaposleni10
VALUES(300, 'Martin Bolein', 'Marketing', 'MK_MAN', 'Marketing Manager', 12000, NULL, seq_zap10_id.NEXTVAL, To_Date('01.04.1998', 'dd.mm.yyyy'));
UPDATE zaposleni10
SET plata = 19000
WHERE sifra_zaposlenog IN (300);
--ZADATAK 2.
/*
Kreirati triger nad vasom tabelom zaposlenih koji ce onemoguciti promjene nad
podacima za radne dane, tj. od ponedjeljka do petka, u periodu od 16:23 i 23:16, i
subotom i nedjeljom od 06:34 i 23:56.
*/
CREATE OR REPLACE TRIGGER trig_zap10_promjene
BEFORE UPDATE ON zaposleni10
BEGIN
IF (Lower(To_Char(SYSDATE, 'dy')) IN ('mon', 'tue', 'wed', 'thu', 'fri')) AND (To_Date(To_Char(SYSDATE, 'hh24-mi'),'hh24-mi') BETWEEN To_Date('16-23','hh24-mi') AND To_Date('23-16', 'hh24-mi'))
THEN Raise_Application_Error(-20500, 'Nije moguce izvrsiti promjene nad podacima za radne dane u periodu od 16:23 i 23:16!');
END IF;
IF (Lower(To_Char(SYSDATE, 'dy')) IN ('sat','sun')) AND (To_Date(To_Char(SYSDATE, 'hh24-mi'),'hh24-mi') BETWEEN To_Date('06-34','hh24-mi') AND To_Date('23-56', 'hh24-mi'))
THEN Raise_Application_Error(-20501, 'Nije moguce izvrsiti promjene nad podacima za dane vikenda u periodu od 06:34 i 23:56!');
END IF;
END;
/
--Provjera
UPDATE zaposleni10
SET plata = 13000
WHERE sifra_zaposlenog = 100;
--ZADATAK 3.
/*
Kreirati triger nad vasom tablom zaposlenih koji ce za sve promjene nad podacima
zaposlenih insertovati stare podatke o zaposlenom u vasu tabelu arhive, koja ce
pored svih kolona kao i vasa tabela zaposlenih sadrzavati jos i informacije o
korisniku koji se azurirao podatke, kao i vrijeme azuriranja podataka.
*/
CREATE TABLE arhiva_zap10(
sifra_zaposlenog NUMBER,
naziv_zaposlenog VARCHAR2(50),
naziv_odjela VARCHAR2(50),
sifra_posla VARCHAR2(15),
plata NUMBER,
dodatak_na_platu NUMBER,
id NUMBER,
datum_zaposlenja DATE,
korisnik VARCHAR2(20),
vrijeme_azuriranja VARCHAR2(50));
CREATE OR REPLACE TRIGGER trig_update_arhiva
AFTER UPDATE ON zaposleni10
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
INSERT INTO arhiva_zap10
VALUES(:old.sifra_zaposlenog, :old.naziv_zaposlenog, :old.naziv_odjela, :old.sifra_posla, :old.plata, :old.dodatak_na_platu, :old.id, :old.datum_zaposlenja, USER, To_Char(SYSDATE, 'hh24:mi'));
END;
/
--Provjera
DROP TRIGGER trig_zap10_promjene;
UPDATE zaposleni10
SET plata = 24000
WHERE sifra_zaposlenog = 100;
SELECT * FROM arhiva_zap10;
--ZADATAK 4.
CREATE TABLE odjeli10 AS SELECT * FROM departments;
CREATE TABLE poslovi10 AS SELECT * FROM jobs;
TRUNCATE TABLE odjeli10;
TRUNCATE TABLE poslovi10;
ALTER TABLE odjeli10
ADD(id NUMBER, korisnik VARCHAR2(20), datum DATE);
ALTER TABLE poslovi10
ADD(id NUMBER, korisnik VARCHAR2(20), datum DATE);
ALTER TABLE odjeli10
ADD CONSTRAINT odjeli10_id_pk PRIMARY KEY(id);
ALTER TABLE poslovi10
ADD CONSTRAINT poslovi10_id_pk PRIMARY KEY(id);
ALTER TABLE zaposleni10
ADD(id_odjela NUMBER, id_posla NUMBER);
ALTER TABLE zaposleni10
ADD CONSTRAINT zap10_odjeli10_fk FOREIGN KEY(id_odjela) REFERENCES odjeli10(id);
ALTER TABLE zaposleni10
ADD CONSTRAINT zap10_poslovi10_fk FOREIGN KEY(id_posla) REFERENCES poslovi10(id);
--Koristimo ranije kreiranu sekvencu
CREATE TABLE odjeli_proba AS SELECT * FROM departments;
TRUNCATE TABLE odjeli_proba;
CREATE OR REPLACE TRIGGER trig_insert_odjeli
AFTER INSERT ON odjeli_proba
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH row
BEGIN
INSERT INTO odjeli10
VALUES(:new.department_id,:new.department_name, :new.manager_id, :new.location_id, seq_zap10_id.NEXTVAL, USER, SYSDATE);
END;
/
CREATE TABLE poslovi_proba AS SELECT * FROM jobs;
TRUNCATE TABLE poslovi_proba;
SELECT * FROM poslovi10;
CREATE OR REPLACE TRIGGER trig_insert_poslovi
AFTER INSERT ON poslovi_proba
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH row
BEGIN
INSERT INTO poslovi10
VALUES(:new.job_id,:new.job_title, :new.min_salary, :new.max_salary, seq_zap10_id.NEXTVAL, USER, SYSDATE);
END;
/
-Provjera
INSERT INTO odjeli_proba
VALUES(10, 'Administration', 200,1700);
INSERT INTO odjeli_proba
VALUES(20, 'Marketing', 201,1800);
SELECT * FROM odjeli10;
INSERT INTO poslovi_proba
VALUES('AD_PRES', 'President', 20080,40000);
INSERT INTO poslovi_proba
VALUES('AD_VP', 'Admiistration Vice President', 15000,30000);
SELECT * FROM poslovi10;
--ZADATAK 5.
CREATE OR REPLACE TRIGGER trig_insert_odjeli
AFTER INSERT ON odjeli_proba
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
INSERT INTO odjeli10
VALUES(:new.department_id,:new.department_name, (SELECT DISTINCT m.employee_id
FROM employees e, employees m
WHERE e.manager_id = m.employee_id AND
(SELECT Count(e1.employee_id)
FROM employees e1
WHERE e1.manager_id = m.employee_id) = (SELECT Max(Count(e2.employee_id))
FROM employees e2, employees m1
where e2.manager_id = m1.employee_id
GROUP BY m1.employee_id))
, :new.location_id, seq_zap10_id.NEXTVAL, USER, SYSDATE);
END;
/
--Provjera
TRUNCATE TABLE odjeli_proba;
INSERT INTO odjeli_proba
VALUES(10, 'Administration', 200,1700);
INSERT INTO odjeli_proba
VALUES(20, 'Marketing', 201,1800);
SELECT * FROM odjeli10;
--ZADATAK 6.
--Kako u tabeli zaposlenih, svaki sef rukovodi sa najvise 1 odjelom, kreirat cemo novu tabelu odjela i azurirati je
CREATE TABLE departments2 AS SELECT * FROM departments;
UPDATE departments2
SET manager_id = 200
WHERE department_id = 190;
CREATE OR REPLACE TRIGGER trig_insert_odjeli
AFTER INSERT ON odjeli_proba
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
INSERT INTO odjeli10
VALUES((SELECT d.department_id
FROM departments2 d
WHERE d.manager_id = (SELECT e.employee_id
FROM employees e
WHERE 2<= (SELECT Count(d1.department_id)
FROM departments2 d1
WHERE d1.manager_id = e.employee_id
GROUP BY e.employee_id))
AND ROWNUM=1)
,:new.department_name, (SELECT DISTINCT m.employee_id
FROM employees e, employees m
WHERE e.manager_id = m.employee_id AND
(SELECT Count(e1.employee_id)
FROM employees e1
WHERE e1.manager_id = m.employee_id) = (SELECT Max(Count(e2.employee_id))
FROM employees e2, employees m1
WHERE e2.manager_id = m1.employee_id
GROUP BY m1.employee_id))
, :new.location_id, seq_zap10_id.NEXTVAL, USER, SYSDATE);
END;
/
--Provjera
TRUNCATE TABLE odjeli_proba;
SELECT * FROM odjeli_proba;
INSERT INTO odjeli_proba
VALUES(10, 'Administration', 200,1700);
INSERT INTO odjeli_proba
VALUES(20, 'Marketing', 201,1800);
SELECT * FROM odjeli10;
--ZADATAK 7.
CREATE TABLE arhiva_zap10_brisanje(
id_tabela_arhive NUMBER PRIMARY KEY,
sifra_zaposlenog NUMBER,
naziv_zaposlenog VARCHAR2(50),
naziv_odjela VARCHAR2(50),
sifra_posla VARCHAR2(15),
naziv_posla VARCHAR2(50),
plata NUMBER,
dodatak_na_platu NUMBER,
id NUMBER,
datum_zaposlenja DATE,
id_odjela NUMBER,
id_posla NUMBER,
korisnik VARCHAR2(20),
vrijeme_brisanja VARCHAR2(50));
CREATE OR REPLACE TRIGGER trig_delete_arhiva
AFTER DELETE ON zaposleni10
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
INSERT INTO arhiva_zap10_brisanje
VALUES(seq_zap10_id.NEXTVAL,:old.sifra_zaposlenog, :old.naziv_zaposlenog, :old.naziv_odjela, :old.sifra_posla, :old.naziv_posla, :old.plata, :old.dodatak_na_platu, :old.id, :old.datum_zaposlenja,
:old.id_odjela, :old.id_posla, USER, To_Char(SYSDATE, 'hh24:mi'));
END;
/
--Provjera
SELECT * FROM zaposleni10;
DELETE FROM zaposleni10
WHERE sifra_zaposlenog = 201;
SELECT * FROM arhiva_zap10_brisanje;
SELECT * FROM odjeli10;
SELECT * FROM locations;
--ZADATAK 9.
CREATE TABLE log_odjeli_lokacije(
id NUMBER,
department_id NUMBER,
stari_location_id NUMBER,
novi_location_id NUMBER);
CREATE OR REPLACE TRIGGER trig_amerika_engleska
BEFORE UPDATE ON odjeli10
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
lokacija_US odjeli10.location_id%TYPE := 1700;
lokacija_UK odjeli10.location_id%TYPE := 2500;
BEGIN
IF :old.location_id = lokacija_US AND :new.location_id = lokacija_UK
THEN Raise_Application_Error(-20500, 'Greska! Nije moguce promijeniti lokaciju odjela iz Amerike u Englesku!');
ELSE
INSERT INTO log_odjeli_lokacije
VALUES(seq_zap10_id.NEXTVAL, :old.department_id, :old.location_id, :new.location_id);
END IF;
END;
/
SELECT * FROM odjeli10;
UPDATE odjeli10
SET location_id = 2500
WHERE location_id = 1700;Editor is loading...