SELECT K.BROJPRIJAVE, K.PREZIME, K.IME, K.UKUPANUSPEH, S.NAZIVSKOLE, M. NAZIVMESTA
FROM KANDIDAT K LEFT OUTER JOIN SKOLA S
ON K.ZAVRSENASKOLA=S.SIFRASKOLE
LEFT OUTER JOIN MESTO M ON
M.SIFRAMESTA=S.SIFRAMESTA
WHERE K.DATUMPRIJAVE= DATE'2003-06-24' AND S.TIPSKOLE='GIMNAZIJA'
ORDER BY K.BROJPRIJAVE ASC;

CREATE VIEW OPSTI_USPEH(USPEH, BROJ_KANDIDATA, PROSECAN_USPEH) AS
SELECT CASE 
WHEN S.PROSEK>=4.5 THEN 'ODLICAN'
WHEN S.PROSEK>=3.5 AND S.PROSEK<4.5 THEN 'VRLO DOBAR'
WHEN S.PROSEK>=2.5 AND S.PROSEK<3.5 THEN 'DOBAR'
ELSE 'DOVOLJAN'
END AS USPEH, COUNT(*),AVG(S.PROSEK)
FROM SVEDOCANSTVO S
WHERE S.GODINA=4
GROUP BY USPEH;

CREATE VIEW USPEH_PO_MESTIMA(NAZIV_MESTA, USPEH, BROJ_KANDIDATA) AS
SELECT M.NAZIVMESTA, 
CASE 
	WHEN S.PROSEK>=4.5 THEN 'ODLICAN'
	WHEN S.PROSEK>=3.5 AND S.PROSEK<4.5 THEN 'VRLO DOBAR'
	WHEN S.PROSEK>=2.5 AND S.PROSEK<3.5 THEN 'DOBAR'
	WHEN S.PROSEK<2.5 THEN 'DOVOLJAN'
	ELSE NULL
END AS USPEH,
COUNT(*)
FROM SVEDOCANSTVO S INNER JOIN SKOLA SK
ON S.SIFRASKOLE=SK.SIFRASKOLE
RIGHT OUTER JOIN MESTO M
ON M.SIFRAMESTA = SK.SIFRAMESTA
GROUP BY M.NAZIVMESTA, USPEH
ORDER BY M.NAZIVMESTA DESC; 
CREATE VIEW USPEH_PO_MESTIMA(NAZIV_MESTA, USPEH, BROJ_KANDIDATA) AS
SELECT M.NAZIVMESTA, 
CASE 
	WHEN S.PROSEK>=4.5 THEN 'ODLICAN'
	WHEN S.PROSEK>=3.5 AND S.PROSEK<4.5 THEN 'VRLO DOBAR'
	WHEN S.PROSEK>=2.5 AND S.PROSEK<3.5 THEN 'DOBAR'
	ELSE 'DOVOLJAN'
END AS USPEH,
COUNT(*)
FROM SVEDOCANSTVO S RIGHT OUTER JOIN SKOLA SK
ON S.SIFRASKOLE=SK.SIFRASKOLE
RIGHT OUTER JOIN MESTO M
ON M.SIFRAMESTA = SK.SIFRAMESTA
GROUP BY M.NAZIVMESTA, USPEH
ORDER BY M.NAZIVMESTA DESC; 