################################################
############### 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;
