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

Autor, Název akce
Práce s více tabulkami
Daniel Klimeš, Databázové systémy a SQL
2
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
ID lekare
Jmeno
Prijmeni
10
Petr
Šikovný
20
Jana
Levá
30
Karel
Starý
Spojení sloupců = JOIN
+
+
Spojení řádků – množinové operace

Autor, Název akce
SELECT – více tabulek
Daniel Klimeš, Databázové systémy a SQL
3
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
4
Vnitřní spojení
SELECT * FROM pacient 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

Autor, Název akce
OUTER JOIN – syntaxe
Daniel Klimeš, Databázové systémy a SQL
5
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
Cvičení
Daniel Klimeš, Databázové systémy a SQL
6
Vypište studenty zapsané do alespoň jednoho předmětu
Vypište všechny studenty s vybraným předmětem/předměty
Vypište všechny předměty a k nim počet zapsaných studentů
Vypište učící učitele a jeho předměty
Vypište své jméno a své předměty
Vypište učící učitele a jeho studenty
Vypište všechny učitele a počet jeho studentů

Autor, Název akce
ER diagram
Daniel Klimeš, Databázové systémy a SQL
7

Autor, Název akce
Práce s více tabulkami
Daniel Klimeš, Databázové systémy a SQL
8
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
ID lekare
Jmeno
Prijmeni
10
Petr
Šikovný
20
Jana
Levá
30
Karel
Starý
Spojení sloupců = JOIN
+
+
Spojení řádků – množinové operace

Autor, Název akce
Množinové operace
Daniel Klimeš, Databázové systémy a SQL
9
• UNION   Sjednocení množin – duplicitní řádky vyloučeny
• UNION ALL  Sjednocení množin včetně duplicit
• INTERSECT Průnik množin – pouze shodné řádky
• EXCEPT   Rozdíl množin
• MINUS Rozdíl množin (ORACLE)
Operace s dotazy, které vrací stejnou datovou strukturu (stejné sloupce)
SELECT sloupec FROM tabulka
UNION
SELECT sloupec FROM tabulka2
Počet sloupců prvního a druhého dotazu musí být stejný
a musí být stejného datového typu

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
10
Vypište seznam všech studentů a učitelů (jméno, příjmení)
Přidejte jednoho učitele mezi studenty a vyzkoušejte všechny množinové operace
(průnik, rozdíl)

Autor, Název akce
Import/export dat z/do textového souboru
•Příkaz COPY  FROM/TO
§Nastavit oprávnění na složku pro NETWORK_SERVICE
•Export dat
•COPY student TO 'c:\aa\student.txt'
•COPY (SELECT uco, jmeno FROM student) TO 'c:\aa\student_jmena.txt'
•
•Import dat
•COPY patients FROM 'c:/Users/student/Documents/data/patients.txt' NULL '' ENCODING 'UTF8';
•Před importem musí tabulka existovat
•
•
•
•
•
•
•
Daniel Klimeš, Databázové systémy a SQL

Autor, Název akce
Import dat z textových souborů
Daniel Klimeš, Databázové systémy a SQL
12
COPY patients FROM 'Z:/DBM/patients.txt' NULL '' ENCODING 'UTF8';
Cílová tabulka
Zdrojový soubor
Podoba NULL
Kódování češtiny
Další parametry příkazu COPY
FORMAT
Selects the data format to be read or written:
text, csv (Comma Separated Values), or binary. The default is text.
DELIMITER
Specifies the character that separates columns within each row (line) of the file.
The default is a tab character in text format, a comma in CSV format.
This must be a single one-byte character

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

Autor, Název akce
Cvičení
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), počet pacientů