[nastava:2003] - Re: Februarski rok iz baza by "Damjan S. Vujnovic" <damjan@galeb.etf.bg.ac.yu>

Bacio sam pogled na ovaj poslednji rok, pa samo da dam mali komentar na zadatak 2c jer je to jedan od standardnijih načina za "pecanje" (doduše pomalo ofucan, stvar je verovatno opštepoznata). Poenta je da agregatne funkcija MIN, MAX, SUM i AVG nad praznom relacijom vraćaju NULL (a ne 0) kao rezultat, pa će ono što prvo pada na pamet:

UPDATE Student
   SET Student.Prosek=(SELECT AVG(I.Ocena)
                         FROM Ispit I
                        WHERE I.SifS=Student.SifS
                          AND I.Ocena>5)

studentima koji nisu položili ni jedan ispit prosek postaviti na NULL (ili još gore, ako postoji constraint da kolona nije NULL-able, ni jedan prosek se neće ažurirati jer će se transakcija Rollbackovati zbog narušavanja integriteta). Jedna ideja je da se prvo ažuriraju proseci svim studentima koji su položili barem jedan ispit:

UPDATE Student
   SET Student.Prosek=(SELECT AVG(I.Ocena)
                         FROM Ispit I
                        WHERE I.SifS=Student.SifS
                          AND I.Ocena>5)
 WHERE EXISTS(SELECT *
                FROM Ispit I
               WHERE I.SifS=Student.SifS
                 AND I.Ocena>5);

A zatim i studentima koji nisu položili ni jedan ispit:

UPDATE Student
   SET Student.Prosek=0
 WHERE NOT EXISTS(SELECT *
                    FROM Ispit I
                   WHERE I.SifS=Student.SifS
                     AND I.Ocena>5);

Alternativno rešenje, pretvaranjem donjih podupita u nekorelisane, upotrebom IN umesto EXISTS je:

UPDATE Student
   SET Student.Prosek=(SELECT AVG(I.Ocena)
                         FROM Ispit I
                        WHERE I.SifS=Student.SifS
                          AND I.Ocena>5)
 WHERE Student.SifS IN (SELECT DISTINCT I.SifS
                          FROM Ispit I
                         WHERE I.Ocena>5);

UPDATE Student
   SET Student.Prosek=0
 WHERE Student.SifS NOT IN (SELECT DISTINCT I.SifS
                              FROM Ispit I
                             WHERE I.Ocena>5);

Brže (a i elegantnije) rešenje je sa COALESCE (koji je uglavnom zbog toga i izmišljen):

UPDATE Student
   SET Student.Prosek=COALESCE((SELECT AVG(I.Ocena)
                                  FROM Ispit I
                                 WHERE I.SifS=Student.SifS
                                   AND I.Ocena>5),
                               0);

Pozdrav,
Damjan S. Vujnović