Dotazy nad více tabulkami - Joiny

Содержание

Слайд 2

Spojení tabulek

STRANA

Spojení tabulek STRANA

Слайд 3

Spojení tabulek

STRANA

Spojení tabulek STRANA

Слайд 4

Spojování tabulek

STRANA

Spojování tabulek STRANA

Слайд 5

Spojení / Join

Spojení tabulek umožňuje v jednom dotazu získat data z více

Spojení / Join Spojení tabulek umožňuje v jednom dotazu získat data z
tabulek
Syntaxe
Alternativní Oracle syntaxe
Podmínka spojení je v klauzuli WHERE

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;

STRANA

SELECT table1.column, table2.column
FROM table1
JOIN table2 ON table1.column1 = table2.column2;

Слайд 6

Spojení tabulek

STRANA

SELECT zamestnanci.jmeno, zamestnanci.prijmeni, oddeleni.jmeno_odd
FROM zamestnanci JOIN oddeleni ON zamestnanci.oddeleni =

Spojení tabulek STRANA SELECT zamestnanci.jmeno, zamestnanci.prijmeni, oddeleni.jmeno_odd FROM zamestnanci JOIN oddeleni ON zamestnanci.oddeleni = oddeleni.oddeleni_id;
oddeleni.oddeleni_id;

Слайд 7

Příklad

Vypište seznam zaměstnanců a jmen oddělení, které sídlí v Praze

STRANA

Příklad Vypište seznam zaměstnanců a jmen oddělení, které sídlí v Praze STRANA

Слайд 8

Příklad - řešení

STRANA

SELECT zamestnanci.jmeno, zamestnanci.prijmeni, oddeleni.jmeno_odd
FROM zamestnanci JOIN oddeleni ON zamestnanci.oddeleni

Příklad - řešení STRANA SELECT zamestnanci.jmeno, zamestnanci.prijmeni, oddeleni.jmeno_odd FROM zamestnanci JOIN oddeleni
= oddeleni.oddeleni_id
WHERE oddeleni.lokalita = 'PRAHA';

Слайд 9

Aliasy pro jména tabulek

STRANA

SELECT zamestnanci.jmeno, zamestnanci.prijmeni,
oddeleni.jmeno_odd
FROM zamestnanci
JOIN oddeleni ON zamestnanci.oddeleni =

Aliasy pro jména tabulek STRANA SELECT zamestnanci.jmeno, zamestnanci.prijmeni, oddeleni.jmeno_odd FROM zamestnanci JOIN
oddeleni.oddeleni_id;

SELECT zam.jmeno, zam.prijmeni,
odd.jmeno_odd
FROM zamestnanci zam
JOIN oddeleni odd ON zam.oddeleni = odd.oddeleni_id;

Слайд 10

Příklad

Vypište seznam oddělení a jména jejich managerů.(Podle manager_id v tabulce oddeleni)

STRANA

Příklad Vypište seznam oddělení a jména jejich managerů.(Podle manager_id v tabulce oddeleni) STRANA

Слайд 11

Příklad řešení

STRANA

SELECT o.jmeno_odd, z.jmeno, z.prijmeni
FROM zamestnanci z
JOIN oddeleni o ON z.zamestnanec_id

Příklad řešení STRANA SELECT o.jmeno_odd, z.jmeno, z.prijmeni FROM zamestnanci z JOIN oddeleni
= o.manager_id;

Слайд 12

Kartézský součin

Pokud podmínka spojení chybí nebo není platná, pak je výsledkem kartézský

Kartézský součin Pokud podmínka spojení chybí nebo není platná, pak je výsledkem
součin, který obsahuje všechny kombinace řádků
Kartézský součin tabulky s 20 záznamy s tabulkou s 30 záznamy obsahuje 600 záznamů

STRANA

Слайд 13

Kartézský součin

STRANA

SELECT COUNT(*)
FROM zamestnanci, oddeleni;

Kartézský součin STRANA SELECT COUNT(*) FROM zamestnanci, oddeleni;

Слайд 14

Spojení tabulky se stejnou tabulkou

STRANA

Spojení tabulky se stejnou tabulkou STRANA

Слайд 15

Příklad

Vypište jména zaměstnanců a jména jejich přímých nadřízených

STRANA

Příklad Vypište jména zaměstnanců a jména jejich přímých nadřízených STRANA

Слайд 16

Příklad - řešení

STRANA

SELECT z.jmeno || ' ' || z.prijmeni AS Zamestnanec,

Příklad - řešení STRANA SELECT z.jmeno || ' ' || z.prijmeni AS
m.jmeno || ' ' || m.prijmeni AS Nadrizeny
FROM zamestnanci z
JOIN zamestnanci m ON z.nadrizeny_id = m.zamestnanec_id;

Слайд 17

Příklad

Kteří zaměstnanci nastoupili dříve než jejich nadřízení?

STRANA

Příklad Kteří zaměstnanci nastoupili dříve než jejich nadřízení? STRANA

Слайд 18

Příklad - řešení

STRANA

SELECT z.jmeno, z.prijmeni
FROM zamestnanci z
JOIN zamestnanci m ON z.nadrizeny_id

Příklad - řešení STRANA SELECT z.jmeno, z.prijmeni FROM zamestnanci z JOIN zamestnanci
= m.zamestnanec_id
WHERE z.datum_nastupu < m.datum_nastupu;

Слайд 19

Spojení více tabulek

Spojení tří tabulek
Alternativní Oracle syntaxe
Pro spojení n tabulek potřebujeme n-1

Spojení více tabulek Spojení tří tabulek Alternativní Oracle syntaxe Pro spojení n
spojovacích podmínek

SELECT table1.column, table2.column, table3.column
FROM table1
JOIN table2 ON table1.column1 = table2.column2
JOIN table3 ON table2.column3 = table3.column4;

STRANA

SELECT table1.column, table2.column, table3.column
FROM table1, table2, table3
WHERE table1.column1 = table2.column2 AND
table2.column3 = table3.column4;

Слайд 20

Příklad

Napište seznam oddělení a celkových částek vyplacených na odměnách. Výsledek setřiďte podle

Příklad Napište seznam oddělení a celkových částek vyplacených na odměnách. Výsledek setřiďte
odměn od nejvyšších po nejnižší.

STRANA

Слайд 21

Příklad - řešení

STRANA

SELECT o.jmeno_odd, SUM (p.premie)
FROM zamestnanci z
JOIN oddeleni o ON

Příklad - řešení STRANA SELECT o.jmeno_odd, SUM (p.premie) FROM zamestnanci z JOIN
o.oddeleni_id = z.oddeleni
JOIN platy p ON z.zamestnanec_id = p.id_zamestnance
GROUP BY o.jmeno_idd
ORDER BY SUM (p.premie) DESC;

Слайд 22

Spojení více tabulek

Příklad:
Jak dlouho byli celkem v roce 2003 nemocní zaměstnanci, kteří

Spojení více tabulek Příklad: Jak dlouho byli celkem v roce 2003 nemocní
pracují v Praze?

STRANA

SELECT SUM(p.dnu_nemoc)
FROM platy p
JOIN zamestnanci z ON z.zamestnanec_id = p.id_zamestnance
JOIN oddeleni o ON o.oddeleni_id = z.oddeleni
WHERE o.lokalita = 'PRAHA' AND p.rok = 2003;

Слайд 23

Neekvivalentní vazba

Neekvivalentní spojení je například vazba mezi tabulkami ZAMESTNANCI a PLAT_TRIDY. Znamená

Neekvivalentní vazba Neekvivalentní spojení je například vazba mezi tabulkami ZAMESTNANCI a PLAT_TRIDY.
to, že žádný sloupec tabulky ZAMESTNANCI neodpovídá přímo žádnému sloupci tabulky PLAT_TRIDY.

STRANA

Слайд 24

Neekvivalentní vazba

Vztah mezi tabulkami je takový, že hodnota sloupce ZAKLADNI_PLAT v tabulce

Neekvivalentní vazba Vztah mezi tabulkami je takový, že hodnota sloupce ZAKLADNI_PLAT v
ZAMESTNANCI musí být mezi hodnotami MIN_PLAT a MAX_PLAT v tabulce PLAT_TRIDY. Takový vztah vzniká použitím jiného operátoru než rovnítka (=).

STRANA

Слайд 25

OUTER JOIN

STRANA

SELECT z.jmeno, z.prijmeni, SUM (p.premie)
FROM zamestnanci z
LEFT JOIN platy p

OUTER JOIN STRANA SELECT z.jmeno, z.prijmeni, SUM (p.premie) FROM zamestnanci z LEFT
ON z.zamestnanec_id = p.id_zamestnance
GROUP BY z.jmeno, z.prijmeni
ORDER BY z.jmeno, z.prijmeni;

Слайд 26

OUTER JOIN – Vnější vazba

Spojení dvou tabulek obsahuje pouze takové záznamy, pro

OUTER JOIN – Vnější vazba Spojení dvou tabulek obsahuje pouze takové záznamy,
které je splněna propojovací tabulka
Vnější vazba umožňuje do výsledku dotazu zařadit i takové záznamy, proto které neexistuje ekvivalentní záznam v jedné z tabulek

STRANA

Слайд 27

OUTER JOIN – Vnější vazba
Syntax (SQL 99):
LEFT nebo RIGHT označuje stranu (tabulku),

OUTER JOIN – Vnější vazba Syntax (SQL 99): LEFT nebo RIGHT označuje
která má veškeré informace a tyto budou zobrazeny – nemusí být zobrazeny všechny informace z připojované tabulky (informace mohou chybět)

SELECT table1.column, table2.column
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

STRANA

Слайд 28

OUTER JOIN – Vnější vazba
Syntax (Oracle):
(+) je na té straně podmínky, kde

OUTER JOIN – Vnější vazba Syntax (Oracle): (+) je na té straně
předpokládáme nedostatek informací

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column [ (+) ] = table2.column[(+)];

STRANA

Слайд 29

Spojování tabulek pomocí SQL: Syntax 99

SELECT table1.column, table2.column
FROM table1
[CROSS JOIN table2] |
[NATURAL JOIN table2] |
[JOIN

Spojování tabulek pomocí SQL: Syntax 99 SELECT table1.column, table2.column FROM table1 [CROSS
table2 USING (column_name)] |
[JOIN table2
ON(table1.column_name = table2.column_name)] |
[LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)];

STRANA

Слайд 30

Cvičení

Kteří zaměstnanci mají vyšší nebo stejný plat jako jejich nadřízení?

STRANA

SELECT z.jmeno,

Cvičení Kteří zaměstnanci mají vyšší nebo stejný plat jako jejich nadřízení? STRANA
z.prijmeni
FROM zamestnanci z
JOIN zametnanci m ON z.nadrizeny_id = m.zamestnanec_id
WHERE z.zakladni_plat >= m.zakladni_plat;

Слайд 31

Cvičení

Kolik bylo v roce 2003 vyplaceno na odměnách zaměstnancům jednotlivých oddělení

STRANA

SELECT

Cvičení Kolik bylo v roce 2003 vyplaceno na odměnách zaměstnancům jednotlivých oddělení
o.jmeno_odd, SUM (p.premie)
FROM oddeleni o
JOIN zamestnanci z ON o.oddeleni_id = z.oddeleni
JOIN platy p ON z.zamestnanec_id = p.id_zamestnance
WHERE p.rok = 2003
GROUP BY o.jmeno_odd;

Слайд 32

Cvičení

Kolik dní dovolené si vybrali zaměstnanci Účtárny?

STRANA

SELECT SUM (p.dnu_dovolena)
FROM oddeleni o
JOIN

Cvičení Kolik dní dovolené si vybrali zaměstnanci Účtárny? STRANA SELECT SUM (p.dnu_dovolena)
zamestnanci z ON o.oddeleni_id = z.oddeleni
JOIN platy p ON z.zamestnanec_id = p.id_zamestnance
WHERE o.jmeno_odd = 'UCTARNA';

Слайд 33

Cvičení

Jaký je průměrný plat zaměstnanců, kteří pracují v Praze?

STRANA

SELECT AVG (z.zakladni_plat)
FROM

Cvičení Jaký je průměrný plat zaměstnanců, kteří pracují v Praze? STRANA SELECT
oddeleni o
JOIN zamestnanci z ON o.oddeleni_id = z.oddeleni
WHERE o.lokalita = 'PRAHA';

Слайд 34

Cvičení

Kdo má nadřízeného z jiného oddělení než sám pracuje? Vypište celé jméno,

Cvičení Kdo má nadřízeného z jiného oddělení než sám pracuje? Vypište celé
pozici a jméno oddělení, ze kterého pochází on i jeho nadřízený.

STRANA

SELECT z.jmeno, z.prijmeni, z.pozice, oz.jmeno_odd, om.jmeno_odd
FROM zamestnanci z
JOIN zamestnanci m ON z.nadrizeny_id = m.zamestnanec_id
AND z.oddeleni <> m.oddeleni
JOIN oddeleni oz ON z.oddeleni = OZ.oddeleni_id
JOIN oddeleni om ON m.oddeleni = om.oddeleni_id;

Слайд 35

Cvičení

Kolik je průměrný plat nadřízených zaměstnanců, kteří mají plat nižší než je

Cvičení Kolik je průměrný plat nadřízených zaměstnanců, kteří mají plat nižší než
průměrný plat na jejich oddělení. Každého nadřízeného započítejte do průměru jen jednou.

STRANA

SELECT AVG (plat)
FROM (
SELECT DISTINCT m.prijmeni, z.zakladni_plat AS plat
FROM zamestnanci a
JOIN zamestnanci m ON a.nadrizeny_id = m.zamestnanec_id
JOIN
(SELECT oddeleni, AVG (zakladni_plat) AS avgsal
FROM zamestnanci GROUP BY oddeleni) b
ON a.oddeleni = b.oddeleni
WHERE a.zakladni_plat < b.avgsal;