Угнежђени упити¶

Код сложенијих упита може бити потребно да се упит формира тако да се до резултата долази кроз неколико фаза. Свака фаза се формулише као нови SELECT упит који поред полазних табела може да користи и резултат упита из претходне фазе.

Прикажимо ово кроз неколико примера.

У првој фази формулишемо упит који за сваког ученика израчунава број оправданих изостанака. Ово је класичан упит у ком се врши груписање и израчунавање статистика (овај пут броја елемената сваке групе).

SELECT COUNT(*) AS broj
FROM izostanak
WHERE status = 'оправдан'
GROUP BY id_ucenik

Извршавањем упита добија се следећи резултат:

до коначног резултата можемо да стигнемо тако што из табеле која се добија као резултат претходног упита израчунамо максималну вредност колоне broj. То се лако постиже коришћењем агрегатне функције MAX.

SELECT MAX(broj)
FROM
    (SELECT COUNT(*) AS broj
     FROM izostanak
     WHERE status = 'оправдан'
     GROUP BY id_ucenik);

Извршавањем упита добија се следећи резултат:

Овај задатак може да се реши спајањем (и то је пожељно решење, поготово ако се жели приказ назива предмета). Могуће је и решење засновано на угнежђеним упитима, тако што се у првој фази из табеле предмета издвоје идентификатори предмета који се предају у првом разреду, а онда се тај скуп идентификатора у другој фази употреби за издвајање одговарајућих оцена из табеле оцена, пре груписања и рачунања статистика по групама.

SELECT id_predmet, round(AVG(ocena), 2) AS prosek
FROM ocena
WHERE id_predmet IN (SELECT id
                     FROM predmet
                     WHERE razred = 1)
GROUP BY id_predmet;

Извршавањем упита добија се следећи резултат:

Када бисмо се задовољили само приказом идентификатора предмета, онда бисмо све могли да решимо упитом над табелом оцена, груписањем на основу идентификатора предмета, израчунавањем просека оцена за сваку групу и затим селекцијом оних предмета код којих је просечна оцена већа од 3,5. Пошто су нам потребни називи предмета, можемо извршити спајање овако добијеног резултата упита са табелом предмета у којој се налазе идентификатори и називи предмета.

SELECT naziv, prosek
FROM predmet
     JOIN (SELECT id_predmet, round(AVG(ocena), 2) AS prosek
           FROM ocena
           WHERE ocena.vrsta = 'писмени задатак'
           GROUP BY id_predmet
           HAVING prosek >= 3.50)
       ON predmet.id = id_predmet;

Извршавањем упита добија се следећи резултат:

У првој фази можемо да изградимо табелу која за сваког ученика садржи идентификатор, разред и одељење и број оправданих изостанака. Пошто се разред и одељење налазе у табели ученика, а број изостанака се рачуна на основу података у табели изостанака, прво вршимо спајање те две табеле, вршимо селекцију само оправданих изостанака, а након тога групишемо податке на основу идентификатора ученика и применом COUNT(*) одређујемо број оправданих изостанака.

SELECT ucenik.id, razred, odeljenje, COUNT(*) as broj_opravdanih
FROM izostanak
     JOIN ucenik on izostanak.id_ucenik = ucenik.id
WHERE status = 'оправдан'
GROUP BY ucenik.id

Извршавањем упита добија се следећи резултат:

Када је у првој фази одређена оваква помоћна табела, тада у другој фази лако можемо да одредимо максималан број оправданих изостанака за свако одељење (груписањем на основу разреда и одељења и применом агрегатне функције MAX).

SELECT razred, odeljenje, MAX(broj_opravdanih)
FROM (SELECT ucenik.id, razred, odeljenje, COUNT(*) as broj_opravdanih
      FROM izostanak
           JOIN ucenik on izostanak.id_ucenik = ucenik.id
      WHERE status = 'оправдан'
      GROUP BY ucenik.id)
GROUP BY razred, odeljenje;

Извршавањем упита добија се следећи резултат:

Приметимо да табела добијена као резултат угнежђеног подупита нема назив. Ако је потребно да јој дамо назив, то се може урадити навођењем алијаса, коришћењем кључне речи AS (у облику FROM (SELECT ...) AS naziv). Алијасе смо раније користили за именовање колона, док се овај пут именује табела.