Stručný úvod do databáz a SQL

Tento návod patrí k úlohe Polročné vysvedčenie. Najlepšie tento návod pochopíte, ak budete počas neho skúšať postupne riešiť jednotlivé podúlohy. Hrať sa s našou databázou a SQL môžete tu: https://people.ksp.sk/~prask/specialne/3/2/3/.

Príklady tabuliek použitých v tomto návode nie vždy (alebo úplne) zodpovedajú tabuľkám v reálnej databáze. Zoznam tabuliek aj s názvami stĺpcov v týchto tabuľkách môžete nájsť tu: https://prask.ksp.sk/navody/udaje_tabuliek/

Čo sú to databázy?

Možno ste o databázach nikdy nepočuli, ale stoja na pozadí každej zložitejšej webovej stránky, určite by ste ich našli vo svojom telefóne alebo v obľúbenej počítačovej hre. Databázy sú spôsob, akým si počítačové programy ukladajú informácie pre neskoršie použitie. To, čo uložíte do databázy, tam zostane, aj keď vypnete počítač. Toto isté by sa dalo povedať napríklad aj o Wordovom dokumente. Ten ale nie je veľmi vhodný na čítanie pre počítač. Databáza má tú výhodu, že dáta v nej sú pekne usporiadané, tak aby sa ich počítaču dobre čítalo a zároveň sa odtiaľ dajú údaje rýchlo získať a rýchlo zapísať. Webové stránky používajú databázy napr. na ukladanie si zoznamu užívateľov, aj váš účet na Facebooku je určite uložený niekde v databáze :).

A načo je SQL?

SQL je jazyk, ktorým môžete s databázami komunikovať. Pomocou SQL viete databáze povedať veci ako napr. „nájdi mi všetkých študentov, ktorí majú viac ako 15 rokov". Samozrejme túto požiadavku musíte napísať v SQL. Ako tento jazyk používať uvidíte nižšie.

Tabuľky

Každá databáza má svoje tabuľky (angl. tables). V tabuľke býva zoznam podobných objektov, u ktorých nás zaujímajú rovnaké vlastnosti. Napr. majme databázu skola, a v nej tabuľky ziaci a ucitelia. Tabuľka ziaci by mohla vyzerať takto:

ID_ziaka	Meno_ziaka	Priezvisko	Rocnik_ziaka	Datum_narodenia
0	Peter	Rusnák	2	1.12.2005
1	Monika	Králová	2	1.12.2005
2	Michal	Boža	3	18.3.2008
3	Dávid	Petrovič	4	1.12.2005
4	Monika	Kasová	4	7.11.2006

Tabuľka ucitelia by zase mohla vyzerať takto:

ID_ucitela	Meno_ucitela	Trieda_ucitela
0	Mgr. Koberec	6
1	Mgr. Osem	7
2	Mgr. Prefíkaná	3
3	Mgr. Kvetináč

V databázach používame nasledujúce termíny:

• riadky (angl. rows): jednotlivé záznamy objektov v databáze

• názvy stĺpcov (angl. fields, poslovenčene fieldy): vlastnosti jednotlivých objektov, ktoré zadávame do databázy. V tabuľke ziaci sú to napr. : Meno_ziaka, Datum_narodenia...

• stĺpce (angl. Columns): hodnoty v jednotlivom stĺpci, na ktoré sa vieme pozerať a pýtať sa na ne.

• ID objektu(z anglického Identity): číslo, podľa ktorého databáza rozlišuje záznamy. Dva záznamy môžu mať rovnaké všetky stĺpce, ale ID musí byť vždy iné pre každý objekt v tabuľke (unikátne, angl. unique). Napr. môžeme mať dvoch študentov, ktorí sa obaja volajú Jožko Mrkvička a narodili sa 2.3.2005 (aj keď je to nepravdepodobné, stať sa to môže), ale vďaka ID ich budeme vedieť rozlíšiť. V skutočnom živote je ID napríklad vaše rodné číslo.

Príkaz SELECT

Najzákladnejší príkaz v jazyku SQL je príkaz SELECT. Slúži na vypísanie dát z tabuľky. Napisaný vyzerá takto:
SELECT nazov_stlpcov FROM nazov_tabulky;
Príkaz vyberie z tabuľky s názvom nazov_tabulky stĺpce nazov_stlpcov. SQL príkazy môžu byť napísané na viacerých riadkoch kvôli prehľadnosti, koniec príkazu označujeme bodkočiarkou. A skutočný príkaz preto môže vyzerať napríklad:
SELECT ID_ucitela, Meno_ucitela FROM ucitelia;
Výsledok tohto príkazu by potom vyzeral takto:

ID_ucitela	Meno_ucitela
0	Mgr. Koberec
1	Mgr. Osem
2	Mgr. Prefíkaná
3	Mgr. Kvetináč

Všimnite si, že trieda učiteľa sa nevypísala, pretože sme špecifikovali, že chceme vypísať iba ID a meno. Alebo môžeme napísať takýto príkaz:
SELECT * FROM ziaci;
Hviezdička ("*") znamená v SQL jazyku „všetko“, konkrétne v tomto príkaze sa myslí všetky stĺpce tabuľky ziaci. Príkaz by vypísal toto:

ID_ziaka	Meno_ziaka	Priezvisko	Rocnik_ziaka	Datum_narodenia
0	Peter	Rusnák	2	1.12.2005
1	Monika	Králová	2	1.12.2005
2	Michal	Boža	3	18.3.2008
3	Dávid	Petrovič	4	1.12.2005
4	Monika	Kasová	4	7.11.2006

Keď ste sa dostali v tutoriáli už takto ďaleko, môžete si trochu oddýchnuť od čítania a skúsiť vyriešiť podúlohu a).

Príkaz WHERE

Vďaka príkazu SELECT vieme vybrať hociktoré stĺpce z hociktorej tabuľky. Problém ale je, že vždy vyberieme všetky riadky danej tabuľky. A tabuľky môžu mať strašne veľa záznamov a my môžeme hľadať napríklad iba človeka s konkrétnym menom. Na to, aby sme to vedeli urobiť rýchlejšie, slúži príkaz WHERE. Príkazom WHERE vieme špecifikovať, aké hodnoty by mali mať jednotlivé stĺpce záznamov, ktoré chceme vybrať. Napríklad, ak chceme vybrať všetkých žiakov 2. ročníka, urobíme to takto:
SELECT * FROM ziaci WHERE Rocnik_ziaka=2;

ID_ziaka	Meno_ziaka	Priezvisko	Rocnik_ziaka	Datum_narodenia
0	Peter	Rusnák	2	1.12.2005
1	Monika	Králová	2	1.12.2005

Alebo takto: v tomto príklade vyberieme všetkých žiakov, ktorí sa volajú Peter, pričom špecifikujeme, ktoré stĺpce chceme zobraziť (tie sa môžu líšiť od tých, ktoré použijeme v podmienke WHERE):
SELECT ID_ziaka, Rocnik_ziaka FROM ziaci WHERE Meno_ziaka="Peter";

Pozor: Všimnite si, že je rozdiel, či porovnávame hodnotu v tabuľke s číslom alebo so slovom. Ak porovnávame so slovom, je treba slovo dať do úvodzoviek, čísla sa do úvodzoviek nedávajú (toto je veľmi zjednodušené vysvetlenie, môže nastať aj prípad, že číslo budeme dávať do úvodzoviek, napr. keď je to názov niečoho). Hlavný dôvod však je, že ak by sme mali napríklad tabuľku Peter tak počítaču musíme vysvetliť, kedy hovoríme o názve tejto tabuľky a kedy o mene.

Podmienka vo vnútri WHERE nemusí obsahovať iba jedno porovnanie; pomocou slov OR (alebo) a AND (a zároveň) vieme spojiť viacero podmienok dokopy.

Príkaz SELECT * FROM ziaci WHERE Meno_ziaka='Monika' AND Rocnik_ziaka=4; vyberie z tabuľky ziaci všetkých žiakov, ktorí sa volajú Monika a zároveň (AND) chodia do štvrtej triedy.

ID_ziaka	Meno_ziaka	Priezvisko	Rocnik_ziaka	Datum_narodenia
4	Monika	Kasová	4	7.11.2006

Príkaz:
SELECT * FROM ucitelia WHERE Predmety_ucitela="Občianska náuka" OR Trieda_ucitela=7;
zase vyberie z tabuľky ucitelia učiteľov, ktorí učia občiansku náuku alebo (OR) sú triedny učitelia 7. triede.

Podmienky môžeme ľubovoľne kombinovať a zoskupovať pomocou zátvoriek:
SELECT * FROM tabuľka WHERE podmienka1 AND (podmienka2 OR podmienka3);
by vybral také riadky, kde by platila podmienka1 a zároveň aspoň jedna z podmienok podmienka2 a podmienka3.

Ak naopak chcem vybrať všetky riadky, ktoré nespĺňajú nejakú podmienku, môžeme použiť výraz NOT, napr:

SELECT * FROM ziaci WHERE NOT Meno_ziaka='Peter'; vyberie z tabuľky ziaci všetkých žiakov, ktorí sa nevolajú Peter. Podmienky s NOT môžeme aj kombinovať s AND a OR:
SELECT * FROM tabuľka WHERE NOT Meno_ziaka='Peter' AND NOT Rocnik_ziaka=3;

Teraz už môžete vyskúšať vyriešiť podúlohy b) a c).

Príkaz ORDER BY

Poradie vypisovania výsledkov môžeme ovplyvniť pomocou príkazu ORDER BY:
SELECT * FROM ziaci ORDER BY Meno_ziaka;
vyberie všetky záznamy z tabuľky ziaci a zoradí ich (abecedne) podľa ich mena od "A" po "Z".

ID_ziaka	Meno_ziaka	Priezvisko	Rocnik_ziaka	Datum_narodenia
3	Dávid	Petrovič	4	1.12.2005
2	Michal	Boža	3	18.3.2008
4	Monika	Kasová	4	7.11.2006
1	Monika	Králová	2	1.12.2005
0	Peter	Rusnák	2	1.12.2005

Ak nakoniec, za ORDER BY pripojíme slovo DESC dostaneme výsledky zoradené naopak, v predošlom prípade by boli zoradene od "Z" po "A". ORDER BY vie zoraďovať výsledky aj podľa viacerých kritérií - najprv zoradí výsledky podľa prvého kritéria, ak je pre nejaké výsledky toto kritérium rovnaké, tak ich zoradí podľa druhého kritéria atď. V našej tabuľke žiakov máme napr. 2 Moniky. Ak by sme použili príkaz:
SELECT * FROM ziaci ORDER BY Meno_ziaka, Rocnik_ziaka;

ID_ziaka	Meno_ziaka	Priezvisko	Rocnik_ziaka	Datum_narodenia
3	Dávid	Petrovič	4	1.12.2005
2	Michal	Boža	3	18.3.2008
1	Monika	Králová	2	1.12.2005
4	Monika	Kasová	4	7.11.2006
0	Peter	Rusnák	2	1.12.2005

tabuľka by sa zoradila najskôr podľa mena, a ak sú mená rovnaké, zoradili by sa podľa ročníka.

Alebo môžeme skúsiť aj skombinovať príkazy WHERE a ORDER BY:

SELECT * FROM ziaci WHERE Rocnik_ziaka>2 ORDER BY Meno_ziaka;

ID_ziaka	Meno_ziaka	Priezvisko	Rocnik_ziaka	Datum_narodenia
3	Dávid	Petrovič	4	1.12.2005
2	Michal	Boža	3	18.3.2008
4	Monika	Kasová	4	7.11.2006

A teraz si môžete vyskúšať vyriešiť podúlohy d) a e). Ak sa budete v úlohe e) príliš trápiť, prečítajte si najskôr nasledujúcu časť.

Spájanie tabuliek

Najskôr si vytvoríme 2 veľmi krátke tabuľky, ktoré sa nám budú pre túto úlohu veľmi hodiť. Prvá tabuľka sa vola mena:

Druhá tabuľka sa vola cisla:

Čo by sa stalo, ak by sme spustili výraz SELECT * from cisla, mena? Tento príkaz by skombinoval každý riadok z tabuľky cisla a spojil ho s každým riadkom v tabuľke mena a zobrazil ich spolu v jednom riadku. Vyzeralo by to asi takto:

Táto operácia v podstate kombinuje všetky riadky v prvej tabuľke s riadkami z druhej tabuľky. Nazýva sa kartézsky súčin. A pýtate sa načo nám to je? Potom, čo tabuľky spojíme, môžeme zadávať podmienky na kombinácie týchto riadkov. Uvidíte neskôr :).

Vytvorme si teraz ešte jednu tabuľku znamky, kde budeme zaznamenávať známky. Pre každú známku si budeme pamätať, kto ju dostal a z akého predmetu bola (a samozrejme každá známka musí mať svoje unikátne ID, pretože tu môže ľahko nastať situácia, kedy má jeden žiak viacero rovnakých známok z toho istého predmetu - záznamy by boli potom identické a systém by ich nevedel od seba odlíšiť.

Údaj o tom, kto danú známku dostal, si nemôžeme pamätať menom, lebo sa môže veľmi ľahko stať, že dvaja žiaci budú mať rovnaké meno (aj priezvisko) - preto si tento údaj musíme pamätať niečím unikátnym. Presne na takéto účely používame ID objektu(v tomto prípade ID_ziaka).

Predmet známky by sme tiež mali ukladať pomocou ID, ale kvôli prehľadnosti nám teraz postačí jeho názov a vedomie, že na škole nie sú dva predmety s rovnakým názvom (ak by boli, potom by sa nám známky z týchto predmetov pomiešali).

ID_znamky	Znamka	ID_ziaka	Predmet
0	5	0	Technická výchova
1	1	1	Matematika
2	1	3	Matematika
3	3	1	Občianska náuka
4	1	4	Biológia
5	2	3	Technická výchova
6	1	0	Chémia

Teraz by sme mohli chcieť zistiť napr. aké známky dostal Peter. Na to, aby sme to vedeli urobiť sami by sme sa najskôr potrebovali pozrieť do tabuľky ziaci a zistiť, aké ID má Peter. To si zapamätať, prejsť do tabuľky znamky a hľadať všetky známky, ktoré majú ID_ziaka také isté, ako Peter. To ale určite zakaždým robiť nechceme a práve na to existuje spôsob, ako tabuľky spájať dokopy.

Ak by ste sa chceli o tejto časti dozvedieť podrobnejšie, ďalšie informácie pribudnú časom na konci tutorialu. Odporúčame si ich prečítať, ak by ste sa chystali naozaj začať robiť niečo s databázami :). Na vyriešenie úlohy v Prasku Vám to ale netreba.

Ako by teda vyzeral príkaz, ktorý spojí naše dve tabuľky? Asi takto:
SELECT * FROM ziaci, znamky WHERE znamky.ID_ziaka = ziaci.ID_ziaka;

znamky. ID_znamky	znamky. Znamka	znamky. ID_ziaka	znamky. Predmet	ziaci. ID_ziaka	ziaci. Meno_ziaka	ziaci. Priezvisko	ziaci. Rocník_ ziaka	ziaci. Datum_ narodenia
0	5	0	Technická výchova	0	Peter	Rusnák	2	1.12.2005
1	1	1	Matematika	1	Monika	Králová	2	1.12.2005
2	1	3	Matematika	3	Dávid	Petrovič	4	1.12.2005
3	3	1	Občianska náuka	1	Monika	Králová	2	1.12.2005
4	1	4	Biológia	3	Dávid	Petrovič	4	1.12.2005
5	2	3	Technická výchova	3	Dávid	Petrovič	4	1.12.2005
6	1	0	Chémia	0	Peter	Rusnák	2	1.12.2005

Ako vidíte, museli sme špecifikovať, ktoré stĺpce tabuliek sa majú rovnať (ID_ziaka). Niekedy nemusíme chcieť vypísať z obidvoch tabuliek všetky dáta. Tak ako predtým môžeme špecifikovať, ktoré stĺpce chceme zobraziť. Avšak ak majú stĺpce v obidvoch tabuľkách rovnaké mená, môže dôjsť ku kolízii, preto je lepšie špecifikovať názov, tak, že najskôr napíšeme meno tabuľky, ktorej ten stĺpec má patriť a bodku, napr:

SELECT znamky.ID_znamky, znamky.Znamka, znamky.Predmet, ziaci.Meno_ziaka, ziaci.ID_ziaka

FROM znamky, ziaci

WHERE znamky.ID_ziaka=ziaci.ID_ziaka;

Spájať samozrejme môžete aj viac ako 2 tabuľky :).

A teraz ste pripravení riešiť úlohy f)-h). Veľa šťastia :).

Agregačné funkcie

Nezľaknite sa toho hrozivého názvu, agregačné funkcie sú veľmi užitočné a uľahčia vám veľa práce s databázami :). Sú tu funkcie, ktoré vedia zobrať nejaký stĺpec a z jeho dát niečo výratať, napr. priemer. Agregačné funkcie, ktoré sa Vám môžu hodiť sú:

• AVG() - zoberie všetky hodnoty v stĺpci a spraví z nich (aritmetický) priemer. (ak slovo aritmetický nepoznáte, nevšímajte si ho)

• COUNT() - spočíta počet riadkov v stĺpci

• FIRST() - vráti prvú hodnotu v stĺpci

• LAST() - vráti poslednú hodnotu v stĺpci

• MAX() - vráti najväčšiu hodnotu v stĺpci

• MIN() - vráti najmenšiu hodnotu v stĺpci

Začneme použitím funkcie AVG.

SELECT AVG(stlpec) AS pomenovanie FROM tabulka;

Tento príkaz zoberie dáta v stlpci stlpec z tabuľky tabulka a vypočíta jeho priemer, ktorému potom priradí názov pomenovanie.

Príklad: povedzme, že chceme zistiť priemer všetkých známok v našej databáze. Použijeme príkaz

SELECT AVG(Znamka) AS Priemerna_znamka FROM znamky;

Tento príkaz v podstate hovorí: zober všetky hodnoty známok, ktoré máš v tabuľke znamky, spriemeruj ich a túto hodnotu pomenuj Priemerna_znamka.

Priemerna_znamka

2

Už vieme, aká je priemerná známka všetkých študentov, ale možno by sme chceli vedieť priemernú známku pre jedného študenta. Na to slúži príkaz GROUP BY, vďaka ktorému bude agregačná funkcia vedieť, že má robiť priemer iba pre jedného študenta. Za GROUP BY zadávame názvy stĺpcov, podľa ktorých chceme riadky združovať dokopy. Príkaz potom ako keby najskôr zoskupí k sebe všetky riadky, ktoré majú rovnakú hodnotu stĺpca, ktorý sme uviedli za GROUP BY a následne vykoná zadanú agregačnú funkciu v rámci každej skupiny. Ukážme si príklad:

SELECT AVG(Znamka) AS Priemerna_znamka, Meno_ziaka, Priezvisko

FROM znamky, ziaci

WHERE znamky.ID_ziaka=ziaci.ID_ziaka

GROUP BY Meno_ziaka, Priezvisko;

Priemerna_známka	ziaci. Meno_ziaka	ziaci. Priezvisko
2.5	Peter	Rusnák
2	Monika	Králová
1.33	Dávid	Petrovič

Tento príkaz zoberie najskôr všetky riadky v tabuľkách znamky a ziaci a pospája ich dokopy tak, aby ID_ziaka zodpovedalo. To znamená, že dostaneme jednu veľkú tabuľku, kde o každej známke budeme vedieť aj ktorému žiakovi patrí a všetky vlastnosti tohto žiaka. Následne sa tieto riadky združia podľa parametrov v GROUP BY, teda podľa Meno_ziaka a Priezvisko. Takto sa vytvoria skupiny riadkov (známok), každá skupina obsahuje všetky známky jedného študenta. Následne sa vykoná SELECT, v ktorom sa vyberie z každej skupiny Meno_ziaka a Priezvisko a hodnoty známok v tejto skupine sa zpriemerujú - zistíme priemernú hodnotu známok, ktoré dostal tento študent.

Všimnite si, že v SELECT sme vybrali iba stĺpce, na ktoré sa použili agregačné funkcie alebo sa použili v GROUP BY. Čo by sa stalo, ak by sme napríklad napísali príkaz:

SELECT AVG(Znamka) AS Priemerna_znamka, Meno_ziaka, Priezvisko, znamky.Predmet

FROM znamky, ziaci

WHERE znamky.ID_ziaka = ziaci.ID_ziaka

GROUP BY Meno_ziaka, Priezvisko;

Príkaz by nevedel čo od neho chceme. V skupine žiaka je totiž veľa riadkov a každý z tých riadkov môže mať inú hodnotu znamky.Predmet. Pri Meno_ziaka a Priezvisko je to ľahké, lebo tie sú v skupine rovnaké. Pre hodnotu Znamka je to tiež v poriadku, lebo tam mu vysvetlíme, že z tých veľa hodnôt, čo má v skupine má spraviť priemer. Ale čo má spraviť s predmetom počítač netuší. Preto ak používame GROUP BY, tak v časti SELECT sa môžu buď nachádzať stĺpce z časti GROUP BY alebo na ne musíme použíť agregačné funkcie.

Priemerna_znamka	ziaci. Meno_ziaka	ziaci. Priezvisko	znamky.Predmet
2.5	Peter	Rusnák	????????
2	Monika	Králová	????????
1.33	Dávid	Petrovič	????????

Predošlá tabuľka by sa pravdepodobne nikdy nevypísala, namiesto nej by sme dostali chybovú hlášku, ale vďaka nej môžete vidieť, ako by asi nad vašim príkazom počítač rozmýšlal :).

Nakoniec ešte môžeme chcieť vypísať iba študentov, ktorí majú priemernú známku menšiu ako 2. Asi by ste skúsili napísať niečo takéto:

SELECT AVG(Znamka) AS Priemerna_znamka, Meno_ziaka, Priezvisko, znamky.Predmet

FROM znamky, ziaci

WHERE znamky.ID_ziaka = ziaci.ID_ziaka AND AVG(Znamka)<2

GROUP BY Meno_ziaka, Priezvisko;

tento príkaz však vyhodí chybu. Pokiaľ totiž chceme stanoviť nejakú podmienku pre výsledok agregačnej funkcie, nepoužívame príkaz WHERE ale príkaz HAVING. Správny príkaz by mal vyzerať takto:

SELECT AVG(Znamka) AS Priemerna_znamka, Meno_ziaka, Priezvisko

FROM znamky, ziaci

WHERE znamky.ID_ziaka = ziaci.ID_ziaka

GROUP BY Meno_ziaka, Priezvisko

HAVING AVG(Znamka)<2;

Priemerna_znamka	ziaci. Meno_ziaka	ziaci. Priezvisko
1.33	Dávid	Petrovič

Ak ste sa dostali v tutoriali až sem, môžete si vydýchnuť. Je to za vami :). Teraz by ste už mali byť schopní riešiť všetky podúlohy.