Autor, Název akce
Databázové systémy a SQL
Lekce 2
Daniel Klimeš
1

Autor, Název akce
Operátory a funkce
Daniel Klimeš, Databázové systémy a SQL
2
+,-
Sčítání, odečítání
*,/
Násobení, dělení
=, <>, >=, <=
Rovnost, nerovnost
[NOT] IN (hodnota, hodnota, …)
Rovnost [NEROVNOST] se skupinou hodnot
||
Spojení textových řetězců
ORACLE, FB
NOT, AND, OR
Negace, logický součin, logický součet
TRUNC(x,[n])
Odstranění desetinných míst (odstranění časové frakce z datumu)
ORACLE
ROUND(x,[n])
zaokrouhlení
ORACLE, FB
CEIL()
zaokrouhlení nahoru
ORACLE, FB
DECODE
Nahrazování hodnot
ORACLE, FB
CASE WHEN podm END
Podmíněný výraz
ORACLE
NVL, NVL2
Nahrazení hodnoty NULL
ORACLE

Autor, Název akce
Operátory a funkce – práce s datumy
Daniel Klimeš, Databázové systémy a SQL
3
SYSDATE
Aktuální datum a čas
ORACLE
CURRENT_TIMESTAMP
Aktuální datum a čas
FB
TO_CHAR()
Konverze na text
ORACLE
TO_DATE()
Konverze na datum
ORACLE
Datum +- počet dnů
Přičítání, odečítání dnů
Datum - datum
Počet dnů mezi datumy (desetinná část udává časový rozdíl)
MONTHS_BETWEEN(datum, datum)
Rozdíl datumů
ORACLE
ADD_MONTHS(datum, počet)
Přičtení měsíců
ORACLE

Autor, Název akce
Operátory a funkce – práce s textem
Daniel Klimeš, Databázové systémy a SQL
4
SUBSTR(text, od, počet)
Vrací podřetězec textu dle pozice
ORACLE
INSTR(text, subtext)
Hledání podřetězce v textu, vrací pozici
ORACLE
REPLACE(text, puvodni, nove)
Nahrazení podřetězce
ORACLE, FB
LOWER(text)
Převod na malá písmena
ORACLE
UPPER(text)
Převod na velká písmena
ORACLE
LTRIM(text), RTRIM(text)
Odstranění mezer zleva zprava
ORACLE
LENGTH(text)
Délka řetězce
ORACLE
CHARACTER_LENGTH('text')
Délka řetězce
FB
TRANSLATE(text, znaky,znaky)
Nahrazení po znacích
ORACLE

Autor, Název akce
Operátory a funkce – práce s čísly
Daniel Klimeš, Databázové systémy a SQL
5
ABS(cislo)
Absolutní hodnota
SIN(cislo), COS(cislo), TAN(cislo)
POWER(cislo,exp)
Mocnina
SQRT(cislo)
Druhá odmocnina
MOD(cislo, cislo)
Zbytek po dělení
LN(cislo)
Přirozený logaritmus
LOG(cislo)
Dekadický logaritmus
EXP(x)
ex

Autor, Název akce
Operátory a funkce – agregační funkce
Daniel Klimeš, Databázové systémy a SQL
6
COUNT()
Počet
AVG()
Průměr
MIN()
Minimum
MAX()
Maximum
STDDEV()
Směrodatná odchylka
SUM()
Suma
MEDIAN()
Medián
ORACLE

Autor, Název akce
NULL, prázdná hodnota
Daniel Klimeš, Databázové systémy a SQL
7

Autor, Název akce
MODIFIKÁTOR DISTINCT
Daniel Klimeš, Databázové systémy a SQL
8
SELECT DISTINCT sloupec1 FROM tabulka; -- unikátní hodnoty sloupce
SELECT DISTINCT sloupec1, sloupec2 FROM tabulka; -- unikátní kombinace sloupců
SELECT    COUNT(*),    COUNT(sloupec),             COUNT(DISTINCT sloupec)
Počet        všech řádků,  všech NOT NULL řádků,   unikátních hodnot
FROM tabulka;

Autor, Název akce
Cvičení 1
Daniel Klimeš, Databázové systémy a SQL
9
•Vytvořte tabulku
• textový sloupec manager
• textový sloupec study_name
• datumový sloupec managed_since
•Naplňte první 2 sloupce tabulky záznamy z tabulky studies
(principal_investigator, study_name)
• Do řádků bez managera zapište svoje příjmení
a sloupce managed_since dnešní datum
• Do zbylých řádků vložte datum 1.1.2000
• Smažte řádky s managerem Chroust

Autor, Název akce
SELECT – více tabulek
Daniel Klimeš, Databázové systémy a SQL
10
ID pacienta
Jmeno
Prijmeni
1
Jan
Novák
2
Jana
Nová
3
Karel
Starý
ID pacienta
Datum vysetreni
Vysledek vysetreni
1
12.1.2011
39,5
1
15.3.2011
36,8
2
2.2.2011
37,5
Spojování tabulek = join
Druhy spojení:
• vnitřní – inner join – jen spojitelné řádky
• vnější – outer join -  left join, right join, full join
všechny řádky jedné tabulky + napojitelné řádky druhé tabulky

Autor, Název akce
JOIN - syntaxe
Daniel Klimeš, Databázové systémy a SQL
11
Vnitřní spojení
SELECT * FROM tabulka1, tabulka2 WHERE tabulka1.sloupec (PK) = tabulka2.sloupec (FK)
SELECT * FROM pacient, vysetreni WHERE pacient.id_pacienta = vysetreni.id_pacienta
ID pacienta
Jmeno
Prijmeni
ID_pacienta
Datum vysetreni
Vysledek vysetreni
1
Jan
Novák
1
12.1.2011
39,5
1
Jan
Novák
1
15.3.2011
36,8
2
Jana
Nová
2
2.2.2011
37,5

Autor, Název akce
OUTER JOIN – syntaxe
Daniel Klimeš, Databázové systémy a SQL
12
Vnější spojení
SELECT * FROM tabulka1 LEFT JOIN tabulka2 ON tabulka1.sloupec = tabulka2.sloupec
SELECT * FROM pacient LEFT JOIN vysetreni ON pacient.id_pacienta = vysetreni.id_pacienta
ID pacienta
Jmeno
Prijmeni
ID_pacienta
Datum vysetreni
Vysledek vysetreni
1
Jan
Novák
1
12.1.2011
39,5
1
Jan
Novák
1
15.3.2011
36,8
2
Jana
Nová
2
2.2.2011
37,5
3
Karel
Starý
ORACLE varianta
SELECT * FROM tabulka1, tabulka2 WHERE tabulka1.sloupec = tabulka2.sloupec(+)
SELECT * FROM pacient, vysetreni WHERE pacient.id_pacienta = vysetreni.id_pacienta(+)

Autor, Název akce
TRIALDB – datový model
Daniel Klimeš, Databázové systémy a SQL
13
export_patients.jpg
Vazba pacienti – studie m-n => „mezitabulka“ PATIENT_STUDY
Vazba studie – pracoviště m-n => „mezitabulka“ STUDIES_SITES

Autor, Název akce
Cvičení 2
Daniel Klimeš, Databázové systémy a SQL
14
Zjistěte počet pacientů v jednotlivých  studiích
STUDY_NAME, počet pacientů
Zjistěte počet pacientů dle pohlaví v jednotlivých  studiích
STUDY_NAME, pohlaví, počet pacientů
Zjistěte počet zapojených pracovišť do jednotlivých studií
STUDY_NAME, počet pracovišť
Vypište pracoviště zapojená do více studií
SITE, počet studií
Vypište všechny studie a počet zařazených pacientů v jednotlivých letech
STUDY_NAME, rok(DATE_OF_ENROLLMENT)
Zjistěte počet pacientů v jednotlivých  studiích po pracovištích
STUDY_NAME, SITE, počet pacientů