LV 8

/*Zadatak 1*/
SELECT * FROM zaposleni;

ALTER TABLE zaposleni ADD(id NUMBER(4));

CREATE SEQUENCE sek
NOCACHE
NOCYCLE;

UPDATE zaposleni
SET id = sek.NEXTVAL;

COMMIT;

ALTER TABLE zaposleni
ADD CONSTRAINT c_zaposleni_id_pk PRIMARY KEY (id);

/*Zadatak 2*/
ALTER TABLE odjel
ADD CONSTRAINT c_odjel_id_pk ¸PRIMARY KEY (id,datum);

/*Drugi nacin

CREATE TABLE o2 AS SELECT * FROM departments;

ALTER TABLE o2
ADD (id NUMBER(4), datum DATE);

UPDATE o2
SET id = sek.NEXTVAL, datum = SYSDATE;

COMMIT;

ALTER TABLE o2
ADD CONSTRAINT c_o2_id_dat_pk PRIMARY KEY (id, datum);*/

/*Zadatak 3*/
SELECT * FROM user_constraints;

SELECT * FROM all_constraints
WHERE owner = 'HR'
ORDER BY table_name;

SELECT * FROM all_constraints
WHERE owner = 'test';

/*Zadatak 5*/
SELECT a.table_name, b.table_name
FROM all_cons_columns a, all_cons_columns b
WHERE a.owner = 'HR' AND b.owner = 'HR' AND a.column_name = b.column_name AND a.table_name <> b.table_name AND
      (a.table_name = 'EMPLOYEES' AND b.table_name <> 'EMPLOYEES' OR a.table_name = 'DEPARTMENTS' AND b.table_name <> 'DEPARTMENTS') AND
      a.constraint_name LIKE '%FK' AND b.constraint_name LIKE '%PK';

/*Zadatak 6*/
ALTER TABLE zaposleni
ADD(plata_dodatak NUMBER(12,2));

UPDATE zaposleni z
SET z.plata_dodatak = (1 + z.iznos_dodatak_na_platu) * z.plata
WHERE kontinent LIKE 'Americas';

COMMIT;

/*Zadatak 7*/
ALTER TABLE zaposleni
ADD CONSTRAINT zaposleni_pdodatak_ck CHECK (plata_dodatak BETWEEN 100 AND 5000); --ne radi

/*Zadatak 8*/
CREATE VIEW zap_pogled(sifra_zaposlenog, naziv_zaposlenog, naziv_odjela)
AS SELECT e.employee_id, e.first_name || ' ' || e.last_name, d.department_name
   FROM employees e, departments d
   WHERE e.department_id = d.department_id;

/*Zadatak 9*/
SELECT * FROM zap_pogled;

/*Zadatak 10*/
CREATE VIEW moj_pogled2(naziv_posla, naziv_odjela, prosjecna_plata, iznos_dodatka_na_platu)
AS SELECT j.job_title, d.department_name, Avg(e.salary), Sum(e.salary*e.commission_pct)
   FROM employees e, departments d, jobs j
   WHERE e.department_id = d.department_id AND e.job_id = j.job_id AND
      (Lower(j.job_title) LIKE '%a%' OR Lower(j.job_title) LIKE '%b%' OR Lower(j.job_title) LIKE '%c%') AND
      (Lower(d.department_name) LIKE '%a%' OR Lower(d.department_name) LIKE '%b%' OR Lower(d.department_name) LIKE '%c%')
   GROUP BY j.job_title, d.department_name
WITH READ ONLY;

SELECT * FROM moj_pogled;
SELECT * FROM moj_pogled2;

-- Zadatak 13
CREATE OR REPLACE VIEW sef_pog (naziv, broj_zaposlenih, min_plata_odjela, max_plata_odjela)
AS
SELECT DISTINCT s.first_name || ' ' || s.last_name,
       (SELECT Count(e1.employee_id)
        FROM employees e1
        WHERE e1.manager_id = s.employee_id),
       (SELECT Min(e1.salary)
        FROM employees e1
        WHERE e1.department_id = s.department_id),
       (SELECT Max(e1.salary)
        FROM employees e1
        WHERE e1.department_id = s.department_id)
FROM employees s, employees z
WHERE s.employee_id = z.manager_id;


SELECT * FROM sef_pog;

-- Zadatak 14
CREATE OR REPLACE VIEW sef_pog (naziv, broj_zaposlenih, min_plata_odjela, max_plata_odjela, sumarna_plata_zaposlenih)
AS
SELECT DISTINCT s.first_name || ' ' || s.last_name,
       (SELECT Count(e1.employee_id)
        FROM employees e1
        WHERE e1.manager_id = s.employee_id),
       (SELECT Min(e1.salary)
        FROM employees e1
        WHERE e1.department_id = s.department_id),
       (SELECT Max(e1.salary)
        FROM employees e1
        WHERE e1.department_id = s.department_id),
       (SELECT Sum((1 + Nvl(e1.commission_pct, 0))*e1.salary)
        FROM employees e1
        WHERE e1.manager_id = s.employee_id)
FROM employees s, employees z
WHERE s.employee_id = z.manager_id
WITH READ ONLY;