Autor, Název akce
Databázové systémy a SQL
Lekce 6
Monika Kratochvílová, Daniel Klimeš

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
Množinové operace
Daniel Klimeš, Databázové systémy a SQL
3
• 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
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
4
Vypište všechny pacienty ze studií 3 a 454 (study_id).

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
5
Vypište všechny pacienty ze studií 3 a 454 (study_id).
SELECT p.* FROM patients p
JOIN patient_study ps
ON ps.patient_id=p.patient_id
WHERE ps.study_id=3
 UNION ALL
SELECT p.* FROM patients p
JOIN patient_study ps
ON ps.patient_id=p.patient_id
WHERE ps.study_id=454

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
6
Vypište všechny unikátní pacienty ze studií 3 a 454 (study_id).

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
7
Vypište všechny unikátní pacienty ze studií 3 a 454 (study_id).
SELECT p.* FROM patients p
JOIN patient_study ps
ON ps.patient_id=p.patient_id
WHERE ps.study_id=3
UNION
SELECT p.* FROM patients p
JOIN patient_study ps
ON ps.patient_id=p.patient_id
WHERE ps.study_id=454

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
8
Vypište všechny společné pacienty ze studií 3 a 454 (study_id).

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
9
Vypište všechny společné pacienty ze studií 3 a 454 (study_id).
SELECT p.* FROM patients p
JOIN patient_study ps
ON ps.patient_id=p.patient_id
WHERE ps.study_id=3
INTERSECT
SELECT p.* FROM patients p
JOIN patient_study ps
ON ps.patient_id=p.patient_id
WHERE ps.study_id=454

Autor, Název akce
Poddotazy SQL
Daniel Klimeš, Databázové systémy a SQL
10
Zanořené dotazy
• uzavřené v kulatých závorkách ()
• poddotazem je myšlen příkaz SELECT
SELECT    sloupec
FROM         tabulka
WHERE      podmínka
GROUP BY
HAVING
ORDER BY
•
• místo názvu sloupce
• místo názvu tabulky
• v sekci WHERE

Autor, Název akce
Subdotazy SQL - místo sloupce
Daniel Klimeš, Databázové systémy a SQL
11
Vnořený dotaz na pozici sloupce musí vrátit právě jeden řádek a právě jeden sloupec!
SELECT COUNT(patient_id),
(SELECT COUNT  (*) FROM patients)
FROM patient_study;
SELECT COUNT(student_uco),
(SELECT COUNT (*) FROM student)
FROM vyuka;

Autor, Název akce
Subdotazy SQL - místo sloupce
Daniel Klimeš, Databázové systémy a SQL
12
CVIČENÍ:
Napište dotaz, který vrátí seznam všech studentů,
počet jejich registrovaných předmětů
a kolik je to procent ze všech dostupných předmětů.

Autor, Název akce
Subdotazy SQL - místo sloupce
Daniel Klimeš, Databázové systémy a SQL
13
CVIČENÍ:
Napište dotaz, který vrátí seznam všech studentů,
počet jejich registrovaných předmětů
a kolik je to procent ze všech dostupných předmětů
SELECT s.uco,  COUNT(v.predmet_id),
ROUND(100.0 * (COUNT(v.predmet_id)) /
(SELECT COUNT(*) FROM predmet) )
FROM student s JOIN vyuka v
ON s.uco=v.student_uco
GROUP BY s.uco;

Autor, Název akce
Zanořený dotaz – místo názvu tabulky
Daniel Klimeš, Databázové systémy a SQL
14
Poddotaz na pozici FROM nahrazuje tabulku.
V postgreSQL musí být poddotaz na pozici tabulky VŽDY pojmenován!
SELECT  COUNT(*)  FROM (
   SELECT study_id, COUNT(patient_id)
      FROM patient_study GROUP BY study_id
    ) sub

Autor, Název akce
Zanořený dotaz – místo názvu tabulky
Daniel Klimeš, Databázové systémy a SQL
15
CVIČENÍ:
Napište dotaz, který vrátí seznam studentů, kteří jsou registrováni do více než jednoho předmětu.

Autor, Název akce
Zanořený dotaz – místo názvu tabulky
Daniel Klimeš, Databázové systémy a SQL
16
CVIČENÍ:
Napište dotaz, který vrátí seznam studentů, kteří jsou registrováni do více než jednoho předmětu.
SELECT * FROM (
SELECT s.jmeno, s.prijmeni, s.uco, COUNT(v.predmet_id) pocet
FROM student s JOIN vyuka v ON s.uco=v.student_uco
GROUP BY s.jmeno, s.prijmeni, s.uco) sub
WHERE pocet>1;
SELECT s.jmeno, s.prijmeni, s.uco, COUNT(v.predmet_id)
FROM student s JOIN vyuka v ON s.uco=v.student_uco
GROUP BY s.jmeno, s.prijmeni, s.uco
HAVING COUNT(v.predmet_id)>1
ORDER BY s.jmeno;

Autor, Název akce
Vnořený dotaz za WHERE
Daniel Klimeš, Databázové systémy a SQL
17
Varianty:
• WHERE sloupec = (SELECT sloupec FROM…
zanořený dotaz musí vrátit právě 1 řádek a 1 sloupec
• WHERE sloupec = ANY (SELECT sloupec FROM…
• WHERE sloupec IN (SELECT sloupec FROM …
• WHERE sloupec > ALL (SELECT sloupec FROM …
zanořený dotaz musí vrátit 1 sloupec a libovolný počet řádků
•
•
•WHERE EXISTS (SELECT * FROM….
• WHERE NOT EXISTS (SELECT * FROM…
zanořený dotaz může vracet libovolný počet řádků i sloupců
Zanořené dotazy se obvykle propojují s nadřazeným dotazem
pomocí podmínky v sekci WHERE

Autor, Název akce
Vnořený dotaz za WHERE sloupec = (SELECT …
Daniel Klimeš, Databázové systémy a SQL
18
Varianty:
• WHERE sloupec = (SELECT sloupec FROM…
zanořený dotaz musí vrátit právě 1 řádek a 1 sloupec
SELECT * FROM patients
WHERE date_of_birth = (SELECT MAX(date_of_birth) FROM patients);

Autor, Název akce
Vnořený dotaz za WHERE sloupec ANY/IN/ALL
Daniel Klimeš, Databázové systémy a SQL
19
Varianty:
• WHERE sloupec = ANY (SELECT sloupec FROM…
• WHERE sloupec IN (SELECT sloupec FROM …
• WHERE sloupec > ALL (SELECT sloupec FROM …
zanořený dotaz musí vrátit 1 sloupec a libovolný počet řádků
SELECT * FROM student
WHERE uco = ANY (SELECT student_uco FROM vyuka WHERE predmet_id=10);
SELECT * FROM student
WHERE uco IN (SELECT student_uco FROM vyuka WHERE predmet_id=10);

Autor, Název akce
Vnořený dotaz za WHERE EXISTS/NOT EXISTS
Daniel Klimeš, Databázové systémy a SQL
20
Varianty:
• WHERE EXISTS (SELECT * FROM….
• WHERE NOT EXISTS (SELECT * FROM…
zanořený dotaz může vracet libovolný počet řádků i sloupců
SELECT * FROM student s
WHERE EXISTS (SELECT * FROM vyuka v
WHERE predmet_id=10
AND s.uco=v.student_uco);

Autor, Název akce
Subdotazy SQL - Vnořený dotaz za WHERE
Daniel Klimeš, Databázové systémy a SQL
21
CVIČENÍ:
Vypište seznam studentů, kteří nemají registrovaný žádný předmět.

Autor, Název akce
Subdotazy SQL - Vnořený dotaz za WHERE
Daniel Klimeš, Databázové systémy a SQL
22
CVIČENÍ:
Vypište seznam studentů, kteří nemají registrovaný žádný předmět.
SELECT * FROM student s
WHERE NOT EXISTS (
SELECT * FROM vyuka v
WHERE s.uco=v.student_uco
);

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
23
CVIČENÍ

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
24
Najděte všechny učitele, kteří nevyučují žádný předmět.

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
25
Najděte všechny učitele, kteří nevyučují žádný předmět.
SELECT * FROM ucitel u
WHERE NOT EXISTS (
SELECT * FROM predmet p
WHERE u.ucitel_uco=p.ucitel_uco);

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
26
Vypište všechny studenty, kteří mají zapsaný předmět Databáze v biomedicíně i Černou magii.
(predmet_id 1 a 10)

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
27
Vypište všechny studenty, kteří mají zapsaný předmět Databáze v biomedicíně i Černou magii.
(predmet_id 1 a 10)
 SELECT * FROM student s
 WHERE EXISTS (SELECT * FROM vyuka v
  WHERE s.uco=v.student_uco AND predmet_id=1)
 INTERSECT
 SELECT * FROM student s
 WHERE EXISTS (SELECT * FROM vyuka v
  WHERE s.uco=v.student_uco AND predmet_id=10);

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
28
Vypište všechny studenty, kteří mají zapsaný předmět Databáze v biomedicíně i Černou magii.
(predmet_id 1 a 10)
 SELECT * FROM student s
 WHERE EXISTS (SELECT * FROM vyuka v
  WHERE s.uco=v.student_uco AND predmet_id=1)
AND EXISTS (SELECT * FROM vyuka v
  WHERE s.uco=v.student_uco AND predmet_id=10);

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
29
Vypište všechny studenty, kteří mají zapsaný předmět Databáze v biomedicíně (predmet_id 1), ale
nemají zapsanou Černou magii (predmet_id 10).

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
30
Vypište všechny studenty, kteří mají zapsaný předmět Databáze v biomedicíně (predmet_id 1), ale
nemají zapsanou Černou magii (predmet_id 10).
 SELECT * FROM student s
 WHERE EXISTS (SELECT * FROM vyuka v
  WHERE s.uco=v.student_uco AND predmet_id=1)
 INTERSECT
 SELECT * FROM student s
 WHERE NOT EXISTS (SELECT * FROM vyuka v
  WHERE s.uco=v.student_uco AND predmet_id=10);

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
31
Vypište všechny studenty, kteří mají zapsaný předmět Databáze v biomedicíně (predmet_id 1), ale
nemají zapsanou Černou magii (predmet_id 10).
 SELECT * FROM student s
 WHERE EXISTS (SELECT * FROM vyuka v
  WHERE s.uco=v.student_uco AND predmet_id=1)
 AND NOT EXISTS (SELECT * FROM vyuka v
  WHERE s.uco=v.student_uco AND predmet_id=10);

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
32
Vypište všechna pracoviště, která v roce 2010 nezařadila do studie žádného pacienta.

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
33
Vypište všechna pracoviště, která v roce 2010 nezařadila do studie žádného pacienta.
SELECT * FROM sites s
WHERE NOT EXISTS (
SELECT * FROM patient_study ps
WHERE EXTRACT(YEAR FROM date_of_enrollment)=2010
AND ps.study_site=s.site_id
);

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
34
Vypište všechna pracoviště, která zařadila pacienta naposledy v  roce 2010.

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
35
Vypište všechna pracoviště, která zařadila pacienta naposledy v  roce 2010.
SELECT * FROM sites s
WHERE EXISTS (
SELECT * FROM patient_study ps
WHERE EXTRACT(YEAR FROM date_of_enrollment)=2010
AND ps.study_site=s.site_id
)
AND NOT EXISTS (
SELECT * FROM patient_study ps
WHERE EXTRACT(YEAR FROM date_of_enrollment)>2010
AND ps.study_site=s.site_id
);

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
36
Vypište všechna pracoviště, která zařadila pacienta naposledy v  roce 2010.
SELECT s.site, s.site_id,
MAX(EXTRACT(YEAR FROM ps.date_of_enrollment)) rok
FROM sites s JOIN patient_study ps
ON s.site_id=ps.study_site
GROUP BY s.site, s.site_id
HAVING MAX(EXTRACT(YEAR FROM ps.date_of_enrollment))=2010;

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
37
Najděte předměty, kam se přihlásil alespoň jeden student (muž) a vypište celkový počet přihlášených
studentů.

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
38
Najděte předměty, kam se přihlásil alespoň jeden student (muž) a vypište celkový počet přihlášených
studentů.
SELECT predmet_id, COUNT(*) FROM student s, vyuka v
WHERE s.uco = v.student_uco
AND EXISTS  (
SELECT predmet_id FROM student s2, vyuka v2
WHERE s2.uco = v2.student_uco
AND s2.pohlavi = 'M' AND v.predmet_id=v2.predmet_id
)
GROUP BY predmet_id;

Autor, Název akce
Domácí úkol
Daniel Klimeš, Databázové systémy a SQL
 Zjistěte počet pacientů v jednotlivých  studiích po pracovištích a dle pohlaví
STUDY_NAME, SITE, SEX, počet pacientů
•

Autor, Název akce
Daniel Klimeš, Databázové systémy a SQL
Děkuji za pozornost.