Databázové systémy a R v datové vědě Veronika Eclerová eclerova@math.muni.cz Přírodovědecká fakulta, Masarykova Universita 28. března 2022 Úvod do databázových systémů Úvod do datové vědy Entity-relationship model Relační databáze Úvod do jazyka SQL Základní příkazy jazyka SQL DML - pohledy, uložené funkce DDL - vytváření tabulek, mazání tabulek, úprava tabulek Systémové databáze a jejich funkce Programování v SQL (T-SQL) V.Eclerová ·Databáze ·28. března 2022 2 / 51 Úvod do databázových systémů Datová věda Datová věda Data Science metody pro sběr dat zpracování dat interpretace dat Věda založená na datech Data intensive science astronomie, molekulární biologie, epidemiologie ... objev jsou podpořeny nasbíranými a zpracovanými daty V.Eclerová ·Databáze ·28. března 2022 3 / 51 Úvod do databázových systémů Datová věda Johan Gregor Mendel1 1822-1884 je považován za otce genetiky 1866 publikoval Pokusy s rostlinnými hybridy 1 mendelmuseum.muni.cz/o-muzeu/gregor-johann-mendel V.Eclerová ·Databáze ·28. března 2022 4 / 51 Úvod do databázových systémů Datová věda Postup při návrhu databáze analýza tvorba datového modelu návrh designu databáze implementace Cíle 1. uklání dat 2. změna dat 3. vyhodnocení dat V.Eclerová ·Databáze ·28. března 2022 5 / 51 Úvod do databázových systémů E-R model Tvorba datového modelu Entity-relationship model = E-R model I entita = konkrétní věc, kterou chce uživatel sledovat (například konkrétní územní celky: okres Benešov, Středočeský kraj) třída entit = soubor všech entit (např. všech okresů, krajů) atribut = charakteristika dané entity Speciálními atributy jsou identifikátory. Jedná se například o atributy příjmení, název, kód (např. CZNUTS). Kód CZNUTS je jednoznačný identifikátor. Naopak příjmení obecně není jednoznačný identifikátor. Jméno a příjmení je složený identifikátor. V.Eclerová ·Databáze ·28. března 2022 6 / 51 Úvod do databázových systémů E-R model Tvorba datového modelu Entity-relationship model = E-R model II vztah (relationship) = vazba entity na jinou entitu Binární vztah Vztah mezi dvěma třídami entit. Existuje několik typů binárních vztahů 1:1 - one-to-one 1:N - one-to-many N:M - many-to-many Speciálním typem vztahů jsou rekurzivní vztahy, které mohou sloužit například k definování stromových struktur. ke grafické reprezentaci E-R modelu slouží entity-relationship (E-R) diagramy V.Eclerová ·Databáze ·28. března 2022 7 / 51 Úvod do databázových systémů E-R model Osoba Okres Místo bydli?t? 1N Jméno P?íjmení Datum nast?hování CZNUTS Název V.Eclerová ·Databáze ·28. března 2022 8 / 51 Úvod do databázových systémů Relační databáze Relační databáze základním stavebním blokem jsou datové tabulky neboli relace v datové tabulce jsou uložena data týkající se jedné oblasti řádky datové tabulky = záznamy: obsahují data týkající se konrétního případu/výskytu = instance sloupce datové tabulky = pole: obsahují konkrétní charakteristiku sledovanou pro všechny řádky pokud pro některé záznam není uvedena hodnota některého pole, je v databázi reprezentována NULL hodnotou záznamy v datové tabulce je možné jednoznačně identifikovat pomocí pole tzv. primárního klíče kandidátní klíč je atribut (skupina atributů), kterým jsou jednoznačně určeny řádky v tabulce Primární klíč Často je realizován pomocí sloupce ID - sloupec nenese žádný význam, ale zajišťuje jednoznačnou identifikaci. V.Eclerová ·Databáze ·28. března 2022 9 / 51 Úvod do databázových systémů Relační databáze Příklad - datová tabulka okres sloupce řádky V.Eclerová ·Databáze ·28. března 2022 10 / 51 Úvod do databázových systémů Relační databáze vztahy mezi tabulkami jsou realizovány prostřednictví primárního klíče primární klíč jedné tabulky je uložen jako pole druhé tabulky, toto pole se nazývá cizí klíč Primární a cizí klíč Tabulka okres obsahuje pole kr_kod, které je cizím klíčem do tabulky kraj. V.Eclerová ·Databáze ·28. března 2022 11 / 51 Úvod do databázových systémů Relační databáze Základní charakteristiky relačního modelu 1. Řádky obsahují informace o entitách 2. Sloupce obsahují hodnoty atributů vztahující se k dané entitě 3. Každé pole tabulky obsahuje jedinou hodnotu 4. Všechny údaje v jednom sloupci mají stejný typ (domain integrity constraint) 5. Každý sloupec má v rámci tabulky jednoznačné jméno 6. Na pořadí řádků nezáleží 7. Na pořadí sloupců nezáleží 8. Žádné dva řádky neobsahují shodná data V.Eclerová ·Databáze ·28. března 2022 12 / 51 Úvod do databázových systémů Relační databáze Tabulka nesplňující relační schéma V.Eclerová ·Databáze ·28. března 2022 13 / 51 Úvod do databázových systémů Relační databáze Tabulka nesplňující relační schéma komentář v řádku s kódem 1 obsahuje více než jednu informaci u paní Zelené v řádku 3 jsou uvedeny dva emaily. V.Eclerová ·Databáze ·28. března 2022 13 / 51 Úvod do databázových systémů Relační databáze Databázový diagram Grafická reprezentace databáze, která obsahuje názvy jednotlivých datových tabulek a jejich polí. Označuje primární klíče datových tabulek a znázorňuje relace mezi datovými tabulkami. V.Eclerová ·Databáze ·28. března 2022 14 / 51 Úvod do databázových systémů Relační databáze Příklad - reprezentace binárních vztahů Uvažujte následující tři situace a rozhodněte, která nejlépe odpovídá binárnímu one-to-one, one-to-many, many-to-many: Uvažujte skupinu vysokoškolských studentů, kteří si zapisují různé předměty. U studentů sledujeme atributy: jméno, příjmení, ročník, typ studia. U předmětů sledujeme atributy: název, vyučující, rozvrhovaný čas začátku a konce. Uvažujte skupinu tanečnic a tanečníků. U tanečnic sledujeme: jméno, příjmení, tělesné míry a výšku. U tanečníků sledujeme: jméno, příjmení, výšku a váhu. Dále sledujeme informaci o vytvořených tanečních párech. Uvažujte skupinu zaměstnanců, kteří jsou zaměstnání na různých odděleních dané firmy. U zaměstnanců sledujeme: jméno, příjmení, pracovní pozici. U oddělení sledujeme: název, jméno ředitele, adresu sídla. Navrhněte různé způsoby ukládání těchto dat. V.Eclerová ·Databáze ·28. března 2022 15 / 51 Úvod do databázových systémů Relační databáze Příklad - uložení dat o organizačním členění v České republice Uvažujme data, která obsahují různé organizační celky v České republice (okresy, kraje, oblasti). U každého s těchto celků známe zkrácený název název kód CZNUTS. V případě okresu víme, do kterého patří kraje. V případě kraje víme, do které patří oblasti. U okresu známe jeho počet obcí a rozlohu. Navrhněte různé způsoby ukládání těchto dat. V.Eclerová ·Databáze ·28. března 2022 16 / 51 Úvod do databázových systémů Relační databáze Příklad - uložení dat o hlášených případech COVID 19 Uvažujme data, která obsahují různé hlášení o událostech (úmrtí, vyléčení, uzdravení) v souvislosti s nemocí COVID 19 v České republice. U každé události známe datum, kdy nastala věk osoby, u které nastala pohlaví osoby, u které nastala okres, ve kterém nastala. Navrhněte různé způsoby ukládání těchto dat. V.Eclerová ·Databáze ·28. března 2022 17 / 51 Úvod do databázových systémů Relační databáze Příklad - uložení dat o historii bydliště Uvažujme data, která obsahují pro různé osoby informace o jejich bydlišti. Chcete ukládat nejen aktuální data, ale i všechna předešlá bydliště dané osoby, konkrétně: jméno a příjmení osoby rok, kdy osoba začala bydlet na daném místě okres, ve kterém osoba bydlí/bydlela zpřesňující informace k adrese (přesná adresa, město ...). Navrhněte různé způsoby ukládání těchto dat. V.Eclerová ·Databáze ·28. března 2022 18 / 51 Úvod do databázových systémů Relační databáze Princip návrhu relačních databází, normalizace kandidátní klíč je atribut (skupina atributů), který jednoznačně identifikuje řádek relace (tabulky) funkční závislost je vztah mezi dvěma atributy v rámci jedné tabulky Atribut Y je funkčně závislý na atributu X, pokud každá platná hodnota X určuje Y jednoznačně. X se nazývá určující atribut (determinant), Y se nazývá závislý atribut (dependent). Funkční závislost Atributy jméno, příjmení, věk, pohlaví jsou funkčně závislé na atributu rodné číslo. V.Eclerová ·Databáze ·28. března 2022 19 / 51 Úvod do databázových systémů Relační databáze Princip návrhu relačních databází 1. Relace (tabulka) se nazývá správně vytvořená (well-formed), jestli je každý její určující atribut zároveň kandidátním klíčem. 2. Každá relace, která není správně vytvořená, by měla být rozdělena na jednu nebo více dobře vytvořených relací. normalizace proces zkoumání a přetváření relací tak, aby byly správně vytvořeny (dle principu návrhu relačních databází) normální formy jsou sady pravidel, jejichž splnění vyžadujeme v relačním databázovém návrhu V.Eclerová ·Databáze ·28. března 2022 20 / 51 Úvod do databázových systémů Relační databáze Proces normalizace 1. Identifikujte všechny kandidátní klíče. 2. Identifikujte všechny funkční závislosti. 3. Zjistěte určující atributy všech funkčních závislostí a ověřte, že jsou kandidátními klíči. Pokud nejsou: 3.1 Vytvořte novou relaci (tabulku), do které umístěte všechny určující i závislé atributy problematické funkční závislosti. 3.2 Jako primární klíč nové relace zvolte určující atribut/y sledované funkční závislosti. 3.3 Ponechte kopii určujícího/ch atributu/ů v původní relaci a označte je jako cizí klíč. 4. Opakujte krok 3. V.Eclerová ·Databáze ·28. března 2022 21 / 51 Úvod do databázových systémů Relační databáze Příklad - uložení dat o ubytování studentů na kolejích Uvažujme data o studentech a kolejích, na kterých jsou ubytování. Příklad takto získaných dat je vidět na obrázku: Upravte uložení dat tak, aby respektovalo principy návrhu relačních databází. V.Eclerová ·Databáze ·28. března 2022 22 / 51 Úvod do databázových systémů Relační databáze Hodnocení studentů Uvažujme data o známkách studentů získaných v jednotlivých předmětech. Příklad takto získaných dat je vidět na obrázku: Upravte uložení dat tak, aby respektovalo principy návrhu relačních databází. V.Eclerová ·Databáze ·28. března 2022 23 / 51 Úvod do databázových systémů Relační databáze Integritní omezení Pravidla/systémy pravidel, která zaručují integritu databáze. Příklady unikátnost zadaných hodnot v rámci datových tabulek daný datový typ nebo rozsah hodnot v tabulkách primární a cizí klíče Hodnocení studentů Uvažujme data o známkách studentů získaných v jednotlivých předmětech (viz předchozí příklad). Vymyslete vhodná integritní omezení pro tato data. V.Eclerová ·Databáze ·28. března 2022 24 / 51 SQL Základní příkazy Základy jazyka SQL SQL = Structured Query Language Základní součásti jazyka SQL: Data definition language (DDL): vytváření tabulek, vztahů a dalších struktur datbáze Data manipulation language (DML): pohledy na data, ukládání, změna a mazání dat SQL/Persistent stored modules (SQL/PSM): příkazy, které umožňují procedurální programování v SQL Transaction control language (TCL): příkazy, které umožňují definování transakcí a jejich řízení Data control language (DCL): příkazy umožňují zprávu přístupu k databázi V.Eclerová ·Databáze ·28. března 2022 25 / 51 SQL Základní příkazy DML - pohledy na data Základní syntaxe select výběr polí tabulky from výběr tabulky where podmínky na zobrazované záznamy DATOVÉ TYPY řetězec: char, varchar číslo: int, numeric, float datum: datetime identifikátory: uniqueidentifier Klíčové slovo AS slouží k přejmenování sloupců tabulky/celých tabulek v rámci dotazů. Je vhodné při použití klausule SELECT a při spojování tabulek. V.Eclerová ·Databáze ·28. března 2022 26 / 51 SQL Základní příkazy Příklad - výpis dat z tabulky okresů Vypište dlouhý název a CZNUTS okresů za následujících podmínek: všech okresů ČR pouze okresů v Jihomoravském kraji pouze okresů v Jihomoravském kraji nebo Zlínském kraji pouze okresů, které nejsou v Jihomoravském kraji ani Zlínském kraji okresů, kde počet obcí převyšuje 100 okresů, jejichž rozloha je mezi 700 a 800 km2 Výpis opakujte, ale tentokrát zobrazte všechny sloupce tabulky. V.Eclerová ·Databáze ·28. března 2022 27 / 51 SQL Základní příkazy ORDER BY slouží k seřazení záznamů ve výpisu dat, zapisuje se za klausuli „where“ klíčová slova ASC a DESC slouží k určení vzestupného resp. sestupného pořadí záznamů (vzestupné řazení je výchozí) Příklad - výpis dat z tabulky okresů Vypište dlouhý název a CZNUTS okresů za následujících podmínek: všech okresů ČR seřazených abecedně podle dlouhého názvu pouze okresů v Jihomoravském kraji seřazených abecedně podle dlouhého názvu všech okresů ČR seřazených vzestupně/sestupně podle počtu obcí všech okresů ČR seřazených nejdříve podle počtu obcí vzestupně a v případě stejného počtu obcí podle rozlohy sestupně V.Eclerová ·Databáze ·28. března 2022 28 / 51 SQL Základní příkazy Rozšířená syntaxe select výběr polí tabulky from výběr tabulky where podmínky aplikované na tabulku před agregací group by seznam sloupců, podle kterých se agreguje having podmínky na zobrazené záznamy order by sloupce, podle kterých se provádí řazení Příklady agregačních funkcí MAX, MIN, COUNT, COUNT + DISTINCT, SUM, AVG, VAR další agregační funkce lze najít na tomto odkazu V.Eclerová ·Databáze ·28. března 2022 29 / 51 SQL Základní příkazy Příklad - výpis dat z tabulky o případech nákazy COVID-19 Vypište celkový počet nově nakažených, uzdravených a vyléčených v celé tabulce. Kolik řádků v tabulce nemá vyplněný okres? Vypište celkový počet nově nakažených, uzdravených a vyléčených po jednotlivých dnech v dubnu a květnu 2020. Data vhodně seřaďte. Vypište celkový počet nově nakažených, uzdravených a vyléčených po jednotlivých měsících. Data vhodně seřaďte. Vypište průměrný, maximální a minimální počet nově nakažených mužů za den. Jakou má výpis limitaci? Vypište průměrný počet nově nakažených mužů za den po měsících. Vypište měsíc, ve kterém byl nejvyšší počet nově nakažených a jejich počet. V.Eclerová ·Databáze ·28. března 2022 30 / 51 SQL Základní příkazy Spojování tabulek cross join: jedná se o operaci, která zprostředkovává kartézský součin tabulek; syntaxe v jazyce SQL je oddělení jednotlivých tabulek čárkou (inner) JOIN: je podmnožinou kartézského součinu tabulek; podmnožina je definována logickým výrazem uvedeným za klíčovým slovem ON LEFT JOIN: výsledná tabulka obsahuje (i) všechny záznamy „levé“ tabulky, (ii) podmnožinu kartézského součinu, která je definována logickým výrazem uvedeným za klíčovým slovem ON; pokud záznam z „levé“ tabulky nesplňuje podmínky na přiřazení žádného záznamu „pravé“ tabulky je řádek doplněn hodnotami NULL RIGHT JOIN: lze jej definovat podobně jako LEFT JOIN FULL JOIN: lze jej definovat podobně jako LEFT JOIN nebo RIGHT JOIN; výsledná tabulka obsahuje (i) všechny záznamy „levé“ i „pravé“ tabulky V.Eclerová ·Databáze ·28. března 2022 31 / 51 SQL Základní příkazy Příklad - výpis dat z tabulky okresů a krajů Vypište tabulku obsahující všechny okresy společně s příslušnými kraji a oblastmi. Užijte různé varianty datových návrhů. Vypište všechny okresy v oblasti Severozápad, které mají více než 100 obcí. Vypište průměrný počet obcí v okrese pro všechny okresy v oblasti Severozápad. Vypište celkovou rozlohu jednotlivých oblastí ČR. Příklad - výpis dat z tabulky o případech nákazy COVID-19 Vypište celkový počet nově nakažených mužů po dnech v Jihomoravském kraji. Vypište průměrný počet nově nakažených mužů za den po měsících a krajích. Vypište dny, kdy nebyl žádný nově nakažený od počátku března 2020 do konce roku 2020. V.Eclerová ·Databáze ·28. března 2022 32 / 51 SQL Základní příkazy Pořadí prováděných operací a vliv na rychlost dotazů U komplikovanějších dotazů je vhodné dotazy optimalizovat za účelem zvýšení rychlosti nebo snížení užitích zdrojů. Jeden z nástrojů, který se dá využít jsou tzv. „execution plans“,více informací zde. Nástroje používané ke zvýšení rychlosti indexy - struktura uložená na disku, která umožňuje rychlé vyhledávání v tabulkách a spojování tabulek. Automaticky se vytváří pro primární klíče. dočasné tabulky - umožňují dočasně uložit část dat (například agregovaných) potřebných pro běh dotazu. V.Eclerová ·Databáze ·28. března 2022 33 / 51 SQL Základní příkazy Příklad - execution plans, indexy V obou následujících případech užijte příkazy: set statistics time on na začátku skriptu set statistics time off na konci skriptu. Zkorodujte si SQL Server Execution Times na záložce Messages. Z tabulky nakaza vypište celkový počet případů nákaz zaznamenaných v Jihomoravském kraji. Dotaz proveďte (i) s použitím příkazu join, (ii) s použitím vnořeného dotazu. Zobrazte v obou případech předpokládaný a reálný execution plan. Vytvořte vhodný index na tabulce nakaza. Výpis opakujte. Viz slide 30: Vypište měsíc, ve kterém byl nejvyšší počet nově nakažených a jejich počet. Výpis proveďte bez použití dočasné tabulky a s použitím dočasné tabulky. V.Eclerová ·Databáze ·28. března 2022 34 / 51 SQL DML - pohledy, uložené funkce Pohledy, uložené funkce Pohled na data CREATE VIEW název tabulky AS SELECT název sloupce 1, název sloupce 2, ... FROM název tabulky (tabulka nebo spojení několika tabulek) WHERE podmínky Funkce vracející tabulku CREATE FUNCTION název funkce ( název parametru 1 a datový typ, název parametru 2 a datový typ, ... ) RETURNS TABLE AS RETURN ( SELECT ... ) V.Eclerová ·Databáze ·28. března 2022 35 / 51 SQL DML - pohledy, uložené funkce Pohledy, uložené funkce Funkce vracející jednu hodnotu CREATE FUNCTION název funkce ( název parametru 1 a datový typ, název parametru 2 a datový typ, ... ) RETURNS datový typ výsledku AS BEGIN DECLARE názvy lokálních proměnných a datový typ SELECT ... RETURN ... END V.Eclerová ·Databáze ·28. března 2022 36 / 51 SQL DML - pohledy, uložené funkce Příklad - uložené funkce se skalárním výstupem Vytvořte funkci, která pro zadané datum mezi lety 2010 a 2030 vrátí den v týdnu, který v zadaný den byl/bude. Vytvořte funkci, která pro dané rozmezí věků a rok vrátí počet úmrtí na COVID-19. Vytvořte funkci, která pro zadaný CZNUTS (okresu, kraje nebo oblasti) vrátí název. V.Eclerová ·Databáze ·28. března 2022 37 / 51 SQL DML - pohledy, uložené funkce Příklad - pohledy na data Vytvořte pohled, který vytvoří tabulku obsahující ve sloupcích název oblasti, kraje, okresu. Užijte různé datové návrhy. Vytvořte pohled, který vytvoří tabulku obsahující ve sloupcích název oblasti, kraje, rozlohu kraje a počet obcí v kraji. Užijte různé datové návrhy. Viz slide 30: Vypište měsíc, ve kterém byl nejvyšší počet nově nakažených a jejich počet. Výpis proveďte s použitím pohledu. Porovnejte s užitím dočasné tabulky z pohledu (i) rychlosti, (ii) využití diskové kapacity. Vytvořte pohled, který vrátí počet pracovních a nepracovních dní v každém měsíci po letech za roky 2022-2024. V.Eclerová ·Databáze ·28. března 2022 38 / 51 SQL DML - pohledy, uložené funkce Příklad - uložené funkce Vytvořte pohled, který vrátí počet pracovních a nepracovních dní pro všechny měsíce zvoleného roku. Vytvořte funkci, která pro zvolenou organizační jednotku dle CZNUTS (pro libovolnou úroveň klasifikace) vrátí název této jednotky a všech podřízených. V.Eclerová ·Databáze ·28. března 2022 39 / 51 SQL DML - pohledy, uložené funkce Ukládání dat INSERT INTO název tabulky(název sloupce 1, název sloupce 2, ...) VALUES (hodnota 1, hodnota 2...) INSERT INTO název tabulky(název sloupce 1, název sloupce 2, ...) SELECT ... SELECT hodnota 1, hodnota 2... INTO název tabulky (nová tabulka) FROM název tabulky (tabulka nebo spojení několika tabulek) WHERE podmínky V.Eclerová ·Databáze ·28. března 2022 40 / 51 SQL DML - pohledy, uložené funkce Změna dat a mazání dat UPDATE název tabulky SET název sloupce 1=hodnota 1, název sloupce 2=hodnota 2 WHERE podmínky DELETE FROM název tabulky (tabulka nebo spojení několika tabulek) WHERE podmínky V.Eclerová ·Databáze ·28. března 2022 41 / 51 SQL DDL - vytváření tabulek, mazání tabulek, úprava tabulek Vytváření tabulek, mazání tabulek, úprava tabulek Vytváření tabulky: CREATE TABLE název tabulky (název sloupce 1, název sloupce 2, ...) Přidání sloupce: ALTER TABLE název tabulky ADD název sloupce [datový typ](délka datového typu); Smazání všech záznamů z tabulky: TRUNCATE TABLE název tabulky Smazání tabulky: DROP TABLE název tabulky V.Eclerová ·Databáze ·28. března 2022 42 / 51 SQL DDL - vytváření tabulek, mazání tabulek, úprava tabulek Příklad - data o onemocnění COVID-19 Vytvořte novou tabulku, do které vložte agregovaná data obsahující celkový počet nově nakažených osob pro každý den od začátku pandemie do posledního data uvedeného v tabulce. Na webu najděte údaje pro 5.5.2021 a vložte je ručně do tabulky. Záznamy v tabulce aktualizujte. Vypočtěte rozlohu ČR a data přepočtěte na km2. Smažte z tabulky všechny záznamy, které se netýkají roku 2020. Přidejte do tabulky nový sloupec určující den v týdnu. Naplňte korektně sloupec „den v týdnu“. Smažte z tabulky všechny záznamy. Vložte do tabulky záznamy, ale nyní data mají obsahovat pouze celkový počet nově nakažených žen nemocí COVID-19 pro každý den. Nevyplňujte sloupec „den v týdnu“. Odstraňte celou tabulku. V.Eclerová ·Databáze ·28. března 2022 43 / 51 SQL Programování v SQL (T-SQL) Programování v SQL (T-SQL) Větvení: IF logický výraz BEGIN Sekvence příkazů END ELSE BEGIN Sekvence příkazů END CASE vstupní výraz WHEN výraz k porovnání THEN výstupní výraz [ ELSE výstupní výraz ] END CASE WHEN logický výraz THEN výstupní výraz [ ELSE výstupní výraz ] END V.Eclerová ·Databáze ·28. března 2022 44 / 51 SQL Programování v SQL (T-SQL) Cykly: WHILE logický výraz BEGIN Sekvence příkazů END BREAK a CONTINUE - umožňují v kombinaci z příkazem IF předčasně ukončit cyklus WHILE V.Eclerová ·Databáze ·28. března 2022 45 / 51 SQL Programování v SQL (T-SQL) Kursor - umožňuje spouštět cyklus přes výsledek příkazu SELECT DECLARE název proměnné datový typ DECLARE název kurzoru CURSOR FOR Příkaz select OPEN název kurzoru FETCH NEXT FROM název kurzoru INTO název proměnné WHILE @@FETCH_STATUS = 0 BEGIN Sekvence příkazů FETCH NEXT FROM název kurzoru INTO název proměnné END CLOSE název kurzoru DEALLOCATE název kurzoru V.Eclerová ·Databáze ·28. března 2022 46 / 51 SQL Programování v SQL (T-SQL) Uložená procedura: SET NOCOUNT ON - umožňuje pozastavit výpis počtu provedených operací v SQL, je doporučeno ho použít u uložených procedur CREATE PROCEDURE název uložené procedury (název proměnné datový typ) AS BEGIN SET NOCOUNT ON Sekvence příkazů END V.Eclerová ·Databáze ·28. března 2022 47 / 51 SQL Programování v SQL (T-SQL) Příklad - programování v SQL Užijte příkaz CASE k získání celkového počet nákaz, úmrtí a vyléčení pro každý den zvoleného měsíce z tabulky nakaza. Dotaz proveďte bez použití vnořeného příkazu SELECT a příkazu JOIN. Vytvořte kursor přes všechny měsíce roku 2020. Pro každý s těchto měsíců proveďte příkaz z předchozího bodu. Vytvořte novou prázdnou tabulku o čtyřech sloupcích (datum, počet nákaz, úmrtí a vyléčení). Vytvořte uloženou proceduru, která tuto funkci naplní pro zvolený měsíc pomocí dotazu z prvního bodu v této úloze. Užijte data z tabulky vytvořené v předchozím bodu a vypočtěte kumulativní počet nákaz, úmrtí a smrtí pro všechny dny v březnu 2021. Užijte příkaz JOIN. V.Eclerová ·Databáze ·28. března 2022 48 / 51 SQL Programování v SQL (T-SQL) Příklad - programování v SQL Nepovinné: Vytvořte uloženou funkci, která bude pro zvolený časový úsek (vstupem je datum od a datum do) vypisovat (i) celkový počet nových nákaz, úmrtí a vyléčení, které nastaly před vybraným měsícem (1 řádek); (ii) celkový počet nových nákaz, úmrtí a vyléčení pro každý den zvoleného měsíce; (iii) celkový počet aktivních případů nákazy pro každý den zvoleného měsíce. Data organizujte do jedné datové tabulky. Užijte příkaz UNION. V.Eclerová ·Databáze ·28. března 2022 49 / 51 SQL Programování v SQL (T-SQL) Trigger Trigger je databázový objekt, který zahajuje svoji činnost ve chvíli, kdy se v databázi stane nějaká událost. Triggery dělíme na DDL Trigger - spouští se po zavolání příkazu CREATE, DROP nebo ALTER DML Trigger spouští se po zavolání (AFTER/FOR Triggery) nebo místo (INSTEAD Of Triggery) příkazu INSERT, UPDATE, DELETE V.Eclerová ·Databáze ·28. března 2022 50 / 51 SQL Systémové databáze a jejich funkce Systémové databáze a jejich funkce Katalog objektů systémové databáze Příklad Vypište všechny tabulky a sloupce tabulek, které byly vytvořeny v březnu. select o.name,c.name from sys.columns c join sys.objects o on o.object_id=c.object_id where o.type_desc=’USER_TABLE’ and month(o.create_date)=3 order by o.object_id, c.column_id V.Eclerová ·Databáze ·28. března 2022 51 / 51