ÚINF/DBS1b/15 Databázové systémy

Úvod

Študent po absolvovaní predmetu zvládne použitie náročnejších techník relačných databáz, teoretickú analýzu funkčných závislostí atribútov a je schopný pracovať s nerelačnými databázami.

Informačný list predmetu

ÚINF/DBS1a/15 Databázové systémy

Vysoká škola: Univerzita P. J. Šafárika v Košiciach
Fakulta: Prírodovedecká fakulta
Kód predmetu: ÚINF/DBS1a/15
Názov predmetu: Databázové systémy
Druh, rozsah a metóda vzdelávacích činností:
Forma výučby: Prednáška / Cvičenie
Odporúčaný rozsah výučby ( v hodinách ):
Týždenný: 2 / 2
Za obdobie štúdia: 28 / 28
Metóda štúdia: prezenčná
Počet ECTS kreditov: 5
Odporúčaný semester/trimester štúdia: 3.
Stupeň štúdia: I.
Podmieňujúce predmety:
Podmienky na absolvovanie predmetu:
Preukázanie primeraného zvládnutia obsahového štandardu predmetu pri priebežnom a záverečnom hodnotení, schopnosť sformulovať problém v osvojenej terminológii a riešiť ho v rámci projektu.
Písomná previerka v priebehu semestra, zadanie.
Skúška písomná a ústna.
Výsledky vzdelávania:
Študent po absolvovaní predmetu ovláda princípy relačných databáz, je schopný aplikovať štandardné dátové modely, navrhovať relačné databázy a sformulovať filtračné dopyty.
Stručná osnova predmetu:
1) Relačné databázy a jazyk SQL, filtrácia.
2) Dátové typy, operátory, funkcie numerické, reťazcové a časové.
3) JOIN.
4) Agregácia a GROUP BY.
5) Dátové a databázové modely. Relačná schéma. Princípy RDB. Integrita dát.
6) Návrh DB, ER diagramy.
7) Systémové príkazy o DB a tabuľkách. Kaskádovité mazanie a aktualizácia.
8) Vnorené dopyty. ROLLUP. CASE výraz.
9) Trojhodnotová logika. Kvantifikátory a NOT. Množinové operácie.
10) Data science a získavanie znalostí pomocou R.
11) Dátové sklady. Dátová kocka. Pivot tabuľky.
12) Normalizácia relačných databáz - 1. Relačná algebra.
Odporúčaná literatúra:
C.J. Date, Database Design and Relational Theory, 2012, O’Reilly Media, Inc., ISBN: 978-1-449-32801-6
J. Murach, Murach's MySQL, 3rd Edition, 2019, Mike Murach & Associates, Inc., ISBN-10: 1943872368
R. Ramakrishnan, J. Gehrke, Database Management Systems, 2020, McGraw-Hill, ISBN139780071231510
S. Krajči: Databázové systémy, UPJŠ, 2005
S. Krajči: Databázy, UPJŠ, 2020 - materiál
Poznámky:Prezenčná alebo online výuka.
Vyučujúci:
doc. RNDr. Csaba Török, CSc.
RNDr. Lukáš Miňo, PhD.

ÚINF/DBS/15 Databázové systémy pre matematikov

Vysoká škola: Univerzita P. J. Šafárika v Košiciach
Fakulta: Prírodovedecká fakulta
Kód predmetu: ÚINF/DBS/15
Názov predmetu: Databázové systémy pre matematikov
Druh, rozsah a metóda vzdelávacích činností:
Forma výučby: Prednáška / Cvičenie
Odporúčaný rozsah výučby ( v hodinách ):
Týždenný: 3 / 2
Za obdobie štúdia: 42 / 28
Metóda štúdia: prezenčná
Počet ECTS kreditov: 6
Odporúčaný semester/trimester štúdia: 1., 3.
Stupeň štúdia: I., II.
Podmieňujúce predmety:
Podmienky na absolvovanie predmetu:
Preukázanie primeraného zvládnutia obsahového štandardu predmetu pri priebežnom a záverečnom hodnotení, schopnosť sformulovať problém v osvojenej terminológii a riešiť ho v rámci projektu.
Písomná previerka v priebehu semestra, zadanie.
Skúška písomná a ústna.
Výsledky vzdelávania:
Študent po absolvovaní predmetu ovláda princípy relačných databáz, je schopný aplikovať štandardné dátové modely, navrhovať relačné databázy a sformulovať filtračné dopyty.
Stručná osnova predmetu:
1) Relačné databázy. Dopytovací jazyk SQL, filtrácia; Uložené procedúry.
2) Dátové typy, operátory, funkcie numerické, reťazcové a časové; Funkcie systémové a užívateľské.
3) JOIN operácie; Pohľady. CTE.
4) Agregácia a GROUP BY; Rekurzia a transitívny uzáver.
5) Dátové a databázové modely. Relačná schéma. Princípy RDB. Integrita dát; Transakcie.
6) Návrh DB, ER diagramy; Triggery a integrita.
7) Systémové príkazy o DB a tabuľkách. Kaskádovité mazanie a aktualizácia; Kurzory.
8) Vnorené dopyty. ROLLUP. CASE výraz; Fyzická organizácia dát.
9) Trojhodnotová logika. Kvantifikátory a NOT. Množinové operácie; B-stromy a indexy.
10) Data science a získavanie znalostí pomocou R. Funkčné závislosti.
11) Dátové sklady. Dátová kocka. Pivot tabuľky.
12) Normalizácia relačných databáz; Najnovšia normálna forma - ETNF. Relačná algebra.
Odporúčaná literatúra:
C.J. Date, Database Design and Relational Theory, 2012, O’Reilly Media, Inc., ISBN: 978-1-449-32801-6
J. Murach, Murach's MySQL, 3rd Edition, 2019, Mike Murach & Associates, Inc., ISBN-10: 1943872368
R. Ramakrishnan, J. Gehrke, Database Management Systems, 2020, McGraw-Hill, ISBN139780071231510
S. Krajči: Databázové systémy, UPJŠ, 2005
S. Krajči: Databázy, UPJŠ, 2020 - materiál
I. Ben-Gan, D. Sarka, A. Machanic, K. Farlee, T-SQL Querying, 2015, Microsoft Press, ISBN: 978-0-7356-8504-8
I. Ben-Gan, T-SQL Fundamentals, Third Edition, 2016, Microsoft Press, ISBN: 978-1-5093-0200-0
Poznámky:Prezenčná alebo online výuka.
Vyučujúci:
doc. RNDr. Csaba Török, CSc.
RNDr. Lukáš Miňo, PhD.

Informačný list predmetu

ÚINF/DBS1b/15 Databázové systémy

Vysoká škola: Univerzita P. J. Šafárika v Košiciach
Fakulta: Prírodovedecká fakulta
Kód predmetu: ÚINF/DBS1b/15
Názov predmetu: Databázové systémy
Druh, rozsah a metóda vzdelávacích činností:
Forma výučby: Prednáška / Cvičenie
Odporúčaný rozsah výučby ( v hodinách ):
Týždenný: 2 / 2
Za obdobie štúdia: 28 / 28
Metóda štúdia: prezenčná
Počet ECTS kreditov: 6
Odporúčaný semester/trimester štúdia: 4.
Stupeň štúdia: I.
Podmieňujúce predmety: ÚINF/DBS1a/15
Podmienky na absolvovanie predmetu:
Preukázanie primeraného zvládnutia obsahového štandardu predmetu pri priebežnom a záverečnom hodnotení, schopnosť sformulovať problém v osvojenej terminológii a riešiť ho v rámci projektu. Písomná previerka v priebehu semestra, zadanie.
Skúška písomná a ústna.
Výsledky vzdelávania:
Študent po absolvovaní predmetu zvládne použitie náročnejších techník relačných databáz, teoretickú analýzu funkčných závislostí atribútov a je schopný pracovať nerelačnými databázami.
Stručná osnova predmetu:
1) Úvod do SQL Server. Množinové operácie. Window functions.
2) Uložené procedúry. Funkcie systémové a užívateľské.
3) Pohľady. CTE, rekurzia a transitívny uzáver.
4) Transakcie. Kurzory. Pivot.
5) Triggery a integrita. Fyzická organizácia dát, B-stromy a indexy.
6) XML dokumenty a ich dopytovanie. JSON.
7) Funkčné závislosti a NF.
8) Najnovšia normálna forma - ETNF.
9) Big data a NoSQL.
10) MongoDB, CRUD a kurzory.
11) Agregácie a indexy.
12) Replikácia a sharding.
Odporúčaná literatúra:
C.J. Date, Database Design and Relational Theory, 2012, O’Reilly Media, Inc., ISBN: 978-1-449-32801-6
J. Murach, Murach's MySQL, 3rd Edition, 2019, Mike Murach & Associates, Inc., ISBN-10: 1943872368
R. Ramakrishnan, J. Gehrke, Database Management Systems, 2020, McGraw-Hill, ISBN139780071231510
S. Krajči: Databázové systémy, UPJŠ, 2005
S. Krajči: Databázy, UPJŠ, 2020 - materiál
Poznámky:Prezenčná alebo online výuka.
Vyučujúci:
doc. RNDr. Csaba Török, CSc.
RNDr. Lukáš Miňo, PhD.

Motivácia k pokračovaniu

Dotazník

Neoficiálne hodnotenie pre interné účely Lukáša Miňa Oficiálny dotazník (ohodnoďte osobitne prednášku a cvičenie) pre celkové hodnotenie v rámci fakulty

Kontakt

Prednášajúci

doc. RNDr. Csaba Török, CSc.
csaba.torok@upjs.sk

Cvičiaci

RNDr. Lukáš Miňo, PhD.
lukas.mino@upjs.sk

Harmonogram

Prednáška

Čas: Streda 10:45 - 12:15
Miestnosť: SJ2P11 - Jesenná 5, 2. poschodie, poslucháreň

Cvičenie

Čas: Štvrtok 07:05 - 08:35
Miestnosť: SA1C03 - Park Angelinum 9, 1. poschodie, počítačová učebňa

Hodnotenie

Cvičenie

Domáce úlohy: 6 bodov
Priebežné hodnotenia: 4 body
Polsemestrálne hodnotenie: 20 bodov
Koncosemestrálne hodnotenie: 20 bodov
Aktívne zapájanie sa do výučby: body navyše

Skúška

Skúška: 50 bodov

Projekt

Zadanie robia iba tí študenti, ktorí sa NEzúčastnili obhajob návrhov projektov z predmetu PAZ1c alebo na prednáške predmetu DBS (matematici). Riešenie projektu pozostáva z nasledujúcich úloh:

  • Načrtnite entitno relačný diagram vašej databázy, kde navrhnete databázu s aspoň 4 entitnými tabuľkami, ktoré budú vhodne prepojené cudzími kľúčmi priamo, alebo pomocou prepájacích tabuliek. Pod pojmom entitná tabuľka budeme rozumieť tabuľku, ktorá má primárny kľúč a aspoň jeden stĺpec, ktorý nie je súčasťou primárneho kľúča.
  • Tabuľky naplnite vhodnými ilustračnými dátami (t.j. riadkami).
  • Vymyslite aspoň tri vhodné dopyty tak, aby sa aspoň v jednom použilo prepojenie dvoch rôznych tabuliek a aspoň v jednom ďalšom použilo prepojenie minimálne troch rôznych tabuliek.

Software

Microsoft SQL Server

MongoDB + MongoDB Compass

Prednáška

Domáca úloha

    1. Napíšte SQL dopyt/dopyty a pokúste sa ho previesť do relačnej algebry. Použite aspoň 5 rôznych operátorov. (1 bod)
    2. Vymyslíte si príklad (môžete sa inšpirovať vlastným projektom z minulého semestra), kde demonštrujete normalizáciu tabuľky/tabuliek do 1NF, 2NF, 3NF... (1 bod)
    3. Napíšte aspoň 10 dopytov pre MySQL a MS SQL, kde demonštrujete rôzne zápisy dopytov v MySQL a MS SQL. (1 bod)
    4. Prepíšte procedúru usp_create_table ako funkciu. Vymyslíte si praktický príklad pre užívateľky uloženú procedúru (user stored procedure), užívateľskú skalárnu funkciu (Scalar Function), inline tabuľkovú funkciu bez deklarácie stĺpcov tabuľky (inline table-valued function), tabuľkovú funkciu s definovanými stĺpcami (table-valued-function). (1 bod)
    5. Uvažujme databázu OsobaVztah. Vytvorte pohľad (VIEW), ktorý bude mať stĺpce id osoby, plné meno osoby, plné meno matky, plné meno otca. Ak otec alebo matka nie su uvedení, tak v danej bunke bude NULL hodnota. (1 bod)
    6. Uvažujme databázu EShop a v nej tabuľku Logistika. Nižšie nakreslený graf znázorňuje logistiku skladov, kde ktorý sklad aké sklady zásobuje. Z daneho grafu vytvorte SQL dopyt obsahujúci tranzitívny uzáver (1 bod) závislosti skladov pomocou rekurzie. Napríklad jeden riadok v tabuľke bude mať tvar
      KE → GL → PO
      Graf logistiky
      1. Uvažujme databázu EShop. Vytvorte a naplňte tabuľku Ubytovanie, kde bude id, meno, priezvisko, pohlavie zakazníka a čislo izby na ktorej bude ubytovaný. Pomocou kurzora (0.5 boda) priraďte zakaznikov do izieb, pričom musí platiť, že na izbe môžu byť len trojice rovnakého pohlavia. Jeden riadok zo záznamu môže vyzerať nasledovne
        idZ Meno Priezvisko Pohlavie Izba
        2 Ruzena Sipka z 5
      2. Uvažujme databázu EShop. Vypočítajte počet nákupov mužov a žien v jednotlivých dňoch v týždni. Záznamy, kde je NULL hodnota nahraďte nulou. Výsledná pivot tabuľka (0.5 boda) by mala vyzerať nasledovne
        den muz zena
        pondelok 2 2
        utorok 1 2
        streda 1 2
        štvrtok 2 1
        piatok 2 1
        sobota 2 0
        nedeľa 3 1
      1. Uvažujme databázu EShop. Vytvorte jeden trigger, ktorý v tabuľke Tovar aktualizuje počet tovarov na sklade, kde zabezpečíte, aby sa pri nákupe daných kusov správne odrátal/prirátal ich počet zo skladu tovarov pri vlození/aktualizácii/zmazaní nákupu. V prípade ak počet kusov pri nákupe presiahne počet kusov tovarov na sklade, informujte o tom zákazníka, že daný počet kusov na sklade nie je (v tomto prípade počet kusov zo skladu samozrejme neodrátate). Skúste uviesť príklad na úspešný a neúspešný nákup. (0.5 boda)
      2. Vytvorte tabuľku, kde bude milión náhodných čisel v hodnote od 0 po miliardu. Zmerajte čas dopytu, kde zistite najväčšie číslo v tabuľke. Nad tabuľkou vytvorte najprv neklastrovaný, potom klastrovaný index a meranie svojho dopytu zopakujte. Svoje namerané riešenia zdôvodnite. (0.5 boda)
      1. Uvažujme databázu OsobaVztah. Nájdite tretiu najvyššiu osobu pomocou MIN/MAX, pomocou ALL a pomocou WINDOW funkcie (0.3 bodov)
      2. Vytvorte jednoduchý well-formed XML dokument, ktorý bude obsahovať označkované údaje z nejakej domény (oblasti). Príklady domén: zoznam študentov zapísaných na predmete s ich hodnotením, ponuka jedál v reštaurácii, súbor faktúr alebo iných účtovných dokumentov, katalóg filmov so žánrovým zaradením, obsadením a hodnotením, ... (očakáva sa, že si vymyslíte vlastnú doménu, ku ktorej máte nejakým spôsobom blízko).
        Well-formed znamená, že vo vytvorenom dokumente budú dodržané syntaktické pravidlá XML, t.j. že prejde validátorom na stránke: http://www.w3schools.com/xml/xml_validator.asp
        Nad daným XML dokumentom vytvorte XSD s nejakými obmedzeniami.
        Vytvorte tabuľku, ktorá bude ukladať XML pre dané XSD.
        Skúste vložiť aspoň 3 validné záznamy a demonštrujte pokus aj o vloženie nevalidného záznamu.
        Napíšte aspoň 3 XQuery dopyty nad daným XML. (0.7 bodov)
    10. Pripravte si vhodnú databázu v MongoDB, kde budú objekty, polia aj objekty v rámci objektov. Vložte do nej aspoň 10 záznamov pomocou insertMany. Napíšte aspoň 10 dopytov, kde demonštrujete aktualizáciu, zmazanie, základné vyhľadávania aj agregácie. (1 bod)

Sylaby štátnicového predmetu

Cvičenie

1. cvičenie

Ciele

  • Relačná algebra
  • Funkčné závislosti

Materiál na študovanie

Relačná algebra

Funkčné závislosti

Domáca úloha

  • Napíšte SQL dopyt/dopyty a pokúste sa ho previesť do relačnej algebry. Použite aspoň 5 rôznych operátorov. (1 bod)

2. cvičenie

Ciele

  • Normálne formy

Materiál na študovanie

Domáca úloha

  • Vymyslíte si príklad (môžete sa inšpirovať vlastným projektom z minulého semestra), kde demonštrujete normalizáciu tabuľky/tabuliek do 1NF, 2NF, 3NF... (1 bod)

3. cvičenie

Ciele

  • Úvod do MS SQL Server

Softvér

Databáza

Tutoriál

MySQL

Microsoft SQL Server

Dokumentácia

MySQL

Microsoft SQL Server

Riešenia z cvičení

Domáca úloha

  • Napíšte aspoň 10 dopytov pre MySQL a MS SQL, kde demonštrujete rôzne zápisy dopytov v MySQL a MS SQL. (1 bod)

4. cvičenie

Ciele

  • Reťazcové príkazy
  • Uložená procedúra
  • Funkcia
  • Systémové pohľady
  • SysColumns
  • Ošetrenie chýb

Dokumentácia

Uložená procedúra

Funkcia

Systémové pohľady

Ošetrenie chýb

Riešenie

Domáca úloha

  • Prepíšte procedúru usp_create_table ako funkciu. Vymyslíte si praktický príklad pre užívateľky uloženú procedúru (user stored procedure), užívateľskú skalárnu funkciu (Scalar Function), inline tabuľkovú funkciu bez deklarácie stĺpcov tabuľky (inline table-valued function), tabuľkovú funkciu s definovanými stĺpcami (table-valued-function). (1 bod)

5. cvičenie

Ciele

  • VIEW
  • WITH

Dokumentácia

Riešenie

Domáca úloha

  • Uvažujme databázu OsobaVztah. Vytvorte pohľad (VIEW), ktorý bude mať stĺpce id osoby, plné meno osoby, plné meno matky, plné meno otca. Ak otec alebo matka nie su uvedení, tak v danej bunke bude NULL hodnota. (1 bod)

6. cvičenie

Ciele

  • Tranzitívny uzáver a rekurzia

Dokumentácia

Riešenie

Domáca úloha

  • Uvažujme databázu EShop a v nej tabuľku Logistika. Nižšie nakreslený graf znázorňuje logistiku skladov, kde ktorý sklad aké sklady zásobuje. Z daneho grafu vytvorte SQL dopyt obsahujúci tranzitívny uzáver (1 bod) závislosti skladov pomocou rekurzie. Napríklad jeden riadok v tabuľke bude mať tvar
    KE → GL → PO
    Graf logistiky

7. cvičenie

Ciele

  • Transakcia
  • Kurzor
  • Pivot tabuľka

Dokumentácia

Transakcia

Kurzor

Pivot tabuľka

Riešenie

Domáca úloha

  • Uvažujme databázu EShop. Vytvorte a naplňte tabuľku Ubytovanie, kde bude id, meno, priezvisko, pohlavie zakazníka a čislo izby na ktorej bude ubytovaný. Pomocou kurzora (0.5 boda) priraďte zakaznikov do izieb, pričom musí platiť, že na izbe môžu byť len trojice rovnakého pohlavia. Jeden riadok zo záznamu môže vyzerať nasledovne
    idZ Meno Priezvisko Pohlavie Izba
    2 Ruzena Sipka z 5
  • Uvažujme databázu EShop. Vypočítajte počet nákupov mužov a žien v jednotlivých dňoch v týždni. Záznamy, kde je NULL hodnota nahraďte nulou. Výsledná pivot tabuľka (0.5 boda) by mala vyzerať nasledovne
    den muz zena
    pondelok 2 2
    utorok 1 2
    streda 1 2
    štvrtok 2 1
    piatok 2 1
    sobota 2 0
    nedeľa 3 1

8. cvičenie

Ciele

  • Triggery
  • B-stromy
  • Indexy

Dokumentácia

Triggery

Indexy

B-stromy

Indexy

Riešenie

Domáca úloha

  • Uvažujme databázu EShop. Vytvorte jeden trigger, ktorý v tabuľke Tovar aktualizuje počet tovarov na sklade, kde zabezpečíte, aby sa pri nákupe daných kusov správne odrátal/prirátal ich počet zo skladu tovarov pri vlození/aktualizácii/zmazaní nákupu. V prípade ak počet kusov pri nákupe presiahne počet kusov tovarov na sklade, informujte o tom zákazníka, že daný počet kusov na sklade nie je (v tomto prípade počet kusov zo skladu samozrejme neodrátate). Skúste uviesť príklad na úspešný a neúspešný nákup. (0.5 boda)
  • Vytvorte tabuľku, kde bude milión náhodných čisel v hodnote od 0 po miliardu. Zmerajte čas dopytu, kde zistite najväčšie číslo v tabuľke. Nad tabuľkou vytvorte najprv neklastrovaný, potom klastrovaný index a meranie svojho dopytu zopakujte. Svoje namerané riešenia zdôvodnite. (0.5 boda)

9. cvičenie

Ciele

  • Polsemestrálna previerka
    • Relačná algebra
    • Funkčné závislosti
    • Normálne formy
    • Procedúry, funkcie, ošetrenie chýb
    • VIEW, WITH
    • Tranzitívny uzáver a rekurzia
    • Tranzakcie, kurzory, pivot tabuľky

10. cvičenie

Ciele

  • Aktívna/pasívna účasť na Prírodovedeckých dňoch

Dokumentácia

Úloha

  • Aktívna/pasívna účasť na Prírodovedeckých dňoch

11. cvičenie

Ciele

  • Window funkcie
  • Možnosti administrácie SQL Servera
  • XML
  • JSON
  • Iné

Dokumentácia

Window funkcie

Možnosti administrácie SQL Servera

XML

XPath

XQuery

XSD

JSON

Riešenie

Window funkcie

XML

Domáca úloha

  • Uvažujme databázu OsobaVztah. Nájdite tretiu najvyššiu osobu pomocou MIN/MAX, pomocou ALL a pomocou WINDOW funkcie (0.3 bodov)
  • Vytvorte jednoduchý well-formed XML dokument, ktorý bude obsahovať označkované údaje z nejakej domény (oblasti). Príklady domén: zoznam študentov zapísaných na predmete s ich hodnotením, ponuka jedál v reštaurácii, súbor faktúr alebo iných účtovných dokumentov, katalóg filmov so žánrovým zaradením, obsadením a hodnotením, ... (očakáva sa, že si vymyslíte vlastnú doménu, ku ktorej máte nejakým spôsobom blízko).
    Well-formed znamená, že vo vytvorenom dokumente budú dodržané syntaktické pravidlá XML, t.j. že prejde validátorom na stránke: http://www.w3schools.com/xml/xml_validator.asp
    Nad daným XML dokumentom vytvorte XSD s nejakými obmedzeniami.
    Vytvorte tabuľku, ktorá bude ukladať XML pre dané XSD.
    Skúste vložiť aspoň 3 validné záznamy a demonštrujte pokus aj o vloženie nevalidného záznamu.
    Napíšte aspoň 3 XQuery dopyty nad daným XML. (0.7 bodov)

12. cvičenie

Ciele

  • MongoDB

Softvér

Databáza

JSON

Tutoriál

Dokumentácia

Zaujímavé linky

Riešenia z cvičení

Domáca úloha

  • Pripravte si vhodnú databázu v MongoDB, kde budú objekty, polia aj objekty v rámci objektov. Vložte do nej aspoň 10 záznamov pomocou insertMany. Napíšte aspoň 10 dopytov, kde demonštrujete aktualizáciu, zmazanie, základné vyhľadávania aj agregácie. (1 bod)

13. cvičenie

Ciele

  • Koncosemestrálna previerka
    • Tranzitívny uzáver a rekurzia
    • Kurzory
    • Triggery
    • B-stromy
    • Indexy
    • Window funkcie
    • XML
    • JSON
    • MongoDB

14. cvičenie

Ciele

  • Opravný zápočet
  • Konzultácie
  • Vyhodnotenie semestra

14. cvičenie

Ciele

  • MongoDB

Dokumentácia

  • ...

Riešenia z cvičení

  • ...

Domáca úloha

  • ...

5. cvičenie

Ciele

  • Agregačné funkcie

Zadania

Po úspešnom absolvovaní predchádzajúcich týždoch Vás presunuli na medicínsky projekt. Dostali ste za úlohu spravovať databázu polikliniky. Importnite si ju a zoznámte sa s jej štruktúrou.

1. zadanie

Zobrazte informácie o pacientoch.

Návody
Kľučové slová
  • SELECT
Riešenie 
                                 
-- Informacie o pacientoch
SELECT 
   *
FROM pacienti;
2. zadanie

Zobrazte len pacientov, ktorí majú príjem nad 10000.

Návody
Kľučové slová
  • SELECT
  • WHERE
Riešenie 
                                 
-- Informacie o pacientoch
SELECT 
   *
FROM pacienti
WHERE mesPrijem > 10000;
3. zadanie

Zobrazte pacientov, ktorí majú uvedený príjem.

Návody
Kľučové slová
  • SELECT
  • WHERE
  • IS NOT NULL
Riešenie 
                                 
-- Informacie o pacientoch, ktori maju mes. prijem uvedeny
SELECT 
   *
FROM pacienti
WHERE mesPrijem IS NOT NULL;
4. zadanie

Zobrazte najväčší mesačný príjem spomedzi pacientov.

Návody
Kľučové slová
  • MAX
Riešenie 
                                 
-- Maximalny mesacny prijem
SELECT 
   MAX(mesPrijem)
FROM pacienti;
5. zadanie

Zobrazte meno pacienta, ktorý ma najväčší mesačný príjem.

Návody
Kľučové slová
  • MAX
Riešenie 
                                 
-- Meno pacienta, ktory ma mes. prijem 35000
SELECT 
   krstne
FROM pacienti
WHERE mesPrijem = 35000;

-- Meno pacienta, ktory ma najvacsi mesacny prijem
SELECT 
   krstne 
FROM pacienti
WHERE mesPrijem = (SELECT MAX(mesPrijem) FROM pacienti);
6. zadanie

Zistite koľko návštev mal lekár s id = 3.

Návody
Kľučové slová
  • COUNT
Riešenie 
                                 
-- Pocet navstev u lekara s id = 3
SELECT 
   COUNT(*) 'Počet návštev' 
FROM navstevy
WHERE idL = 3;
7. zadanie

Zobrazte počet návštev u jednotlivých lekárov.

Návody
Kľučové slová
  • COUNT
Riešenie 
                                 
-- Pocet navstev u jednotlivych lekarov
SELECT 
   idL, 
   COUNT(*) 'Počet návštev' 
FROM navstevy
GROUP BY idL;
8. zadanie

Zobrazte koľko rôznych pacientov navštívilo jednotlivých lekárov.

Návody
Kľučové slová
  • COUNT
  • DISTINCT
Riešenie 
                                 
-- Pocet roznych pacientov u jednotlivych lekarov
SELECT 
   idL, 
   COUNT(DISTINCT idP) 'Počet pacientov' 
FROM navstevy
GROUP BY idL;
9. zadanie

Zobrazte lekárov a počet návštev, ktorí mali viac ako 3 návštevy.

Návody
Kľučové slová
  • COUNT
Riešenie 
                                 
-- Pocet navstev u jednotlivych lekarov, ktori mali viac ako 3 navstevy
SELECT 
   idL, 
   COUNT(*) 'Počet návštev' 
FROM navstevy
GROUP BY idL
HAVING COUNT(*) > 3;
10. zadanie

Zobrazte počet návštev jednotlivých pacientov.

Návody
Kľučové slová
  • COUNT
Riešenie 
                                 
-- Pocet navstev jednotlivych pacientov
SELECT 
   idP, 
   COUNT(*) 'Počet návštev' 
FROM navstevy
GROUP BY idP;
11. zadanie

Zobrazte počet návštev pacientov u jednotlivých lekárov.

Návody
Kľučové slová
  • COUNT
Riešenie 
                                 
-- Pocet navstev pacientov u lekarov
SELECT 
   idP Pacient, 
   idL Lekar, 
   COUNT(*) Pocet 
FROM navstevy
GROUP BY idP, idL
ORDER By idP, idL;

-- alebo

SELECT 
   idP Pacient, 
   idL Lekar, 
   COUNT(*) Pocet 
FROM navstevy
GROUP BY idL, idP
ORDER By idP, idL;
12. zadanie

Zobrazte návštevy medzi 5.7. a 9.9.2008.

Návody
Kľučové slová
  • BETWEEN
Riešenie 
                                 
-- Navstevy medzi 5.7.2008 a 9.9.2008
SELECT 
   * 
FROM navstevy
WHERE den BETWEEN '2008-07-05' AND '2008-09-09';
13. zadanie

Zobrazte počet návštev medzi 5.7. a 9.9.2008.

Návody
Kľučové slová
  • COUNT
  • BETWEEN
Riešenie 
                                 
-- Pocet navstev medzi 5.7.2008 a 9.9.2008
SELECT 
   COUNT(*) 
FROM navstevy
WHERE den BETWEEN '2008-07-05' AND '2008-09-09';
14. zadanie

Zobrazte počet návštev medzi 5.7. a 9.9.2008 u jednotlivých lekárov.

Návody
Kľučové slová
  • COUNT
  • BETWEEN
Riešenie 
                                 
-- Pocet navstev medzi 5.7.2008 a 9.9.2008
SELECT 
   idL,
   COUNT(*) 'Počet návštev'
FROM navstevy
WHERE den BETWEEN '2008-07-05' AND '2008-09-09';
15. zadanie

Zobrazte prvý deň návštevy.

Návody
Kľučové slová
  • MIN
Riešenie 
                                 
-- Prvy den navstevy
SELECT 
   MIN(den)
FROM navstevy;
16. zadanie

Zobrazte informácie o prvej návšteve.

Návody
Kľučové slová
  • MIN
Riešenie 
                                 
-- Prvy den navstevy
SELECT 
   MIN(den)
FROM navstevy;
17. zadanie

Zistite ktorý lekár by bol v abecednom poradí posledný.

Návody
Kľučové slová
  • MAX
Riešenie 
                                 
-- Posledne krstne meno lekara v abecede
SELECT 
   MAX(krstne) 
FROM lekari;

6. cvičenie

Ciele

  • Práca s viacerými tabuľkami

Zadania

Dostali ste za úlohu spravovať databázu polikliniky. Importnite si ju a zoznámte sa s jej štruktúrou.

1. zadanie

Importnite si databázu množiny a zoznámte sa s jej štruktúrou. Zobrazte výsledky spojenia dvoch tabuliek a a b cez stĺpec x pomocou CROSS JOIN, (INNER) JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN.

Návody
Kľučové slová
  • CROSS JOIN
  • (INNER) JOIN
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
Riešenie 
                                 
USE mnoziny;

-- CROSS JOIN
SELECT * FROM a, b;

-- INNER JOIN
SELECT * FROM a JOIN b ON b.x = a.x;
-- alebo
SELECT * FROM a JOIN b USING(x);

-- LEFT OUTER JOIN
SELECT * FROM a LEFT OUTER JOIN b ON b.x = a.x;
-- alebo
SELECT * FROM a LEFT OUTER JOIN b USING(x);

-- RIGHT OUTER JOIN
SELECT * FROM a RIGHT OUTER JOIN b ON b.x = a.x;
-- alebo
SELECT * FROM a RIGHT OUTER JOIN b USING(x);
2. zadanie

V ďalšich zadaniach budete pracovať s databázou poliklinika. Zobrazte id rôznych pacientov, ktorí už kedysi navštívili zubného lekára.

Návody
Kľučové slová
  • JOIN
Riešenie 
                                 
USE poliklinika;

-- Neefektivne riesenie - karteziansky sucin dvoch tabuliek 
SELECT 
   DISTINCT n.idp
FROM
   navstevy n, -- <=> navstevy AS n,
   lekari l
WHERE
   l.idl = n.idl AND l.spec = 'zubny';
-- (optimizer to v skutocnosti radsej prevedie na riesenie nizsie)

-- Spravne riesenie pomocou prepajacieho operatora JOIN
SELECT 
   DISTINCT n.idp
FROM navstevy n
   JOIN lekari l ON l.idl = n.idl
WHERE
   l.spec = 'zubny';
      
-- Ak prepajame dve tabulky na identickych stlpcov, tak MySQL podporuje takuto skratenu syntax.
SELECT 
   DISTINCT n.idp
FROM navstevy n
   JOIN lekari l USING(idl)
WHERE
   l.spec = 'zubny';
3. zadanie

Zobrazte id pacientov, ktorí navštívili zubára, pričom vypíšte aj dátum návštevy.

Návody
Kľučové slová
  • JOIN
Riešenie 
                                 
SELECT 
   n.idp, 
   n.den
FROM navstevy n
   JOIN lekari l USING (idl)
WHERE
   l.spec = 'zubny';
4. zadanie

Zobrazte id pacientov, ktorí navštívili zubára, pričom vypíšte aj dátum návštevy, meno lekára a meno pacienta.

Návody
Kľučové slová
  • JOIN
Riešenie 
                                 
SELECT 
   p.idp, 
   p.krstne 'Meno pacienta',
   l.krstne 'Meno lekara',
   n.den
FROM navstevy n
   JOIN lekari l ON l.idL = n.idL
   JOIN pacienti p ON p.idP = n.idP
WHERE
   l.spec = 'zubny';

-- alebo pomocou USING

SELECT 
   p.idp, 
   p.krstne 'Meno pacienta',
   l.krstne 'Meno lekara',
   n.den
FROM navstevy n
   JOIN lekari l USING (idl)
   JOIN pacienti p USING (idp)
WHERE
   l.spec = 'zubny';

-- Skratena syntax pre viac JOIN operatorov

SELECT 
   p.idp, 
   p.krstne 'Meno',
   l.krstne 'Meno lekara',
   n.den
FROM navstevy n
   JOIN (lekari l, pacienti p) USING (idl, idp)
WHERE
   l.spec = 'zubny';

-- alebo

SELECT 
   p.idp, 
   p.krstne 'Meno', 
   n.den
FROM navstevy n 
   JOIN (lekari l, pacienti p) ON l.idl = n.idl AND p.idp = n.idp
WHERE
   l.spec = 'zubny';
5. zadanie

Zobrazte počet všetkých návštev u zubára.

Návody
Kľučové slová
  • JOIN
Riešenie 
                                 
SELECT
   COUNT(n.idp)
FROM navstevy n
   JOIN lekari l ON l.idl = n.idl
WHERE 
   l.spec = 'zubny';

-- alebo

SELECT 
   COUNT(*) 
FROM
	(
      SELECT 
         n.idP, n.den
	   FROM navstevy n 
         JOIN lekari l ON l.idL = n.idL
	   WHERE 
         l.spec = 'Zubny'
	) AS T;

-- alebo

SELECT 
   SUM(T.pocet) 
FROM
	(
      SELECT 
         n.idP, 
         COUNT(*) AS pocet
	   FROM navstevy n 
         JOIN lekari l ON L.idL = N.idL
      WHERE 
         l.spec = 'Zubny'
	   GROUP BY n.idP
	) AS T;
6. zadanie

Zobrazte mená pacientov, ktorí už navštívili lekára Ota alebo Imra. Uveďte aj mená lekárov a výsledok zoraďte podľa mien lekárov, pacientov.

Návody
Kľučové slová
  • JOIN
Riešenie 
                                 
SELECT
   p.krstne 'Pacient', 
   l.krstne 'Lekar'
FROM pacienti p
   JOIN navstevy n ON n.idp = p.idp
      JOIN lekari l ON l.idl = n.idl
WHERE
   l.krstne IN ('Oto' , 'Imro')
ORDER BY l.krstne, p.krstne;
7. zadanie

Zobrazte mená pacientov, ktorí už navštívili lekára Ota a Imra.

Návody
Kľučové slová
  • JOIN
Riešenie 
                                 
-- NEFUNGUJE    
SELECT
   p.krstne 'Pacient', 
   l.krstne 'Lekar'
FROM pacienti p
   JOIN navstevy n ON n.idp = p.idp
      JOIN lekari l ON l.idl = n.idl
WHERE
   l.krstne = 'Oto' AND l.krstne = 'Imro' # ved navsteva prislucha iba jednemu lekarovi, a neexistuje navsteva kde je lekar aj Oto aj Imro
ORDER BY l.krstne, p.krstne;

-- SPRAVNE
SELECT 
   p.krstne 'Pacient', 
   lekar_oto.krstne 'Lekar Oto',
   lekar_imro.krstne 'Lekar Imro'
FROM pacienti p
   JOIN navstevy navstevy_u_ota ON p.idP = navstevy_u_ota.idP
		JOIN lekari lekar_oto ON navstevy_u_ota.idL = lekar_oto.idL 
	JOIN navstevy navstevy_u_imra ON p.idP = navstevy_u_imra.idP 
	 	JOIN lekari lekar_imro ON navstevy_u_imra.idL = lekar_imro.idL
WHERE
   lekar_oto.krstne = 'Oto' AND lekar_imro.krstne = 'Imro';

-- alebo

SELECT 
   p2.krstne 'Pacient'
FROM navstevy n2 
   JOIN lekari l2 ON l2.idL = n2.idL
      JOIN pacienti p2 ON p2.idP = n2.idP
WHERE 
   l2.krstne = 'Imro' 
   AND p2.idP IN 
   (
      SELECT 
         p.idP
      FROM navstevy n 
         JOIN lekari l ON l.idL = n.idL
            JOIN Pacienti p ON p.idP = n.idP
		WHERE L.krstne = 'Oto'
	);

7. cvičenie

Ciele

  • Konceptuálny návrh entitno-relačného diagramu

Zadania

Dostali ste za úlohu navrhnúť štruktúru na sprevádzkovanie eshopu.

Zadanie

Navrhnite vhodnú štruktúru na sprevádzkovanie eshopu.

Návody
Kľučové slová
  • Entitno-Relačný Diagram (ERD)
Riešenie

8. cvičenie

Ciele

  • Množinové operácie
  • Vnorené dopyty

Zadania

Dostali ste za úlohu spravovať databázu polikliniky. Importnite si ju a zoznámte sa s jej štruktúrou.

1. zadanie

Importnite si databázu množiny a zoznámte sa s jej štruktúrou. Zobrazte výsledky zjednotenia tabuliek a a b.

Návody
Kľučové slová
  • UNION
Riešenie 
                                 
USE mnoziny;

-- Zjednotenie
SELECT x, y FROM a 
UNION 
SELECT x, y FROM b;
2. zadanie

Zobrazte výsledky zjednotenia tabuliek a a b tak, aby sa riadky mohli opakovať.

Návody
Kľučové slová
  • UNION ALL
Riešenie 
                                 
USE mnoziny;

-- Zjednotenie s opakovanim
SELECT x, y FROM a 
UNION ALL
SELECT x, y FROM b;
3. zadanie

Zobrazte výsledky prieniku tabuliek a a b.

Návody
Kľučové slová
  • INTERSECT
  • IN
  • EXISTS
  • INNER JOIN
  • NATURAL JOIN
Riešenie 
                                 
USE mnoziny;

-- Prienik

-- Prienik pomocou IN
SELECT x, y 
FROM a 
WHERE (x, y) IN (SELECT x, y FROM b);

-- Prienik pomocou EXISTS
SELECT x, y 
FROM a 
WHERE EXISTS (SELECT * FROM b WHERE b.x = a.x AND b.y = a.y);

-- Prienik pomocou INNER JOIN
SELECT a.x, a.y 
FROM a
JOIN b ON b.x = a.x AND b.y = a.y;

-- Prienik pomocou INNER JOIN
SELECT DISTINCT a.x AS x, a.y AS y 
FROM a 
INNER JOIN b USING (x,y);

-- Prienik pomocou NATURAL JOIN
SELECT x, y 
FROM a
NATURAL JOIN b;

-- Prienik pomocou INTERSECT (od verzie 8.0.31)
TABLE a INTERSECT TABLE b;
4. zadanie

Zobrazte výsledky rozdielu tabuliek a a b.

Návody
Kľučové slová
  • EXCEPT
  • NOT IN
  • NOT EXISTS
  • LEFT OUTER JOIN
Riešenie 
                                 
USE mnoziny;

-- Rozdiel

-- Rozdiel pomocou NOT IN
SELECT x, y 
FROM a 
WHERE (x, y) NOT IN (SELECT x, y FROM b);

-- Rozdiel pomocou NOT EXISTS
SELECT x, y 
FROM a 
WHERE NOT EXISTS (SELECT * FROM b WHERE b.x = a.x AND b.y = a.y);

-- Rozdiel pomocou LEFT OUTER JOIN
SELECT a.x, a.y 
FROM a
LEFT OUTER JOIN b ON b.x = a.x AND b.y = a.y
WHERE b.x IS NULL;

-- Rozdiel pomocou LEFT OUTER JOIN
SELECT DISTINCT a.x AS x, a.y AS y 
FROM a 
LEFT OUTER JOIN b USING (x,y) 
WHERE b.x IS NULL;

-- Rozdiel pomocou EXCEPT (od verzie 8.0.31)
TABLE a EXCEPT TABLE b;
5. zadanie

Zobrazte mená pacientov, ktorí už navštívili lekára Ota alebo Imra. Uveďte aj mená lekárov a výsledok zoraďte podľa mien lekárov, pacientov.

Návody
Kľučové slová
  • JOIN
  • UNION
Riešenie 
                                 
-- Len s pouzitim JOIN
SELECT
   p.krstne 'Pacient', 
   l.krstne 'Lekar'
FROM pacienti p
   JOIN navstevy n ON n.idp = p.idp
      JOIN lekari l ON l.idl = n.idl
WHERE
   l.krstne IN ('Oto' , 'Imro')
ORDER BY l.krstne, p.krstne;

-- alebo 

-- S pouzitim JOIN a UNION
SELECT
	p.krstne 'Pacient', 
   l.krstne 'Lekar'
FROM pacienti p
   JOIN navstevy n ON n.idp = p.idp
      JOIN lekari l ON n.idl = l.idl
WHERE
   l.krstne = 'Oto'
UNION 
SELECT
	p.krstne 'Pacient', 
   l.krstne 'Lekar'
FROM pacienti p
   JOIN navstevy n ON n.idp = p.idp
      JOIN lekari l ON n.idl = l.idl
WHERE
   l.krstne = 'Imro'
ORDER BY Lekar, Pacient;

-- alebo 

-- Bez pouzitia JOIN a pouzitim UNION
SELECT
	p.krstne 'Pacient',
   'Oto' as 'Lekar'
FROM pacienti p
WHERE
   idp IN 
   ( 
      SELECT n.idp FROM navstevy n WHERE n.idl = 
      (
         SELECT l.idl FROM lekari l where krstne = 'Oto'
      )
   )
UNION
SELECT
	p.krstne 'Pacient',
   'Imro' as 'Lekar'
FROM pacienti p
WHERE
   idp IN 
   ( 
      SELECT n.idp FROM navstevy n WHERE n.idl = 
      (
         SELECT l.idl FROM lekari l where krstne = 'Imro'
      )
   )
ORDER BY Lekar, Pacient;
6. zadanie

Zobrazte mená pacientov, ktorí už navštívili lekára Ota a Imra.

Návody
Kľučové slová
  • JOIN
  • IN
  • EXISTS
Riešenie 
                                 
-- S pouzitim JOIN
SELECT 
   p.krstne 'Pacient', 
   lekar_oto.krstne 'Lekar Oto',
   lekar_imro.krstne 'Lekar Imro'
FROM pacienti p
   JOIN navstevy navstevy_u_ota ON p.idP = navstevy_u_ota.idP
		JOIN lekari lekar_oto ON navstevy_u_ota.idL = lekar_oto.idL 
	JOIN navstevy navstevy_u_imra ON p.idP = navstevy_u_imra.idP 
	 	JOIN lekari lekar_imro ON navstevy_u_imra.idL = lekar_imro.idL
WHERE
   lekar_oto.krstne = 'Oto' AND lekar_imro.krstne = 'Imro';

-- alebo

-- S pouzitim JOIN a IN
SELECT 
   p2.krstne 'Pacient'
FROM navstevy n2 
   JOIN lekari l2 ON l2.idL = n2.idL
      JOIN pacienti p2 ON p2.idP = n2.idP
WHERE 
   l2.krstne = 'Imro' 
   AND p2.idP IN 
   (
      SELECT 
         p.idP
      FROM navstevy n 
         JOIN lekari l ON l.idL = n.idL
            JOIN Pacienti p ON p.idP = n.idP
		WHERE L.krstne = 'Oto'
	);

-- alebo

-- Bez pouzitia JOIN
SELECT 
   p.krstne 'Pacient'
FROM pacienti p 
WHERE 
   idp IN 
   (
      SELECT idp 
      FROM 
      (
         SELECT n.idp FROM navstevy n WHERE n.idl = 
         (
            SELECT l.idl FROM lekari l WHERE krstne = 'Oto'
         )
      ) a 
      WHERE EXISTS 
      (
         SELECT * 
         FROM 
         (
            SELECT n.idp FROM navstevy n WHERE n.idl = 
            (
               SELECT l.idl FROM lekari l WHERE krstne = 'Imro'
            )
         ) b 
         WHERE b.idp = a.idp
      )
   );
7. zadanie

Zobrazte mená pacientov, ktorí navštívili lekára s id 1.

Návody
Kľučové slová
  • JOIN
  • IN
  • ANY
  • EXISTS
Riešenie 
                                 
-- S pouzitim JOIN
SELECT 
   p.krstne 'Pacient'
FROM pacienti p
JOIN navstevy n ON n.idP = p.idP
WHERE
   idL = 1;

-- alebo

-- S pouzitim IN
SELECT 
   p.krstne 'Pacient' 
FROM pacienti p
WHERE p.idP IN
(
   SELECT n.idP FROM navstevy n WHERE n.idL = 1
);

-- alebo

-- S pouzitim ANY
SELECT 
   p.krstne 'Pacient' 
FROM pacienti p
WHERE p.idP = ANY
(
   SELECT n.idP FROM navstevy n WHERE n.idL = 1
);

-- alebo

-- S pouzitim EXISTS
SELECT 
   p.krstne 'Pacient' 
FROM pacienti p
WHERE EXISTS -- EXISTS vrati true ak vnutorny SELECT vrati aspon jeden riadok
(
   SELECT n.idP FROM navstevy n WHERE n.idL = 1 AND n.idP = p.idP -- korelacia, t.j. stlpec p.idP z vonkajsieho SELECT pouzijeme vo vnutorom SELECT
);
8. zadanie

Zobrazte počet všetkých návštev u zubára.

Návody
Kľučové slová
  • JOIN
  • IN
  • COUNT
  • SUM
Riešenie 
                                 
SELECT
   COUNT(n.idp)
FROM navstevy n
   JOIN lekari l ON l.idl = n.idl
WHERE 
   l.spec = 'zubny';

-- alebo

SELECT 
   COUNT(*) 
FROM
	(
      SELECT 
         n.idP, n.den
	   FROM navstevy n 
         JOIN lekari l ON l.idL = n.idL
	   WHERE 
         l.spec = 'Zubny'
	) AS T;

-- alebo

SELECT 
   SUM(T.pocet) 
FROM
	(
      SELECT 
         n.idP, 
         COUNT(*) AS pocet
	   FROM navstevy n 
         JOIN lekari l ON L.idL = N.idL
      WHERE 
         l.spec = 'Zubny'
	   GROUP BY n.idP
	) AS T;

-- alebo

-- S pouzitim IN
SELECT 
    COUNT(idN)
FROM
    navstevy
WHERE
    idL IN (
		SELECT 
         idl
      FROM
         lekari
      WHERE
         spec = 'Zubny'
	);
9. zadanie

Zistite mená pacientov, ktorí už kedysi navštívili zubného lekára.

Návody
Kľučové slová
  • JOIN
  • IN
Riešenie 
                                 
SELECT 
   p.krstne 'Pacient'
FROM pacienti p
   JOIN navstevy n ON n.idP = p.idP
      JOIN lekari l ON l.idL = n.idL
WHERE
   l.spec = 'zubny';

-- alebo

-- S pouzitim IN
SELECT 
   p.krstne 'Pacient'
FROM pacienti p
WHERE 
   p.idP IN
   (
      SELECT n.idP FROM navstevy n
      WHERE n.idL IN
      (
         SELECT idL FROM lekari l
         WHERE l.spec = 'Zubny'
      )
   );
10. zadanie

Zobrazte dátum narodenia najmladšieho/-ej lekára/-ky.

Návody
Kľučové slová
  • MAX
Riešenie 
                                 
SELECT MAX(datnar) 'Najmladsi'
FROM lekari;
11. zadanie

Zobrazte krstné meno, špecializáciu a dátum narodenia najmladšieho/-ej lekára/-ky.

Návody
Kľučové slová
  • MAX
Riešenie 
                                 
-- Pomocou MAX
SELECT 
   l.krstne, l.spec, l.datnar
FROM lekari l
WHERE
   l.datNar = 
   (
      SELECT MAX(l1.datnar) FROM lekari l1
   );

-- alebo

-- Pomocou ALL
SELECT 
   l.krstne, l.spec, l.datnar
FROM lekari l
WHERE
   l.datNar >= ALL 
   (
		SELECT l1.datnar FROM lekari l1
	);
12. zadanie

Nájdite údaje druhého najmladšieho/-ej lekára/-ky.

Návody
Kľučové slová
  • MAX
Riešenie 
                                 
-- Pomocou MAX
SELECT 
   l.krstne, l.spec, l.datnar
FROM lekari l
WHERE
   l.datNar = 
   (
		SELECT 
         MAX(l2.datnar) 
      FROM 
         lekari l2
      WHERE
         l2.datnar < 
         (
				SELECT 
               MAX(l1.datnar)
            FROM
               lekari l1
		   )
   );

-- alebo

-- Pomocou ALL
SELECT 
    l.krstne, l.spec, l.datnar
FROM 
(
	SELECT 
        l3.krstne, l3.spec, l3.datnar
    FROM
        lekari l3
    WHERE
        l3.datnar < 
         (
			SELECT 
                l2.datnar
            FROM
                lekari l2
            WHERE
                l2.datnar >= ALL 
                (
					   SELECT 
                     l1.datnar
                  FROM
                     lekari l1
				   ) 
		   )
) l
WHERE 
	l.datnar >= ALL (
		SELECT 
		l3.datnar
		FROM
			lekari l3
		WHERE
			l3.datnar < 
         (
				SELECT 
					l2.datnar
				FROM
					lekari l2
				WHERE
					l2.datnar >= ALL 
               (
						SELECT 
							l1.datnar
						FROM
							lekari l1
					)
			)
	);
13. zadanie

Nájdite všetky údaje o treťom/-tej najmladšom/-ej lekárovi/-ke.

Návody
Kľučové slová
  • MAX
Riešenie 
                                 
-- Pomocou MAX
SELECT * 
FROM lekari
WHERE 
   datnar =
   (
      SELECT max(l3.datNar) 
      FROM lekari l3
      WHERE l3.datNar <
      (
         SELECT max(l2.datnar) 
         FROM lekari l2
         WHERE l2.datNar <
         (
            SELECT max(l1.datnar) FROM lekari l1
         )
      )
   );
14. zadanie

Zistite koľko zarobili jednotliví lekári. Vo výsledku chceme vidieť krstné meno lekára a ich celkový príjem.

Návody
Kľučové slová
  • SUM
Riešenie 
                                 
SELECT 
   l.krstne, 
   SUM(n.poplatok) as prijem 
FROM navstevy n
JOIN lekari l ON l.idL = n.idL
GROUP BY l.krstne;
15. zadanie

Zistite koľko zarobili jednotliví lekári. Vo výsledku chceme vidieť aj koľko zarobili dokopy.

Návody
Kľučové slová
  • SUM
  • WITH ROLLUP
Riešenie 
                                 
SELECT 
   CASE WHEN l.krstne IS NULL THEN 'Spolu' ELSE  l.krstne END krstne,
   SUM(n.poplatok) as prijem 
FROM navstevy n
JOIN lekari l ON l.idL = n.idL
GROUP BY l.krstne 
WITH ROLLUP;

9. cvičenie

Ciele

  • Polsemestrálna previerka

Príklad zápočtu

1. Návrh štruktúry (5 bodov)

Majstrovstvá sveta v ľadovom hokeji sa v roku 2022 konali vo Fínsku. V základnej A skupine sa stretlo 8 mužstiev a odohrali nasledujúcich 28 zápasov:
DÁTUM DOMÁCI HOSTIA VÝSLEDOK
13.05.2022 15:20 Francúzsko Slovensko 2:4
13.05.2022 19:20 Nemecko Kanada 3:5
14.05.2022 11:20 Dánsko Kazachstan 9:1
14.05.2022 15:20 Švajčiarsko Taliansko 5:2
14.05.2022 19:20 Slovensko Nemecko 1:2
15.05.2022 11:20 Taliansko Kanada 1:6
15.05.2022 15:20 Francúzsko Kazachstan 2:1
15.05.2022 19:20 Dánsko Švajčiarsko 0:6
16.05.2022 15:20 Slovensko Kanada 1:5
16.05.2022 19:20 Francúzsko Nemecko 2:3
17.05.2022 15:20 Taliansko Dánsko 1:2
17.05.2022 19:20 Švajčiarsko Kazachstan 3:2
18.05.2022 15:20 Francúzsko Taliansko 2:1 pp
18.05.2022 19:20 Švajčiarsko Slovensko 5:3
19.05.2022 17:00 Nemecko Dánsko 1:0
19.05.2022 20:30 Kanada Kazachstan 6:3
20.05.2022 15:20 Nemecko Taliansko 9:4
20.05.2022 19:20 Kazachstan Slovensko 3:4
21.05.2022 11:20 Dánsko Francúzsko 3:0
21.05.2022 15:20 Kanada Švajčiarsko 3:6
21.05.2022 19:20 Taliansko Slovensko 1:3
22.05.2022 15:20 Kazachstan Nemecko 4:5
22.05.2022 19:20 Švajčiarsko Francúzsko 5:2
23.05.2022 15:20 Kazachstan Taliansko 5:2
23.05.2022 19:20 Kanada Dánsko 2:3
24.05.2022 11:20 Nemecko Švajčiarsko 3:4 sn
24.05.2022 15:20 Slovensko Dánsko 7:1
24.05.2022 19:20 Kanada Francúzsko 7:1
pp - po predĺžení
sn - samostatné nájazdy

Navrhnite vhodnú štruktúru a naplňte tabuľky uvedenými dátami (na zápočte pre krátkosť času stačí vložiť aspoň 6 záznamov).
(vytvorenie vlastnej databázy 1 bod, vytvorenie tabuľky 1 bod (lepšie je vytvoriť 2 tabuľky za 2 body), naplnenie tabuliek 1 bod, výpis tabuľky (aj s pomenovanými stĺpcami) 1 bod)

2. Základné informácie (3 body)

  1. Koľko ubehlo dní od prvého zápasu (po dnešný dátum)? Skúste namiesto dní zistiť koľko mesiacov mohlo už ubehnúť od prvého zápasu (0.5 boda za počet dní, 0.5 boda za počet mesiacov)
  2. Vypíšte štáty, ktoré začínajú na S a končia na o. Štáty zoraďte zostupne (podmienka 0.5 boda, zoradenie 0.5 boda)
  3. Janko Hraško sa stavil s kamarátom o to, koľko zápasov vyhrá hráč uvedený ako prvý (domáci). Dopomôžte mu vygenerovať záznamy dátumov a mužstiev, kde domáci vyhrali (1 bod)

3. Prehľad (3 body)

  1. Vypíšte duel prvého zápasu (0.5 boda za nájdenie dátumu, 0.5 boda za výpis zápasu)
  2. V ktorom zápase padlo najviac gólov? (0.5 bod za nájdenie počtu gólov, 0.5 boda za výpis zápasu)
  3. V ktorých zápasoch bol rozdiel gólov najtesnejší? (0.5 bod za nájdenie najmenšieho rozdielu, 0.5 boda za výpis záznamu)

4. Informácie pre slovenského fanúšika (5 bodov)

Slovenský fanúšik chce vidieť len zápasy svojho mužstva. Dopomôžte mu tým, že uvidí dátumy zápasov so Slovenskom, dni , súperov, s ktorými hrali a informáciu o tom, či zápas pre Slovákov skončil výhrou alebo prehrou. Záznamy nech sú zotriedené podľa dátumu ako je vidieť na tomto príklade:
Dátum Deň Domáci Hostia Konečný stav
13.05.2022 15:20 utorok Francúzsko Slovensko Vyhrali
14.05.2022 19:20 sobota Slovensko Nemecko Prehrali
16.05.2022 15:20 pondelok Slovensko Kanada Prehrali
18.05.2022 19:20 streda Švajčiarsko Slovensko Prehrali
20.05.2022 19:20 piatok Kazachstan Slovensko Vyhrali
21.05.2022 19:20 sobota Taliansko Slovensko Vyhrali
24.05.2022 15:20 utorok Slovensko Dánsko Vyhrali
(výpis stĺpcov dátum, domácich a hostí 1 bod, filter 1 bod, stĺpec deň 0.5 boda (vypísaný aj po slovensky 0.5 boda), stĺpec konečný stav 1 bod , zoradenie 1 bod)

5. Tabuľka výsledkov (4 body)

Vypíšte kompletnú výsledkovú tabuľku, ktorá bude obsahovať názov tímu, počet zápasov, počet výhier, počet výhier po predĺžení, počet prehier po predĺžení, počet prehier, počet daných gólov, počet získaných gólov a body (za výhru sú 3 body, za výhru po predĺžení 2 body, za prehru po predĺžení 1 bod, za prehru 0 bodov). Tabuľku zoradte podľa počtu bodov a ak je počet bodov rovnaký, tak pre jednoduchosť stačí podľa daných gólov.
(stĺpce názov 1 bod, výhry/výhry po predĺžení/prehry po predĺžení/prehry 1 bod, dané góly 0.5 boda, získané góly 0.5 boda, body 0.5 boda boda, zoradenie 0.5 boda – spolu 4 body)
Tabuľku výsledkov jsi môžete pozrieť na stránke https://en.wikipedia.org/wiki/2022_IIHF_World_Championship_Group_A

10. cvičenie

Ciele

  • Integritné obmedzenia

Zadania

Dostali ste za úlohu spravovať upravenú databázu osôb. Importnite si ju a zoznámte sa s jej štruktúrou.

1. zadanie

Importnite si databázu osôb a zoznámte sa s jej štruktúrou. Vložte záznamy z pôvodnej databázy osôb do novej databázy.

Návody
Kľučové slová
  • INSERT SELECT
Riešenie 
                                 
USE OsobaDB2;

INSERT osoba(id,meno,priezvisko,rodne_priezvisko,dat_nar,dat_smrti,pohlavie, vyska, vaha)
SELECT id,meno,priezvisko,rodne_priezvisko,dat_nar,dat_smrti,pohlavie, vyska, vaha
FROM osobadb.osoba;
2. zadanie

Vložte záznam s id 14 do nového záznamu (s novým id 24).

Návody
Kľučové slová
  • INSERT SELECT
Riešenie 
                                 
INSERT osoba(id,meno,priezvisko,rodne_priezvisko,datum_narodenia,dat_smrti,pohlavie, vyska, vaha)
SELECT 24,meno,priezvisko,rodne_priezvisko,dat_nar,dat_smrti,pohlavie, vyska, vaha
FROM osoba
WHERE id = 14;
3. zadanie

Osoba s novovytvoreným záznam s id = 24 sa vydala. Upravte priezvisko na Dáša, pôvodné priezvisko nastavte podľa aktuálneho priezviska a nové priezvisko osoby je Dášová.

Návody
Kľučové slová
  • UPDATE
Riešenie 
                                 
SET SQL_SAFE_UPDATES = 0;

UPDATE osoba
SET meno = 'Dáša', rodne_priezvisko = priezvisko, priezvisko = 'Dášová'
WHERE id = 24;
4. zadanie

Odstráňte záznam s id = 24.

Návody
Kľučové slová
  • DELETE
Riešenie 
                                 
SET SQL_SAFE_UPDATES = 0;

DELETE FROM osoba
WHERE id = 24;
5. zadanie

Pridajte stĺpec vek a zaktualizujte vek u žijúcich osôb.

Návody
Kľučové slová
  • ALTER TABLE
  • ADD COLUMN
Riešenie 
                                 
-- Pridanie stlpca
ALTER TABLE osoba ADD COLUMN vek INT;

-- alebo

ALTER TABLE osoba ADD vek INT;

-- Aktualizacia veku
UPDATE Osoba
SET vek = TIMESTAMPDIFF(YEAR, dat_nar, CURRENT_DATE())
WHERE dat_smrti NULL;
6. zadanie

U zosnulých osôb zaktualizujte vek na vek úmrtia. Nastavte tĺpec vek na povinný s tým, že ak sa pri pridaní záznamu vek neuvedie, nastaví sa jeho hodnota na nulu.

Návody
Kľučové slová
  • ALTER TABLE
  • MODIFY COLUMN
  • DEFAULT
Riešenie 
                                 
-- Aktualizacia veku
UPDATE Osoba
SET vek = TIMESTAMPDIFF(YEAR, dat_nar, IFNULL(dat_smrti, CURRENT_DATE()))
WHERE dat_smrti IS NOT NULL;

-- Modifikacia stlpca
ALTER TABLE osoba MODIFY COLUMN vek INT NOT NULL DEFAULT 0;

-- alebo

ALTER TABLE osoba MODIFY vek INT NOT NULL DEFAULT 0;

-- alebo

ALTER TABLE osoba CHANGE COLUMN vek vek INT NOT NULL DEFAULT 0;

-- alebo

ALTER TABLE osoba CHANGE vek vek INT NOT NULL DEFAULT 0;
7. zadanie

Premenujte stĺpec vek na pocet_rokov.

Návody
Kľučové slová
  • ALTER TABLE
  • CHANGE COLUMN
Riešenie 
                                 
ALTER TABLE osoba CHANGE COLUMN vek pocet_rokov INT NOT NULL DEFAULT 0;

-- alebo

ALTER TABLE osoba CHANGE vek pocet_rokov INT NOT NULL DEFAULT 0;
8. zadanie

Odstráňte stĺpec vek/pocet_rokov.

Návody
Kľučové slová
  • ALTER TABLE
  • DROP COLUMN
Riešenie 
                                 
ALTER TABLE osoba DROP COLUMN pocet_rokov;

-- alebo

ALTER TABLE osoba DROP pocet_rokov;
9. zadanie

Zistite aké integritné obmedzenia už sú v databáze vytvorené.

Návody
Kľučové slová
  • INFORMATION_SCHEMA.TABLE_CONSTRAINTS
Riešenie 
                                 
SELECT * FROM information_schema.table_constraints
WHERE constraint_schema = 'osobadb2';
10. zadanie

Pridajte primárny kľúč nad stĺpcom id a nastavte, aby sa automaticky zvyšovali hodnoty od 101. Overte správanie sa primárného kľúča na vyhovujúcich/nevyhovujúcich záznamoch.

Návody
Kľučové slová
  • PRIMARY KEY
Riešenie 
                                 
ALTER TABLE osoba CHANGE id id INT NOT NULL PRIMARY KEY AUTO_INCREMENT;

-- alebo len pridanie kluca
ALTER TABLE osoba ADD PRIMARY KEY (id);

ALTER TABLE osoba AUTO_INCREMENT = 101;
11. zadanie

Odstráňte primárny kľúč nad tabuľkou osôb.

Návody
Kľučové slová
  • PRIMARY KEY
Riešenie 
                                 
ALTER TABLE osoba DROP PRIMARY KEY;
12. zadanie

Pridajte obmedzenie, že pohlavie môže nadobúdať len hodnoty m alebo z. Pokúste sa pridať záznamy, ktoré vyhovujú/nevyhovujú danej podmienke. Na konci odstránte dané obmedzenie a skúste vložiť nevyhovujúci záznam.

Návody
Kľučové slová
  • CHECK
Riešenie 
                                 
ALTER TABLE osoba ADD CONSTRAINT CHK_pohlavie CHECK(pohlavie IN('m','z'));

-- alebo

ALTER TABLE osoba ADD CHECK(pohlavie IN('m','z'));

-- odstranenie
ALTER TABLE osoba DROP CHECK CHK_pohlavie;
13. zadanie

Pridajte obmedzenie, kde meno alebo priezvisko musia byť uvedené. Pokúste sa pridať záznamy, ktoré vyhovujú/nevyhovujú danej podmienke. Na konci odstránte dané obmedzenie a skúste vložiť nevyhovujúci záznam.

Návody
Kľučové slová
  • CHECK
Riešenie 
                                 
ALTER TABLE osoba ADD CONSTRAINT CHK_not_null CHECK ( meno is not null or priezvisko is not null ); 

-- alebo

ALTER TABLE osoba ADD CHECK ( meno is not null or priezvisko is not null );

-- odstranenie
ALTER TABLE osoba DROP CHECK CHK_not_null;
14. zadanie

Pridajte obmedzenie, kde trojica pohlavie, výška a váha musia byť jedinečné. Pokúste sa pridať záznamy, ktoré vyhovujú/nevyhovujú danej podmienke. Na konci odstránte dané obmedzenie a skúste vložiť nevyhovujúci záznam.

Návody
Kľučové slová
  • UNIQUE
Riešenie 
                                 
ALTER TABLE osoba ADD CONSTRAINT UQ_pvv UNIQUE (pohlavie, vaha, vyska);

-- alebo

ALTER TABLE osoba ADD UNIQUE (pohlavie, vaha, vyska);

-- odstranenie
ALTER TABLE osoba DROP INDEX UQ_pvv;
15. zadanie

Pridajte stĺpce otec, matka, ktoré budú zároveň cudzím kľúčom. Pokúste sa pridať záznamy, ktoré vyhovujú/nevyhovujú danej podmienke. Na konci odstránte dané obmedzenie a skúste vložiť nevyhovujúci záznam.

Návody
Kľučové slová
  • FOREIGN KEY
Riešenie 
                                 
ALTER TABLE osoba ADD otec INT;

ALTER TABLE osoba ADD CONSTRAINT FK_otec FOREIGN KEY (otec) REFERENCES osoba (id);

ALTER TABLE osoba ADD COLUMN matka INTEGER, ADD FOREIGN KEY (matka) REFERENCES osoba (id);

-- odstranenie
ALTER TABLE osoba FOREIGN KEY FK_otec;

ALTER TABLE osoba FOREIGN KEY FK_matka;
16. zadanie

Povoľte/zakážte integritné obmedzenie pre cudzie kľúče. Pokúste sa pridať záznamy, ktoré vyhovujú/nevyhovujú danej podmienke.

Návody
Kľučové slová
  • FOREIGN KEY
Riešenie 
                                 
SET FOREIGN_KEY_CHECKS=0;

SET FOREIGN_KEY_CHECKS=1;

ALTER TABLE osoba DISABLE KEYS;

ALTER TABLE osoba ENABLE KEYS;
17. zadanie

Importnite si databázu MOZ a zoznámte sa s jej štruktúrou. Skúste nastaviť cudzie kľúče tak, aby sa pri aktualizácii hodnoty primárneho kľúča zaktualizovali aj hodnoty cudzích kľučov a pri mazaní miestnosti aby sa miestnosť na oddelení nastavila na NULL hodnotu. Mazanie pracovísk pokial majú zamestnanca nepovolíme.

Návody
Kľučové slová
  • FOREIGN KEY
Riešenie 
                                 
USE MOZ;

ALTER TABLE Oddelenie 
ADD CONSTRAINT fk_Miest 
FOREIGN KEY (Miestnost) 
REFERENCES Miestnost(idM) 
ON DELETE SET NULL 
ON UPDATE CASCADE;
-- RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

ALTER TABLE Zamestnanci	
ADD CONSTRAINT fk_PNaOdd FOREIGN KEY (Prac_na_odd) 
REFERENCES Oddelenie(Nazov_odd) 
ON DELETE NO ACTION
ON UPDATE CASCADE;
-- RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
18. zadanie

Importnite si upravenú databázu Osôb a zoznámte sa s jej štruktúrou. Premenujte tabľku Osoba na Person.

Návody
Kľučové slová
  • RENAME TABLE
Riešenie 
                                 
USE OsobaDB2;

RENAME TABLE osoba TO person;

11. cvičenie

Ciele

  • Data science a získavanie znalostí pomocou R

Materiál

12. cvičenie

Ciele

  • Koncosemestrálna previerka

13. cvičenie

Ciele

  • Opravný zápočet