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

Š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. (Motivácia)

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

Zaujímavé udalosti

Databáza

MySQL

Microsoft SQL Server

Prednáška

Čas: Utorok 15:20 - 16:50
Miestnosť: SJ2P11 - Jesenná 5, 2. poschodie, poslucháreň

Cvičenie

Čas: Štvrtok 13:30 - 15:00
Miestnosť: SA1C04 - Park Angelinum 9, 1. poschodie, počítačová učebňa

Čas: Štvrtok 15:20 - 16:50
Miestnosť: SA1C03 - Park Angelinum 9, 1. poschodie, počítačová učebňa

Motivácia

Š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.

Ciele

Prerekvizita k predmetom

Ďalšie nadväzujúce predmety

Dotazník

Hodnotenie

Cvičenie

  • Priebežné previerky
  • Domáce úlohy: 15 bodov
  • 1. previerka: 15 bodov
  • 2. previerka: 20 bodov
  • Aktívne zapájanie sa do výučby: body navyše

Skúška

  • Skúška: 50 bodov

Plagiátorstvo

Plagiátorstvo nie je akceptovateľné. U poskytovateľa a samozrejme prijímateľa/plagiátora budú všetky doteraz získané body anulované. Body sa anulujú od momentu zistenia podvodu, nie kedy bol podvod zrealizovaný. Zainteresované osoby môžu sa naďalej zúčastňovať danej výučby a získavať body od daného momentu. O danom incidente budú informovaní všetci kolegovia a daný prehrešok bude posunutý etickej komisii, ktorá môže rozhodnúť aj vylúčením zo štúdia.

Príklad 1:
V 9. týždni výučby sa zistilo, že poskytovateľ poskytol prijímateľovi svoje riešenie 5. sady úloh v čase riešení 5. domáceho zadania. V danom čase už boli tri priebežné previerky, prebieha 7. sada domácich úloh, bol obhájený projekt a napísaná polsemestrálna previerka. Doteraz získané body (do riešenia 7. sady úloh vrátane) sa obom aktérom anulujú. Avšak môžu získať body za poslednú priebežnú previerku, zvyšné sady domácich úloh a koncosemestrálnu previerku. Maximálny počet bodov, ktoré ešte môžu získať je 1+3+15=19 bodov plus nové body za aktivitu. Musia ešte obhájiť svoje správanie pred etickou komisiou, ktorá ich môže vylúčiť zo štúdia.

Príklad 2:
V 12. týždni výučby bol u plagiátora objavený ťahák pri písaní poslednej priebežnej previerky. Do konca výučby zostáva posledná sada domácich úloh a koncosemestálna previerka. Doteraz získané body sa anulujú. Plagiátor môže získať body za poslednú sadu domácich úloh a koncosemestrálnu previerku. Maximálny počet bodov, ktoré ešte môže získať je 1+15=16 bodov plus nové body za aktivitu. Musí ešte obhájiť svoje správanie pred etickou komisiou, ktorá ho môže vylúčiť zo štúdia.

Príklad 3:
Na/po koncosemstrálnej previerke sa zistilo, že poskytovateľ poskytol časť svojho riešenia prijímateľovi. U poskytovateľa a prijímateľa/plagiátora budú všetky doteraz získané body anulované. Zainteresované osoby môžu naďalej ísť na skúšku, avšak z cvičení dostávajú automaticky nula bodov. O danom incidente budú informovaní všetci kolegovia a daný prehrešok bude posunutý etickej komisii, ktorá môže rozhodnúť aj vylúčením zo štúdia.

Príklad 4:
Pred koncosemstrálnou previerkou sa zistilo, že poskytovateľ poskytol časť svojich riešení počas 3. až 5. sady domácich úloh prijímateľovi. U poskytovateľa a prijímateľa/plagiátora budú všetky doteraz získané body anulované. Maximálny počet bodov, ktoré ešte môžu získať je 15 bodov za koncosemestrálnu previerku. O danom incidente budú informovaní všetci kolegovia a daný prehrešok bude posunutý etickej komisii, ktorá môže rozhodnúť aj vylúčením zo štúdia.

Príklad 5:
V poslednom týždni semestra sa zistilo, že poskytovateľ poskytol svoje riešenia domácich úloh počas riešenia 1. sady domácich úloh druhej skupine. U poskytovateľa a prijímateľov budú všetky doteraz získané body anulované. Keďže poskytovateľ už absolvoval koncosemestrálnu previerku, z cvičení získava nula bodov. Keďže prijímatelia ešte nepísali koncosemestrálnu previerku, môžu získať maximálne 15 bodov. O danom incidente budú informovaní všetci kolegovia a daný prehrešok bude posunutý etickej komisii, ktorá môže rozhodnúť aj vylúčením zo štúdia.

Materiál

Databáza

MySQL

Microsoft SQL Server

Software

Microsoft SQL Server

Tutoriál

MongoDB + MongoDB Compass

Tutoriál

Odporúčaná literatúra

Prednáška

Domáca úloha

Sylaby štátnicového predmetu

Cvičenie

2. cvičenie

Ciele

  • Window Functions

Window Functions

Príklad

1. cvičenie

Ciele

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

Materiál na študovanie

1. Relačná algebra

Relačná algebra - online nástroje

2. Funkčné závislosti

Materiál k cvičeniu

Zadanie

  • Pre daný SQL dopyt vytvorte postupnosť krokov na prevod do relačnej algebry
                         
    SELECT
       p.krstne AS Pacient,
       l.krstne AS Lekar,
       SUM(n.poplatok) AS Poplatok
    FROM navstevy n
       JOIN lekari l ON l.idL = n.idL
       JOIN pacienti p ON p.idP = n.idP
    WHERE
       n.id < 4
    GROUP BY p.krstne, l.krstne
                         
                      
    Pomôcka: Query-converter – SQL to Relational Algebra (www.grammaticalframework.org)
  • Pomocou amstrongových pravidiel a pomocou definície dokážte:
    • (x → y) & (wy → z) ⇒ wx → wz
    • (x → y) & (z ⊆ y) ⇒ x → z

2. cvičenie

Ciele

  • Funkčný a atribútový uzáver
  • Bezstrátová dekompozícia
  • Normálne formy

Materiál na študovanie

Materiál k cvičeniu

Zadanie

  • Definujte: Funkčná závislosť, triviálnu funkčnú závislosť, kandidátny kľúč, kompozitný kľúč, primárny kľúč, nadkľúč, podkľúč.
  • Analyzujte tabuľku Osoba(id, meno, pohlavie). Určte funkčné závislosti. Čo je kandidátny/nad/pod-kľúč?.
  • Uvažujme rozklad {T1,T2} tabuľky T.
    • Presvedčte sa, že rozklad bude stratový (SQL kódom a potom aj pomocou kritéria).
    • Zmeňte jednu hodnotu v tabuľke T, aby daný rozklad bol bezstratový (je prienik NK?).

3. cvičenie

Ciele

  • Essential Tuple Normal Form

Materiál na študovanie

4. cvičenie

Ciele

  • Previerka
    • Relačná algebra
    • Funkčné závislosti
    • Funkčný a atribútový uzáver
    • Bezstrátová dekompozícia
    • Normálne formy
  • Úvod do Microsoft SQL Server

Úvod do Microsoft SQL Server

DBMS MySQL Microsoft SQL Server
Developer
  • Oracle
  • Microsoft
Licence
  • Open Source
  • Commercial
Website
Dokumentácia
Tutorial
Comments
CREATE/ALTER/DROP DATABASE
Schema
CREATE/ALTER/DROP TABLE
Data Types
Type Conversion
Constraints
  • PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT, NOT NULL
  • ALTER TABLE table_name DROP CONSTRAINT constraint_name; (MySQL 8.0.19 and later)
  • DROP CONSTRAINT (www.w3schools.com)
INSERT/UPDATE/DELETE
SHOW
SELECT
Aliases
  • Column
  • Table
  • Column
  • Table
Sorting Data
  • ORDER BY
  • ORDER BY
Limiting Rows
Filtering Data
  • DISTINCT, =, >, <, >=, <=, !>, !<, <>, !=, AND, OR, NOT, IN, NOT IN, BETWEEN, NOT BETWEEN, LIKE, NOT LIKE, IS NULL, IS NOT NULL
  • LIKE (learn.microsoft.com)
Operators and Functions
Joining Tables
  • CROSS JOIN, INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, ...
  • CROSS JOIN, INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, ...
Grouping Data
Subqueries
  • =, >, <, >=, <=, <>, !=, IN, NOT IN, ANY, SOME, ALL, EXISTS, NOT EXISTS
  • =, >, <, >=, <=, !>, !<, <>, !=, IN, NOT IN, ANY, SOME, ALL, EXISTS, NOT EXISTS
Set Operators
  • UNION
  • INTERSECT (MySQL 8.0.31 and later)
  • EXCEPT (MySQL 8.0.31 and later)
  • UNION
  • INTERSECT
  • EXCEPT

Softvér

Databáza

MySQL

Microsoft SQL Server

Príklad

5. cvičenie

Ciele

  • Úvod do Microsoft SQL Server
  • Systémové pohľady
  • Úvod do T-SQL
  • Uložené procedúry
    • Reťazcové príkazy
  • Funkcie
  • Ošetrenie chýb
  • Common Language Runtime

1. Úvod do Microsoft SQL Server

Softvér

Databáza

MySQL

Microsoft SQL Server

Príklad

MySQL

Microsoft SQL Server

2. Systémové pohľady

Príklad

3. Úvod do T-SQL

Extension of the SQL MySQL (Oracle) Transact-SQL (Microsoft SQL Server)
Data Types
Operators and Functions
Variables
Flow Control
Temporary Tables
  • DECLARE ... HANDLER
  • PROCEDURE, FUNCTION, VIEW, TRIGGER, INDEX
  • TRANSACTION, CURSOR
  • WITH, PIVOT
  • XML, JSON
  • TRY ... CATCH
  • PROCEDURE, FUNCTION, VIEW, TRIGGER, INDEX
  • TRANSACTION, CURSOR
  • WITH, PIVOT
  • XML, JSON

Príklad

4. Uložené procedúry

Príklad

5. Funkcie

Príklad

6. Ošetrenie chýb

Príklad

7. Common Language Runtime

6. cvičenie

Ciele

  • Pohľady
  • Common Table Expression
  • Tranzitívny uzáver a rekurzia

1. Pohľady (VIEW)

Príklad

2. Common Table Expression (CTE - WITH)

Príklad

3. Tranzitívny uzáver a rekurzia

Príklad

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

  • Transakcie (TRANSACTION)
  • Spúšťače (TRIGGER)
  • B-stromy a indexy (INDEX)
  • Kurzory (CURSOR)
  • Kontingenčná tabuľka (PIVOT)

Trvanlivosť

  • Dočasne
    • Premenné
    • #, ## tabuľky
    • CTE/WITH - súčasť SELECT dopytu
    • PIVOT - súčasť SELECT dopytu
    • CURSOR
  • Trvajúce
    • sys.objects (learn.microsoft.com)
    • Tabuľky (TABLE)
      • U = Table (user-defined)
      • S = System base table
      • IT = Internal table
      • ET = External Table
    • Pohľady (VIEW)
      • V = View
    • Integritné obmedzenia (CONSTRAINT)
      • D = DEFAULT (constraint or stand-alone)
      • PK = PRIMARY KEY constraint
      • F = FOREIGN KEY constraint
      • UQ = UNIQUE constraint
      • C = CHECK constraint
    • Uložené procedúry (STORED PROCEDURE)
      • P = SQL Stored Procedure
      • RF = Replication-filter-procedure
      • X = Extended stored procedure
      • PC = Assembly (CLR) stored-procedure
    • Funkcie (FUNCTION)
      • FN = SQL scalar function
      • IF = SQL inline table-valued function
      • TF = SQL table-valued-function
      • FS = Assembly (CLR) scalar-function
      • FT = Assembly (CLR) table-valued function
      • AF = Aggregate function (CLR)
    • Spúšťače (TRIGGER)
      • TR = SQL DML trigger
      • TA = Assembly (CLR) DML trigger
    • Iné
      • PG = Plan guide
      • R = Rule (old-style, stand-alone)
      • SN = Synonym
      • SO = Sequence object

1. Transakcie (TRANSACTION)

  • Tvoria základ pre konkurentné vykonávanie a zotavenie sa po zlyhaní systému
  • 4 dôležité vlastnosti, ktoré transakcia musí mať (kvôli konkurentným prístupom a možnému zlyhaniu systému):
    • Atomic - Atomickosť (buď sa vykoná celá alebo sa databáza musí dostať do stavu, akoby sa nevykonala vôbec)
    • Consistency - Konzistentnosť (po transakcii musí byť DB znova konzistentná – zachované integritné obmedzenia)
    • Isolation - Nezávislosť (sú nezávislé od iných transakcií)
    • Durability - Trvanlivosť (po potvrdení vykonania sú zmeny uložené)

Príklad

2. Spúšťače (TRIGGER)

Príklad

3. B-stromy a indexy (INDEX)

3.1. Indexy

Hlavnou výhodou indexu je možnosť rýchleho vyhľadávania záznamov podľa nejakej podmienky.

Návrh indexu

Výber indexu a jeho typu treba dôkladne premyslieť. Existujú všeobecné odporúčania, kedy (ne)použiť index a aký typ. SQL Server má index-tuning system: Database Tuning Advisor (DTA).

Dobrý index
  • Ak tabuľka je viac dopytovaná ako modifikovaná
  • Stĺpce, ktoré nie sú často modifikované
  • Stĺpec/-ce s cudzím kľúčom
  • Stĺpec, ktorý má veľa odlišných hodnôt
  • Stĺpce vo WHERE klauzule
  • Dopyty s ORDER BY / JOIN / agregačné
Zlý index
  • Ak tabuľka je viac modifikovaná ako dopytovaná
  • Tabuľka s malým počtom riadkov
  • Stĺpec, ktorý má málo odlišných hodnôt
  • Stĺpec, ktorý sa v dopyte nepoužíva
  • Stĺpce s dátovými typmi: text, ntext, image, a bit

Treba mať na pamäti, že ak index sa skladá z viacerých atribútov, potom pri vkladaní nových riadkov alebo modifikovaní daných stĺpcov prebieha viac dodatočných úkonov, súvisiacich s údržbou indexu.

Výhody jedinečného/unique indexu
  • Viacstĺpcové jedinečné indexy zaručujú, že žiadne dva riadky v tabuľke nemôžu mať rovnakú kombináciu hodnôt pre tieto stĺpce
  • Jedinečné indexy zabezpečujú integritu údajov definovaných stĺpcov
  • Jedinečné indexy poskytujú dodatočné informácie užitočné pre optimalizátor dotazov, ktorý môže vytvárať efektívnejšie plány vykonávania.

Materiál na študovanie

Príklad

3.3. B-stromy

B+ strom je univerzálny (rôzne kľúče), jeho hlavnou výhodou je rozsahové vyhľadávanie, má dynamickú štruktúru.

Príklad

4. Kurzory (CURSOR)

Príklad

5. Kontingenčná tabuľka (PIVOT)

Príklad

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.

5. zadanie

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

  • MAX
                                 
-- 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.

  • COUNT
                                 
-- 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.

  • COUNT
                                 
-- 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.

  • COUNT
  • DISTINCT
                                 
-- 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.

  • COUNT
                                 
-- 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.

  • COUNT
                                 
-- 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.

  • COUNT
                                 
-- 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.

  • BETWEEN
                                 
-- Navstevy medzi 5.7.2008 a 9.9.2008
SELECT 
   * 
FROM navstevy
WHERE den BETWEEN '2008-07-05' AND '2008-09-09';
                                 
                              
17. zadanie

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.

  • CROSS JOIN
  • (INNER) JOIN
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
                                 
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.

  • JOIN
                                 
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.

  • JOIN
                                 
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.

  • JOIN
                                 
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.

  • JOIN
                                 
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.

  • 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;
                                 
                              
7. zadanie

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

  • JOIN
                                 
-- 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.

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.

  • UNION
                                 
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ť.

  • UNION ALL
                                 
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.

  • INTERSECT
  • IN
  • EXISTS
  • INNER JOIN
  • NATURAL JOIN
                                 
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.

  • EXCEPT
  • NOT IN
  • NOT EXISTS
  • LEFT OUTER JOIN
                                 
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.

  • JOIN
  • UNION
                                 
-- 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.

  • JOIN
  • IN
  • EXISTS
                                 
-- 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.

  • JOIN
  • IN
  • ANY
  • EXISTS
                                 
-- 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.

  • JOIN
  • IN
  • COUNT
  • SUM
                                 
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.

  • JOIN
  • IN
                                 
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'
      )
   );
                                 
                              
11. zadanie

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

  • MAX
                                 
-- 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.

  • MAX
                                 
-- 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.

  • MAX
                                 
-- 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.

  • SUM
                                 
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.

  • SUM
  • WITH ROLLUP
                                 
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.

  • INSERT SELECT
                                 
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).

  • INSERT SELECT
                                 
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á.

  • UPDATE
                                 
SET SQL_SAFE_UPDATES = 0;

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

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

  • ALTER TABLE
  • ADD COLUMN
                                 
-- 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.

  • ALTER TABLE
  • MODIFY COLUMN
  • DEFAULT
                                 
-- 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.

  • ALTER TABLE
  • CHANGE COLUMN
                                 
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.

  • ALTER TABLE
  • DROP COLUMN
                                 
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é.

  • INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                                 
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.

  • PRIMARY KEY
                                 
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.

  • PRIMARY KEY
                                 
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.

  • CHECK
                                 
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.

  • CHECK
                                 
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.

  • UNIQUE
                                 
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.

  • FOREIGN KEY
                                 
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.

  • FOREIGN KEY
                                 
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.

  • FOREIGN KEY
                                 
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.

  • RENAME TABLE
                                 
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