-- 1. napraviti tabelu polozeni predmeti u kojoj ce se nalaziti
--    podaci o polozneim predmetima studenata
--    tabela koja ima iste kolone kao tabela ispit
-- dodamo strani kljuc na tabelu dosije sa opcijom kaskadnog brisanja
-- dodati ogranicenje da ocena mora biti izmedju 6 i 10
--    

CREATE TABLE polozeni_predmeti(
	skgodina SMALLINT NOT NULL,
	oznakaroka VARCHAR(20) NOT NULL,
	indeks INTEGER NOT NULL,
	idpredmeta INTEGER NOT NULL,
	status CHAR NOT NULL,
	datpolaganja DATE,
	poeni SMALLINT,
	ocena SMALLINT,
	PRIMARY KEY (skgodina, oznakaroka, indeks, idpredmeta),
	FOREIGN KEY fk_PolozeniPredmeti_Dosije(indeks)
		REFERENCES dosije ON DELETE CASCADE,
	CONSTRAINT chk_pp_vrednost_ocene check (ocena BETWEEN 6 AND 10)
);


-- 2. izbirsati tabelu polozeni_predmeti iz nase baze

DROP TABLE polozeni_predmeti;


-- ubacivanje podataka u tabele

-- 3. Dodati ispitni rok oktobar 3 2015.

INSERT INTO ispitnirok
VALUES (2015, 'okt3', 'Oktobar 3 2015', '2016-10-08', '2016-10-09');

SELECT * FROM ispitnirok WHERE skgodina = 2015;

-- 4. Dodati studenta ivana markovica sa brojem indeksa 20140058 rodjenog u Beogradu..

INSERT INTO dosije(ime, prezime, indeks, mestorodjenja, idprograma, idstatusa, datupisa)
VALUES ('Ivan', 'Markovic', 20140058, 'Beograd', 101, 1, '2014-06-10');

-- 5. dodati u tabelu ispit polaganje ispita analiza 3 u roku okt3 2015 sa ocenom 5
-- VISE O OVOME KAD(AKO) BUDEMO RADILI TRIGGERE
INSERT INTO upisankurs(indeks, idpredmeta, skgodina, semestar)
SELECT DISTINCT 20140058, p.id, 2015, uk.semestar
FROM predmet p JOIN upisankurs uk
	ON p.id = uk.idpredmeta
WHERE p.naziv = 'Analiza 3';

INSERT INTO ispit(skgodina, oznakaroka, indeks, idpredmeta, ocena, status)
SELECT DISTINCT 2015, 'okt3', 20140058, p.id, 5, 'o'
FROM predmet p 
WHERE p.naziv = 'Analiza 3';

-- 6. za sve ispite iz programiranja 1 sa ocenom 5 promeniti ocenu na 6

SELECT DISTINCT ocena FROM ispit
WHERE idpredmeta IN (SELECT id FROM predmet WHERE naziv = 'Programiranje 1');

UPDATE ispit
SET ocena = 6
WHERE ocena = 5 
	AND status = 'o'
	AND idpredmeta IN (SELECT id FROM predmet WHERE naziv = 'Diskretne strukture 1');  
	                  
	                  
-- 1. na 'bolji nacin'

CREATE TABLE polozeni_predmeti LIKE ispit;

ALTER TABLE polozeni_predmeti
	ADD PRIMARY KEY (skgodina, oznakaroka, indeks, idpredmeta)
	ADD FOREIGN KEY fk_PolozeniPredmeti_Dosije(indeks)
		REFERENCES dosije ON DELETE CASCADE
	ADD CONSTRAINT chk_pp_vrednost_ocene check (ocena BETWEEN 6 AND 10);
               
DROP TABLE polozeni_predmeti;

CREATE table polozeni_predmeti AS
(
 SELECT * FROM ispit
)
DEFINITION ONLY;

ALTER TABLE polozeni_predmeti
	ADD PRIMARY KEY (skgodina, oznakaroka, indeks, idpredmeta)
	ADD FOREIGN KEY fk_PolozeniPredmeti_Dosije(indeks)
		REFERENCES dosije ON DELETE CASCADE
	ADD CONSTRAINT chk_pp_vrednost_ocene check (ocena BETWEEN 6 AND 10);
 
INSERT INTO polozeni_predmeti
SELECT * FROM ispit
WHERE ocena > 5 AND status = 'o';

SELECT * FROM polozeni_predmeti;

ALTER TABLE polozeni_predmeti
ADD CONSTRAINT chk_pp_broj_bodova CHECK (poeni > 51);


ALTER TABLE polozeni_predmeti
DROP datpolaganja;

SELECT * FROM polozeni_predmeti
ORDER BY 1, 2, 3, 4;


-- napraviti tabelu student_ispiti koja ima sledece kolone:

-- indeks - indeks studenta
-- ime    - ime studenta
-- prezime - prezime studenta
-- polozeni_ispiti - proj polozenih ispita
-- prosek - prosek studenta
-- strani kljuc na tabelu dosije
-- primarni kljuc je indeks


CREATE TABLE student_ispiti(
	indeks INTEGER NOT NULL PRIMARY KEY,
	ime VARCHAR(50) NOT NULL,
	prezime VARCHAR(50) NOT NULL,
	polozeni_ispiti SMALLINT,
	prosek DOUBLE,
	FOREIGN KEY fk_si_dosije (indeks)
		REFERENCES dosije
);

-- dodacemo kolonu broj_ispita predstavlja broj izlazaka na ispit
-- dodamo ogranicenje da je broj_ispita >= polozeni_ispiti

ALTER TABLE student_ispiti
	ADD broj_ispita SMALLINT
	ADD constraint chk_br_ispita_polozenih CHECK (broj_ispita >= polozeni_ispiti);

-- ubacimo info u tabelu:
-- dodati podatke za SVAKOG studenta iz tabele dosije
-- ukoliko nema polozenih ispita ili nema izlazaka na ispite 
-- hocemo da te kolone budu NULL

INSERT INTO student_ispiti(indeks, ime, prezime, polozeni_ispiti, broj_ispita, prosek)
SELECT d.indeks, d.ime, d.prezime, NULLIF(SUM(
											CASE
												WHEN ocena > 5 AND status = 'o' THEN 1
												ELSE 0
											END
									), 0)
								, NULLIF(COUNT(i.indeks), 0)
								, AVG(
									CASE
										WHEN ocena > 5 AND status = 'o' THEN ocena * 1.0
										ELSE NULL
									END
								 )
FROM dosije d LEFT JOIN ispit i
	ON d.indeks = i.indeks
GROUP BY d.indeks, d.ime, d.prezime;