Спајање табела¶

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

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

Приказати све изостанке на дан 14. 05. 2021. године.

Први покушај може да буде просто читање свих података из табеле изостанака.

SELECT *
FROM izostanak
WHERE datum = '2021-05-14';

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

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

Један од начина да то урадимо је да употребимо оператор спајања ... JOIN ... ON ....

SELECT *
FROM izostanak JOIN ucenik ON izostanak.id_ucenik = ucenik.id;

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

У овом упиту смо извршили спајање табеле изостанака и табеле ученика коришћењем једнакости одговарајућих идентификатора. Резултат спајања садржи све врсте које се добијају надовезивањем једне врсте из табеле ученика на једну врсту из табеле изостанака, тако да је вредност колоне id_ucenik у врсти из табеле изостанака једнака вредности колоне id у врсти из табеле ученика.

На пример, претпоставимо да у табели изостанака постоји ред (2, 1, 2021-05-14, 2, неоправдан), а у табели ученика постоји ред``(1, Петар, Петровић, м, 2007-07-01, 1, 1)``. При том је други број у табели изостанака колона id_ucenik (идентификатор ученика који је направио изостанак), док је први број у табели ученик заправо колона id (идентификатор ученика). Пошто се у обе те колоне налази вредност 1 (ради се о истом ученику), у привременој табели добијеној спајањем ће постојати врста (2, 1, 2021-05-14, 2, неоправдан, 1, Петар, Петровић, м, 2007-07-01, 1, 1). Овај пример је део следеће илустрације на којој можемо да видимо још један изостанак ученика Петра Петровића и два изостанка ученика Петра Миловановића са идентификатором 4.

Приметимо да смо у услову спајања ON називе колона оквалификовали називима табела. Што се тиче колоне id то је било неопходно, јер колона id постоји у обе наведене табеле. Са друге стране, колона id_ucenik постоји само у табели изостанака, па није било неопходно наводити назив табеле уз назив колоне, међутим, експлицитним навођењем имена табеле упит постаје јаснији и прегледнији.

Текст упита може мало да се скрати ако се табелама у оквиру упита дају нова, краћа имена, навођењем алијаса.

SELECT *
FROM izostanak AS i
     JOIN ucenik AS u ON i.id_ucenik = u.id;

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

Кључна реч AS може да се изостави, и у том случају би исти упит изгледао овако:

SELECT *
FROM izostanak i
     JOIN ucenik u ON i.id_ucenik = u.id;

У систему SQLite Studio упити се пишу након што се кликне на креирану базу dnevnik у прозору Databases и потом изабере команда менија Tools → Open SQL Editor. Када се напише упит, кликне се на дугме Execute query (F9) (плави троуглић). Савет је да се у прозору Databases увек прво провере тачни називи табела, као и колона. Посебно је важно да проверимо називе страног кључа и примарног кључа који му одговара у другој табели када желимо да извршимо спајање, тј. да прикажемо податке из више табела које су повезане.

Исти упит покренут у систему SQLite Studio може да се види на следећој слици. Види се само првих неколико редова и информација о томе да има укупно 10194 редова, односно толико има евидентираних изостанака у овој бази података, а уз сваки смо додали и податке о ученику који га је направио.

Спојене табеле могу да се филтрирају (коришћењем клаузуле WHERE) и резултат може да се пројектује само на оне колоне које нас занимају. Напоменимо да се у овом случају филтрирање, ефикасности ради, врши пре спајања (мада би резултат био исти и кад би се прво вршило спајање).

За сваки изостанак начињен 14. маја 2021. године, приказати име и презиме ученика, редни број часа и статус изостанка.

SELECT ime, prezime, cas, status
FROM izostanak i
     JOIN ucenik u ON i.id_ucenik = u.id
WHERE datum = '2021-05-14';

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

Приказати све оцене из математике за први разред.

Проблем код оваквих упита је то што не знамо идентификатор траженог предмета. Не желимо (и не треба) да памтимо вредности идентификатора, који могу да нам затребају у разним сличним упитима над овом и другим базама података.

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

Овде претпостављамо да постоји само један предмет у првом разреду који се зове математика, тј. да је пар (naziv, razred) довољан за јединствено идентификовање сваког реда у табели predmet. У противном (ако постоји више предмета са називом математика у првом разреду) биће приказане све оцене из свих таквих предмета.

SELECT id_ucenik, ocena, datum, vrsta
FROM ocena
     JOIN predmet ON ocena.id_predmet = predmet.id
WHERE predmet.naziv = 'Математика' AND predmet.razred = 1;

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

Спајање је могуће применити и на више од две табеле.

Приказати све оцене у читљивом формату (тако да се виде име и презиме ученика и назив предмета)

SELECT p.naziv, u.ime, u.prezime, o.ocena, o.datum, o.vrsta
FROM ocena o
     JOIN predmet p ON o.id_predmet = p.id
     JOIN ucenik u ON o.id_ucenik = u.id;

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

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

Наравно, и тада је могуће извршити додатно филтрирање коришћењем клаузуле WHERE.

Приказати све оцене на писменим задацима из предмета првог разреда у читљивом формату.

SELECT p.naziv, u.ime, u.prezime, o.ocena, o.datum, o.vrsta
FROM ocena o
     JOIN predmet p ON o.id_predmet = p.id
     JOIN ucenik u ON o.id_ucenik = u.id
WHERE p.razred = 1 AND vrsta = 'писмени задатак';

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

Табеле при спајању не морају бити различите – могуће је спојити табелу саму са собом.

Приказати све парове близанаца (под близанцима ћемо подразумевати све парове ученика који се исто презивају и имају исти датум рођења).

Први покушај подразумева само да се табела ucenik споји сама са собом, уз услов да презиме и датум рођења буду једнаки.

SELECT u1.ime as ime1, u2.ime as ime2, u1.prezime
FROM ucenik as u1
     JOIN ucenik as u2 ON u1.datum_rodjenja = u2.datum_rodjenja AND u1.prezime = u2.prezime;

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

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

SELECT u1.ime as ime1, u2.ime as ime2, u1.prezime
FROM ucenik as u1
     JOIN ucenik as u2 ON u1.datum_rodjenja = u2.datum_rodjenja AND u1.prezime = u2.prezime
WHERE u1.id < u2.id;

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

Вежба¶

Покушај да наредних неколико упита напишеш самостално.

Приказати све регулисане изостанке у читљивом формату (у ком се види име и презиме ученика и датум, час и статус изостанка).

Тестирај упит Изврши упит Прикажи очекивани резултат

За сваког ученика приказати списак предмета које похађа (свака врста треба да садржи име и презиме ученика, разред и одељење у које иде и назив једног предмета).

Тестирај упит Изврши упит Прикажи очекивани резултат