################################################
############### SQL UPITI ######################
################################################

1. SLEKTUJ IME, RPEZIME, GODINU, MESEC I DAN RODJENJA
KOD STUDENATA STARIJIH OD 2001 GENERACIJE

------------------------------------------------

SELECT IME, PREZIME,
       EXTRACT(YEAR FROM DATUMRODJENJA) AS GODINA,
       EXTRACT(MONTH FROM DATUMRODJENJA) AS MESEC ,
       EXTRACT(DAY FROM DATUMRODJENJA) AS DAN
FROM STUDENT
WHERE EXTRACT(YEAR FROM DATUMRODJENJA) < 2001

################################################

2. SELEKTUJ IME, PREZIME, INICIJALE I POLOZAJ PRVOG POJAVLJIVANJA SLOVA A
KOD STUDENATA CIJE JE PREZIME DUZE OD 9 SLOVA I ZAVRSAVA SE NA IC

------------------------------------------------

SELECT IME, PREZIME,
       CONCAT(SUBSTR(IME,1,1),'.',SUBSTR(PREZIME,1,1),'.'),
       INSTR(LOWER(PREZIME),'a')
FROM STUDENT
WHERE LENGTH(PREZIME) > 9 AND LOWER(PREZIME) LIKE '%i%c'

################################################

3.Prikazati indeks ime i prezime svih sutdenata koji
imaju mentora koji se zove Ivan.

------------------------------------------------

SELECT BROJINDEKSA, IME, PREZIME, MENTOR
FROM STUDENT
WHERE MENTOR IN (SELECT SIFRANASTAVNIKA
        FROM NASTAVNIK
        WHERE IME LIKE 'Ivan')

################################################

4. Prikazati sifru predmdeta, naziv i broj ESPB poena
za sve predmete ciji je broj ESPB manji do 5 I nemaju
nijedan predmet kao uslov

------------------------------------------------

SELECT SIFRAPREDMETA,NAZIV, ESPB
FROM PREDMET
WHERE ESPB < 5 AND USLOV IS NULL

################################################

5.

------------------------------------------------

SELECT BROJINDEKSA, IME, PREZIME, SIFRAMODULA
FROM STUDENT
WHERE SKOLARINA > 150000 AND SIFRAMODULA NOT IN (9,11,12,14)
AND IME NOT LIKE 'Jovan'

################################################

6.

------------------------------------------------

SELECT *
FROM POLAGANJE
WHERE GODINA = 2022 AND REDNIBROJ = 9
AND (OCENA BETWEEN 8 AND 10)
ORDER BY OCENA ASC

################################################

7.

------------------------------------------------

SELECT IME, PREZIME
FROM STUDENT
WHERE SIFRAMODULA = (SELECT SIFRAPROFILA
                    FROM MODUL
                    WHERE KVOTA = 180)

################################################

8.

------------------------------------------------

SELECT MESEC, DATUMPOCETKA, DATUMZAVRSETKA, (DATUMZAVRSETKA - DATUMPOCETKA) AS BROJ_DANA,
        NEXT_DAY(DATUMPOCETKA,'wednesday') AS PRVA_SREDAM,
        ROUND(MONTHS_BETWEEN(SYSDATE, DATUMZAVRSETKA),2) AS BROJ_MESECI
FROM ISPITNIROK
WHERE GODINA = 2023

################################################

9.

------------------------------------------------

SELECT *
FROM POLAGANJE
WHERE OCENA = 5 AND DATUMPOLAGANJA + INTERVAL '1-9' YEAR TO MONTH > SYSDATE

################################################

10.

------------------------------------------------

SELECT GODINA, MESEC,
       CASE
            WHEN EXTRACT(MONTH FROM DATUMPOCETKA) = 1  THEN 'Januarski'
            WHEN EXTRACT(MONTH FROM DATUMPOCETKA) = 2  THEN 'Februarski'
            WHEN EXTRACT(MONTH FROM DATUMPOCETKA) = 3  THEN 'Martovski'
            WHEN EXTRACT(MONTH FROM DATUMPOCETKA) = 4  THEN 'Aprilski'
            WHEN EXTRACT(MONTH FROM DATUMPOCETKA) = 5  THEN 'Majski'
            WHEN EXTRACT(MONTH FROM DATUMPOCETKA) = 6  THEN 'Junski'
            WHEN EXTRACT(MONTH FROM DATUMPOCETKA) = 7  THEN 'Julski'
            WHEN EXTRACT(MONTH FROM DATUMPOCETKA) = 8  THEN 'Avgustovski'
            WHEN EXTRACT(MONTH FROM DATUMPOCETKA) = 9  THEN 'Septembarski'
            WHEN EXTRACT(MONTH FROM DATUMPOCETKA) = 10 THEN 'Oktobarski'
            WHEN EXTRACT(MONTH FROM DATUMPOCETKA) = 11 THEN 'Novembarski'
            WHEN EXTRACT(MONTH FROM DATUMPOCETKA) = 12 THEN 'Decembarski'
            ELSE 'Neodređen'
        END AS NAZIV
FROM ISPITNIROK
WHERE TIP LIKE 'apsol%' AND GODINA IN (2023, 2024)

################################################

11.

------------------------------------------------

SELECT IME, PREZIME, LENGTH(IME) AS D_IME, LENGTH(PREZIME) AS D_PREZIME,
        NULLIF(LENGTH(IME),LENGTH(PREZIME)) AS REZULTAT
FROM NASTAVNIK
WHERE TITULA IS NULL AND LENGTH(IME) < 6

################################################

12.

------------------------------------------------

SELECT OCENA, COALESCE(DATUMUPISA, DATUMPOLAGANJA) AS REZULTAT
FROM POLAGANJE
WHERE SIFRAPREDMETA = 24

################################################

13.

------------------------------------------------

SELECT BROJINDEKSA, IME, PREZIME, TO_CHAR(DATUMRODJENJA,'DD.MM.YYYY.') AS DATUM_RODEJNJA
FROM STUDENT
WHERE INSTR(TO_CHAR(BROJINDEKSA),'12')

################################################

14.

------------------------------------------------

SELECT DATUMOD, DATUMDO, PLATA,
        ROUND(MONTHS_BETWEEN(COALESCE(DATUMDO, ADD_MONTHS(SYSDATE,-3) ), DATUMOD)) AS BROJ_MESECI,
        CASE TRUNC(MONTHS_BETWEEN(COALESCE(DATUMDO, ADD_MONTHS(SYSDATE,-3) ), DATUMOD)/12)
            WHEN 0 THEN 'nema bonusa'
            WHEN 1 THEN TO_CHAR(ROUND(PLATA*0.1))
            WHEN 2 THEN TO_CHAR(ROUND(PLATA*0.2))
            WHEN 3 THEN TO_CHAR(ROUND(PLATA*0.3))
            ELSE TO_CHAR(ROUND(PLATA*0.4))
        END AS BONUS,
        CASE TRUNC(MONTHS_BETWEEN(COALESCE(DATUMDO, ADD_MONTHS(SYSDATE,-3) ), DATUMOD)/12)
            WHEN 0 THEN '0'
            WHEN 1 THEN '10%'
            WHEN 2 THEN '20%'
            WHEN 3 THEN '30%'
            ELSE '40%'
        END AS PROCENAT_BONUSA
FROM ANGAZOVANJE
WHERE PLATA >= 15000 AND PLATA <= 90000 AND DATUMOD + INTERVAL '5-8' YEAR TO MONTH > ADD_MONTHS(SYSDATE,-3)
ORDER BY PROCENAT_BONUSA DESC, BONUS ASC

################################################

15. Prikazati sifru i naziv svih modula koji se nalaze na programu ISIT,
a ciji naziv pocinje na slovo I.

------------------------------------------------

I NACIN:

SELECT PR.SIFRAPROFILA, PR.NAZIVPROFILA
FROM MODUL M
INNER JOIN PROFIL P ON P.SIFRAPROFILA = M.SIFRAPROGRAMA
INNER JOIN PROFIL PR ON PR.SIFRAPROFILA = M.SIFRAPROFILA
WHERE P.NAZIVPROFILA LIKE 'ISIT' AND PR.NAZIVPROFILA LIKE 'I%'

------------------------------------------------

II NACIN

SELECT SIFRAPROFILA, NAZIVPROFILA
FROM PROFIL
WHERE LOWER(NAZIVPROFILA) LIKE 'i%'
        AND SIFRAPROFILA IN (SELECT SIFRAPROFILA
                            FROM MODUL
                            WHERE SIFRAPROGRAMA = (SELECT SIFRAPROFILA
                                                  FROM PROFIL
                                                  WHERE NAZIVPROFILA = 'ISIT'))

################################################

16.

------------------------------------------------

I NACIN

SELECT S.BROJINDEKSA, S.IME, S.PREZIME, P.DATUMPOLAGANJA
FROM STUDENT S
INNER JOIN POLAGANJE P  ON S.BROJINDEKSA = P.BROJINDEKSA
WHERE MOD(S.SKOLARINA,6) = 0

------------------------------------------------

I NACIN

SELECT BROJINDEKSA, S.IME, S.PREZIME, P.DATUMPOLAGANJA
FROM STUDENT S
INNER JOIN POLAGANJE P USING (BROJINDEKSA)
WHERE MOD(S.SKOLARINA,6) = 0


################################################

17.

------------------------------------------------

SELECT N.IME, N.PREZIME, P.NAZIV, A.PLATA, N.TITULA
FROM NASTAVNIK N INNER JOIN ANGAZOVANJE A ON  A.SIFRANASTAVNIKA = N.SIFRANASTAVNIKA
INNER JOIN PREDMET P ON A.SIFRAPREDMETA = P.SIFRAPREDMETA
WHERE N.TITULA LIKE 'doktor' AND P.NAZIV LIKE 'O%'

################################################

18.

------------------------------------------------

SELECT PO.*,I.MESEC
FROM POLAGANJE PO
INNER JOIN PREDMET PR ON PO.SIFRAPREDMETA = PR.SIFRAPREDMETA
INNER JOIN ISPITNIROK I ON PO.REDNIBROJ = I.REDNIBROJ AND PO.GODINA = I.GODINA
WHERE PR.NAZIV LIKE 'Osnove inf%' AND I.TIP LIKE 'apsolventski'

################################################

18.

------------------------------------------------

I NACIN

SELECT S.IME, EXTRACT(YEAR FROM S.DATUMRODJENJA) AS GODINA_RODJENJA,
        CONCAT(N.IME,' ', N.PREZIME) AS MENTOR
FROM STUDENT S
LEFT JOIN NASTAVNIK N ON S.MENTOR = N.SIFRANASTAVNIKA
WHERE TRUNC(MONTHS_BETWEEN(SYSDATE,S.DATUMRODJENJA)/12)>=22

------------------------------------------------

II NACIN

SELECT S.IME, EXTRACT(YEAR FROM S.DATUMRODJENJA) AS GODINA_RODJENJA,
        CONCAT(N.IME,' ', N.PREZIME) AS MENTOR
FROM STUDENT S
LEFT JOIN NASTAVNIK N ON S.MENTOR = N.SIFRANASTAVNIKA
WHERE S.DATUMRODJENJA + INTERVAL '22' YEAR < SYSDATE

################################################

19.

------------------------------------------------

SELECT S.IME, EXTRACT(YEAR FROM S.DATUMRODJENJA) AS GODINA_RODJENJA,
        CONCAT(N.IME,' ', N.PREZIME) AS MENTOR
FROM STUDENT S
LEFT JOIN NASTAVNIK N ON S.MENTOR = N.SIFRANASTAVNIKA
WHERE LENGTH(N.PREZIME) = 12 OR N.PREZIME IS NULL

################################################

20.

------------------------------------------------

SELECT S.IME, EXTRACT(YEAR FROM S.DATUMRODJENJA) AS GODINA_RODJENJA,
        CONCAT(N.IME,' ', N.PREZIME) AS MENTOR
FROM STUDENT S
RIGHT JOIN NASTAVNIK N ON S.MENTOR = N.SIFRANASTAVNIKA
WHERE LENGTH(N.IME) > 7

################################################

21.

------------------------------------------------

SELECT S.IME, EXTRACT(YEAR FROM S.DATUMRODJENJA) AS GODINA_RODJENJA,
        CONCAT(N.IME,' ', N.PREZIME) AS MENTOR
FROM STUDENT S
FULL JOIN NASTAVNIK N ON S.MENTOR = N.SIFRANASTAVNIKA
WHERE LENGTH(S.IME) * 3 < LENGTH(S.PREZIME) OR S.IME IS NULL

################################################

22.

------------------------------------------------

I NACIN:

SELECT PR.ESPB ||'-'|| SUBSTR(PR.NAZIV,1,3) AS INICIJALI, P.NAZIV
FROM PREDMET PR
LEFT JOIN PREDMET P ON PR.USLOV = P.SIFRAPREDMETA
INNER JOIN POLAGANJE PO ON PR.SIFRAPREDMETA = PO.SIFRAPREDMETA
WHERE PR.ESPB < 6
HAVING LENGTH(COUNT(PO.SIFRAPREDMETA)) >= 1
GROUP BY PR.ESPB, SUBSTR(PR.NAZIV,1,3), P.NAZIV

------------------------------------------------

II NACIN:

SELECT PR.ESPB ||'-'|| SUBSTR(PR.NAZIV,1,3) AS INICIJALI, P.NAZIV
FROM PREDMET PR
LEFT JOIN PREDMET P ON PR.USLOV = P.SIFRAPREDMETA
WHERE PR.ESPB < 6 AND PR.SIFRAPREDMETA IN ( SELECT SIFRAPREDMETA
                                            FROM POLAGANJE PO)

################################################

23.

------------------------------------------------


SELECT N.IME ||' '|| N.PREZIME AS NASTAVNIK, P.NAZIV AS PREDMET
FROM NASTAVNIK N
INNER JOIN ANGAZOVANJE A ON N.SIFRANASTAVNIKA = A.SIFRANASTAVNIKA
INNER JOIN PREDMET P ON P.SIFRAPREDMETA = A.SIFRAPREDMETA
WHERE P.ESPB > 3 AND TITULA LIKE 'doktor'

################################################

24.

------------------------------------------------

SELECT N.IME, N.PREZIME, P.NAZIV
FROM NASTAVNIK N CROSS JOIN PREDMET P
WHERE N.TITULA = 'doktor' AND P.ESPB > 3;

################################################

25.

------------------------------------------------

SELECT N.SIFRANASTAVNIKA, N.PREZIME, A.SIFRAPREDMETA, A.PLATA
FROM NASTAVNIK N INNER JOIN ANGAZOVANJE A ON N.SIFRANASTAVNIKA = A.SIFRANASTAVNIKA
WHERE A.PLATA > ANY(
        SELECT A.PLATA
        FROM ANGAZOVANJE A
        INNER JOIN PREDMET P ON A.SIFRAPREDMETA = P.SIFRAPREDMETA
        WHERE P.NAZIV LIKE 'Osnove kvaliteta'
)

################################################

26.

------------------------------------------------

I NACIN:

SELECT S.BROJINDEKSA, S.PREZIME, S.SKOLARINA
FROM STUDENT S
WHERE S.SKOLARINA > ALL (
                    SELECT ST.SKOLARINA
                    FROM STUDENT ST
                    WHERE ST.SKOLARINA IS NOT NULL AND
                    ST.MENTOR = (SELECT M.SIFRANASTAVNIKA
                                        FROM NASTAVNIK M
                                        WHERE LOWER(M.IME) LIKE 'anita'));

------------------------------------------------

II NACIN:

SELECT S.BROJINDEKSA, S.PREZIME
FROM STUDENT S
WHERE S.SKOLARINA > ALL (
                    SELECT ST.SKOLARINA
                    FROM STUDENT ST
                    INNER JOIN NASTAVNIK M ON ST.MENTOR = M.SIFRANASTAVNIKA
                    WHERE M.IME LIKE 'Anita' AND ST.SKOLARINA IS NOT NULL);


################################################

27.

------------------------------------------------

SELECT S.*, P.NAZIVPROFILA
FROM STUDENT S
INNER JOIN PROFIL P ON P.SIFRAPROFILA = S.SIFRAMODULA
INNER JOIN POLAGANJE PO ON S.BROJINDEKSA = PO.BROJINDEKSA
WHERE (UPPER(P.NAZIVPROFILA) LIKE '%MEN%' OR P.SIFRAPROFILA NOT IN (SELECT SIFRAPROFILA FROM PROFIL))
AND TO_NUMBER(SUBSTR(S.BROJINDEKSA,1,4)) < EXTRACT(YEAR FROM SYSDATE) - 2
AND ADD_MONTHS(SYSDATE, -24) < PO.DATUMPOLAGANJA;


################################################

28.

------------------------------------------------

SELECT MIN(S.SKOLARINA) AS MIN_SKOALRINA,
        MAX(S.SKOLARINA) AS MAX_SKOALRINA
FROM STUDENT S
WHERE S.SIFRAMODULA = 11;

################################################

29.

------------------------------------------------

SELECT COUNT(*) AS BROJ_STUDENATA,
            MIN(S.SKOLARINA) AS MIN_SKOLARINA,
            ROUND(AVG(S.SKOLARINA),2) AS PROSECNA_SKOLARINA
FROM STUDENT S
WHERE S.MENTOR IS NOT NULL


################################################

30.

------------------------------------------------

SELECT SIFRAMODULA, MIN(SKOLARINA),MAX(SKOLARINA), COUNT(*)
FROM STUDENT
WHERE SIFRAMODULA IS NOT NULL
GROUP BY SIFRAMODULA;

################################################

31.

------------------------------------------------

SELECT IME, SKOLARINA
FROM STUDENT
WHERE SKOLARINA < (SELECT MAX(SKOLARINA)
                    FROM STUDENT
                    WHERE SIFRAMODULA = 10);


################################################

32.

------------------------------------------------

SELECT S.SIFRAMODULA, S.MENTOR,COUNT(*)
FROM STUDENT S
INNER JOIN PROFIL P ON S.SIFRAMODULA = P.SIFRAPROFILA
WHERE S.MENTOR IS NOT NULL AND S.SKOLARINA IS NOT NULL
AND LENGTH(P.NAZIVPROFILA) < 22
GROUP BY S.SIFRAMODULA, S.MENTOR

################################################

33.

------------------------------------------------

SELECT S.SIFRAMODULA, S.MENTOR, SUM(S.SKOLARINA)
FROM STUDENT S
GROUP BY S.SIFRAMODULA, S.MENTOR
HAVING MAX(S.SKOLARINA)<100000;

################################################

34.

------------------------------------------------

SELECT S.SIFRAMODULA,
    EXTRACT(YEAR FROM S.DATUMRODJENJA) AS GODINA_RODJENJA,
    COUNT(*) AS BROJ_STUDENATA
FROM STUDENT S
WHERE S.SKOLARINA IS NULL
GROUP BY S.SIFRAMODULA, EXTRACT(YEAR FROM S.DATUMRODJENJA)
HAVING COUNT(*) >= 2;

################################################

35.

------------------------------------------------

SELECT SIFRAMODULA, MIN(SKOLARINA)
FROM STUDENT
WHERE MENTOR IS NOT NULL
GROUP BY SIFRAMODULA
HAVING MIN(SKOLARINA) > (SELECT MIN(SKOLARINA)
                        FROM STUDENT
                        WHERE SIFRAMODULA = 13);


################################################

36.

------------------------------------------------

I NACIN:

SELECT S.PREZIME, S.IME, S.SIFRAMODULA
FROM STUDENT S
WHERE S.SKOLARINA = (SELECT MIN(S1.SKOLARINA)
                        FROM STUDENT S1
                        WHERE S1.SIFRAMODULA = S.SIFRAMODULA)

------------------------------------------------

II NACIN:

SELECT PREZIME, IME, SIFRAMODULA
FROM STUDENT
WHERE (SIFRAMODULA, SKOLARINA) IN (SELECT SIFRAMODULA, MIN(SKOLARINA)
                                    FROM STUDENT
                                    GROUP BY SIFRAMODULA);

################################################

37.

------------------------------------------------

SELECT CONCAT(SUBSTR(S.IME,1,1),'.',SUBSTR(S.PREZIME,1,1),'.') AS INICIJALI,
        ROUND(AVG(PO.POENI),2)
FROM STUDENT S INNER JOIN POLAGANJE PO ON S.BROJINDEKSA = PO.BROJINDEKSA
WHERE S.BROJINDEKSA IN (
               SELECT POL.BROJINDEKSA
                FROM POLAGANJE POL INNER JOIN PREDMET PRE ON POL.SIFRAPREDMETA = PRE.SIFRAPREDMETA
                WHERE PRE.NAZIV LIKE 'Baze%')
GROUP BY CONCAT(SUBSTR(S.IME,1,1),'.',SUBSTR(S.PREZIME,1,1),'.');


################################################

38.

------------------------------------------------

I NACIN:

SELECT SUBSTR(PO.BROJINDEKSA,1,4), COUNT(DISTINCT PO.SIFRAPREDMETA), COUNT(PO.BROJINDEKSA), ROUND(AVG(PO.POENI),2)
FROM POLAGANJE PO INNER JOIN ISPITNIROK I ON (I.REDNIBROJ, I.GODINA) = (PO.REDNIBROJ,PO.GODINA)
WHERE I.MESEC = 2 AND PO.POENI > 30
GROUP BY SUBSTR(PO.BROJINDEKSA,1,4)
ORDER BY AVG(PO.POENI);

------------------------------------------------

II NACIN:

SELECT SUBSTR(PO.BROJINDEKSA,1,4), COUNT(DISTINCT PO.SIFRAPREDMETA), COUNT(PO.BROJINDEKSA), ROUND(AVG(PO.POENI),2)
FROM POLAGANJE PO
WHERE LOWER(EXTRACT(MONTH FROM DATUMPOLAGANJA)) = 2
AND PO.POENI > 30
GROUP BY SUBSTR(PO.BROJINDEKSA,1,4)
ORDER BY AVG(PO.POENI) ASC;


################################################

39.

------------------------------------------------

SELECT S.SIFRAMODULA, COUNT(*) AS BROJ_STUDENATA,
        SUM(CASE WHEN S.SKOLARINA > 100000 THEN 1 ELSE 0 END ) AS SK_PREKO_100K,
        CASE
            WHEN(SUM(CASE WHEN S.SKOLARINA > 100000 THEN 1 ELSE 0 END )>
                SUM(CASE WHEN S.SKOLARINA IS NULL THEN 1 ELSE 0 END))
            THEN 'Vise samofinansirajucih'
            WHEN(SUM(CASE WHEN S.SKOLARINA > 100000 THEN 1 ELSE 0 END )<
                SUM(CASE WHEN S.SKOLARINA IS NULL THEN 1 ELSE 0 END))
            THEN 'Vise na budzetu'
            ELSE 'Podjedanko'
        END AS ODNOS
FROM STUDENT S
GROUP BY S.SIFRAMODULA
HAVING MAX(S.SKOLARINA) > (SELECT MIN(SKOLARINA) FROM STUDENT WHERE SIFRAMODULA = 9)
ORDER BY 3 DESC;

UMESTO NAZIVA KOLONE U ORDER BY MOZE SE PISATI REDNI BROJ KOLONE

################################################

40.

------------------------------------------------

CREATE OR REPLACE VIEW ANGAZOVANJA_PROFESORA AS
SELECT N.SIFRANASTAVNIKA, ROUND(AVG(A.PLATA),2) AS PROSECNA_PLATA,
                ROUND((CASE
                WHEN SUM(CASE WHEN DATUMDO IS NULL THEN 1 ELSE 0 END) > 0
                THEN SYSDATE
                ELSE MAX(A.DATUMDO) END) - MIN(A.DATUMOD),2) AS STAZ
FROM NASTAVNIK N JOIN ANGAZOVANJE A ON N.SIFRANASTAVNIKA = A.SIFRANASTAVNIKA
WHERE N.TITULA LIKE 'doktor'
GROUP BY N.SIFRANASTAVNIKA
ORDER BY N.SIFRANASTAVNIKA;

################################################

41.

------------------------------------------------

I NACIN:

CREATE OR REPLACE VIEW STATISTIKA_STUDENATA AS
SELECT S.BROJINDEKSA, P.GODINA, COUNT(*) AS BROJ_POLAGANJA,
        ROUND(AVG(P.POENI),2) AS PROSECNO_POENA,
        CASE WHEN
            SUM(CASE WHEN P.OCENA > 5 THEN 1 ELSE 0 END) >
            SUM(CASE WHEN P.OCENA <= 5 THEN 1 ELSE 0 END)
            THEN 'ISPUNJEN USLOV'
            ELSE 'NIJE ISPUNJEN USLOV'
            END AS USLOV
FROM STUDENT S JOIN POLAGANJE P ON S.BROJINDEKSA = P.BROJINDEKSA
GROUP BY P.GODINA, S.BROJINDEKSA
HAVING COUNT(*)>2

II NACIN:

SELECT S.BROJINDEKSA, P.GODINA, COUNT(*) AS BROJ_POLAGANJA,
        ROUND(AVG(P.POENI),2) AS PROSECNO_POENA,
        CASE WHEN
            SIGN(SUM(CASE WHEN P.POENI > 50 THEN 1 ELSE -1 END)) = 1
            THEN 'ISPUNJEN USLOV'
            ELSE 'NIJE ISPUNJEN USLOV'
            END AS USLOV
FROM STUDENT S JOIN POLAGANJE P ON S.BROJINDEKSA = P.BROJINDEKSA
GROUP BY P.GODINA, S.BROJINDEKSA
HAVING COUNT(*) > 2;

################################################

42.

------------------------------------------------

I NACIN:

CREATE OR REPLACE VIEW STATISTIKA_PREDMETA AS
SELECT P.SIFRAPREDMETA, MAX(PO.POENI)-MIN(PO.POENI) AS RAZLIKA,
    CASE SIGN(SUM(CASE WHEN PO.OCENA > 7 THEN 1 ELSE -1 END))
        WHEN 1 THEN 'LAK'
        WHEN -1 THEN 'TEZAK'
        ELSE 'SREDNJE'
        END AS TEZINA_ISPITA
FROM PREDMET P JOIN POLAGANJE PO ON P.SIFRAPREDMETA = PO.SIFRAPREDMETA
WHERE PO.POENI > 50
GROUP BY P.SIFRAPREDMETA, PO.REDNIBROJ
HAVING COUNT(*)>6

------------------------------------------------

II NACIN:

CREATE OR REPLACE VIEW STATISTIKA_PREDMETA AS
SELECT P.SIFRAPREDMETA, MAX(PO.POENI)-MIN(PO.POENI) AS RAZLIKA,
    CASE WHEN
            SUM(CASE WHEN PO.OCENA > 7 THEN 1 ELSE 0 END) >
            SUM(CASE WHEN PO.OCENA > 5 AND PO.OCENA < 8 THEN 1 ELSE 0 END)
        THEN 'LAK'
        WHEN
            SUM(CASE WHEN PO.OCENA > 7 THEN 1 ELSE 0 END) <
            SUM(CASE WHEN PO.OCENA > 5 AND PO.OCENA < 8 THEN 1 ELSE 0 END)
        THEN 'TEZAK'
        ELSE 'SREDNJE'
        END AS TEZINA_ISPITA
FROM PREDMET P JOIN POLAGANJE PO ON P.SIFRAPREDMETA = PO.SIFRAPREDMETA
WHERE PO.POENI > 50
GROUP BY P.SIFRAPREDMETA, PO.REDNIBROJ
HAVING COUNT(*)>6

################################################

43. FIBONACIJEV NIZ REKURZIJA

------------------------------------------------

WITH FIB (RB, PREDHODNI, TRENUTNI) AS
(
    SELECT 1, 0, 1
    FROM DUAL
    UNION ALL
    SELECT RB+1, TRENUTNI, PREDHODNI+TRENUTNI
    FROM FIB
    WHERE RB < 20
)
SELECT * FROM FIB;

################################################

44. Rekurzija uslovni predmet

------------------------------------------------

WITH USLOVNI_PREDMETI (SIFRAUSLOVA) AS
(
    SELECT USLOV
    FROM PREDMET
    WHERE NAZIV LIKE 'Vestacka%'
        UNION ALL
    SELECT USLOV
    FROM PREDMET JOIN USLOVNI_PREDMETI ON (SIFRAUSLOVA = SIFRAPREDMETA)
    WHERE USLOV IS NOT NULL
)
SELECT * FROM USLOVNI_PREDMETI;


################################################

45. Prikazati broj polaganja prema godinama i rokovima, ali
i zbirno prema godinama za sva polaganja koja su realizovana
nakon 2021. godine.

ROLLUP

------------------------------------------------


SELECT IR.GODINA, IR.REDNIBROJ, GROUPING(IR.GODINA), GROUPING(IR.REDNIBROJ), COUNT(*) AS BROJ
FROM POLAGANJE IR
WHERE IR.GODINA > 2021
GROUP BY ROLLUP(IR.GODINA, IR.REDNIBROJ)
ORDER BY IR.GODINA

################################################

46. Prikazati broj polaganja prema godini i predmetu,
za ona polaganja koja su se desila posle 2021. godine,
zbirno po godinama, predmetima i godinama i predmetima.
U rezultat ukljuciti samo predmete sa parnim siframa

CUBE

------------------------------------------------

SELECT GODINA, SIFRAPREDMETA, GROUPING(GODINA), GROUPING(SIFRAPREDMETA), COUNT(*) AS BROJ
FROM POLAGANJE
WHERE GODINA > 2021 AND MOD(SIFRAPREDMETA,2) = 0
GROUP BY CUBE(GODINA, SIFRAPREDMETA);

################################################

47. NAJJEBENIJI DO SAD

------------------------------------------------


SELECT S.SIFRAMODULA, COUNT(*) AS BROJ_POLAGANJA,
        ROUND(
            SUM(CASE WHEN PO.POENI > 50 THEN 1 ELSE 0 END)/COUNT(*)
        ,2)
        AS UDEO_USPESNIH_POLAGANJA,
        ROUND(MONTHS_BETWEEN(SYSDATE, MIN(PO.DATUMPOLAGANJA)),2) AS BROJ_MESECI
FROM POLAGANJE PO JOIN STUDENT S ON S.BROJINDEKSA = PO.BROJINDEKSA
WHERE PO.DATUMPOLAGANJA + INTERVAL '2-5' YEAR TO MONTH > SYSDATE
GROUP BY S.SIFRAMODULA
HAVING COUNT(*)>10 AND COUNT(*) < (SELECT MAX(COUNT(*))
                                    FROM POLAGANJE POL JOIN STUDENT S1 ON S1.BROJINDEKSA = POL.BROJINDEKSA
                                    WHERE POL.DATUMPOLAGANJA + INTERVAL '2-5' YEAR TO MONTH > SYSDATE
                                    GROUP BY S1.SIFRAMODULA)
ORDER BY BROJ_MESECI;