-- POGLEDI --
-- 1. napraviti pogled sa ispitnim rokovima gde je skgodina >= 2018

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

SELECT *
FROM ispitni_rok_2018;

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');

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

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

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

UPDATE ispitni_rok_2018
SET naziv = 'TEST 2018'
WHERE oznakaroka = 'test' AND skgodina = '2018';

UPDATE ispitni_rok_2018
SET skgodina = 2016
WHERE naziv = 'TEST 2018';

CREATE VIEW ispitni_rok_test AS
SELECT skgodina, oznakaroka, naziv, datpocetka, datkraja
FROM ispitni_rok_2018
WHERE oznakaroka LIKE 'test%'
WITH CHECK OPTION;

INSERT INTO ispitni_rok_test
VALUES (2018, '_test2', 'test2 2018', '04/06/2018', '10/06/2018');

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

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

SELECT * FROM ispitni_rok_test;

INSERT INTO ispitni_rok_test
VALUES (2017, 'test2', 'test2 2017', '04/06/2017', '10/06/2017');


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


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

CREATE VIEW  AS
SELECT ___
FROM dosije d JOIN ispit i
	ON d.indeks = i.indeks;

SELECT * FROM proseci_u_rok;

-- 2. kreirati pogled kojim se izdvajaju sva polaganja studenata
--    koji se zovu Marko ili Maja i koji su u junskom roku 2018.
--    polozili bar jedan predmet. 

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

CREATE OR REPLACE VIEW polganja_marko_maja_2018 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');
		   						
---------------
--- INDEKS ---- 'fensi pokazivaci'
----------------
-- B - stablo --
----------------
-- napraviti indeks na tabeli dosije po imenu i prezimenu opadajuce
CREATE INDEX dosije_ime_prezime ON dosije
(ime DESC, prezime DESC);

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

DROP INDEX dosije_ime_prezime;

---------------------------------------

-- 1.  ZA studenta Osnovnih akadmeskih studija matematika
--     koji su upisani 2018. godine, pronaci koji su 
---    obavezni
--     predmeti ostali do kraja studeija
--     izdovjiti indeks, ime, prezime kao i naziv 
--     obaveznog nepolozenog predmeta
--     student je upisan 2018 <=> indeks ~ 2018****;

SELECT d.indeks, d.ime, d.prezime, p.naziv
FROM dosije d JOIN studijskiprogram sp
	ON d.idprograma = sp.id
	JOIN predmetprograma pp ON d.idprograma = pp.idprograma
	JOIN predmet p ON pp.idpredmeta = p.id
	JOIN nivokvalifikacije nk ON sp.idnivoa = nk.id
WHERE pp.vrsta = 'obavezan'
   AND 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 studijskiprogram
SELECT char(indeks) FROM dosije WHERE char(indeks) LIKE '2018%';
SELECT * FROM nivokvalifikacije