SELECT I.IGRACID, I.IME_PREZIME, I.DAT_RODJENJA, T.NAZIV
FROM IGRAC I INNER JOIN FUDBALSKI_TIM T
WHERE T.NAZIV='Crvena zvezda' AND I.IGRACID NOT IN (SELECT DISTINCT I1.IGRACID FROM IGRAC I1 INNER JOIN UTAKMICA_IGRAC UI ON I1.IGRACID=UI.IGRACID WHERE UI.RBKOLA=1);

SELECT SU.RBKOLA,I.IME_PREZIME, T.NAZIV, COUNT(*) AS BROJ
FROM STAT_UTAKMICE SU INNER JOIN IGRAC I 
ON I.IGRACID=SU.IGRACID
LEFT OUTER JOIN FUDBALSKI_TIM T
ON T.TIMID=I.TIMID
WHERE SU.DOGADJAJ='GOL'
GROUP BY SU.RBKOLA, SU.RBPARA, I.IGRACID
HAVING 	BROJ >= ALL(SELECT COUNT(*)
FROM STAT_UTAKMICE SU1 INNER JOIN IGRAC I1
ON I1.IGRACID=SU1.IGRACID
WHERE SU1.DOGADJAJ='GOL' AND SU1.RBKOLA=SU.RBKOLA
GROUP BY SU1.RBKOLA, SU1.RBPARA, I1.IGRACID);

CREATE VIEW TABELA_PRVENSTVA(NAZIV_TIMA, BROJ_ODIGRANIH_UTAKMICA, BROJ_POSTIGNUTIH_GOLOVA,BROJ_PRIMLJENIH_GOLOVA, GOL_RAZLIKA, UKUPAN_BROJ_BODOVA)
AS SELECT T.NAZIV, COUNT(*), SUM(CASE WHEN U.TIM_DOMACI =T.TIMID THEN TOTAL_DOMACI WHEN U.TIM_GOSTI=T.TIMID THEN TOTAL_GOSTI ELSE 0 END),
 SUM(CASE WHEN U.TIM_DOMACI =T.TIMID THEN TOTAL_GOSTI WHEN U.TIM_GOSTI=T.TIMID THEN TOTAL_DOMACI ELSE 0 END),
SUM(CASE WHEN U.TIM_DOMACI =T.TIMID THEN TOTAL_DOMACI-TOTAL_GOSTI WHEN U.TIM_GOSTI=T.TIMID THEN TOTAL_GOSTI-TOTAL_DOMACI ELSE 0 END),
SUM(
CASE 
	WHEN U.TIM_DOMACI =T.TIMID THEN
		CASE 
			WHEN TOTAL_DOMACI-TOTAL_GOSTI =0 THEN 1
			WHEN TOTAL_DOMACI-TOTAL_GOSTI >0 THEN 3
			ELSE 0
		END
 	WHEN U.TIM_GOSTI=T.TIMID THEN CASE 
			WHEN TOTAL_DOMACI-TOTAL_GOSTI =0 THEN 1
			WHEN TOTAL_DOMACI-TOTAL_GOSTI <0 THEN 3
			ELSE 0
		END
END)

FROM FUDBALSKI_TIM T LEFT OUTER JOIN UTAKMICA U
ON T.TIMID=U.TIM_DOMACI OR T.TIMID=U.TIM_GOSTI
GROUP BY T.TIMID;