-- POGLEDE VIEW
-- napraviti pogled
-- u pogledu imati ispitne rokove cija je skolska godina >= 2018

CREATE VIEW ispitni_rok_2018 AS
SELECT skgodina, oznakaroka, naziv, datpocetka, datkraja
FROM ispitnirok
WHERE skgodina >= 2018;

SELECT * 
FROM ispitnirok
WHERE skgodina >=2018
	AND oznakaroka LIKE '%test%';

SELECT * 
FROM ispitni_rok_2018
WHERE oznakaroka LIKE '%test%';

INSERT INTO ispitni_rok_2018(skgodina, oznakaroka, naziv, datpocetka, datkraja)
VALUES (2018, 'test', 'test 2018', '04/04/2018', '10/04/2018');

INSERT INTO ispitni_rok_2018(skgodina, oznakaroka, naziv, datpocetka, datkraja)
VALUES (2018, 'test1', 'test1 2018', '04/05/2018', '10/05/2018');

INSERT INTO ispitni_rok_2018(skgodina, oznakaroka, naziv, datpocetka, datkraja)
VALUES (2018, 'test2', 'test2 2018', '04/06/2018', '10/06/2018');

INSERT INTO ispitni_rok_2018(skgodina, oznakaroka, naziv, datpocetka, datkraja)
VALUES (2019, 'test', 'test 2019', '04/04/2019', '10/04/2019');

INSERT INTO ispitni_rok_2018(skgodina, oznakaroka, naziv, datpocetka, datkraja)
VALUES (2019, 'test1', 'test1 2019', '04/05/2019', '10/05/2019');

UPDATE ispitni_rok_2018
SET oznakaroka = 'test0'
WHERE oznakaroka = 'test';

INSERT INTO ispitni_rok_2018(skgodina, oznakaroka, naziv, datpocetka, datkraja)
VALUES (2017, 'test', 'test 2017', '04/05/2017', '10/05/2017');

UPDATE ispitni_rok_2018
SET skgodina = 2017
WHERE oznakaroka = 'test2';

SELECT * 
FROM ispitnirok
WHERE oznakaroka LIKE '%test%';

SELECT * 
FROM ispitni_rok_2018
WHERE oznakaroka LIKE '%test%';

-- Dok posmatramo ovaj pogled treba primetiti razlike ukoliko zadamo
-- LOCAL ili CASCADED(isto sto i bez specifikacije) CHECK OPTION
-- takodje probati sta se desava kada se te opcije dodaju 
-- samo u pogled ispitni_rok_2018
CREATE VIEW ispitni_rok_test AS
SELECT skgodina, oznakaroka, naziv, datpocetka, datkraja
FROM ispitni_rok_2018
WHERE oznakaroka LIKE 'test%'
WITH LOCAL CHECK OPTION;

SELECT *
FROM ispitni_rok_test;

INSERT INTO ispitni_rok_test(skgodina, oznakaroka, naziv, datpocetka, datkraja)
VALUES (2019, 'test2', 'test 2019', '04/06/2019', '10/06/2019');


INSERT INTO ispitni_rok_test(skgodina, oznakaroka, naziv, datpocetka, datkraja)
VALUES (2017, 'test', 'test 2017', '04/05/2017', '10/05/2017');

INSERT INTO ispitni_rok_test(skgodina, oznakaroka, naziv, datpocetka, datkraja)
VALUES (2017, '_test', '_test 2017', '04/05/2017', '10/05/2017');

DELETE FROM ispitnirok
WHERE oznakaroka LIKE '%test%';
DROP VIEW ispitni_rok_2018;
DROP VIEW ispitni_rok_test;


CREATE VIEW ispitni_rok_2018 AS
SELECT ir.skgodina, ir.oznakaroka, ir.naziv, ir.datpocetka, ir.datkraja
FROM ispitnirok ir JOIN skolskagodina sg
	ON ir.skgodina = sg.skgodina
WHERE YEAR(sg.datpocetka) >= 2018;

SELECT * FROM ispitni_rok_2018;

INSERT INTO ispitni_rok_2018(skgodina, oznakaroka, naziv, datpocetka, datkraja)
VALUES (2019, 'test1', 'test1 2019', '04/05/2019', '10/05/2019');

CREATE VIEW ir_proseci
AS
SELECT skgodina, oznakaroka, AVG(ocena + 0.0) prosek
FROM ispit
WHERE ocena > 5 AND status = 'o'
GROUP BY skgodina, oznakaroka;

SELECT * FROM ir_proseci;

DROP VIEW ir_proseci;


-- 1. Kreirati pogled kojim se izdvajaju sva polaganja studenata
--   koji se zovu Marko ili Maja
--   i koji su u junskom ispitnom roku 2018 godine polozili bar jedan ispit
--   napraviti pogled nad kojim je dozvoljeno azuriranje ili ubacivanje novih podataka

CREATE OR REPLACE VIEW ispiti_marko_maja AS
SELECT DISTINCT i.*
FROM ispit i JOIN dosije d
		ON i.indeks = d.indeks
	 JOIN ispit i1 ON i1.indeks = d.indeks
WHERE d.ime IN ('Marko', 'Maja')
	AND i1.oznakaroka = 'jun1'
	AND i1.skgodina = 2018
	AND i1.ocena > 5
	AND i1.status = 'o';

CREATE OR REPLACE VIEW ispiti_marko_maja AS
SELECT *
FROM ispit i
WHERE i.indeks IN (SELECT indeks FROM dosije
				   WHERE ime IN ('Marko', 'Maja'))
   	AND i.indeks IN (SELECT DISTINCT indeks
   					 FROM ispit
   					 WHERE skgodina = 2018
   					 	AND oznakaroka = 'jun1'
   					 	AND ocena > 5 AND status = 'o')
WITH CHECK OPTION;

SELECT * FROM ispiti_marko_maja;

DROP VIEW ispiti_marko_maja;

-- INDEX
-- 1. napraviti indeks nad tabelom dosije nad atributima ime i prezime
CREATE INDEX ON predmet (naziv DESC);

DROP INDEX dosij_ime_prezime_index;

-- primetimo da sledeca naredba 'puca' (nije ispostovan unique uslov koji smo naveli

CREATE UNIQUE INDEX dosij_ime_prezime_index ON dosije
(ime DESC, prezime DESC);

-- dok ovako nesto prolazi:
CREATE UNIQUE INDEX dosij_ime_prezime_index ON dosije
(ime DESC, prezime DESC, indeks);

-- 3. Za studente Osnovnih akademskih studija Matematike
--     koji su upisani 2018. godine pronaci
--    koji su obavezni predmeti ostali do zavrsetka studiranja
--    izdvoijit indeks, ime, prezime i naziv predmeta koji je ostao
--    student je upisao fakultet 2018 ako mu jeindeks oblika 2018****

SELECT d.indeks, d.ime, d.prezime, p.naziv
FROM dosije d JOIN studijskiprogram sp
		ON d.idprograma = sp.id
	JOIN nivokvalifikacije nk ON sp.idnivoa = nk.id
	JOIN predmetprograma pp ON pp.idprograma = d.idprograma
	JOIN predmet p ON pp.idpredmeta = p.id
WHERE char(d.indeks) LIKE '2018%'
	AND sp.naziv = 'Matematika'
	AND nk.naziv = 'Osnovne akademske studije'	
	AND NOT EXISTS(SELECT * FROM ispit i
					WHERE i.indeks = d.indeks
					AND i.ocena > 5 AND i.status = 'o'
					AND i.idpredmeta = p.id);


SELECT * FROM nivokvalifikacije