-- 1. napraviti tabelu polozni predmeti
-- u kojoj cemo cuvati informacije o polozenim predmetima studenata
-- ima iste kolone kao i tabela ispit
-- dodati ogranicenje da ocena mora biti izmedju 6 i 10
-- da ima strane kljuceve na teblu dosije i ispitni..

CREATE TABLE da.polozenipredmeti (
	skgodina SMALLINT NOT NULL,
	oznakaroka CHAR(20) NOT NULL,
	indeks INTEGER NOT NULL,
	idpredmeta INTEGER NOT NULL,
	status CHAR(1) NOT NULL,
	datpolaganja DATE,
	poeni SMALLINT,
	ocena SMALLINT,
	PRIMARY KEY (skgodina, oznakaroka, indeks, idpredmeta),
	FOREIGN KEY fk_PolozeniPredmeti_Dosije(indeks) 
	REFERENCES da.dosije ON DELETE CASCADE,
	FOREIGN KEY fk_PolozneiPredmeti_IspitniRok(skgodina, oznakaroka)
	REFERENCES da.ispitnirok,
	CONSTRAINT chk_ocena CHECK (ocena BETWEEN 6 AND 10)
);

SELECT * FROM da.polozenipredmeti;

DROP TABLE polozenipredmeti;

CREATE TABLE polozenipredmeti LIKE ispit;

CREATE TABLE polozenipredmeti AS
(
SELECT * FROM ispit
) DEFINITION ONLY;
-- ALTER TABLE
ALTER TABLE polozenipredmeti
	ADD PRIMARY KEY (skgodina, oznakaroka, indeks, idpredmeta)
	ADD FOREIGN KEY fk_PolozeniPredmeti_Dosije(indeks)
	    REFERENCES da.dosije ON DELETE CASCADE
    ADD FOREIGN KEY fk_PolozneiPredmeti_IspitniRok(skgodina, oznakaroka)
	    REFERENCES da.ispitnirok
    ADD CONSTRAINT chk_ocena CHECK (ocena BETWEEN 6 AND 10);

INSERT INTO polozenipredmeti
(
SELECT * FROM ispit
WHERE ocena > 5 AND status = 'o'
);

-- 2. obrisati kolonu datpolaganja iz tabele polozenipredmeti
ALTER TABLE polozenipredmeti
DROP datpolaganja;

SELECT * FROM polozenipredmeti
LIMIT 10;

-- 3. postovaimo uslov ogranicenja da svi studenti 
-- moraju imati izmedju 51 i 100 poena
-- id a imaju podrazumevanu ocenu 6

SELECT * FROM polozenipredmeti
WHERE poeni NOT BETWEEN 51 AND 100;

ALTER TABLE polozenipredmeti
	ADD CONSTRAINT chk_bodovi_pp CHECK (poeni BETWEEN 51 AND 100);
	
ALTER TABLE polozenipredmeti
	ALTER COLUMN ocena SET DEFAULT 6;
	
-- 4. ukloni tabelu polozenipredmeti
DROP TABLE polozenipredmeti;

-- 5. napraviti tabelu student_ispit koja ima sledece atribute:
--    indeks
--    polozeniispiti
--    prosek
--    primarni kljuc bude indeks
--    strani kluc na tabelu dosije

CREATE TABLE student_ispit(
	indeks INTEGER NOT NULL PRIMARY KEY,
	poloeniispiti SMALLINT,
	prosek DOUBLE,
    FOREIGN KEY (indeks) REFERENCES dosije
);

-- 6. tabeli student_ispit dodati kolonu
--    broj ispita koja predstavlja broj
--    polaganih ispita
--     dodati ogranicenje da je br_polozenih <= br_polaganih

ALTER TABLE student_ispit
	ADD broj_polaganih SMALLINT
	ADD CONSTRAINT ck_polagani_polozeni CHECK (poloeniispiti <=  broj_polaganih);

-- 7. u tabelu student_ispit uneti podatke za svaku studenta iz tabele dosije
-- ukoliko student nije polagao ili polozi ni jedan ispit ti atributi su NULL
-- ostali atributi treba da imaju odg vrednosti

INSERT INTO student_ispit(indeks, poloeniispiti, prosek, broj_polaganih)
SELECT d.indeks,
	NULLIF(SUM(
			CASE
				WHEN ocena > 5 AND status = 'o' THEN 1
				ELSE 0
			END
		), 0) polozeni,
	AVG(
		CASE
			WHEN ocena > 5 AND status = 'o' THEN ocena + 0.0
			ELSE NULL
		END
	) prosek,
	NULLIF(COUNT(i.indeks), 0) brojIspita
FROM dosije d LEFT JOIN ispit i
	ON d.indeks = i.indeks
GROUP BY d.indeks;

-- 8. obrisati tabelu student_ispit

DROP TABLE student_ispit;

--------------------------------------------
----  VEzbanje SQL -----
------------------------

-- 1. za svakog studenta izdvojojit
--    ime studenta, naziv predmeta iz kog je polozio
--    poslednji ispit
--    datum tog polaganja

SELECT d.ime, d.prezime, p.naziv, i.datpolaganja
FROM dosije d JOIN ispit i
	ON d.indeks = i.indeks
	JOIN predmet p
		ON i.idpredmeta = p.id
WHERE i.ocena > 5 AND i.status = 'o'
	AND i.datpolaganja >= ALL(SELECT i2.datpolaganja
							  FROM ispit i2
							  WHERE i2.ocena > 5 AND i2.status = 'o'
							  	AND i2.indeks = d.indeks)
UNION
SELECT d.ime, d.prezime, NULL, NULL
FROM dosije d
WHERE d.indeks NOT IN (SELECT DISTINCT indeks
					   FROM ispit
					   WHERE ocena > 5 AND status = 'o');
					   
-- 2. izdvojiti indekse i broj promena statusa studiranja
--    za sve studente koji su menjali status bar 3 puta
SELECT ug1.indeks, COUNT(*)
FROM upisgodine ug1 JOIN upisgodine ug2
	ON ug1.indeks = ug2.indeks
	AND ug1.idstatusa <> ug2.idstatusa
WHERE ug1.skgodina = ug2.skgodina - 1
GROUP BY ug1.indeks
HAVING COUNT(*) >= 3;

-- sta moze da se desi ako student moze da pauzira godinu
-- tj da postoje godine koje nije upisato
SELECT ug1.indeks, COUNT(*)
FROM upisgodine ug1 JOIN upisgodine ug2
	ON ug1.indeks = ug2.indeks
	AND ug1.idstatusa <> ug2.idstatusa
	AND ug1.skgodina < ug2.skgodina
WHERE NOT EXISTS(
		SELECT *
		FROM upisgodine ug3
		WHERE ug3.indeks = ug1.indeks
	 		AND ug3.skgodina > ug1.skgodina
	 		AND ug3.skgodina < ug2.skgodina 
	)
GROUP BY ug1.indeks
HAVING COUNT(*) >= 3;

-- 3. za smer sa nazivom Matematika sa osnovnih studija
-- izdvojiti naziv svih obaveznih predmeta kao i broj uslovnih premdeta
-- za svaki od obaveznih predmeta
-- ukoliko predmet nema uslovne izdvojiti 0
-- uredimo po nazivu predmeta

SELECT p.naziv, COUNT(up.iduslovnogpredmeta)
FROM studijskiprogram sp JOIN nivokvalifikacije nk
		ON sp.idnivoa = nk.id
	JOIN predmetprograma pp
		ON sp.id = pp.idprograma
	JOIN predmet p ON pp.idpredmeta = p.id
	LEFT JOIN uslovnipredmet up
		ON p.id = up.idpredmeta
		AND sp.id = up.idprograma
WHERE pp.vrsta = 'obavezan'
	AND sp.naziv = 'Matematika'
	AND nk.naziv = 'Osnovne akademske studije'
GROUP BY p.naziv, p.id;
	

SELECT * FROM nivokvalifikacije;