Studoši

Ispisati podatke o praznim predmetnim grupama iz akademske godine 2016./2017. ( ispisivati: naziv predmeta, oznaku grupe, akademsku godinu i šifru nastavnika). Prazne predmetne grupe su one predmetne grupe kojima (te akademske godine) nije bio pridijeljen niti jedan student. U obzir uzeti samo one predmete kojima te akademske godine nije bio pridijeljen niti jedan drugi nastavnik.

Primjer rezultata:

Tumačenje prvog retka rezultata: akademske godine 2016./2017. je definirano da grupi “D-B2” nastavnik sa šifrom 570 predaje predmet “Logička algebra”, ali niti jedan student nije bio raspoređen u tu grupu. Također, Logičku algebru je te akademske godine predavao samo nastavnik sa šifrom 570.

SELECT nazpredmet, ozngrupa, akgodina, sifnastavnik
    FROM predmetgrupa AS prgr JOIN predmet ON prgr.sifpredmet = predmet.sifpredmet
    WHERE akgodina = 2016
        AND NOT EXISTS (
            SELECT * FROM upisanpredmet
                WHERE sifpredmet = prgr.sifpredmet
                    AND ozngrupa = prgr.ozngrupa
                    AND akgodina = prgr.akgodina
        ) AND (
            SELECT COUNT(DISTINCT sifnastavnik) FROM predmetgrupa
                WHERE sifpredmet = prgr.sifpredmet
                    AND akgodina = prgr.akgodina
        ) = 1;

Ispisati nazive onih predmeta za koje je broj studenata koji su predmet položili ocjenom 4 ili 5 veći od broja studenata koji su taj isti predmet položili ocjenom 2 ili 3. Pored naziva predmeta ispisati i broj studenata koji su uspješno položili taj predmet.

Zapise poredati po broju studenata koji su položili predmet tako da se na vrhu popisa nalaze predmeti s najvećim brojem, a nazive predmeta s jednakim brojem poredati abecedno.

SELECT nazpredmet, COUNT(*)
    FROM predmet AS pred JOIN upisanpredmet ON pred.sifpredmet = upisanpredmet.sifpredmet
       WHERE ocjena > 1
        AND (
            SELECT COUNT(*) FROM upisanpredmet
                 WHERE sifpredmet = pred.sifpredmet
                     AND ocjena >= 4
        ) > (
            SELECT COUNT(*) FROM upisanpredmet
                 WHERE sifpredmet = pred.sifpredmet
                    AND ocjena > 1
                    AND ocjena < 4
        )
    GROUP BY nazpredmet
       ORDER BY COUNT(DISTINCT jmbag) DESC, nazpredmet ASC;

Ispisati JMBAG, prezime i ime studenata koji ne stanuju u istoj županiji u kojoj su i rođeni, a na dan koji pripada prošloj kalendarskoj godini su iz bilo kojeg predmeta dobili ocjenu 4. Pored JMBAG-a, imena i prezimena studenta ispisati naziv županije rođenja i naziv županije stanovanja. Upit mora biti neovisan o trenutku izvođenja. U rezultatu se ne smiju pojavljivati jednake n-torke (duplikati) a njihov poredak nije bitan.

SELECT DISTINCT student.jmbag, imestudent, prezimestudent, zupanijaRod.nazzupanija, zupanijaStan.nazzupanija
    FROM student JOIN upisanpredmet ON student.jmbag = upisanpredmet.jmbag
        JOIN mjesto AS mjestoStan ON mjestoStan.pbr = pbrstanstudent
        JOIN zupanija AS zupanijaStan ON zupanijaStan.sifzupanija = mjestoStan.sifzupanija
        JOIN mjesto AS mjestoRod ON mjestoRod.pbr = pbrrodstudent
        JOIN zupanija AS zupanijaRod ON zupanijaRod.sifzupanija = mjestoRod.sifzupanija
    WHERE zupanijaRod.sifzupanija <> zupanijaStan.sifzupanija
        AND EXTRACT(YEAR FROM CURRENT_DATE) - 1 = EXTRACT(YEAR FROM datumocjena)
        AND upisanpredmet.ocjena = 4;

Ispisati različite datume na koje su studenti osvojili prolaznu ocjenu iz predmeta (stupac nazvati datum_ocjene), broj studenata koji su tog dana osvojili prolaznu ocjenu (stupac nazvati broj_kandidata) te prosječnu prolaznu ocjenu koju su kandidati tog datuma dobili (stupac nazvati prosjek_ocjena). Prosječnu ocjenu zaokružiti na 2 decimalne znamenke. Ispisati podatke samo za one datume na kojima nitko nije dobio ocjenu manju od 4, a ostale zanemariti.

Napomena: Ako je jedan student na isti datum polagao više ispita, u broj_kandidata treba ga uračunati više puta.

SELECT datumocjena AS datum_ocjene, COUNT(*) AS broj_kandidata, ROUND(AVG(ocjena), 2) AS prosjek_ocjena
    FROM upisanpredmet LEFT JOIN student ON upisanpredmet.jmbag = student.jmbag
    WHERE ocjena > 1
    GROUP BY datum_ocjene
    HAVING MIN(ocjena) >= 4

Za zadanu relacijsku shemu STUDSTIP={ime, prezime, jmbag, prosjekOcjena} napisati naredbu kojom će se napraviti relacija studStip(STUDSTIP). Tipovi podataka neka budu sljedeći:

• ime, prezime- VARCHAR(25)
• jmbag- VARCHAR(10)
• prosjekOcjena- NUMERIC(5, 2)

U relaciju studStip upisati studente koji imaju prosjek ocjena veći ili jednak 4 (u prosjek ocjena ulaze samo pozitivne ocjene).

CREATE TABLE studStip(
    ime VARCHAR(25),
    prezime VARCHAR(25),
    jmbag VARCHAR(10),
    prosjekOcjena NUMERIC(5, 2)
); 

INSERT INTO studStip(ime, prezime, jmbag, prosjekOcjena)
    SELECT imestudent, prezimestudent, student.jmbag, AVG(ocjena)
        FROM student LEFT JOIN upisanpredmet ON student.jmbag = upisanpredmet.jmbag
        WHERE ocjena > 1
        GROUP BY imestudent, prezimestudent, student.jmbag
        HAVING AVG(ocjena) >= 4;

SELECT student.jmbag, imeStudent, prezimeStudent, sifPredmet, ocjena, akGodina
FROM student NATURAL JOIN upisanPredmet
WHERE akGodina = 2018 AND ocjena > 1 AND (SELECT AVG(upisanPredmet.ocjena)
                        FROM upisanpredmet
                        WHERE student.jmbag = upisanpredmet.jmbag and upisanpredmet.ocjena >1) > 3.5
 						AND NOT EXISTS
                           (SELECT *
                                   FROM upisanpredmet as upi_pred
                                   WHERE student.jmbag <> jmbag and ocjena = upisanpredmet.ocjena
                                         and upisanpredmet.sifpredmet = sifpredmet AND ocjena > 1
                           				AND akGodina = 2018); 

Ispisati imena studenata (svako ime samo jednom) koja sadrže malo slovo “i” i dugačka su 8 znakova (npr. Krešimir). Uzeti u obzir samo studente rođene radnim danom (pon-pet).

SELECT DISTINCT imestudent
FROM student
WHERE CHAR_LENGTH(imestudent) = 8 AND TRIM(imestudent) LIKE '%i%'AND (EXTRACT(DOW FROM datumrod)) NOT IN (6,0);

Ispisati oznaku i kapacitet svih dvorana kojima oznaka počinje slovom “A”. Za one dvorane u kojima se izvodila nastava za grupe kojima oznaka grupe počinje slovom “C”, ispisati uz podatke o dvorani i akademsku godinu kada se ta nastava odvijala, u suprotnom ispisati NULL vrijednost. Dodatno, ispisati i naziv predmeta iz kojeg se odvijala takva nastava, ako je predmet nosio 5 ECTS bodova, inače ispisati NULL vrijednost. Iz ispisa izbaciti duplikate.
Primjer rezultata:

SELECT DISTINCT dvorana.*, pom.akgodina, pom.nazpredmet
FROM dvorana LEFT JOIN 
(SELECT * 
 FROM predmetgrupa LEFT JOIN predmet
 ON predmetgrupa.sifpredmet = predmet.sifpredmet AND predmet.ectsbod = 5) AS pom
 ON dvorana.ozndvorana = pom.ozndvorana AND dvorana.ozndvorana LIKE 'A%' AND pom.ozngrupa LIKE 'C%'
 WHERE dvorana.ozndvorana LIKE 'A%'

Ispisati JMBAG, ime i prezime studenta, te dodatno prosječnu vrijednost ECTS bodova (nazovite stupac prosjek_ects) predmeta koje je student položio. Ispisati samo one studente koji su položili više od 15 predmeta.

SELECT student.jmbag, imestudent, prezimestudent, 
(SELECT AVG(predmet.ectsbod) 
 FROM predmet JOIN upisanpredmet
 ON upisanpredmet.sifpredmet = predmet.sifpredmet AND upisanpredmet.jmbag = student.jmbag
 WHERE ocjena > 1) AS prosjek_ects
FROM student JOIN upisanpredmet
ON student.jmbag = upisanpredmet.jmbag AND
(SELECT COUNT(upisanpredmet.sifpredmet)
 FROM upisanpredmet
 WHERE jmbag = student.jmbag AND ocjena > 1) > 15
 JOIN predmet
 ON predmet.sifpredmet = upisanpredmet.sifpredmet
 GROUP BY student.jmbag, imestudent, prezimestudent

Potrebno je dodati novu organizacijsku jedinicu “Republika Hrvatska” sa šifrom 1 (nema nadređenu organizacijsku jedinicu). Zatim postaviti Republiku Hrvatsku kao nadređenu svim organizacijskim jedinicama koje dosad nisu imale nadređenu organizacijsku jedinicu (osim same Republike Hrvatske).

INSERT INTO orgjed VALUES(1, 'Republika Hrvatska', NULL);
UPDATE orgjed SET sifnadorgjed = 1
WHERE sifnadorgjed IS NULL and siforgjed <> 1;

Za zadanu relacijsku shemu STUDSTIP={ime, prezime, jmbag, ukEcts} napisati naredbu kojom će se napraviti relacija studStip(STUDSTIP). Tipovi podataka neka budu sljedeći:
ime, prezime - VARCHAR(25)
jmbag - VARCHAR(10)
ukEcts (ukupan broj ECTS bodova koji je student ostvario polaganjem predmeta) - NUMERIC(4, 1)
U relaciju studStip upisati studente čiji je ukupan broj ECTS bodova ostvaren polaganjem ispita bio veći ili jednak od 80 (u sumu ulaze, naravno, samo položeni ispiti).

CREATE TABLE studStip(
    ime		VARCHAR(25),
    prezime	VARCHAR(25),
    jmbag	VARCHAR(10),
    ukEcts	NUMERIC(4,1)
);
INSERT INTO studStip
	SELECT student.imestudent, student.prezimestudent, student.jmbag, SUM(predmet.ectsbod)
    FROM student NATURAL JOIN upisanpredmet NATURAL JOIN predmet
    WHERE ocjena > 1
    GROUP BY student.imestudent, student.prezimestudent, student.jmbag
    HAVING SUM(predmet.ectsbod) >= 80;```

!<