Distanční opora předmětu: Databázové systémy Tématický blok č. 1: Dotazovací jazyk SQL Autor: RNDr. Jan Lánský, Ph.D. Obsah kapitoly 1 Instalace Microsoft SQL Serveru 2 Dotazovací jazyk SQL 3 Příkaz SELECT 3.1 Klauzule SELECT a FROM 3.2 Klauzule WHERE 3.3 Spojení tabulek 3.4 Klauzule GROUP BY a HAVING Studijní cíle Cíle nutné k zahájení studia dalšího tématického bloku Znalost základní databázové terminologie. Schopnost vytvářet, modifikovat a mazat tabulky v SQL serveru. Schopnost vkládat, modifikovat a mazat řádky z tabulek v SQL serveru. Schopnost vytvořit jednoduchý SQL dotaz (s maximálně jedním vnořeným SELECTem) dle slovního zadání. Schopnost v dotazu využít klauzule GROUP BY a HAVING Další cíle Znalost různých typů spojení tabulek. Znalost všech konstrukcí, které se mohou nacházet v jednotlivých klauzulích příkazu SELECT.
Čas potřebný ke studiu 2 - 6 hodiny na prostudování výukových textů + zodpovězení otázek k rekapitulaci 1 - 4 hodiny na vypracování modelových úloh na PC 1 - 2 hodiny na praktické zopakování učiva na PC ( v jiný den) 30 min - 1 hodina na (znovu)zodpovězení otázek k rekapitulaci (v jiný den) Časy jsou hodně individuální a jsou závislé na míře znalostí z oblasti databázových systémů získaných během bakalářského studia.
Úvod Při zahájení studia tohoto tématického bloku se očekávají znalosti základní terminologie z oblasti databází. Velkou výhodou jsou i praktické zkušenost s vytvářením tabulek, plněním tabulek daty a dotazováním se nad libovolným databázovým systémem. Zejména je očekávána alespoň středně dobrá schopnost vytváření dotazů. V tomto bloku probereme následující témata. Nainstalujeme si SQL Server 2005 Express Edition a grafické rozhraní pro práci s ním Microsoft SQL Server Management Studio Express (MSSMSE) Naučíme se vytvářet, modifikovat a mazat tabulky v SQL Serveru (pomocí MSSMSE). Naučíme se vkládat, modifikovat a mazat řádky z tabulek v SQL Serveru (pomocí MSSMSE). Podrobně si vysvětlíme příkaz SELECT a jeho klauzule, ke každé z nich si představíme úplný výčet možností, které nabízejí. Vysvětlíme se rozdíly mezi jednotlivými typy spojování tabulek. Podrobně si vysvětlíme zpracování dotazu obsahujícího klauzuli GROUP BY. Výkladová část Vysvětlivky Červený text – Porušením nebo opomenutím takto označených pravidel vznikají těžko odladitelné chyby (zejména pro začínající programátory). Modrý text – Doporučení jak programovat v praxi. Často prevence závažných chyb. 1 Instalace Microsoft SQL Serveru Pro potřeby praktického procvičování učiva tématických bloků č. 1 – 3 si musíme nainstalovat vhodný databázový server. Naše volba padla na Microsoft SQL Server 2005 Express Edition, který lze zdarma stáhnout z webových stránek firmy Microsoft [2]. Nejprve si stáhneme instalátor "Install Microsoft SQL Server 2005 Express Edition with Advanced Services" (234 MB). Při výběru komponent k instalaci je k základnímu výběru nutno přidat Management Studio Express a Full-Text Search. V dalším průběhu instalace lze jen odklikávat přednastavené volby. Pro naše účely je zejména vhodné ponechat volbu "Windows Authentication", vyhneme se opakovanému zadávání přístupových hesel. Můžeme (ale nemusíme) si stáhnout a nainstalovat také nápovědu SQL Server 2005 Books Online (135 MB). V nabídce Start - Programy se nám objevila nová složka Microsoft SQL Server 2005, v ní je složka Configuration Tools. Zde nalezneme SQL Server Configuration Manager. Spustíme
ho. V pravém okně v prvním řádku nahoře nalezneme SQL Server (SQLEXPRESS). Na něm klikneme pravým tlačítkem myši a vyvoláme menu, ve kterém vybereme položku Vlastnosti. Objeví se okno, ve kterém můžeme prohlížet a měnit nastavení. V záložce Service je položka Start Mode. Start mód určuje, zda se bude databázový server pouštět automaticky po startu operačního systému (volba Automatic) nebo zda ho budeme muset spouštět ručně před začátkem práce (volba Manual). Při volbě Automatic se nemusíme o nic dále starat, ale databázový server bude spotřebovávat prostředky operačního systému, i když s ním nebudeme chtít pracovat. Volba Manual naopak vyžaduje ruční spouštění databázového serveru před začátkem práce s ním přes tento SQL Server Configuration Manager.
Obrázek 1: SQL Server Configuration Manager
Ujistěme se, že máme databázový server spuštěný (sloupec State má hodnotu Running – viz obrázek 1). Nyní si spustíme Microsoft SQL Server Management Studio Express (MSSMSE), které se nachází v nabídce Start – Programy ve složce Microsoft SQL Server 2005. Potvrdíme tlačítko connect. Z informačního systému ze složky studijní materiály k tomuto předmětu si stáhneme skript instnwnd.sql [3], pomocí kterého si nainstalujeme poměrně rozsáhlou databázi Nortwind, kterou budeme využívat při procvičování učiva. Dvojitým kliknutím na soubor instnwnd.sql, se nám tento skript otevře v našem MSSMSE. Klávesou F5 (nebo tlačítkem Execute) skript spustíme a databáze je Nortwind je pochvíli vytvořena a naplněna daty. Pomocí MSSMSE Object Exploreru (v levé části aplikace) v si můžeme pohodlně prohlédnout strukturu existujících databází, případně vytvářet nové databáze. Pro každou databázi zde nalezneme přehled objektů (tabulek, pohledů atd.), které lze i jednoduše přidávat, modifikovat a mazat. V MSSMSE tlačítkem New Query (nachází se nalevo nahoře, první tlačítko ve druhém řádku) vyvoláme okno (v pravé části obrazovky), do kterého můžeme psát zdrojový kód dotazu. Je velmi důležité, abychom před smáčknutím tlačítka New Query byli nastaveni na databázi (v Object Exploreru), se kterou chceme právě pracovat. Nyní si nainstalujeme databázi Northwind, jejíž instalační skript instnwnd.sql [3] najdeme v adresáři se studijními materiály. Instalační skript stačí spustit v MSSMSE. 2 Dotazovací jazyk SQL Dotazovací jazyk SQL (structured query language) se používá pro práci s daty uloženými v relačních databázích (například v SQL Serveru). Historie jazyka je popsána na slajdu č. 5 (pouze pro zájemce). Jazyk SQL obsahuje čtyři hlavní skupiny příkazů: pro definici dat, manipulaci s daty, řízení přístupových práv a řízení transakcí. První skupinou příkazů jsou příkazy pro definici dat. Jazyk SQL umožňuje definovat databázové objekty (tabulky, pohledy, indexy, uložené procedury, …). Pomocí příkazu
CREATE lze objekty vytvářet, pomocí příkazu ALTER modifikovat a pomocí příkazu DROP rušit. Tyto příkazy jsme podrobně probírali v databázových předmětech v rámci bakalářského studia, jsou také vysvětleny v doporučené literatuře [4, 6]. V našem grafickém rozhraní MSSMSE lze pomocí Object Exploreru jednotlivé databázové objekty vytvářet, modulovat a rušit i bez znalosti těchto příkazů. Pro naše pokusy je vhodné nejprve vytvořit vlastní databázi. Ve stromové struktuře Object Exploreru najdeme položku Databases a na ní pravým tlačítkem myši vyvoláme menu a vybereme položku New Databáze. Podobným způsobem lze vytvářet i nové tabulky, či pohledy. Pro modifikaci existující tabulky máme na výběr mezi dvěmi možnostmi Design a Edit. Možnost Design nám otevře to samé grafické rozhraní, ve kterém jsme tabulku vytvářeli. Možnost Edit nám otevře zdrojový kód, pomocí kterého by bylo možné tabulku vytvořit. Modifikace je v tomto případě nutné provádět příkazem ALTER, nestačí jen přepsat zdrojový kód. Druhou skupinou příkazů jsou příkazy pro manipulaci s daty. Patří sem příkazy SELECT pro zobrazení dat, INSERT pro vložení dat, UPDATE pro modifikaci dat a DELETE pro smazání dat. Tyto příkazy jsme opět podrobně probírali v databázových předmětech v rámci bakalářského studia, jsou také vysvětleny v doporučené literatuře [4, 6]. Příkaz SELECT si podrobně zopakujeme v tomto tématickém bloku. V našem grafickém rozhraní MSSMSE lze v Object Exploreru pomocí nabídky Open Table (vyvolané pravým tlačítkem myši na dané tabulce) prohlížet záznamy (1 řádek = 1 záznam) uložené v tabulce. Záznamy lze modifikovat pouhým přepsáním, rovněž je lze přidávat či mazat. Poznámka: jazyk SQL není case-sensitive, nerozlišuje velikost písmen. Přesto bývá dobrým zvykem odlišovat klíčová slova jazyka SQL od zbytku zdrojového kódu, tím že je budeme psát velkými písmeny. 3 Příkaz SELECT Tabulka obsahuje řádky (záznamy) a sloupce (atributy). Sloupce se definují při vytváření tabulky pomocí příkazu CREATE. Při vkládání dat do tabulky pomocí příkazu INSERT vkládáme řádky, které musejí odpovídat podmínkám nadefinovanými pro danou tabulku (integritní omezení). Pomocí příkazu SELECT pokládáme databázi dotaz, jehož výsledkem je multimnožina řádků. Multimnožina řádků nemá definované uspořádání prvků (řádků) a navíc se v ní shodné prvky (řádky) mohou opakovat. Příkaz SELECT obsahuje nejméně dvě klauzule (SELECT, FROM), ke kterým lze přidat ještě tři nepovinné klauzule (WHERE,.GROUP BY, HAVING). Na slajdu č. 11 je schéma příkazu SELECT, na slajdu č. 10 jsou jednotlivé klauzule podrobněji vysvětleny. Jednoduchým příkladem dotazu využívajícím pouze klauzule SELECT a FROM může být vypsání všech řádků (záznamů) uložených v jedné tabulce. Složitější dotaz může využívat informací z více tabulek, které vhodným způsobem spojíme. Klauzule SELECT specifikuje výrazy (například jména sloupců), jejichž hodnoty se mají objevit ve výsledku. Klauzule FROM určuje tabulku či tabulky, ze kterých čerpáme data. Přidáním klauzule WHERE ponecháme ve výsledku pouze ty řádky, které vyhovují námi zadané podmínce. Nepovinná klauzule GROUP BY určuje přes které výrazy (například sloupce) se provede agregace dat.
Klauzule GROUP BY může být doplněna klauzulí HAVING, pomocí které lze stanovit podmínku na agregovaný řádek, při jejímž splnění bude daný řádek zařazen do výsledku. Výsledkem příkazu SELECT je multimnožina řádků. Za příkaz SELECT lze nepovinně přidat klauzuli ORDER BY, která odpověď setřídí vzestupně nebo sestupně dle hodnot zadaného sloupce, případně sloupců (viz slajd č. 9). Odpovědi (multimnožiny řádků) získané příkazem SELECT lze dále zpracovat pomocí množinových operací sjednocení (UNION), průnik INTERSECT a rozdíl EXCEPT (viz slajd č. 8). Výsledky těchto množinových operací jsou množiny, přestože na vstupu mohly být multimnožiny. 3.1 Klauzule SELECT a FROM Klauzule SELECT a FROM jsou povinné části každého dotazu. Klauzule SELECT specifikuje výrazy (například jména sloupců), jejichž hodnoty se mají objevit ve výsledku. Klauzule FROM určuje tabulku či tabulky, ze kterých čerpáme data. Na slajdu č. 12 vidíme co vše může klauzule SELECT obsahovat. Rozšiřující komentář lze nalézt na slajdu č. 15. Ihned za klíčovým slovem SELECT může následovat jedno z klíčových slov ALL nebo DISTINCT. Při použití ALL je výsledkem dotazu multimnožina, při použití DISTINCT je výsledkem dotazu množina (odstraní se druhé a další výskyty duplicitních řádků). Pokud explicitně neuvedeme ani jedno z těchto klíčových slov, implicitně je použito ALL. Příklad: Mějme tabulku Zaměstnanci se sloupci Jméno a Příjmení typu řetězec. V tabulce Zaměstnanci jsou záznamy (Petr, Novák), (Petr, Novák) a (Jan, Dvořák). Při vypsání tabulky příkazem SELECT Jméno, Příjmení FROM Zaměstnanci je nám vrácen výsledek obsahující tři řádky (řádek (Petr, Novák) tam bude dvakrát). Při vypsání tabulky příkazem SELECT DISTINCT Jméno, Příjmení FROM Zaměstnanci je nám vrácen výsledek obsahující pouze dva řádky (Petr, Novák) a (Jan, Dvořák). Zkuste nyní sami vypsat jména a příjmení zaměstnanců z tabulky Employees v databázi Northwind. V klauzuli SELECT dále následuje čárkou oddělovaný seznam výrazů, které bude obsahovat výsledek dotazu. Těmito výrazy mohou být mohou být konstantní výrazy nebo sloupce některé z tabulek uvedené v klauzuli FROM. Na výrazy zde uvedené lze navíc aplikovat agregační funkce (COUNT, SUM, MAX, MIN, AVG), případně z nich vytvořit aritmetické výrazy (sečtení hodnot dvou výrazů, vynásobení hodnoty výrazu konstantou, atd.). Seznam výrazů uvedený v klauzuli SELECT lze úplně nahradit (nebo jen rozšířit) symbolem *, který do výsledku zařadí všechny sloupce tabulek uvedených v klauzuli WHERE. Symbol * lze použít nejen samostatně ale i jako argument agregačních funkcí. Agregačním funkcím lze předat jako parametr konkrétní výraz (například sloupec), funkci COUNT navíc ještě symbol * zastupující celý řádek odpovědi dotazu. Ke spočtení počtu řádek zpracovávaného dotazu se obvykle používá COUNT(*). Ke spočtení počtu unikátních hodnot v daném sloupci se používá COUNT(DISTINCT sloupec). Agregační funkce umějí počítat součet (funkce SUM), průměr (funkce AVG), minimum (funkce MIN) a maximum (funkce MAX) z hodnot daného výrazu (sloupce) přes všechny řádky zpracovávaného dotazu.
Klauzule FROM udává zdroje, ze kterých se čerpají data pro dotaz. Kromě tabulek zde mohou být uvedeny i pohledy nebo vnořené dotazy. Jednotlivé zdroje lze oddělit čárkou, pak se použije jejich kartézský součin, nebo je lze spojit pomocí klíčových slov (viz kapitola 3.3). Na slajdu č. 14 jsou dvě tabulky, které budeme využívat v našich příkladech. V tabulce Letadla je ke každému z letadel uvedena letecká společnost, které letadlo patří a kapacita letadla, kolik cestujících je schopno přepravit. V tabulce lety je uveden kód letu, letecká společnost, která ho provozuje, destinace, do které let směřuje, a počet cestujících. Na slajdu č. 16 jsou předvedeny dva dotazy. První z dotazů demonstruje využití klíčového slova DISTINCT pro vrácení unikátních výskytů leteckých společností v tabulce Lety. Tento příklad demonstruje i použití konstantního výrazu 'Spol.‘ jako hodnoty sloupce ve výsledku dotazu. Druhý z dotazů na slajdu č. 16 demonstruje použití kartézského součinu dvou tabulek v klauzuli FROM. Protože chceme provést kartézský součin tabulky Letadla sama se sebou, je nutné nejméně jeden z jejich výskytů pro potřeby dotazu přejmenovat za pomocí klíčového slova AS. V našem příkladu jsme přejmenovali oba výskyty tabulky Letadla. První výskyt jako L1, druhý jako L2. Na jednotlivé sloupce se potom odkazujeme pomocí nového identifikátoru (L1 nebo L2), tečky a jména sloupce. Na slajdu č. 17 jsou předvedeny tři dotazy, které využívají agregační funkce. V prvním z dotazů je použita funkce COUNT v kombinaci s klíčovým slovem DISTINCT pro zjištění počtu unikátních hodnot ve sloupci zpracovávaného dotazu. Druhý z příkladů demonstruje, že funkci COUNT bez použití klíčového slova DISTINICT lze předat jako parametr * nebo libovolný sloupec zpracovávaného dotazu a výsledek je v obou případech shodný. Třetí z příkladu demonstruje použití všech agregačních funkcí v jednom dotazu. 3.2 Klauzule WHERE Klauzule WHERE je nepovinou součástí dotazu. Při jejím použití ve výsledku dotazu ponecháme pouze ty řádky, které vyhovují námi zadané podmínce. Schéma klauzule WHERE je znázorněno na slajdu č. 13. Základním stavebním kamenem podmínky v klauzuli WHERE je výraz (jeho struktura je vysvětlena na slajdu č. 12), se kterým jsme se seznámili již u klauzule SELECT. Tento vyraz je na slajdu č. 13 je označen červeně jako expression. Příkladem tohoto výrazu může být třeba sloupec tabulky nebo konstantní hodnota. Podmínku lze vytvořit následujícími pravidly (upravená verze slajdu č. 18) 1. Porovnáním dvou výrazů pomocí operátorů =, <>, <, >, <=, >=. 2. Vyhodnocením (ne)příslušnosti výrazu do intervalu pomocí syntaktického zápisu: výraz1 [NOT] BETWEEN (výraz2 AND výraz3). 3. Řetězcový výraz lze porovnat s maskou, ve které znak % reprezentuje libovolný podřetězec a znak _ reprezentuje libovolný znak. Syntax: vyraz [NOT] LIKE maska. 4. Testem na (ne)definovanou hodnotu. Syntax: výraz IS [NOT] NULL 5. Testem na (ne)příslušnost výrazu do množiny. Syntax: výraz [NOT] IN (dotaz) 6. Testem neprázdnosti množiny. Syntax: EXISTS (dotaz) 7. Vyhodnocením, zda alespoň jeden prvek (řádek) z množiny splňuje porovnání s výrazem pomocí operátorů z bodu č. 1. Syntax: výraz operátor ANY (dotaz)
8. Vyhodnocením, zda všechny prvky (řádky) z množiny splňují porovnání s výrazem pomocí operátorů z bodu č. 1. Syntax: výraz operátor ALL (dotaz) 9. Podmínky č. 1-9 lze kombinovat logickými spojkami NOT, AND, OR Poznámka: Pravidla č. 5-8 využívají vnořených dotazů, je vhodné zvolit rozumné formátování zdrojových kódů tak, aby hlavní dotaz šel na první pohled odlišit od vnořeného dotazu. Předvedeme si několik příkladů, používáme tabulky Letadla a Lety ze slajdu č. 14. Na slajdu č. 19 jsou dva dotazy. První z dotazů demonstruje porovnání hodnoty sloupce s konstantou (pravidlo č. 1). V druhém z dotazů na slajdu č. 19 si nejprve v klauzuli SELECT vytvoříme nový výraz Naplněnost (označen červeně). Klauzule FROM obsahuje dvě tabulky, jejich spojení podle sloupce Společnost dosáhneme v první částí podmínky uvedené v klauzuli WHERE. Druhá část podmínky testuje, zda dané letadlo má dostatečnou kapacitu pro příslušný let. Třetí část podmínky testuje dostatečnou naplněnost letu, pomocí porovnání výrazu Naplněnost s konstantou. Na slajdu č. 20 nahoře vidíme dva různé zápisy stejného dotazu. Pomocí predikátu LIKE (pravidlo č. 3) testujeme hodnotou sloupce, zda obsahuje hledaný podřetězec. V dotazu jsme potřebovaly využít data ze dvou tabulek. V prvním zápisu dotazu jsme použili predikát IN (pravidlo č. 5) a vnořený dotaz. V druhém zápisu dotazu jsme použili spojení dvou tabulek. Dotaz dole na slajdu č. 20 demonstruje použití predikátu ALL (pravidlo č. 8) a vnořeného dotazu. 3.3 Spojení tabulek V klauzuli FROM specifikujeme tabulky (případně pohledy či vnořené dotazy), ze kterých čerpáme data. Pro jednoduchost budeme uvažovat pouze tabulky. Pokud jsou v klauzuli FROM uvedeny alespoň dvě tabulky, musíme vybrat způsob jakým je navzájem spojíme. Na výběr máme mezi kartézským součinem, přirozeným spojením, vnitřním a vnějším spojením. Jednotlivé pojmy této kapitoly si budeme vysvětlovat na následujícím příkladě: Mějme tabulku T1 se sloupci A a B typu celé číslo a tabulka T2 se sloupci A a C typu celé číslo. V tabulce T1 jsou záznamy (1,1), (1,2), (2,3) a (3,5). V tabulce T2 jsou záznamy (1,1), (1,3), (2,4) a (4,6). Viz obrázek 2. T1 A 1 1 2 3
T2 B 1 2 3 5
A 1 1 2 4
C 1 3 4 6
Obrázek 2: Tabulka T1 a T2
Kartézský součin spojí každý řádek z první tabulky s každým řádkem z druhé tabulky. Pokud má první tabulka n1 řádků a druhá tabulka n2 řádků, výsledek bude mít n1*n2 řádků. V našem případě kartézský součin tabulek T1 a T2 bude mít 16 řádků. Syntax: Tabulky jsou spojeny kartézským součinem, pokud je oddělíme čárkou, nebo mezi ně napíšeme klíčová
slova CROSS JOIN. Náš příklad: SELECT * FROM T1, T2 nebo SELECT * FROM T1 CROSS JOIN T2). Přirozené spojení je speciální druh vnitřního spojení (viz dále). V tabulkách T1 a T2 vyhledáme sloupce S1, …, Sn se shodnými názvy a datovými typy v obou tabulkách. Obvykle se jedná o primární klíč jedné tabulky a cizí klíč druhé tabulky. Do výsledku jsou zařazeny pouze ty dvojice řádků (z kartézského součinu), které mají shodné hodnoty ve sloupcích shodného názvu a typu (platí ( k=1...n) T1.Sk = T2.Sk). Syntax: Tabulky jsou spojeny přirozeným spojením, pokud mezi ně napíšeme klíčová slova NATURAL JOIN. Vnitřní spojení není v SQL Serveru 2005 implementováno. V případě tabulek T1 a T2 z našeho příkladu se jedná o společný sloupec A. Přirozeným spojením (SELECT * FROM T1 NATURAL JOIN T2) získáme 5 řádků (1,1,1,1), (1,2,1,1), (1,1,1,3), (1,2,1,3) a (2,3,2,4). Dalším způsobem spojení tabulek je vnitřní spojení. Výsledkem vnitřního spojeni tabulek T1 a T2 jsou ty řádky z kartézského součinu, které splňují spojovací podmínku (stejnou jaká může být v klauzuli WHERE – slajd č. 13) danou vnitřním spojením. Tato podmínka bývá obvykle rovností primárního klíče z jedné tabulky s cizím klíčem z druhé tabulky. Syntax: Tabulky jsou spojeny vnitřním spojením, pokud mezi ně napíšeme klíčová slova INNER JOIN (V SQL Serveru stačí jen JOIN) a za druhou ze spojovaných tabulek napíšeme klíčové slovo ON, za kterým uvedeme spojovací podmínku. Vnitřní spojení tabulek lze také nahradit kartézským součinem a spojovací podmínku uvést jako jednu z částí podmínky v klauzuli WHERE. V případě tabulek T1 a T2 je vnitřně spojíme podmínkou na rovnost hodnot ve společném sloupci A. Příkaz SELECT * FROM T1 INNER JOIN T2 ON T1.A=T2.A vrátí stejných pět řádků jaké jsou uvedeny (viz výše) ve výsledku příkladu na přirozené spojení. Vnější spojení (OUTER JOIN) je tří typů. Plné FULL, levé LEFT a pravé RIGHT. Vnější spojení obsahuje všechny řádky, které by obsahovalo vnitřní spojení. Navíc pro každý řádek z dané tabulky (z levé při LEFT, z pravé při RIGHT) či obou tabulek (při FULL), který není spárován s žádným řádkem z druhé tabulky, je přidána řádek, který má ve sloupcích z druhé tabulky (ze které se nepodařilo nalézt párový řádek) dosazeny hodnoty NULL Syntax: je podobná jako u INNER JOINU, pouze místo INNER JOIN je uvedeno LEFT OUTER JOIN, respektive RIGHT OUTER JOIN, respektive FULL OUTER JOIN. V SQL Serveru lze klíčové slovo OUTER vynechat. V našem příkladě s tabulekami T1 a T2 provedeme postupně všechna tři vnější spojení s podmínkou na rovnost hodnot ve společném sloupci A. Příkaz na levé vnější spojení SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.A=T2.A vrátí stejných pět řádků jaké jsou uvedeny (viz výše) ve výsledku příkladu na přirozené spojení a navíc ještě šestý řádek (3,5, NULL, NULL). Pravé vnější spojení by vrátilo již zmíněných pět řádků a navíc šestý řádek (NULL, NULL, 4,6). Plné vnější spojení by vrátilo již zmíněných pět řádků a navíc oba dva řádky, které byly vráceny navíc při levém a pravém vnějším spojení. Na slajdu č. 22 vidíme dva příklady na spojení tabulek. Používáme tabulky Lety a Letadla ze slajdu č.14. V prvním příkladě použijeme vnitřní spojení obou tabulek přes rovnost hodnot
v jejich společném sloupci Společnost a provedené spojení je navíc ještě omezeno druhou podmínkou na nerovnost hodnot dvou sloupců s číselnými údaji (kapacita letadla, počet cestujících). V klauzuli SELECT je navíc vyroben nový výraz Volnych_mist, podle kterého výsledek dotazu setřídíme s pomocí klauzule ORDER BY. V druhém dotazu je použito vnější spojení, základ dotazu je stejný jako v prvním příkladě. Naším cílem je v tabulce Lety vyhledat ty řádky, ke kterým neexistuje odpovídající řádek v tabulce Letadla (letecká společnost nevlastní vhodné letadlo pro provozování daného letu). Pomocí levého vnějšího spojení budou mít tyto hledané řádky ve sloupcích tabulky Letadla hodnoty NULL. Pomocí predikátu testujícího hodnotu sloupce na hodnotu NULL, tyto řádky najdeme. 3.4 Klauzule GROUP BY a HAVING V klauzuli GROUP BY je uveden čárkou oddělovaný seznam výrazů, přes které se provede agregace dat. Těmito výrazy mohou být mohou sloupce některé z tabulek uvedené v klauzuli FROM, případně z nich vytvořit aritmetické výrazy (sečtení hodnot dvou výrazů, vynásobení hodnoty výrazu konstantou, atd.). Pro jednoduchost budeme dále pracovat pouze se sloupci. Nyní si vysvětlíme princip agregace. Předpokládejme, že agregaci provádíme přes N sloupců, které budeme nazývat agregační sloupce (analogicky by byly agregační výrazy). Multimnožina řádků dotazu se rozdělí na podmnožiny. V každé vzniklé podmnožině budou mít všechny řádky shodné hodnoty všech agregačních sloupců. Hodnoty ostatních sloupců se v rámci každé podmnožiny mohou různit. Po aplikaci klauzule GROUP BY se nahradí všechny řádky každé z podmnožin pouze jedním novým agregovaným řádkem. Budeme mít stejný počet agregovaných řádků, jako bylo vzniklých podmnožin. Výsledkem dotazu bude množina agregovaných řádků. Jak nahradit celou podmnožinu řádků jedním agregovaným řádkem? Jaké bude mít nový agregovaný řádek hodnoty? Řádky v jedné podmnožině mají shodné hodnoty agregačních sloupců, tedy v nově vzniklém agregovaném řádku budou mít tyto sloupce také tyto hodnoty. Problém nastává u neagregačních sloupců, které mohou mít hodnoty různé. Aby mohli být neagregační sloupce zařazeny do dotazu (vyskytnout se v klauzuli SELECT), musí být na ně aplikována některá z agregačních funkcí. Agregační funkce jsou blíže popsány v kapitole 3.1, šestý odstavec. Agregační funkce pracuje s hodnotami sloupce pouze v rámci jedné podmnožiny. Pokud dotaz obsahuje klauzuli GROUP BY, platí výrazná omezení na výrazy uvedené v klauzuli SELECT. Těmito výrazy mohou být mohou být konstantní výrazy nebo výrazy uvedené v klauzuli GROUP BY (agregační výrazy). Případně výrazy vzniklé použitím aritmetických operátorů, které mají oba operandy agregační výraz či konstantu. Na (neagregační) výrazy neuvedené v klauzuli GROUP BY a nepatřící do předchozí skupiny musí být aplikována některé z agregačních funkcí. Klauzule GROUP BY může být doplněna klauzulí HAVING, pomocí které lze stanovit podmínku na agregovaný řádek, při jejímž splnění bude daný řádek zařazen do výsledku. Podmínky mohou být stejně bohaté jako v případě klauzule WHERE, platí jediné omezení. Neagregační výrazy mohou být použity pouze jako parametr agregačních funkcí.
Příklady: Mějme tabulku T1 se sloupci A, B a C typu celé číslo.V tabulce T1 jsou záznamy (1,1,1), (1,1,2), (1,2,3), (1,2,5), (2,1,3), (2,3,5) a (3,1,5). Ukážeme si dva příklady. Příklad č. 1.: Provedeme příkaz SELECT A, MAX(B) FROM T1 GROUP BY A. Agregační sloupec je A, který v dotazu nabývá tří různých hodnot: 1, 2 a 3. Řádky dotazu se nám rozdělí do tří podmnožin. V první podmnožině budou řádky (1,1,1), (1,1,2), (1,2,3), (1,2,5), v druhé budou řádky (2,1,3), (2,3,5) a ve třetí bude řádek (3,1,5). Nyní vyrobíme agregované řádky, z každé podmnožiny vznikne jeden. Agregovaný řádek bude obsahovat hodnotu sloupce A v dané podmnožině a maximální z hodnot ve sloupci B v rámci dané podmnožiny. Vzniknou tři agregované řádky: (1,2), (2,3) a (3,1) Příklad č. 2.: Provedeme příkaz SELECT A, B, COUNT(*), SUM(C) FROM T1 GROUP BY A,B. Agregační sloupce jsou A a B, které v dotazu nabývají pěti různých vzájemných kombinací hodnot (1,1), (1,2), (2,1), (2,3) a (3,1) Řádky dotazu se nám rozdělí do pěti podmnožin. V první podmnožině budou řádky (1,1,1), (1,1,2), v druhé budou řádky (1,2,3), (1,2,5), ve třetí bude řádek (2,1,3), ve čtvrté bude řádek (2,3,5) a v páté bude řádek (3,1,5). Nyní vyrobíme agregované řádky, z každé podmnožiny vznikne jeden. Agregovaný řádek bude obsahovat hodnotu sloupce A v dané podmnožině, hodnotu sloupce B v dané podmnožině, počet prvků dané podmnožiny a součet hodnot ve sloupci C v rámci dané podmnožiny. Vznikne pět agregovaných řádků: (1,1,2,3), (1,2,2,8), (2,1,1,3), (2,3,1,5) a (3,1,1,5). Na slajdu č. 25 jsou dva příklady použití agregačních funkcí, využíváme tabulky Lety a Letadla ze slajdu č. 14. V prvním příkladě máme jeden agregační atribut Společnost a pomocí agregační funkce SUM sčítáme hodnoty sloupce Kapacita v rámci každé podmnožiny, do kterých se řádky dotazu rozdělí. Druhý z dotazů mé také jeden agregační atribut Společnost, podle jehož hodnot se řádky rozdělí do podmnožin. Dotaz využívá klauzuli HAVING, ve které se pro agregovaný řádek porovná výsledek agregační funkce SUM aplikovaný na neagregační sloupec Kapacita s hodnotou vnořeného dotazu. Klíčové pojmy Microsoft SQL Server 2005 Express Edition Microsoft SQL Server Management Studio Express (MSSMSE) databáze Northwind dotaz, tabulka, atribut (sloupec), záznam (řádek) SQL CREATE, ALTER, DROP INSERT, UPDATE, DELETE SELECT, FROM, WHERE, GROUP BY, HAVING Agregační funkce COUNT, SUM, MIN, MAX, AVG ALL, DISTINCT, symbol * BETWEEN, LIKE, EXISTS, IN, ANY, ALL NOT, AND, OR Kartézský součin, přirozené spojení, vnitřní a vnější spojení Agregační sloupec (výraz), agregovaný řádek
Otázky k rekapitulaci Upozornění: odpovědi na některé zde uvedené otázky nelze najít ve studijním textu tohoto tématického bloku. Lze je získat vlastním experimentováním se zdrojovými kódy nebo studiem doporučené literatury. S pomocí MSSMSE charakterizujte jednotlivé tabulky v databázi Nothwind a jejich vzájemné vazby. Jakými způsoby (SQL příkaz, MSSMSE) lze vytvářet nové tabulky, modifikovat a rušit stávající tabulky? Jakými způsoby (SQL příkaz, MSSMSE) lze vkládat nové záznamy do tabulky, modifikovat a mazat stávající záznamy? Co obsahuje tabulka ? jaký je rozdíl mezi záznamem, atributem, řádkem, sloupcem ? Co je dotaz, co je výsledkem dotazu? Vysvětlete pojem multimnožina. Jaké klauzule obsahuje příkaz SELECT, které z nich jsou povinné? Jaký je význam jednotlivých klauzulí příkazu SELECT? U každé klauzule příkazu SELECT co nejpřesněji uveďte jaké syntaktické konstrukce může obsahovat. Ke každé z uvedených konstrukcí uveďte vhodný příklad použití. Jaký je rozdíl mezi ALL a DISTINCT? Jaké známe agregační funkce? Co jednotlivé agregační funkce dělají? Jaké jsou typy spojení tabulek? Jaký je mezi nimi rozdíl? Jednotlivé typy spojení vysvětlete na konkrétním příkladě dvou tabulek. Podrobně popište zpracování dotazu obsahujícího klauzuli GROUP BY. Jaké omezení přináší použití klauzule GROUP BY? Jaké jsou rozdíly mezi klauzulemi WHERE a HAVING? Své odpovědi zdůvodněte. Můžete přidat i syntaktické zápisy tam, kde je to vhodné. Doporučené příklady k naprogramování Následující dotazy utvořte nad databázi Northwind. Upozornění: Tabulka Order Details obsahuje ve svém názvu mezeru, při použití ji nutno uzavřít do hranatých závorek [Order Details] 1. Vypište jména zaměstnanců, kteří uzavřeli nějaké obchody. Vytvořte dotaz, v jehož výsledku se budou opakovat shodné řádky. Následně upravte dotaz tak, že se v jeho výsledku nebudou vyskytovat žádné dva shodné řádky. (Uchovejte obě dvě verze dotazu). 2. Vypište jména měst, ve kterých sídlí některý ze zákazníku a zároveň bydlí některý ze zaměstnanců? 3. Vypište jména měst, ve kterých bydlí některý ze zaměstnanců, ale zároveň zde nesídlí žádný ze zákazníků? 4. Vytvořte přehled měst. U každého města bude uveden počet zaměstnanců, kteří v něm pracují a počet zákazníků, kteří v něm sídlí. Město je uvedeno v seznamu, pokud alespoň jeden z těchto dvou údajů je nenulový. Nápověda: možná se vám bude hodit funkce COALESCE pro změnu NULL hodnot na hodnoty 0.
5. Vypište názvy produktů, které začínají na písmeno G a obsahují alespoň dvě slova. 6. Vypište úplné informace o produktech, které jsou ve své kategorii nejlevnější. 7. Vypište úplné informace o produktech, které jsou levnější než je průměrná cena produktu v dané kategorii. 8. Vypište přehled zaměstnanců spolu s počtem území, která jsou jim přiřazena v jednotlivých regionech. 9. Vytvořte netriviální smysluplný dotaz obsahující spojení nejméně pěti tabulek. 10. Vytvořte netriviální smysluplný dotaz obsahující nejméně tři vnořené SELECTy. 11. Vytvořte netriviální smysluplný dotaz obsahující klauzuli alespoň dvakrát GROUP BY (jednou bude ve vnořeném SELECTu). 12. Vytvořte netriviální smysluplný dotaz, ve kterém se neobejdeme bez klauzule HAVING. 13. Na každý z následujících predikátů vytvořte netriviální smysluplný dotaz: IS NULL, BETWEEN, IN, EXISTS, ALL, ANY. 14. Vytvořte netriviální smysluplný dotaz pro každou z množinových operací: UNION, INTERSECT, EXCEPT. 15. Vytvořte netriviální smysluplný dotaz, ve kterém podmínka uvedená při spojení tabulek nejde přesunout do klauzule WHERE. Studijní literatura [1] Skopal: Slajdy k 7. přednášce z předmětu Databázové systémy (DBI025) vyučovaného na MFF UK. (v tomto tématickém bloku označované jako „slajdy“) https://is.vsfs.cz/auth/el/6410/leto2010/EQ_N_DS/um/DS1.ppt [2] Webové stránky firmy Microsoft, odkud lze stáhnout SQL Server 2005 Expresss. http://msdn.microsoft.com/cs-cz/express/bb410792%28en-us%29.aspx [3] Databáze Nortwind od firmy Microsoft https://is.vsfs.cz/auth/el/6410/leto2010/EQ_N_DS/um/instnwnd.sql [4] Pokorný, Halaška: Databázové systémy, skripta FEL ČVUT, 2003 [5] Halaška, Pokorný: Databázové systémy – cvičení, skripta ČVUT, 2002 [6] Ramakrishnan, Gehrke: Database Systems Management, McGraw-Hill, 2003
