Autor, Název akce Databázové systémy a SQL Lekce 10 Monika Kratochvílová, Daniel Klimeš Autor, Název akce Funkce bez argumentu - cvičení •Pokračování z minulého týdne … Daniel Klimeš, Databázové systémy a SQL Autor, Název akce Funkce bez argumentu - cvičení •Vytvořte postupně funkci, v rámci které provedete: 4.Nakategorizujete některé proměnné • Daniel Klimeš, Databázové systémy a SQL 1.Vytvořte nový sloupce vek (v letech) a vek_kategorie (0-44/45-69/70 a starší) 2.Vytvořte nový sloupec nador s hodnotami NEGATIVNI (vysledek_vys 1,2), BENIGNI (vysledek_vys 3,4), MALIGNI (vysledek_vys >=5) a NEZNAMO (vysledek_vys NULL) 3. Autor, Název akce Funkce bez argumentu - cvičení •Vytvořte postupně funkci, v rámci které provedete: 4.Nakategorizujete některé proměnné • Daniel Klimeš, Databázové systémy a SQL 1.Vytvořte nový sloupce vek (v letech) a vek_kategorie (0-44/45-69/70 a starší) 2.Vytvořte nový sloupec nador (negativni/benigni/maligni/neznamo) ALTER TABLE mamo_scr ADD COLUMN vek NUMERIC(2); UPDATE mamo_scr SET vek=vek_roky(datum_narozeni); ALTER TABLE mamo_scr ADD COLUMN vek_kategorie VARCHAR(20); UPDATE mamo_scr SET vek_kategorie = CASE WHEN vek < 45 THEN '0 - 44' WHEN vek < 70 THEN '45 - 69' ELSE '70 a starší' END; ALTER TABLE mamo_scr ADD COLUMN nador VARCHAR(20); UPDATE mamo_scr SET nador = CASE WHEN vysledek_vys IN (1,2) THEN 'NEGATIVNI' WHEN vysledek_vys IN (3,4) THEN 'BENIGNI' WHEN vysledek_vys >= 5 THEN 'MALIGNI' ELSE 'NEZNAMO' END; Autor, Název akce Funkce bez argumentu - cvičení •Vytvořte postupně funkci, v rámci které provedete: 5.Všechny předchozí dotazy pro update a delete zaobalte do funkce • Daniel Klimeš, Databázové systémy a SQL Autor, Název akce Funkce bez argumentu - cvičení •Vytvořte postupně funkci, v rámci které provedete: 5.Všechny předchozí dotazy pro update a delete zaobalte do funkce • Daniel Klimeš, Databázové systémy a SQL CREATE OR REPLACE FUNCTION mamo_scr() RETURNS VOID AS $$ BEGIN Zde vložte všechny předchozí dotazy pro update a delete (Pokud budete spouštět funkci opakovaně, musíte zajistit, aby se vám data neduplikovala, proto musíte do funkce vložit dotaz pro vymazání všech dat!!) END; $$ LANGUAGE PLPGSQL; SELECT mamo_scr(); Autor, Název akce Funkce mamo_scr() Daniel Klimeš, Databázové systémy a SQL •CREATE OR REPLACE FUNCTION mamo_scr() •RETURNS VOID AS $$ •BEGIN •DELETE FROM mamo_scr; •COPY mamo_scr (id_pacientky, datum_narozeni, kraj, metoda, datum_vys, výsledek_vys) •FROM '/home/kratochvilova/vyuka/mamo_scr.csv CSV delimiter ';' header null ''; •DELETE FROM mamo_scr WHERE datum_narozeni IS NULL OR datum_vys IS NULL OR vysledek_vys IS NULL; •DELETE FROM mamo_scr WHERE datum_narozeni > datum_vys OR datum_vys > CURRENT_DATE OR datum_narozeni > CURRENT_DATE; •UPDATE mamo_scr SET kraj = '999' WHERE kraj IS NULL; •UPDATE mamo_scr SET metoda = NULL WHERE metoda <1 OR metoda > 9; •UPDATE mamo_scr SET vysledek_vys = NULL WHERE vysledek_vys <1 OR vysledek_vys > 10; •UPDATE mamo_scr SET vek=vek_roky(datum_narozeni); •UPDATE mamo_scr SET vek_kategorie = CASE WHEN vek < 45 THEN '0 - 44‚ WHEN vek < 70 THEN '45 - 69‚ ELSE '70 a starší' END; •UPDATE mamo_scr SET nador = CASE WHEN vysledek_vys IN (1,2) THEN 'NEGATIVNI‚ WHEN vysledek_vys IN (3,4) THEN 'BENIGNI‚ WHEN vysledek_vys >= 5 THEN 'MALIGNI‚ ELSE 'NEZNAMO' END; •END; •$$ LANGUAGE PLPGSQL; Autor, Název akce Manuál k PostgreSQL •https://www.postgresql.org/docs/manuals/ •» vybrat si verzi serveru • • • • • Daniel Klimeš, Databázové systémy a SQL Autor, Název akce Manuál k PostgreSQL •» příkaz COPY •https://www.postgresql.org/docs/9.5/static/sql-copy.html • • • • • Daniel Klimeš, Databázové systémy a SQL Autor, Název akce Update FROM Daniel Klimeš, Databázové systémy a SQL •Do tabulky ucitel doplňte sloupec, ve kterém bude název předmětu, který učitel vyučuje. Autor, Název akce Update FROM Daniel Klimeš, Databázové systémy a SQL •Do tabulky ucitel doplňte sloupec, ve kterém bude název předmětu, který učitel vyučuje. ALTER TABLE ucitel ADD COLUMN predmet VARCHAR(30); UPDATE ucitel SET predmet = nazev_predmetu FROM predmet p WHERE p.ucitel_uco = ucitel.ucitel_uco; Autor, Název akce Update FROM Daniel Klimeš, Databázové systémy a SQL •Do tabulky vyuka přidejte sloupec, do které vložíte celé jméno studenta. Autor, Název akce Update FROM Daniel Klimeš, Databázové systémy a SQL •Do tabulky vyuka přidejte sloupec, do které vložíte celé jméno studenta. ALTER TABLE vyuka ADD COLUMN cele_jmeno VARCHAR(30); UPDATE vyuka SET cele_jmeno = jmeno||' '||prijmeni FROM student WHERE vyuka.student_uco = student.uco; Autor, Název akce Update FROM Daniel Klimeš, Databázové systémy a SQL •Do tabulky vyuka přidejte sloupec, do které vložíte název předmětu, do kterého je student zaregistrován. Autor, Název akce Update FROM Daniel Klimeš, Databázové systémy a SQL •Do tabulky vyuka přidejte sloupec, do které vložíte název předmětu, do kterého je student zaregistrován. ALTER TABLE vyuka ADD COLUMN nazev_predmetu VARCHAR(30); UPDATE vyuka SET nazev_predmetu = p.nazev_predmetu FROM predmet p WHERE vyuka.predmet_id = p.predmet_id; Autor, Název akce Update FROM Daniel Klimeš, Databázové systémy a SQL •Do tabulky student doplňte sloupec, který bude znázorňovat, kolik předmětů má celkově zapsaný jednotlivý student. Autor, Název akce Update FROM Daniel Klimeš, Databázové systémy a SQL •Do tabulky student doplňte sloupec, který bude znázorňovat, kolik předmětů má celkově zapsaný jednotlivý student. ALTER TABLE student ADD COLUMN pocet_predmetu NUMERIC(2); UPDATE student SET pocet_predmetu = pocet FROM (SELECT student_uco, COUNT(*) pocet FROM vyuka GROUP BY student_uco) s WHERE s.student_uco = student.uco; Autor, Název akce Procvičování na zkoušku Daniel Klimeš, Databázové systémy a SQL Autor, Název akce Procvičování na zkoušku •Zobrazte datum narození a věk u všech pacientů a pacientek ze studie 576. • •Dále zobrazte datum narození a věk u nejstaršího pacienta a nejstarší pacientky ze studie 576. Daniel Klimeš, Databázové systémy a SQL Autor, Název akce Procvičování na zkoušku •Zobrazte datum narození a věk u všech pacientů a pacientek ze studie 576. •SELECT p.sex, p.date_of_birth datum_narozeni, DATE_PART('year', AGE(p.date_of_birth)) vek •FROM patients p, patient_study ps •WHERE ps.patient_id= p.patient_id AND ps.study_id='576' •AND p.sex IN ('F','M'); • •Dále zobrazte datum narození a věk u nejstaršího pacienta a nejstarší pacientky ze studie 576. •SELECT p.sex, min(p.date_of_birth) datum_narozeni, DATE_PART('year', AGE(min(p.date_of_birth))) vek •FROM patients p, patient_study ps •WHERE ps.patient_id= p.patient_id AND ps.study_id='576' •AND p.sex IN ('F','M') •GROUP BY sex; Daniel Klimeš, Databázové systémy a SQL Autor, Název akce Procvičování na zkoušku •Zobrazte počet pacientů a jejich kumulativní počet v jednotlivých letech u studie 487. • •Agregujte pacienty dle věku a zobrazte počty pacientů ve věkových kategoriích: §mladší 50 §50 a starší §neznámo. Daniel Klimeš, Databázové systémy a SQL Autor, Název akce Procvičování na zkoušku •Zobrazte počet pacientů a jejich kumulativní počet v jednotlivých letech u studie 487. •SELECT to_char(date_of_enrollment, 'yyyy') rok,count(patient_id) pocet_pacientu, •SUM(COUNT(patient_id)) OVER (ORDER BY to_char(date_of_enrollment, 'yyyy')) kumul_pocet •FROM patient_study •WHERE study_id='487' •GROUP BY to_char(date_of_enrollment, 'yyyy') •ORDER BY to_char(date_of_enrollment, 'yyyy'); • •Agregujte pacienty dle věku a zobrazte počty pacientů ve věkových kategoriích: mladší 50, 50 a starší, neznámo. •SELECT CASE WHEN DATE_PART('year',age(date_of_birth)) <50 THEN 'kategorie <50' WHEN DATE_PART('year',age(date_of_birth))> 50 THEN 'kategorie 50 a vice' • ELSE 'neznámo' END kategorie, COUNT(*) • FROM patients •GROUP BY CASE WHEN DATE_PART('year',age(date_of_birth)) <50 THEN 'kategorie <50' WHEN DATE_PART('year',age(date_of_birth))> 50 THEN 'kategorie 50 a vice' • ELSE 'neznámo' END; Daniel Klimeš, Databázové systémy a SQL Autor, Název akce Procvičování na zkoušku •Vytvořte tabulku s názvem TAB_vaseuco se sloupci uco, datum narození a pohlaví. • •Do této tabulky vložte jeden záznam s vašimi údaji. • •Napište příkaz, kterým vymažete z této tabulky záznam, pokud je osoba narozena před vznikem České republiky. • Daniel Klimeš, Databázové systémy a SQL Autor, Název akce Procvičování na zkoušku •Vytvořte tabulku s názvem TAB_vaseuco se sloupci uco, datum narození a pohlaví. •CREATE TABLE tab_x •( •UCO NUMERIC(10), •DAT_NAR DATE, •POHLAVI VARCHAR(2) •); • •Do této tabulky vložte jeden záznam s vašimi údaji. •INSERT INTO tab_x VALUES (123456, '1.11.2000', 'F'); • •Napište příkaz, kterým vymažete z této tabulky záznam, pokud je osoba narozena před vznikem České republiky. •DELETE FROM tab_x WHERE DAT_NAR