BP1_vezbe04

CREATE TABLE faze_projekta(
    Spr INTEGER ,
    Sfp INTEGER NOT NULL,
    Rukfp INTEGER NOT NULL,
    Nafp VARCHAR(30) UNIQUE,
    Datp DATE,
    CONSTRAINT faze_projekta_PK PRIMARY KEY (Spr, Sfp),
    CONSTRAINT faze_projekta_proj_FK FOREIGN KEY (Spr) REFERENCES projekat (Spr),
    CONSTRAINT faze_projekta_rad_FK FOREIGN KEY (rukfp) REFERENCES radnik (mbr)

);

ALTER TABLE faze_projekta
ADD CONSTRAINT faze_projekta_junik UNIQUE(datp);

ALTER TABLE faze_projekta
DROP CONSTRAINT faze_projekta_junik;

ALTER TABLE faze_projekta
ADD (datz DATE);

ALTER TABLE faze_projekta
ADD CONSTRAINT faze_projekta_datz CHECK (datz>datp);

INSERT INTO faze_projekta VALUES (10, 1, 100, 'prvi deo prvog','20-jan-2013', '20-dec-2013');
INSERT INTO faze_projekta VALUES (10, 2, 50, 'drugi deo prvog','21-dec-2013', '20-dec-2014');
INSERT INTO faze_projekta VALUES (20, 1, 70, 'prvi deo drugog','20-jan-201', '5-sep-2013');

SELECT p.spr, p.nap, ru.ime, ru.prz, s.prz Sef, fp.nafp,
nvl(rfp.ime, 'nema rukovodioca')Ime_rukovodioca, nvl(rfp.prz, 'nema rukovodioca')Prezime_rukovodioca
FROM  projekat p INNER JOIN radnik ru ON p.ruk = ru.mbr
      LEFT OUTER JOIN radnik s ON ru.sef = s.mbr
      LEFT OUTER JOIN faze_projekta fp ON p.spr = fp.spr
      LEFT OUTER JOIN radnik rfp ON fp.rukfp = rfp.mbr;


CREATE OR REPLACE VIEW
plate_radnika(Ime, Prezime, Plata) AS
SELECT ime, prz, plt
FROM radnik;

SELECT * FROM plate_radnika;

CREATE OR REPLACE VIEW
broj_rada(MBR, Broj_Sati) AS
SELECT r.mbr, nvl(SUM(rp.brc),0)
FROM radnik r, radproj rp
WHERE r.mbr = rp.mbr(+)
GROUP BY r.mbr;

SELECT * FROM BROJ_RADA;

SELECT * FROM broj_rada
ORDER BY mbr;

CREATE OR REPLACE VIEW
sefovi(MBR, Prezime, Ime,Ukupno_radnika, Ukupno_angazovanje) AS

SELECT s.mbr, s.prz, s.ime, COUNT(r.mbr), br.broj_sati
FROM  radnik r INNER JOIN radnik s ON r.sef = s.mbr
      INNER JOIN broj_rada br ON br.mbr = s.mbr
      GROUP BY s.mbr, s.ime, s.prz, br.broj_sati;

SELECT * FROM sefovi;

SELECT SUM(Ukupno_angazovanje) AS UkAngSef
FROM sefovi;

CREATE SEQUENCE seq_mbr
  INCREMENT BY 10
  START WITH 240
  nocycle
  cache 10;

  INSERT INTO radnik (Mbr, prz, ime, god) VALUES (seq_mbr.NEXTVAL, 'Misic', 'Petar', SYSDATE);

  SELECT seq_mbr.currval
  FROM sys.dual;

  SELECT * FROM sys.dual;

  SELECT TABLE_NAME
  FROM user_tables;

  SELECT Mbr, prz, ime, to_char(god, 'dd/mm/yyyy')
  FROM radnik;

SELECT r.mbr, r.prz, rp.spr, COUNT(dr.mbr)
FROM radnik r INNER JOIN  radproj rp ON r.mbr = rp.mbr
    INNER JOIN radnik dr ON dr.mbr = rp.mbr
GROUP BY r.mbr, r.prz, rp.spr;


SELECT r.mbr, r.ime, r.prz, rp1.spr, COUNT (rp2.mbr)-1 ostali
FROM radnik r, radproj rp1, radproj rp2
WHERE r.mbr =rp1.mbr AND rp1.spr =rp2.spr
GROUP BY r.mbr, r.ime, r.prz, rp1.spr;

WITH projinfo AS(
  SELECT rp.spr, COUNT(rp.mbr) AS rad_broj
  FROM radproj rp GROUP BY rp.spr)
  SELECT r.mbr, r.ime, r.prz, rp.spr, pi.rad_broj-1 ostali
  FROM radnik r, radproj rp, projinfo pi
  WHERE r.mbr = rp.mbr AND rp.spr = pi.spr;


  WITH ukupno_angazovanje AS(
    SELECT spr, SUM(brc)UkAng FROM radproj
    GROUP BY spr)
 SELECT r.mbr, r.prz, r.ime, rp.spr, to_char(round((rp.brc/ua.UkAng),4)*100 ) || '%' Udeo_U_Angazovanju
 FROM radnik r, radproj rp, ukupno_angazovanje ua
 WHERE r.mbr = rp.mbr AND rp.spr = ua.spr;

 WITH projinfo AS(
  SELECT rp.spr, SUM(rp.brc) AS cas_suma
  FROM radproj rp GROUP BY rp.spr)

  SELECT r.mbr, r.ime, r.prz, rp.spr, round((rp.brc/pi.cas_suma),2) udeo
  FROM radnik r, radproj rp, projinfo pi
  WHERE r.mbr = rp.mbr AND rp.spr=pi.spr;