OBP LV9

--OGRANICENJA
--Prikaz kreiranih ogranicenja
SELECT * FROM all_constraints;
SELECT * FROM user_constraints;
SELECT * FROM all_cons_columns;
SELECT * FROM user_cons_columns;

--SYS_Cn, n-redni broj ogranicenja

--NOT NULL
--Ogranicenje na nivou kolone(bez preciziranja naziva ogranicenja):
CREATE TABLE tab_primjer1 (
  sifra NUMBER,
  naziv VARCHAR2(50) NOT NULL
);
--Ogranicenje na nivou kolone (uz preciziranje naziva ogranicenja):
CREATE TABLE tab_primjer1_1 (
  sifra NUMBER,
  naziv VARCHAR2(50) CONSTRAINT tab11_naziv_nn NOT NULL
);


--UNIQUE
--Ogranicenje na nivou tabele:
CREATE TABLE tab_primjer2(
  sifra NUMBER,
  naziv VARCHAR2(50),
  CONSTRAINT tab_pr2_uk UNIQUE(sifra)
);
--Ogranicenje na nivou kolone (bez preciziranja naziva ogranicenja):
CREATE TABLE tab_primjer2_1(
  sifra NUMBER UNIQUE,
  naziv VARCHAR2(50)
);
--Ogranicenje na nivou kolone (uz preciziranje naziva ogranicenja):
CREATE TABLE tab_primjer2_2(
  sifra NUMBER CONSTRAINT tab_pr22_uk UNIQUE,
  naziv VARCHAR2(50)
);

--PRIMARY KEY
--Ogranicenje na nivou tabele:
CREATE TABLE tab_primjer3(
  sifra NUMBER,
  naziv VARCHAR2(50),
  CONSTRAINT tab_pr3_pk PRIMARY KEY(sifra)
);
--Ogranicenje na nivou kolone (bez preciziranja naziva ogranicenja):
CREATE TABLE tab_primjer3_1(
  sifra NUMBER PRIMARY KEY,
  naziv VARCHAR2(50)
);
--Ogranicenje na nivou kolone (uz preciziranje naziva ogranicenja):
CREATE TABLE tab_primjer3_2(
  sifra NUMBER CONSTRAINT tab_32_pk PRIMARY KEY,
  naziv VARCHAR2(50)
);

SELECT column_name, data_type, data_length, nullable
FROM all_tab_columns
WHERE table_name='DEPARTMENTS';

--pomocna tabela
CREATE TABLE odjeli (
  sifra_odjela NUMBER PRIMARY KEY,
  naziv_odjela VARCHAR2(30)
);
INSERT INTO odjeli
SELECT department_id sifra_odjela , department_name naziv_odjela
FROM departments;

SELECT * FROM odjeli;

--FOREIGN KEY
--Ogranicenje na nivou tabele:
CREATE TABLE tab_primjer4(
  sifra NUMBER,
  naziv VARCHAR2(50),
  odjel NUMBER,
  CONSTRAINT tab_pr4_fk FOREIGN KEY(odjel) REFERENCES odjeli(sifra_odjela)
);
--Ogranicenje na nivou kolone(bez preciziranja naziva ogranicenja):
CREATE TABLE tab_primjer4_1(
  sifra NUMBER,
  naziv VARCHAR2(50),
  odjel NUMBER REFERENCES odjeli(sifra_odjela)
);
--Ogranicenje na nivou kolone (uz preciziranje naziva ogranicenja):
CREATE TABLE tab_primjer4_2(
  sifra NUMBER,
  naziv VARCHAR2(50),
  odjel NUMBER CONSTRAINT tab_pr42_fk REFERENCES odjeli(sifra_odjela)
);

--CHECK
--Ogranicenje na nivou tabele:
CREATE TABLE tab_primjer5(
  sifra NUMBER,
  naziv VARCHAR2(50),
  plata NUMBER,
  CONSTRAINT tab_pr5_ck CHECK(plata BETWEEN 0 AND 1000)
);
--Ogranicenje na nivou kolone (bez preciziranja naziva ogranicenja):
CREATE TABLE tab_primjer5_1(
  sifra NUMBER,
  naziv VARCHAR2(50),
  plata NUMBER CHECK(plata BETWEEN 0 AND 1000)
);
--Ogranicenje na nivou kolone (uz preciziranje naziva ogranicenja):
CREATE TABLE tab_primjer5_2(
  sifra NUMBER,
  naziv VARCHAR2(50),
  plata NUMBER CONSTRAINT tab_pr52_ck CHECK(plata BETWEEN 0 AND 1000)
);

--Naknadno dodavanje ogranicenja tabeli
--Dodavanje PRIMARY KEY ogranicenja tabeli tab_primjer1 nad kolonom sifra:
ALTER TABLE tab_primjer1 ADD CONSTRAINT tab_pr1_pk PRIMARY KEY(sifra);

--Brisanje ogranicenja
--Brisanje FOREIGN KEY ogranicenja nad tabelom tab_primjer4
ALTER TABLE tab_primjer4 DROP CONSTRAINT tab_pr4_fk;

--Onemogucavanje ogranicenja
--Onemogucavanje CHECK ogranicenja nad tabelom tab_primjer5
ALTER TABLE tab_primjer5 DISABLE CONSTRAINT tab_pr5_ck;
--Omogucavanje CHECK ogranicenja nad tabelom tab_primjer5
ALTER TABLE tab_primjer5 ENABLE CONSTRAINT tab_pr5_ck;

--POGLEDI
--Primjer 1 - jednostavni pogled
/*
Kreirati pogled zap_view sa istim nazivom kolona kao tabela zaposlenih, za sve zaposlene iz odjela 30.
*/
CREATE VIEW zap_view AS
SELECT *
FROM employees
WHERE department_id = 30;

SELECT * FROM zap_view;

--Prikaz strukture pogleda zap_view
SELECT column_name, data_type, data_length, nullable
FROM all_tab_columns
WHERE table_name='ZAP_VIEW';

--Primjer 2 - Slozeni pogled
/*
Kreirati pogled plata_odjela koji posjeduje kolone odjel, min_plata, max_plata, avg_plata za sve zaposlene grupisano po nazivu odjela.
*/
CREATE VIEW plata_odjela (odjel, min_plata, max_plata, avg_plata) AS
SELECT d.department_name, Min(e.salary), Max(e.salary), Avg(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;

SELECT * FROM plata_odjela;

--Brisanje pogleda
DROP VIEW zap_view;
DROP VIEW plata_odjela;

--Pregled sadrzaja pogleda
SELECT * FROM all_views;
SELECT * FROM user_views;


--SEKVENCE
--Primjer 1
/*
Neka je potrebno kreirati sekvencu test_seq koja ce poceti generisati
vrijednosti od broja 50012, sa korakom 1 i maksimalnom dozvoljenom vrijednoscu 50100.
Sekvencu treba kreirati tako da nakon generisanja svih brojeva sekvenca ne generise
vrijednosti dalje. Nije neophodno alociranje vrijednosti sekvence u bazi podataka.
*/
CREATE SEQUENCE test_seq
INCREMENT BY 1
START WITH 50012
MAXVALUE 50100
NOCYCLE
NOCACHE;

--Generisanje nove vrijednosti:
SELECT test_seq.NEXTVAL FROM dual;

--Vracanje zadnje generisane vrijednosti
SELECT test_seq.CURRVAL FROM dual;

--Pregled kreiranih sekvenci
SELECT * FROM all_sequences;
SELECT * FROM user_sequences;

--Promjena vec kreirane sekvence
ALTER SEQUENCE test_seq
INCREMENT BY 1
MAXVALUE 100000
NOCYCLE
NOCACHE;

--Brisanje sekvence
DROP SEQUENCE test_seq;