-- 1. izdvojiti imena i prezimena studenata kao i nazive predmeta
--    koji su tom studentu priznati pri upisu fakulteta

SELECT ime, prezime, nazivpredmeta
FROM dosije, priznatispit
WHERE dosije.indeks = priznatispit.indeks;

-- dosije, priznatispit <- dekartov proizvod tabela dosije i priznat ispit
--                         ako dosije ima M redova, priznatispit N redova dosije, priznatispit ima MxN redova

SELECT d.ime, d.prezime, pi.nazivpredmeta
FROM dosije d, priznatispit pi
WHERE d.indeks = pi.indeks;

-- (INNER) JOIN spajanje dve tabele

SELECT d.ime, d.prezime, pi.nazivpredmeta
FROM dosije d JOIN priznatispit pi
    ON d.indeks = pi.indeks;

-- 2. prikazati podatke o studentima kao i njihovim ispitima

SELECT *
FROM dosije d JOIN ispit i
    ON d.indeks = i.indeks;

-- 3. izdvojiti ime, prezime, idpredmeta, ocenu za ispite odrzane
--    28.1.2016.

SELECT d.ime, d.prezime, i.idpredmeta, i.ocena
FROM dosije d join ispit i
    ON d.indeks = i.indeks
WHERE i.datpolaganja = '28.1.2016';

-- 4. izdvojiti ime, prezime, naziv predmeta i ocenu za sve polozene ispite

SELECT d.ime, d.prezime, p.naziv, i.ocena
FROM dosije d JOIN ispit i
    ON d.indeks = i.indeks
    JOIN premdet p
        ON i.idpredmeta = p.id
WHERE i.ocena > 5 AND i.status = 'o';

-- 5. izdvojiti podatke o studentima za koje vazi da su diplomirali
--    na dan kada je odrzan neki ispit

SELECT DISTINCT d.*
FROM dosije d JOIN ispit i
    ON d.datdiplomiranja = i.datpolaganja;

-- 6. izdvojiti parove predmeta koji imaju isti broj espb
--    izdvojiti njihoe oznake kao i broj espb koji nose

SELECT p1.oznaka, p2.oznaka, p1.espb
FROM  predmet p1 JOIN predmet p2
    ON p1.espb = p2.espb;
-- Pri ovakvom upitu svaki par predmeta se javlja dva puta
-- i takodje imamo parove istog predmeta sto nismo zeleli!
SELECT p1.oznaka, p2.oznaka, p1.espb
FROM  predmet p1 JOIN predmet p2
    ON p1.espb = p2.espb;
WHERE p1.id < p2.id;
    
-- 7. Izdvojiti indeks, ime i prezime studenata čije prezime sadrži 
-- malo slovo a na 4. poziciji i završava na malo slovo c i koji su 
-- predmet čiji je broj espb bodova između 2 i 10 položili sa ocenom 
-- 6, 8 ili 10 između 5. januara 2018. i 15. decembra 2018. Rezultat 
-- urediti prema prezimenu u rastućem poretku i imenu u opadajućem poretku.

SELECT d.indeks, d.ime, d.prezime
FROM dosije d JOIN ispit i
    ON d.indeks = i.indeks
    JOIN predmet p
        ON i.idpredmeta = p.id
WHERE p.espb BETWEEN 2 AND 10
    AND i.ocena IN (6, 8, 10)
    AND i.datpolaganja BETWEEN '5.1.2018' AND '15.12.2018'
    AND d.prezime LIKE '___a%c'
ORDER BY d.ime ASC, d.prezime DESC;

-- 8. Za svakog studenta izdvojiti imena i prezimena kao i nazve predmeta
-- koji su tom studenti priznati pri ispitu

-- LEFT (OUTER) JOIN <- svaki red iz tabele sa leve strane ce biti u rezultatu
--                       ukoliko nema reda u desnoj tabeli koji ispunjava zadati uslov
--                        red desne tabele u rezultatu se popunjava NULL vrednostima
-- RIGHT (OUTER) JOIN <- slicno kao gore samo sto je desna tabela privilegovana
-- FULL (OUTER) JOIN <-  unija prethodna dva slucaja

SELECT d.ime, d.prezime, pi.nazivpredmeta
FROM dosije d LEFT JOIN priznatispit pi
    ON d.indeks = pi.indeks;

-- 9. za svaki predmet koji moze da se slusa na nekom studijskom programu
--    izdvojiti idprogramam, idpredmeta i iduslvonog. u rezultat ubaciti i predmete koji nemaju
--    uslovnih predmeta

SELECT pp.idpredmeta, pp.idprograma, up.idpredmeta
FROM predmetprograma pp LEFT JOIN uslovnipredmet up
    ON pp.idpredmeta = up.idpredmeta
        AND pp.idprograma = up.idprograma;

-- 10 izdvojiti parove student - isptini rok gde je student diplomirao
--    poslednjeg dana roka izdvojiti
--    ime, prezime, indeks, datdiplomiranjam nazivroka, datum kraja roka

SELECT d.ime, d.prezime. d.indeks, d.datdiplomiranja, ir.naziv, ir.datkraja
FROM isptinirok ir FULL JOIN dosije d
    ON ir.datkraja = d.datdiplomiranja;

-- 11. za svaki studijski program izdvojiti njegovu oznaku
--     kao i imena i prezimena studenata sa tog programa
--     koji su diplomirali 2019. godine

-- OVAKVO RESENJE NIJE DOBRO
SELECT d.ime, d.prezime, d.indeks, sp.naziv
FROM dosije d RIGHT JOIN studijskiprogram sp
    ON d.idprograma = sp.id
WHERE d.datdiplomiranja BETWEEN '1.1.2019' AND '31.12.2019';

-- uslov iz WHERE linije ce eleminisati i one redove iz tabele 
-- dobijene spajanjem u kojima su informacije o studentima NULL
-- da bi izbegli ovakvo nezeljneo ponasanje uslov prebacujemo u ON deo

SELECT d.ime, d.prezime, d.indeks, sp.naziv
FROM dosije d RIGHT JOIN studijskiprogram sp
    ON d.idprograma = sp.id
        AND d.datdiplomiranja BETWEEN '1.1.2019' AND '31.12.2019';

-- 12. za svakog studenta koji u imanu sadrzi 'ark' izdvojiti podatke
--    o polozenim ispitima. izdovijit ime, prezime, ocenu i naziv premdeta
--    rezultat urediti po indeksu opadajuce

-- OVAKVO RESENJE NIJE DOBRO: 
SELECT d.ime, d.prezime, p.naziv
FROM dosije d LEFT JOIN ispit i
    ON d.indeks = i.indeks
        AND i.ocena > 5 AND i.status = 'o'
    JOIN predmet p
        ON i.idpredmeta = p.idpredmeta
WHERE d.ime LIKE '%ark%'
ORDER BY d.indeks;

-- u toku spajanja sa tableom predmet svi nullovi
-- nastali u spoljasnjem spajanju nestaju!

SELECT d.ime, d.prezime, p.naziv
FROM dosije d LEFT JOIN ispit i
    ON d.indeks = i.indeks
        AND i.ocena > 5 AND i.status = 'o'
    LEFT JOIN predmet p
        ON i.idpredmeta = p.idpredmeta
WHERE d.ime LIKE '%ark%'
ORDER BY d.indeks;

SELECT d.ime, d.prezime, p.naziv
FROM dosije d 
    LEFT JOIN 
    (ispit i JOIN predmet p ON i.idpredmeta = p.idpredmeta)
    ON d.indeks = i.indeks
        AND i.ocena > 5 AND i.status = 'o'    
WHERE d.ime LIKE '%ark%'
ORDER BY d.indeks;