SELECT R.SIFRAD, R.IMER, R.DATRODJ, R.DATZAP, R.PLATA, R.SIFPRED, R.SRUKOV
FROM RADNIK R INNER JOIN ANGAZOVANJE A
ON R.SIFRAD=A.SIFRAD
WHERE EXTRACT(YEAR FROM (R.DATZAP + INTERVAL '10' YEAR)) = EXTRACT(YEAR FROM CURRENT_DATE) 
AND (R.DATZAP + INTERVAL 10 YEAR) <= CURRENT_DATE
GROUP BY R.SIFRAD
HAVING COUNT(*) >=4;

SELECT P.GRAD, CASE 
	WHEN R.DATRODJ+ INTERVAL '20' YEAR > CURRENT_DATE THEN 'MLADJI OD 20 GODINA'
	WHEN R.DATRODJ+ INTERVAL '20' YEAR <= CURRENT_DATE AND R.DATRODJ+ INTERVAL '50' YEAR > CURRENT_DATE THEN 'IZMEDJU 20 I 50 GODINA'
	WHEN R.DATRODJ+ INTERVAL '50' YEAR <= CURRENT_DATE THEN 'STARIJI OD 50 GODINA'
	ELSE NULL
END AS STAROST, COUNT(ALL R.SIFRAD)
FROM RADNIK R INNER JOIN PREDUZECE P
ON R.SIFPRED=P.SIFPRED
GROUP BY GRAD, STAROST;

CREATE VIEW RUKOVODILAC(NAZIV_PREDUZECA, IME_PREZIME_RUKOVODIOCA, DATUM_ZAPOSLENJA, PLATA) AS
SELECT P.NAZIV, R.IMER, R.DATZAP, R.PLATA
FROM RADNIK R LEFT OUTER JOIN  PREDUZECE P
ON R.SIFPRED=P.SIFPRED
WHERE R.IMER LIKE 'M%c' AND R.PLATA >45000 AND R.SIFRAD IN 
(SELECT DISTINCT  PROJ.RUKOVODILAC_P 
FROM PROJEKAT PROJ INNER JOIN ANGAZOVANJE A
ON PROJ.SIFP=A.SIFP
GROUP BY PROJ.SIFP
HAVING COUNT(*) >=30);
