Databáze jako informační zdroj pro uživatele
Helena Palovská Vysoká škola ekonomická Praha 2004
OBSAH O TĚCHTO SKRIPTECH
5
Instalace řešení
5
1
ÚVODNÍ SLOVO A DATABÁZÍCH
6
2
SEZNÁMENÍ SE VZOROVOU DATABÁZÍ NORTHWIND
7
2.1
Vysvětlení kontextu a významu dat
7
2.2
Tabulky databáze Northwind
8
2.3
Schéma databáze Northwind
9
2.4 Struktura tabulek v databázi Northwind 2.4.1 Vysvětlení k některým polím v databázi Northwind
10 11
2.5
Formuláře
11
2.6
Pochopení dat
11
3 3.1
PŘÍPRAVA NÁSTROJE K PROCVIČOVÁNÍ ÚLOH K čemu slouží relace ve vašem projektu?
12 14
3.2 Dotazy Rozšířené podrobnosti objednávek a Mezisoučty objednávek 3.2.1 Úprava dotazu Rozšířené podrobnosti objednávek
14 15
3.3
15
4
Kopie vzorových řešení
JEDNODUCHÉ DOTAZY
16
4.1 První jednoduchý dotaz 4.1.1 Řazení v návrhu dotazu 4.1.2 Co je dotaz 4.1.3 Úpravy v datovém listu odpovědi 4.1.4 Kritéria v návrhu dotazu
16 18 19 19 19
4.2
Kritéria v dotazu
20
4.3
Jedinečné hodnoty
21
4.4 Výrazy v dotazu 4.4.1 Formát a zobrazení pole v dotazu 4.4.2 Tvůrce výrazů 4.4.3 Lupa
21 22 22 23
4.5
Jak používat průvodce jednoduchým dotazem
23
4.6
Dotaz v návrhovém zobrazení
24
4.7
Přidávání tabulek do návrhu dotazu
25
4.8
Nejvyšší hodnoty
26
4.9
Dotazy s parametry
26
5
SESKUPOVACÍ DOTAZY
27
5.1
Co jsou seskupovací dotazy?
27
5.2
První seskupovací dotaz
28
5.3
Agregační funkce
30
5.4
Průvodce pro souhrnný dotaz
30
5.5
Count v seskupovacím dotazu
31
5.6
Doporučení pro použití průvodce
32
5.7
Omezení počtu řádků výsledku seskupovacího dotazu
32
5.8 Křížové dotazy 5.8.1 Pole křížového dotazu 5.8.2 Datový list křížového dotazu
33 35 35
5.9 Kontingenční tabulky, kontingenční grafy 5.9.1 Kontingenční grafy
36 37
5.10 Zvláštní případy seskupovacích úloh 5.10.1 Dotazy na počet různých hodnot 5.10.2 Agregace First, Last
38 38 39
6
SOUHRNY POMOCÍ DATOVÝCH KOSTEK
42
6.1
Datová kostka z dat Northwind
42
6.2
Jiná kostka z Northwind
44
7
PŘEHLEDY A SOUHRNY JEŠTĚ JINAK
45
8
MEZIVÝSLEDKY
47
8.1
Příklad propojení mezivýsledků
47
8.2
Mezivýsledek pro použití v kritériu
50
8.3 Skalární pod-dotazy 8.3.1 Alternativa pomocí více mezivýsledků 8.3.2 SQL SELECT jako výraz pro pole dotazu
9
DOTAZY NA TO CO V DATABÁZI NENÍ
52 52 53
54
9.1
Alternativní řešení
55
10
POLOSPOJENÍ
56
11
SELF JOIN
60
12
SESTAVY
62
12.1
Automatické sestavy
62
12.2
Průvodce sestavou
62
12.3 Podsestavy 12.3.1 Grafy
67 68
12.4 Parametry 12.4.1 Parametry v podsestavách
68 69
12.5
Obrázky v sestavě
69
13
AKČNÍ DOTAZY
13.1
Vytvářecí dotazy
70
13.2
Přidávací dotazy
70
13.3
Odstraňovací dotazy
72
13.4
Aktualizační dotazy
73
14
CVIČENÍ
14.1
Jednoduché dotazy
70
76 76
14.2 Dotazy se souhrny 14.2.1 Křížové dotazy 14.2.2 Výběr podle vypočteného souhrnu 14.2.3 Souhrny pro celou tabulku 14.2.4 Spojení několika souhrnů 14.2.5 Agregační funkce pro čas 14.2.6 Počet různých hodnot
76 77 78 78 78 79 79
14.3
Kritérium propojením
79
14.4
Řešení cvičení
80
15
TÉMATICKY ZAMĚŘENÉ ÚLOHY
15.1
Výjimky a závislosti
85
15.2
Zaměření zaměstnanců
85
15.3
Tendence nákupů
85
15.4
Průběh cen
85
15.5
Porovnání charakteristik
86
REJSTŘÍK
85
87
O těchto skriptech Cílem těchto skript je naučit uživatele používat databázi jako informační zdroj, použité nástroje jsou ze sady MS Office, ale znalosti zde získané poslouží i při použití jiných uživatelských nástrojů. Hlavními prostředky pro uživatele k získávání nestandardních informací, tj. navíc nad předem připravené výstupy informačního systému, jsou dotazy a tiskové sestavy. Ty jsou také hlavní náplní těchto skript. Rutinní postupy práce, jež jsou velmi dobře popsány a vysvětleny v nápovědě programů MS Office, zde nejsou vykládány. Téma každé kapitoly je nejprve uvedeno popisem jednoduchého příkladu „krok za krokem“, dále se výklad soustředí spíše na naznačení dalších cest k dosažení cílů a na vysvětlení účelu. Pouze některé postupy jsou zde podány „krok za krokem“. Tato skripta jsou určena jak pro počítačově gramotné začátečníky, tak i pro středně pokročilé uživatele. Proto je text doplněn poznámkami rozšiřujícími základní výklad, ty jsou buď ve formě textu drobným písmem nebo jako poznámky pod čarou. Pouze některé poznámky pod čarou slouží jako nápověda pro nějaký rutinní postup. K výkladu je použit program MS Access 2002, některé speciální úkoly jsou řešeny pomocí programu MS Excel 2002 a některých jeho dodatečných modulů. Ke standardní instalaci programu MS Access patří vzorová databáze Northwind. Tato vzorová databáze je používána v příkladech k procvičení. Ve verzi MS Access2002 je standardně instalována do složky C:\Program Files\Microsoft Office\Office10\Samples\. Ke skriptům patří také vzorová řešení úloh 1 dostupná na internetové adrese nb.vse.cz/~palovska/uzida/ Zde vyberte svou verzi programu MS Access a stáhněte si zip-soubor. Tento zip-soubor rozbalte do složky C:\Uživatel databází\ Vzorové řešení předpokládá umístění databáze Northwind ve standardní složce. Pokud máte databázi Northwind umístěnou jinde, musíte si řešení instalovat podle návodu „Instalace řešení“ níže. K práci na úlohách a cvičeních doporučuji připravit si vlastní projekt programu MS Access, návod naleznete v kapitole 3 Příprava nástroje k procvičování úloh.
Instalace řešení Instalaci vzorových řešení úloh nemusíte upravovat, pokud máte vzorovou databázi Northwind umístěnou ve standardní složce C:\Program Files\Microsoft Office\Office10\Samples\ . Pokud tomu tak není, postupujte následujícím způsobem. Odstraňte atribut „jen pro čtení“ souboru C:\Uživatel databází\Řešení.mdb , potom tento soubor otevřte. V menu zvolte Nástroje – Správa databáze – Správce propojených tabulek. V dialogu „Správce propojených tabulek“ zaškrtněte vše kromě „Intervaly“ a „Ceny“a potom stiskněte OK. V následujícím dialogu „Vyberte nové umístění pro Dodavatelé“ nalezněte vaši databázi Northwind. Dostanete zprávu o úspěšném obnovení propojení tabulek, tu potvrďte. Zavřete okno programu MS Access. Nastavte atribut „jen pro čtení“ souboru C:\Uživatel databází\Řešení.mdb .
1
Vzorová řešení jsou dostupná také při výuce kurzů, pro něž jsou tato skripta určena.
5
1 Úvodní slovo a databázích Naprostá většina databází, se kterými se dnes můžete setkat, jsou databáze tzv. relační 2. To znamená, že data v nich jsou uspořádána do tabulek. Jedna tabulka v relační databázi běžně obsahuje data o věcech jednoho druhu, například můžeme mít tabulku Výrobky obsahující data o jednotlivých výrobkách. To znamená, že jednotlivé řádky této tabulky představují záznamy o jednotlivých výrobkách. Sloupce tabulek v relační databázi se nazývají pole, mají jméno, a představují jednotlivé hodnoty, do nichž jsou záznamy v dané tabulce rozděleny. V tabulce Výrobky tak můžeme mít pole „NázevVýrobku“, „JednotkováCena“, „JednotkyNaSkladě“ a pod. Každé pole tabulky má kromě jména a významu také určen tzv. datový typ – pole NázevVýrobku bude mít datový typ „text maximální délky 40 znaků“, pole JednotkováCena bude mít datový typ „měna“ (v konkrétní databázi 3 to může znamenat číslo s maximálně 15ti ciframi před desetinnou čárkou a maximálně 4mi ciframi za desetinnou čárkou, se kterým se nepočítá jako se zaokrouhleným reálným číslem), pole JednotkyNaSkladě bude mít datový typ „malé celé číslo“ (v konkrétní databázi to může znamenat rozsah od – 32 768 do + 32 768, tj. 2byte v binárním kódování čísla). Dalšími často používanými datovými typy, kromě „text zvolené maximální délky“ a „číslo zvolené přesnosti“, jsou „datum“ a „ano/ne“. Moderní relační databáze nabízejí také datové typy pro ukládání delších textů proměnné délky, datové typy pro obrázky, pro zvuky apod. Tabulky v databázi mívají obvykle tzv. klíč – klíč tabulky je skupina polí v této tabulce, podle níž lze jednotlivé záznamy jednoznačně identifikovat. Většinou jde o jediné pole 4. V případě tabulky Výrobky může být jejím klíčem např. pole ČísloVýrobku – klíče jsou často uměle vytvořené identifikační kódy pro účely organizace databáze.
2
Název pochází od matematického pojmu „relace“, jejž v roce 1969 použil pan E.F.Codd jako základní představu pro organizaci dat v databázi – matematickou relaci lze mimo jiné zapsat jako tabulku. Představa, že tabulka, jejíž řádky představují záznamy o objektech v realitě, je matematickou relací, se však může jevit poněkud absurdní. Pouze v případě, že tabulka relační databáze zaznamenává vztah mezi objekty světa, je její chápání jako matematické relace rozumné. Pojem relace používaný v MS Access (viz str. 9) i v některých jiných programech pro správu dat je však něco docela jiného, zde jde o skutečný vztah mezi objekty. 3
Takto to je v SŘBD MS Access. K tomuto datovému typu zde patří i formátová vlastnost, že hodnoty jsou zobrazovány se znakem národní měny, např. Kč nebo €. 4
Ne však vždy, ve vzorové databázi má tabulka Rozpis objednávek klíč složený ze dvou polí.
6
2 Seznámení se vzorovou databází Northwind 2.1 Vysvětlení kontextu a významu dat Databáze Northwind obsahuje data o následujícím obchodním kontextu (viz též orientační obrázek níže): Dodavatelé dodávají výrobky, které nabízíme svým zákazníkům. Výrobky máme roztříděny do kategorií. O výrobkách máme běžné katalogové a skladové záznamy, kromě jiného také aktuální jednotkovou cenu. Naši zákazníci podávají objednávky, v nichž objednávají vybrané výrobky ve zvoleném množství, navíc mohou v dané objednávce pro daný druh výrobku získat slevu. U každého objednání nějakého výrobku v nějaké objednávce máme tedy vždy zaznamenáno množství objednaných kusů tohoto výrobku v dané objednávce, dohodnutou procentuelní slevu pro tento výrobek v dané objednávce, a cenu výrobku platnou v době objednání, protože aktuální cena výrobku se může měnit. Zákazníci i dodavatelé jsou smluvní partneři 5, o kterých si vedeme záznamy: u každého máme kromě jména firmy a adresy také jméno kontaktní osoby a funkci této osoby v partnerské firmě. U některých dodavatelů máme navíc poznamenáno URL domovské stránky. Každou objednávku přijímá nějaký náš zaměstnanec. O zaměstnancích máme zaznamenány personální údaje, dále máme u všech, kromě nejvyššího šéfa, zapsáno kdo je jejich nadřízený. patří do
DRUH VÝROBKU ČísloVýrobku NázevVýrobku MnožstvíVJednotce JednotkováCena PočetKsNaSkladě ObjednánoKs MinimálníÚroveňKs NákupUkončen
KATEGORIE ČísloKategorie NázevKategorie Popis Obrázek
u .. máme
KONTAKTNÍ OSOBA JménoAPříjmení FunkceKontaktníOsoby
je
dodává
je objednán v
PočetKs CenaZaKs Sleva ZAMĚSTNANEC ČísloZaměstnance Příjmení Jméno FunkceZaměstnance FormálníOslovení DatumNarození ZaměstnánOd Adresa TelefonDomů Linka Foto Poznámky
SMLUVNÍ PARTNER JménoFirmy Adresa Telefon [Fax]
je
DODAVATEL ČísloDodavatele [DomovskáStránka] OBJEDNÁVKA ČísloObjednávky DatumObjednávky DodatDne DatumOdeslání
podává
přebírá
ZÁKAZNÍK KódZákazníka PŘÍJEMCE JménoPříjemce Adresa
přepravuje přijímá
Dopravné
podřízený
má nadřízeného nadřízený
PŘEPRAVCE ČísloPřepravce JménoFirmy Telefon
Rozvětvené zakončení znamená „eventuelně více“, například: „Dodavatel dodává eventuelně více druhů výrobků“ , „Druh výrobku je objednán v eventuelně více objednávkách“ , „Objednávka objednává eventuelně více druhů výrobků“ Zakončení bez rozvětvení znamená „jediným/jediného“, například: “Druh výrobku je dodáván jediným dodavatelem“, “Zaměstnanec má jediného nadřízeného“, „U smluvního partnera máme jedinou kontaktní osobu“ Připojení přerušovanou čarou znamená „nemusí“, například: „Zákazník nemusí podávat objednávku“„Zaměstnanec nemusí mít nadřízeného zaměstnance“, „Zaměstnanec nemusí být nadřízeným nějakého zaměstnance“,„Objednávka nemusí být předána nějakému příjemci (tzn. jinému než je zákazník)“
Pro každou objednávku je sjednán některý náš smluvní přepravce, jenž má zboží přepravit, a je dohodnuto a zaznamenáno dopravné.
5
Později zjistíme, že v databázi Northwind je jedna tabulka Zákazníci a jedna tabulka Dodavatelé, smluvní partneři jsou tedy rozděleni do těchto dvou tabulek.
7
Objednávka je běžně zasílána na jméno firmy zákazníka a jeho adresu, ale může mít určeného jiného příjemce, který ji má převzít. Ti nás ovšem zajímají pouze tehdy, když mají převzít nějakou objednávku. Zákazníci nás oproti tomu zajímají i tehdy, když žádnou jejich objednávku momentálně nemáme. Podobně si ponecháváme záznamy o přepravcích, zaměstnancích, výrobkách i v případě, že bychom neměli žádné s nimi spojené objednávky. Taktéž v případě dodavatelů, kteří by momentálně nedodávali žádné výrobky. Ostatní lépe pochopíte prohlížením tabulek v databázi Northwind.
2.2 Tabulky databáze Northwind Nalezněte a spusťte vzorovou databázi Northwind. Otevře se okno programu MS Access a v něm úvodní dialog této vzorové databáze. Tento úvodní dialog vás informuje, že vzorová databáze je výuková, že všechno v ní si lze do detailů prohlédnout. 6 Úvodní dialog zavřete tlačítkem OK, objeví se Hlavní panel. Protože tato skripta jsou určena uživatelům spíše než programátorům, není tento dialog pro nás zajímavý, je to vzor řídícího dialogu ukázkové databázové aplikace. Můžete si sice pomocí něho prohlédnout nabízené formuláře pro kategorie výrobků, pro výrobky, pro dodavatele nebo pro objednávky, dále je nabízen seznam vzorových tiskových sestav, ale toto vše si lze prohlédnout i později z okna databáze Northwind. Takže i tento Hlavní panel nakonec uzavřeme, a konečně vidíme standardní interface programu MS Access s oknem otevřené databáze. Okno databáze má vlevo přepínače typu objektů, které chceme vidět a eventuelně s nimi pracovat: tabulky, dotazy, formuláře, sestavy, stránky, makra, moduly. To všechno jsou objekty, které může databáze programu MS Access obsahovat, a vzorová databáze Northwind také obsahuje ukázky všech těchto typů objektů. Ale v našem kurzu budeme pouze potřebovat porozumět tabulkám a naučit se pracovat s dotazy a sestavami. Pro porozumění datům vzorové databáze ještě použijeme vzorové formuláře Dodavatelé, Kategorie, Objednávky, Výrobky, Zákazníci a Zaměstnanci. Nejprve se seznámíme s tabulkami. Přepněte na seznam tabulek, v okně databáze si přepněte na zobrazení podrobností
. Uvidíte popis jednotlivých tabulek:
Některé tabulky jsou snadné k pochopení, něco vysvětlíme. Tabulka Výrobky popisuje druhy výrobků. Smluvní partneři jsou rozděleni do tabulek Zákazníci a Dodavatelé, údaje o kontaktních osobách u smluvních partnerů jsou součástí záznamů v tabulkách Zákazníci a Dodavatelé. Tabulka Rozpis objednávek obsahuje detailní záznamy o jednotlivých objednáních jednotlivých druhů
6
Prohlížejte, ale nic neměňte. Pokud máte náhodou soubor Northwind.mdb přístupný k zápisu a ne pouze ke čtení, doporučuji pro jistotu změnit vlastnosti tohoto souboru na „pouze ke čtení“.
8
výrobků v jednotlivých objednávkách, prohlédneme si to. Otevřete 7 tabulku Objednávky, a rozviňte v několika řádcích detaily stisknutím + před řádkem:
Vnořené malé tabulky představují to, co dohromady tvoří tabulku Rozpis objednávek. V té je ovšem ještě údaj o tom, ke které objednávce co patří. Po prohlédnutí můžete zavřít okno „Objednávky“, pak si můžete prohlédnout7 i samotnou tabulku Rozpis objednávek.)
2.3 Schéma databáze Northwind 8 Prohlédneme si schéma databáze. Stisknutím tlačítka Relace v panelu nástrojů se otevře okno „Relace“. V něm je pro každou tabulku databáze zobrazeno jedno malé okénko se seznamem polí této tabulky. Okna „Deset nejdražších výrobků“ si nevšímejte, pro nás není k ničemu.
Protože objednávku podává jediný zákazník, přijímá jediný zaměstnanec, přepravuje jediný přepravce a přijímá jediný příjemce, jsou údaje o těchto faktech součástí záznamu o objednávce, tabulka Objednávky má pro ně odpovídající pole. (Pokud objednávku přebírá zákazník a ne jiný příjemce, jsou v záznamu objednávky v polích určených pro příjemce zopakovány údaje o zákazníkovi.) Všechny adresy jsou rozděleny do složek „Adresa“, „Město“, „Region“, „PSČ“, „Země“. Údaj o nadřízeném zaměstnanci je součástí záznamu o zaměstnanci, v poli „Nadřízený“. Údaj o dodavateli výrobku je součástí záznamu o výrobku, v poli „ČísloDodavatele“, podobně je tomu s kategorií výrobku. Čáry spojující dvojice tabulek značí tzv. relace. Ty mají několik významů, vzájemně souvisejících. První a hlavní význam je ten, že čára spojuje to co máme spolu spojovat: v tabulce Objednávky je 7
„Otevření“ tabulky docílíme poklepáním na tabulku v seznamu, nebo pomocí tlačítka otevře tzv. datový list tabulky.
8
Je dosažitelné pouze pokud je aktivní okno Databáze.
9
. Ve skutečnosti se tak
v poli KódZákazníka uveden kód zákazníka, který ji podal, tohoto zákazníka najdeme v tabulce Zákazníci podle shodné hodnoty v poli, jež se také jmenuje KódZákazníka. Podobně je tomu s poli ČísloZaměstance v Objednávkách i v Zaměstnancích: v tabulce Objednávky je uvedeno číslo zaměstnance, který ji přijal. – Překvapivě zjišťujeme, že pole, které obsahuje číslo přepravce, který je sjednán pro přepravu objednávky, se v tabulce Objednávky jmenuje „Přeprava“. I to se v reálné databázi může stát, že spolu související pole se jmenují jinak. Tučně jsou vyznačeny klíče tabulek. V souvislosti s klíči mají relace většinou ještě další význam: spojují vždy klíč v jedné z tabulek s tzv. cizím klíčem v druhé tabulce: např. pole ČísloKategorie v tabulce Výrobky je v této tabulce cizím klíčem, spojeným s klíčem tabulky Kategorie. Proto jsou na koncích čar zobrazujících relace znaky 1 a ∞ . V našem případě tabulek Kategorie a Výrobky to znamená, že každý jednotlivý výrobek patří do jediné kategorie, zatímco do jednotlivé kategorie patří eventuelně více výrobků. Podobně je tomu v ostatních případech relací mezi tabulkami. Zvláštní pozornost zasluhuje tabulka Rozpis objednávek. V ní jsou dva cizí klíče: pole ČísloObjednávky a pole ČísloVýrobku jsou v ní obě cizími klíči, jsou spojeny s klíči tabulek Objednávky nebo Výrobky. Shodou okolností tvoří dvojice (ČísloObjednávky, ČísloVýrobku) zároveň klíč tabulky Rozpis objednávek 9.
2.4 Struktura tabulek v databázi Northwind Dále si prohlédněte jednotlivé tabulky 10. (V programu MS Access se oknu, v němž si prohlížíte tabulku v řádkách a sloupcích, říká „datový list“.) Při prohlížení se dočkáte překvapení – pole tabulek, jež obsahují cizí klíč jiné tabulky (viz předchozí sekci), nezobrazují skutečné klíče-kódy, ale srozumitelná jména odpovídajících věcí či lidí. Podobně v poli „Nadřízený“ v tabulce Zaměstnanci. Navíc v hlavičce datového listu často není jméno pole (např. KódZákazníka v tabulce Objednávky), ale jiný titulek („Zákazník“). Toto a další důležité věci lze pochopit z návrhu tabulek 11: každé pole má datový typ a někdy popis viditelné v seznamu v horní části okna návrhu tabulky. Další vlastnosti polí lze nalézt na kartách Obecné a Vyhledávání v dolní části okna, k tomu je vždy potřeba v seznamu nahoře vybrat to pole, jehož vlastnosti chceme dole prohlížet. Zejména si všimněte vlastností Titulek, Velikost pole (nabízené v závislosti na datovém typu pole). U výše zmíněných cizích klíčů je na kartě Vyhledávání zakódováno, co se má místo cizího klíče-kódu zobrazovat. Např. u pole ČísloZaměstnace v tabulce Objednávky a u pole Nadřízený v tabulce Zaměstnanci je zakódováno, že se má zobrazit celé jméno příslušného zaměstnance. Tyto vlastnosti se použijí, kdykoli se má pole zobrazit, tedy v datovém listu, v dotazech, sestavách a formulářích. (Pokud ovšem explicitně nezvolíme pro daný případ vlastnosti jiné.) V zobrazení datového listu tabulky Kategorie vidíme v poli Obrázek pouze text „Rastrový obrázek“. Samotný obrázek zobrazíte poklepáním na „Rastrový obrázek“. 12
9
Tomu je tak, protože tabulka Rozpis objednávek zaznamenává vztah „eventuelně více – eventuelně více“ mezi objednávkami a druhy výrobků. V takovýchto případech je situace vždy stejná jako zde. 10
Vyberte tabulku v seznamu, pak vyvolané pravým tlačítkem myši…
nebo klávesa Enter či poklepání na jménu tabulky, či lokální menu
11
Tlačítko dosažitelné buď z okna „Databáze“, nebo z lokálního menu na tabulce či na titulkovém pásu datového listu, či jako samostatná volba „Zobrazit“ v panelu nástrojů zcela vlevo. 12
Vzorové databáze Northwind se drobně odlišují ve verzi 2000 a 2002. Pole Foto tabulky Zaměstnanci v jednom případě obsahuje „Rastrový obrázek“, v druhém případě je v něm jméno souboru obsahujícího obrázek. „Rastrový obrázek“ je zaznamenán v databázi, je její součástí, kdežto soubor s obrázkem je mimo databázi.
10
2.4.1 Vysvětlení k některým polím v databázi Northwind Některá pole některých tabulek v databázi Northwind stojí za zvláštní zmínku. Pole Telefon a Fax jsou typu text, a ne nějakého číselného typu. To je proto, že ve skutečnosti nejde o čísla, s nimiž lze dělat výpočty, ale o číselné kódy. Navíc se často používají různé oddělovací znaky na členění telefonních čísel – podívejte se do příslušných datových listů. Pole Kontaktní osoba u Zákazníků a Dodavatelů obsahuje jméno i příjmení, někdy i více částí. To lze spíše považovat za nevhodné. Lépe je tomu v tabulce Zaměstnanci, kde jsou zvlášť pole Jméno a Příjmení. Pole Poznámky v tabulce Zaměstnanci a pole Popis v tabulce kategorie jsou typu „memo“, což je datový typ pro text předem neurčené délky, tzv. „volný text“. Pole Dopravné v tabulce Objednávky a obě pole JednotkováCena v tabulkách Rozpis objednávek i Výrobky jsou typu „měna“. To je speciální číselný typ, určený k ukládání peněžních údajů. Je to přesný číselný typ (tedy se nezaokrouhluje), a v programu MS Access je zobrazován se znaky národní měny. Je třeba mít na paměti, že znaky „Kč“ jsou pouze zobrazovány, nikoli uloženy v datech v tabulkách, jde tedy pouze o formát. Pole Sleva v tabulce Rozpis objednávek je typu „číslo jednoduché přesnosti“. To je datový typ, se kterým se počítá jako se zaokrouhleným reálným číslem, tedy „nepřesný číselný typ“ 13. Navíc má formát „procenta“, tedy například pokud je v tabulce uložena hodnota 0,2 pak je zobrazováno 20%. Pole Obrázek v tabulce Kategorie je typu „objekt OLE“ (obsahuje celé obrázky), kdežto pole Foto v tabulce Zaměstnanci je typu „text“ (osahuje pouze jméno souboru s obrázkem).
2.5 Formuláře Pro názornost a pochopení si můžete prohlédnout vzorové formuláře v databázi Northwind, formuláře najdete přepnutím na v okně Databáze. Jsou zde formuláře pojmenované stejně jako jména tabulek, ty doporučuji vyzkoušet. Formuláře obecně slouží k pohodlnému prohlížení dat či ke vkládání nových dat.
2.6 Pochopení dat Prohlížejte si tabulky, jejich strukturu, a hlavně schéma databáze tak dlouho, až pochopíte význam dat v databázi. Pokud někdy později budete mít pocit, že datům nějak nerozumíte, vždy se vraťte k těmto nástrojům: relace, návrhy tabulek, event. datové listy.
13
Reálné číslo s plovoucí desetinnou čárkou.
11
3 Příprava nástroje k procvičování úloh Vzorová databáze Northwind obsahuje množství vzorových dat i vzorové aplikační objekty ke zpracování dat. Jsou zde i takové objekty, jejichž výklad či vysvětlení přesahuje rámec záměru těchto skript, neboť databáze Northwind je ukázkou řešení i pro tvůrce aplikací – programátory. Abyste tuto vzorovou databázi při svých pokusech neporušili (na učebnách a studovnách VŠE je dokonce přístupná pouze pro čtení), vytvoříte si vlastní nástroj pro pokusy a práci: Spusťte program MS Access a v úvodním dialogu vyberte „Vytvořit novou prázdnou databázi“. V následujícím dialogu uložte nový databázový soubor do nějaké vaší vlastní složky, nějak vhodně si ho pojmenujte. Objeví se standardní interface programu MS Access, s otevřenou vaší novou databází. Protože při práci s vaší databází budete chtít nahlížet také do databáze Řešení či do vzorové databáze Northwind, mohla by se vám okna plést. Proto je dobré zařídit, aby tlačítka na hlavním panelu Windows představující jednotlivé otevřené databáze MS Access byla od sebe odlišitelná. Databáze Řešení má název „řešení“ na odpovídajícím tlačítku, ve vaší databázi totéž zařídíte následujícím postupem: V menu Nástroje vyberte Po spuštění… a dialogu „Po spuštění“ zadejte do pole Název aplikace nějaké vaše zvolené jméno:
To jméno se pak objeví na hlavním panelu Windows, i v titulku okna MS Access s vaší databází. Další krok se týká úspory místa, které vaše databáze bude zabírat: v menu Nástroje vyberte Možnosti…, a na kartě Obecné zaškrtněte vlevo dole „Komprimovat při zavření“ 14:
Nyní do vaší databáze připojíte data ze vzorové databáze Northwind. Z menu Soubor vyberte Načíst externí data,
. V následujícím dialogu „Propojit“ najděte vzorovou databázi
Northwind. V dalším dialogu „Propojit tabulky“ použijte tlačítko a stiskněte OK. Nyní je vaše databáze připojená k datům vzorové databáze Northwind, aniž byste vytvářeli zbytečnou kopii těchto dat. Takto vypadají nyní objekty Tabulky ve vaší databázi:
14
Toto způsobí opravdu významnou úsporu místa, ale je to možno použít pouze u nesdílených souborů MS Access. Sdílené databáze je třeba komprimovat (například správcem databáze) explicitně v době, kdy s databází nikdo jiný nepracuje – volbou v menu Nástroje-Správa databáze-Zkomprimovat a opravit databázi.
12
Poznámka: Analogicky si lze připravit uživatelský nástroj připojující vás k jiné databázi. Podmínkou je dosažitelnost databáze, k níž se chcete připojit (tj. existence a znalost přístupových cest) a schopnost programu MS Access číst data eventuelně cizího formátu. Některé použitelné formáty můžete vidět v dialogu „Propojit“ jako nabízené typy souborů, jiné mohou být dosažitelné prostřednictvím ovladače ODBC (konzultujte případně svého správce systému).
Dále z databáze Northwind zkopírujeme dva dotazy: v menu Soubor vybereme opět Načíst externí data, ale tentokrát zvolíme , a opět nalezneme vzorovou databázi Northwind. Následující dialog „Import objektů“ má více karet, vybereme kartu Dotazy a na ní dotazy „Mezisoučty objednávek“ a „Rozšířené podrobnosti objednávek“. Tyto dva dotazy jsou užitečné pro realizaci mnoha úloh (viz konec této kapitoly). Takto vypadají nyní objekty Dotazy ve vaší databázi:
Ještě si ve svém projektu upravte relace. V panelu nástrojů stiskněte tlačítko Relace , otevře se okno „Relace“. V něm jsou nějaké objekty propojené čarami, to vše se sem dostalo při propojování dat z databáze Northwind, protože jste zvolili „Vybrat vše“. Přidáte oba zkopírované dotazy: Zobrazit tabulku, v následujícím dialogu „Zobrazit tabulku“ na kartě v menu Relace vyberte Dotazy vyberte oba dotazy „Mezisoučty objednávek“ i „Rozšířené podrobnosti objednávek“. Přesouváním a zvětšováním obdélníčků upravte vzhled v okně „Relace“ do podoby podle obrázku na následující straně dole. Dále vytvořte propojení mezi „Rozšířené podrobnosti objednávek“ a „Výrobky“: v menu Relace vyberte Upravit relaci…, v následujícím dialogu zmáčkněte tlačítko „Vytvořit novou“ nastavte
potvrďte, a v dialogu „Upravit relace“ zmáčkněte propojení: 13
a v dialogu
. Podobně vytvořte dvě další
Vaše relace nyní mají vypadat takto:
(Vzory relací si můžete prohlédnout i v databázi Řešení.) Kontrolu správnosti či opravy chyb v okně Relace můžete udělat takto: v menu Relace vyberte Zobrazit všechny relace. Pokud se objevilo něco navíc oproti vaší představě (oproti tomu, jak je to na vzoru zde nahoře), pak odstraňte nejprve přebytečné čáry neboli relace tím, že na ně klepnete myší (je třeba se trefit) a stisknete klávesu Delete, následně potvrďte. Pak teprve odstraňujte eventuelní přebývající objekty obdélníčků (tj. přebytečné tabulky nebo dotazy).
3.1 K čemu slouží relace ve vašem projektu? Okno zobrazující relace vám může sloužit jako zobrazení schématu databáze, při němž si můžete rozmýšlet strategii vašich dotazů. (Tlačítko aktivní okno „Databáze“.)
pro otevření okna „Relace“ je dostupné, když je
Relace definované v databázi MS Access jsou cenným pomocníkem při tvorbě dotazů, sestav, formulářů. Poznámka: Pokud jste si připravili databázi MS Access připojující vás k cizímu zdroji podle dřívější poznámky, je vhodné upravit si také relace. Jaké by měly být, to vám může poradit vlastník či správce zdroje dat.
3.2 Dotazy Rozšířené podrobnosti objednávek a Mezisoučty objednávek Poklepáním na ikony „Rozšířené podrobnosti objednávek“ a „Mezisoučty objednávek“ si prohlédněte datové listy těchto dotazů. Prohlížení ukončete zavřením oken s těmito dotazy. 14
Oba dotazy ze vzorové databáze Northwind slouží jako pomocné dotazy pro mnoho úloh. První z nich, „Rozšířené podrobnosti objednávek“, dopočítává celkovou částku účtovanou za jeden druh výrobku v jedné objednávce. Zhruba řečeno, dopočítává JednotkováCena*Množství*(1-Sleva) k Rozpisu objednávek. (Tento výpočet je ještě upraven kvůli zaokrouhlovacím chybám.) Dotaz „Mezisoučty objednávek“ pak dopočítává pro každou objednávku ∑výrobky v objednávce JednotkováCena*Množství*(1-Sleva) V běžných obchodních databázích bývají tyto vypočtené hodnoty ukládány přímo v databázi, tj. v tabulce podobné Rozpisu objednávek bývá ještě sloupec analogický poli VýslednáCena z dotazu „Rozšířené podrobnosti objednávek“, a v tabulce podobné Objednávkám bývá ještě sloupec analogický poli Mezisoučet z dotazu „Mezisoučty objednávek“.
3.2.1 Úprava dotazu Rozšířené podrobnosti objednávek Dotaz Rozšířené podrobnosti objednávek je poněkud nevhodně navržen pro naše účely. Upravíme ho: Otevřeme návrh tohoto dotazu tlačítkem , klepneme na seznam „Výrobky“ a klávesou Delete ho z dotazu odstraníme. Tím je úprava skončena, uložíme z menu Soubor-Uložit či tlačítkem Uložit v panelu nástrojů. Návrh nyní vypadá takto:
3.3 Kopie vzorových řešení Z databáze Řešení si kdykoli můžete do svého projektu importovat některé objekty, pokud pocítíte takovou potřebu. Postup je následující: v menu Soubor vyberte Načíst externí data, zvolte , a nalezněte databázi Řešení. Následující dialog „Import objektů“ má více karet, vyberte kartu s příslušným typem objektů a nalezněte žádaný objekt. Pokud chcete importovat sestavu, musíte si k ní přiimportovat i příslušný její datový zdroj – jak zjistit, který to je, popisuje kapitola Sestavy. Pro začátek si importujte dotaz „Smluvní partneři“. Tento dotaz poskytuje ucelený pohled na smluvní partnery, ať zákazníky nebo dodavatele. Propojovat ho v relacích nebudeme, neboť se použije jen zřídka. (Příkladem jeho použití jsou dotazy „Tři části jména“ a „Zákazníci a dodavatelé výrobků podle měst“.) Dále se vám může často hodit dotaz „Jména zaměstnanců“ 15, importujte si ho též. V dalším textu se občas setkáte s výzvou, abyste si pořídili kopii nějakých dalších objektů.
15
Pro sloupce křížových dotazů či do kontingenčních tabulek nebo grafů.
15
4 Jednoduché dotazy Dotazy jsou hlavní nástroj pro získávání informací z databáze. Nejčastěji poslouží tzv. jednoduché dotazy. Jednoduché dotazy lze většinou vytvořit pomocí průvodce. Použijeme tohoto průvodce k tomu, abychom vytvořili první dotaz jako příklad, a na něm si vysvětlíme, co dotazy vlastně jsou.
4.1 První jednoduchý dotaz Přepněte okno Databáze na Dotazy:
Zde je ikona „Vytvořit dotaz pomocí průvodce“. Spusťte ji 16. Spustí se průvodce, první krok. V tomto kroku vybíráte některá pole z některých tabulek: nejprve v seznamu „Tabulky či dotazy“ vyberete příslušnou tabulku, čímž se v seznamu „Dostupná pole“ vlevo dole objeví všechna pole vybrané tabulky. Z tohoto seznamu vybíráte 17 některá pole do seznamu „Vybraná pole“ vpravo dole. Můžete pokračovat zvolením další tabulky. Vyberte takto z tabulky Zákazníci pole Firma, z tabulky Kategorie pole NázevKategorie a z tabulky Výrobky pole NázevVýrobku, stiskněte tlačítko „Další“. Následující krok průvodce zatím zcela přeskočíme, tedy ještě jednou „Další“. V následujícím kroku pojmenujte tento první dotaz, a dokončete průvodce tlačítkem „Dokončit“. Otevřel se datový list s odpovědí na dotaz. Odpověď na dotaz je tabulka, sestavená podle nějakého návodu. Tímto návodem je formulace dotazu. Tuto si lze prohlédnout, když přepneme na zobrazení „Návrhové“ 18.
Formulace dotazu říká, z jakých tabulek má být odpověď vytvářena, jak mají být tyto tabulky navzájem spojeny, a jaká pole mají být ve výsledku vybrána. Složitější dotazy mohou mít další specifikace.
16
nebo klávesa Enter či poklepání na ikonu, či lokální menu vyvolané pravým tlačítkem myši…
17
Poklepání nebo klávesa Enter nebo pomocí tlačítek se šipkami mezi oběma seznamy v dialogu průvodce.
18
Z menu Zobrazit, či přepínačem
v panelu nástrojů.
16
V našem případě byly průvodcem vybrány tabulky Zákazníci, Objednávky, Rozpis objednávek, Výrobky a Kategorie, spojeny byly tak jak to naznačují čáry mezi nimi v horní části okna návrhu dotazu. Vybrána byla pole, která jsme vybrali v průvodci – ta jsou v dolní části návrhu dotazu, v tzv. návrhové mřížce dotazu. Co to průvodce udělal a proč? Pomůže nám pohled na schéma databáze. Označme si pole, která jsme v průvodci vybrali: DODAVATELÉ ČísloDodavatele Firma KontaktníOsoba Funkce Adresa Město Region PSČ Země Telefon Fax DomovskáStránka
KATEGORIE ČísloKategorie NázevKategorie Popis Obrázek
1
1
ROZPIS_OBJEDNÁVEK ∞ ČísloObjednávky ∞ ČísloVýrobku JedotkováCena Množství Sleva
VÝROBKY ČísloVýrobku NázevVýrobku ∞ ČísloDodavatele ∞ ČísloKategorie MnožstvíVJednotce JedotkováCena JednotkyNaSkladě ObjednánoJednotek MinimálníÚroveň NákupUkončen
1 ZAMĚSTNANCI ČísloZaměstnance Příjmení Jméno Funkce FormálníOslovení DatumNarození ZaměstnánOd Adresa Město Region PSČ Země TelefonDomů Linka Foto Poznámky Nadřízený
ZÁKAZNÍCI
1 KódZákazníka
Firma KontaktníOsoba Funkce Adresa Město Region OBJEDNÁVKY 1 ČísloObjednávky PSČ ∞ Země KódZákazníka ∞ Telefon ČísloZaměstnance Fax DatumObjednávky 1 DodatDne DatumOdeslání ∞ Přeprava Dopravné JménoPříjemce AdresaPříjemce MěstoPříjemce RegionPříjemce 1 PŘEPRAVCI ČísloPřepravce PSČPříjemce Firma ZeměPříjemce Telefon
Tato vybraná pole jsou z různých tabulek, bez uvažování souvislostí mezi tabulkami databáze by nebylo jasné, jak hodnoty Firma, NázevKategorie a NázevVýrobku vzájemně kombinovat. Vhodné kombinace jsme již dříve určili relacemi mezi tabulkami, v okně „Relace“. Tam jsou zadány souvislosti mezi některými tabulkami. Souvislosti mezi ostatními tabulkami mohou být odvozeny jako „cesty propojení z relací“. Takže například souvislost mezi Výrobky a Zákazníci může být odvozena ze souvislostí mezi Výrobky a Rozpis objednávek, dále mezi Rozpis objednávek a Objednávky a nakonec mezi Objednávky a Zákazníci. Průvodce vlastně hledal cestu mezi tabulkami Výrobky a Zákazníci tvořenou relacemi, jež jsou v databázi zvoleny. Tento postup, tedy hledání cesty spojující pole, jež pro své účely potřebujeme, můžeme dělat i sami, prohlížejíce si schéma databáze. V našem případě bude uděláno toto: Pro danou zákaznickou firmu budou nalezeny všechny objednávky, jež tento zákazník podal, a to podle relace dané kódem zákazníka. Dále každé objednávce podané tímto zákazníkem budou nalezeny všechny řádky z tabulky Rozpis objednávek podle relace dané číslem objednávky. Ke každému z těchto řádků bude nalezen výrobek z tabulky Výrobky podle relace dané číslem výrobku. U tohoto výrobku bude přečten název výrobku – výsledkem je dvojice Firma, NázevVýrobku. Význam této kombinace je, že tato zákaznická firma někdy objednala výrobek tohoto názvu, existuje taková objednávka. Pokud zákaznická firma takové objednání udělala vícekrát, bude kombinace těchto dvou hodnot, Firma a NázevVýrobku, ve výsledku vícekrát – tolikrát, kolik bylo řádků v Rozpisu objednávek s daným číslem výrobku a číslem nějaké objednávky, kterou podal tento zákazník. 17
Pohleďte znovu na odpověď na dotaz tím, že přepnete na zobrazení datového listu 19. Přesvědčte se, že trojice „Save-a-lot Markets, Nápoje, Chang“ je na řádcích 46, 49, 58, 59, 76. To znamená, že zákazník Save-a-lot Markets objednával výrobek Chang celkem 5krát (v 5ti objednávkách).
4.1.1 Řazení v návrhu dotazu V odpovědi se nám špatně vyhledává, je nepřehledná. To napravíme požadavkem na řazení v návrhu dotazu. Upravte mřížku dotazu: , a podívejte se na odpověď v datovém listu. Řádky jsou řazeny podle zákazníků (abecedně podle firem). Ještě je vhodné upravit pořadí výsledků u jednotlivých zákazníků:
Nyní je prvním klíčem uspořádání odpovědi jméno firmy zákazníka, druhým klíčem je název kategorie, a třetím název výrobku. Do našeho dotazu můžeme přidat požadavek na zobrazení datumu objednávky. Asi by v tuto chvíli bylo hloupé začínat celý dotaz znovu od začátku. Místo toho si zde další pole přidáme: Horní část návrhového zobrazení dotazu, ve které jsou tabulky, může sloužit i jako nabídka polí, jež chceme přidat do mřížky dotazu. V okénku zobrazujícím seznam polí tabulky Objednávky vyberte pole DatumObjednávky 20.
(Také bylo možno zvolit toto pole přímo ve volném sloupci v mřížce dotazu.) Pak požádejte o zobrazení datového listu odpovědi na dotaz. Je přidána informace o datu objednávek. Nyní nás nemusí uspokojovat řazení, které jme předtím zadali, a budeme chtít u jednotlivých zákazníků řadit chronologicky. V návrhu dotazu změníme řazení (zrušíme řazení u NázevKategorie a NázevVýrobku):
Pohled na odpověď je nyní vzhledem k požadavku na chronologické řazení lepší, ale možná by bylo lépe, kdyby se DatumObjednávky přesunulo doleva hned za Firma. V návrhovém zobrazení vybereme celý sloupec v mřížce tak, že klepneme nad sloupec a tažením myší za horní lištu sloupce ho přesuneme.
Opět si prohlédněte odpověď.
19
Z menu Zobrazit, či přepínačem
20
Poklepání nebo Enter.
v panelu nástrojů.
18
4.1.2 Co je dotaz Uložte svůj dotaz 21. Co se vlastně uložilo? Uložil se návrh dotazu, nikoli odpověď. Jestliže příště otevřete svoji databázi MS Access, naleznete v ní svůj dotaz, a spustíte ho, tj. požádáte o zobrazení datového listu odpovědi, může být odpověď jiná, pokud se data ve zdrojové databázi mezitím změnila. Podstatnou částí návrhu dotazu je SQL-kód dotazu. Tento kód můžete vidět po otevření dotazu přepnutím na Zobrazení SQL. SQL-kód je to, co MS Access pošle databázovému serveru, pokud máte data propojená z nějakého databázového serveru a dotaz převedete na „předávací“. 22 Databázový server pošle programu MS Access tabulku odpovědi. Další částí uloženého návrhu dotazu mohou být formátové požadavky na zobrazení datového listu v MS Access. O tom je následující sekce.
4.1.3 Úpravy v datovém listu odpovědi Máte-li zobrazen datový list odpovědi, můžete použít menu Formát pro úpravy vzhledu datového listu. Lze též seřadit datový list odpovědi pomocí tlačítek v panelu nástrojů, nebo hledat pomocí tlačítka (totéž jako menu Úpravy-Najít). V tom případě se řadí podle vyznačených sloupců nebo hledá ve vyznačených sloupcích. Zrušení řazení v datovém listu uděláte z menu Záznamy-Zrušit filtr či řazení dat. Dále můžete v datovém listu použít filtr. Tento filtr pracuje nad hotovou odpovědí, neposílá se tedy databázovému serveru. Důvodem k jeho použití může být to, že nechcete znovu a znovu posílat dotazy na databázový server, což může být nákladné, ale chcete různě prozkoumávat odpověď, kterou jste již dostali. V takovém případě by byl postup tento: v menu Záznamy vyberete Rozšířený filtr, a dále postupujete jako při kritériích v návrhu dotazu – tato budou vyložena v následující sekci. Pokud chcete vytvořený filtr použít, zmáčknete tlačítko . Tento filtr lze pak vypínat a zapínat tímto tlačítkem, či změnit na jiný filtr pomocí opětného vyvolání Záznamy-Rozšířený filtr. Pohodlnější cestou v takovém případě ale bude zkopírovat si celý datový list (menu Úpravy-Vybrat všechny záznamy, pak kopie) a vložit ho do programu MS Excel, kde jsou lepší možnosti práce. Jinou možností je uložit tabulku odpovědi do databáze (projektu) MS Access tím, že při návrhovém zobrazení dotazu změníte v menu Dotaz volbu na Vytvářecí dotaz, a novou tabulku pojmenujete, následně dotaz spustíte, čímž se nová tabulka vytvoří. Při práci s touto tabulkou je pak třeba pamatovat na to, že se jedná o kopii zdrojových dat, která již nemusí být aktuální.
4.1.4 Kritéria v návrhu dotazu Dotaz může požadovat, aby do odpovědi byly vybrány pouze některé řádky. Tento výběr určíme zadáním kritérií. Můžeme například požadovat výběr pouze těch řádků, kdy datum objednávky bylo z roku 1997. V řádku Kritéria ve sloupci DatumObjednávky v mřížce návrhu dotazu napíšeme
, znaky # nemusíte na klávesnici hledat, program MS Access je za vás doplní – je tedy možno napsat pouze Between 1.1.1997 and 31.12.1997. Můžeme přidat další kritérium: chceme pouze objednávky firmy Bon app’ v roce 1997 21
Z menu Soubor-Uložit nebo tlačítkem
v panelu nástrojů.
22
Databázový server může podporovat jinou verzi SQL a může mít svůj dialekt. V takovém případě je vždy nutné se seznámit s dotazovacím jazykem, jejž server používá. Kód vygenerovaný z návrhového zobrazení dotazu v MS Access je třeba upravit do podoby vhodné k předání databázovému serveru. Pokud máte propojená data z nějaké klientské databáze, provádí příkaz SQL nad cizími daty MS Access sám.
19
uvozovky obalující text Bon app’ opět nemusíte psát, MS Access je za vás doplní. Další příklady je možno nalézt v databázi Řešení, ve skupině Jednoduché. Následující sekce podává systematický výklad možností kritérií v dotazech.
4.2 Kritéria v dotazu Datový typ pole
Možnosti kritérií
Poznámka
text , memo
"konkrétní text"
V textu nesmí být znak ", lze alternativně použít jednoduché uvozovky: 'text'
Like "text se zástupnými symboly"
Zástupné symboly jsou 23: * … jakýkoli úsek textu ? … jeden znak
>, >=, <, <= "konkrétní text"
číslo
Between "konkrétní text" and "konkrétní text"
Řadí se podle abecedy. Nemá přílišný praktický význam.
číslo
má spíše význam pouze u diskrétní škály možných čísel
>, >=, <, <= číslo
čísla píšeme s národním oddělovačem desetinných míst 24, bez dalších formátových znaků
Between číslo1 and číslo2 datum
datum >, >=, <, <= #datum#
používáme formát datumů národní lokalizace 25
Between #datum1# and #datum2# logická hodnota
ano, ne 26
všechny typy
In (hodnota1;hodnota2;…)
Hodnoty musí být zapsány ve formátu odpovídajícího datového typu.
Poznámky pod čarou týkající se SQL databází budou přicházet v úvahu, pokud budete pomocí MS Access připojeni k nějaké SQL databázi jiné než MS SQL Server.
Kritéria lze kombinovat. Je-li více kritérií v jednom řádku, mají být splněna všechna najednou. Máme-li na mysli alternativní kritéria, musíme použít více řádků: každý řádek je jedna alternativa – jako příklad viz dotaz „Snadno dostupné výrobky“ ve skupině Jednoduché. 23
Ve standardu SQL jsou to znaky % a _ .
24
V SQL-databázích to bývá desetinná tečka.
25
V SQL-databázích bývá obvyklý formát rok/měsíc/den .
26
V SQL-databázích to může být y,n nebo 0,1 nebo f, t (jako false, true).
20
V kritériích lze též vzájemně porovnávat různá pole ze stejné databáze, příklady viz dotazy „Dopravné víc než 5% mezisoučtu“ a „Výrobky k doobjednání“ ve skupině Jednoduché, v nichž se zároveň používají i výrazy. Složitějším příkladem porovnání dvou polí vzájemně je dotaz „Zlevnění výrobku“, to je poměrně velmi náročný dotaz.
4.3 Jedinečné hodnoty Pokud chceme zamezit opakování řádků v odpovědi, jako tomu bylo v našem prvním dotaze, než jsme do něj přidali pole DatumObjednávky, lze to udělat v návrhovém zobrazení dotazu v dialogu Vlastnosti dotazu. Tento dialog zavoláme stištěním tlačítka -Vlastnosti a následným klepnutím myší mimo návrhovou mřížku i mimo seznamy polí tabulek v horní části. V dialogu „Vlastnosti dotazu“ v řádku Jedinečné hodnoty zvolíme Ano 27:
Příklady viz ve skupině Jednoduché, jež mají poznámku „jedinečné hodnoty!“.
4.4 Výrazy v dotazu V dotazech lze použít i výrazy, buď jako nové pole v mřížce návrhu dotazu, nebo v kritériích. Příklady viz dotazy „Den v týdnu objednávky 10305“, „Hodnota skladu“, „Lhůty dodání“, „Jména zaměstnanců“ ve skupině Jednoduché. Nové („vypočítané“) pole v návrhu dotazu má datový typ a formát, implicitně určený programem MS Access. Můžeme chtít formát, eventuelně datový typ, změnit. Jako příklad viz v dotazu „Dopravné víc než 5% mezisoučtu“ pole „Dopravné/Mezisoučet“. To má formát Procenta. Datový typ lze změnit použitím některé z funkcí konverze datových typů. Jako příklad viz dotaz „Rozšířené podrobnosti objednávek“, výraz pro pole „VýslednáCena“. Zde je použita funkce Ccur, konverze na typ měna. Důvodem k tomuto jejímu použití v tomto dotazu je to, že pole „Sleva“ v tabulce „Rozpis objednávek“ je typu „jednoduchá přesnost“, což je datový typ, jenž povoluje nepřesnosti při výpočtech. Pak celý výraz [Rozpis objednávek].JednotkováCena*[Množství]*(1-[Sleva]) je typu „jednoduchá přesnost“, a výsledek je nepřesný. Ve výrazu pro „VýseldnáCena“ je dále ještě dělení 100 a následné násobení 100 proto, že typ měna má v MS Access 4 desetinná místa za desetinnou tečkou, a tímto získáme 2 desetinná místa. Vzorová databáze Northwind se tím, že pole „VýslednáCena“ není přímo obsaženo v tabulce „Rozpis objednávek“ (tedy při zaznamenávání objednávky není tato hodnota vypočítána a následně hned uložena), stává uživatelsky nepříjemnou 28. Podobná poznámka platí i pro „Mezisoučet“, jenž by mohl být přímo součástí záznamů tabulky „Objednávky“. V mnoha obchodních databázích jsou tyto vypočítané hodnoty ukládány ve „vypočítaných polích“ tabulek.
27
Tím přidáme klíčové slovo DISTINCT za SELECT v SQL-kódu.
28
A provozně neefektivní, neboť příslušné výpočty musí být stále znovu prováděny.
21
4.4.1 Formát a zobrazení pole v dotazu Pole v návrhu dotazu, jež je polem nějaké tabulky nebo jiného dotazu vstupujícího jako tabulka do stávajícího dotazu, přebírá všechny vlastnosti, tedy i formát. Pokud něco z toho chceme změnit, uděláme to v dialogu vlastností pole, jejž lze pro sloupec, ve kterém je kurzor, vyvolat kombinací kláves Alt+Enter nebo stisknutím tlačítka -Vlastnosti v panelu nástrojů. Pole může být i přejmenováno, a to vepsáním nového jména a dvojtečky dopředu v prvním řádku v daném sloupci v mřížce návrhu dotazu. Příklady viz pole „ZeměPůvodu“ v dotazu „Snadno dostupné výrobky“ nebo pole „HodnotaSkladu“ v dotazu „Hodnota skladu“, a jiné. V těchto příkladech mají zmíněná pole ještě zvolený titulek. Titulek pole můžeme zadat ve vlastnostech pole. Titulek polí může být, jako ostatní vlastnosti, převzat z původní tabulky nebo dotazu vstupujícího jako tabulka do stávajícího dotazu. Toto lze ve stávající dotazu změnit.
4.4.2 Tvůrce výrazů Pro tvorbu výrazů lze použít tvůrce výrazů, jejž lze zavolat tlačítkem -Sestavit v panelu nástrojů pro návrh dotazu 29. Okno Tvůrce výrazů má v horní části políčko pro sestavovaný výraz, a pod ním je nabídka toho, co lze při sestavování použít.
toto je políčko pro sestavovaný výraz
zde se objevuje seznam daný výběrem vlevo
a zde eventuelně další, podle výběru uprostřed
V levém seznamu dole je základní členění. Pokud například do výrazu potřebuji pole nějaké tabulky, rozvinu nejprve nabídku tabulek pomocí před „Tabulky“, pak naleznu tuto tabulku a vyberu ji v seznamu. V prostředním seznamu dole se objeví seznam polí této tabulky. Poklepáním či vybráním a pak klávesou Enter umístíme vybrané pole na pozici kurzoru do sestavovaného výrazu v políčku nahoře. S poli jiných dotazů je to analogické 30. Dále si povšimněte nabídky Funkce – Vestavěné funkce, kde nalezneme tematicky rozdělené funkce, jež lze ve výrazech MS Access použít. Pokud bychom MS Access použili pro připojení k databázovému serveru a dotaz tomuto serveru předávali, pak lze použít jen ty funkce, jež onen databázový server umí.
29
Tvůrce výrazů lze zavolat i v jiných situacích, nejenom při navrhování dotazu, například při tvorbě výrazu v tiskové sestavě. Pak může být spouštěn jiným způsobem. 30
V jiných situacích nás mohou zajímat i pole jiných typů objektů.
22
Pokud potřebujeme nějakou funkci, nejlépe je do sestavovaného výrazu nejprve vybrat tuto funkci, a pak v jejích argumentech nahrazovat to, co tam chceme dát. Poslední v seznamu jsou Konstanty, Operátory, Běžné výrazy – prohlédněte. Nejběžnější značky jsou na tlačítkách pod políčkem pro sestavovaný výraz. Někdy se stane, že tvůrce výrazů do sestavovaného výrazu vloží text «Výraz», jež se nám nehodí. Je třeba sledovat, co se v políčku pro sestavovaný výraz objevuje, a eventuelně nežádoucí části smazat. Užitečná je první volba v seznamu vlevo, jež nabízí pole dotazu 31, s nímž právě pracujete. Zde se ovšem nabízejí pouze pole z již uložené verze dotazu. Pro pohodlnou práci je tedy možné nejprve do návrhové mřížky dotazu vložit všechna pole, jež budou do výrazu potřeba, pak dotaz uložit, a následně zavolaný tvůrce výrazů nám v první nabídce poskytne všechna pole potřebná k sestavení výrazu. Jen v případě shody jmen polí v různých tabulkách z dotazu je třeba specifikovat, které z nich myslíme. Tehdy je třeba vybrat tabulku, a pak její pole. Do výrazu se pak dostane úplná specifikace obsahující i jméno tabulky. Pozor – do výrazů v dotazu nelze použít jména pojmenovaných nebo přejmenovaných polí tohoto dotazu, to není legální a až na výjimky takový dotaz nefunguje. V takovém případě nezbývá než namísto odpovídajícího jména jiného sloupce použít výraz, který ten jiný sloupec vytvořil. Pokud bychom například v dotazu „Přehled objednávek“ ve skupině „Jednoduché“ chtěli přidat kritérium porovnávající zemi zákazníka se zemí zaměstnance, pak bychom museli ve sloupci Zákazníci_Země vytvořit kritérium
, a nikoli
– toto by bylo špatně.
4.4.3 Lupa Výraz lze psát vždy také ručně, bez pomoci tvůrce. Pokud je náš výraz delší, a chceme ho psát, nebo pokud si chceme delší výraz prohlédnout, vyvolejte lokální menu na tomto výrazu, a vyberte „Lupa“.
4.5 Jak používat průvodce jednoduchým dotazem V této kapitole jsme začali vytvořením prvního dotazu pomocí průvodce jednoduchým dotazem. Nabízí se otázka, jaká pole v průvodci vybírat, když některá pole chceme mít zobrazována ve výsledku, jiná slouží pouze k formulování kritérií, a podle některých chceme řadit. Navíc chceme mít v dotazu nová pole vytvořená jako výrazy. V prvním kroku průvodce vybereme pole: •
jež chceme zobrazit do výsledku,
•
jež budeme potřebovat do výrazů (může následně nastat problém, když průvodce takové pole pojmenuje nebo přejmenuje – pak je použití tvůrce výrazů pracnější),
•
podle kterých chceme řadit nebo pro ně zadat kritéria.
Někdy, když v databázi, se kterou pracujeme, je v relacích možno nalézt alternativní cesty propojení, by mohl průvodce vybrat takové propojení, které nechceme. Potom v průvodci vyberme ještě nějaká pole (například 31
V jiném kontextu to jsou pole aktuálního objektu.
23
klíče) tabulek, přes které chceme vybrat cestu propojení. Například následující schéma umožňuje propojit název žánru s příjmením a jménem umělce buď prostřednictvím relace Titul.režie=Umělec.ID , nebo s použitím tabulky „Hraje“ prostřednictvím relací Titul.ID=Hraje.titul a Hraje.herec=Umělec.ID. Jde o to, zda nás umělec zajímá v roli režiséra či herce. Pokud nás zajímá umělec v roli herce v nějakém titulu daného žánru, pak v průvodci vybereme kromě polí Žánr.název, Umělec.Jméno, Umělec.příjmení ještě např. pole Hraje.titul (nebo Hraje.herec, to vyjde nastejno).
Otázkou je, zda vůbec někdy budeme potřebovat sestavovat dotaz jinak, než pomocí průvodce.
4.6 Dotaz v návrhovém zobrazení Ukažme si dotaz, pro který nemůžeme použít průvodce jednoduchým dotazem. Je to příklad „Města se zaměstnanci i zákazníky“ ve skupině „Jiné propojení“. Jeho smyslem je nalézt případy, kdy nějaká zákaznická firma má sídlo ve městě, ve kterém bydlí některý zaměstnanec. Strategie dotazu je taková, že hledáme dvojice Zákazník, Zaměstnanec, kdy Zákazník.Město je rovno Zaměstnanec.Město . Nemůžeme použít průvodce a v něm vybrat Zákazník.Město a Zaměstnanec.Město , neboť průvodce by do dotazu kromě tabulek Zaměstnanci a Zákazníci vybral i tabulku Objednávky a vytvořil propojení podle relací, tj. ve výsledku bychom měli pouze ty dvojice zákazníků se zaměstnanci, mezi nimiž proběhlo nějaké objednání. Porovnejte případný výsledek takovéhoto pokusu s výsledkem dotazu „Města se zaměstnanci i zákazníky“. V databázi řešení jsou dvě možnosti řešení této úlohy, jedna s kritériem (dotaz „Města se zaměstnanci i zákazníky 2“), druhá s propojením tabulek Zákazníci a Zaměstnanci pomocí polí Město (dotaz „Města se zaměstnanci i zákazníky“). Vytvoříme dotaz stejný, jako je „Města se zaměstnanci i zákazníky“. V okně Databáze, v Dotazy, zvolte „Vytvořit dotaz v návrhovém zobrazení“. Otevře se dialog „Zobrazit tabulku“ → , v němž vybíráme tabulky, jež potřebujeme do dotazu dát. V našem případě vybereme tabulky Zákazníci a Zaměstnanci. Do návrhu dotazu se tyto tabulky vloží nepropojené:
24
Propojení vytvoříme přetažením myší pole Město z tabulky Zákazníci na pole Město z tabulky Zaměstnanci, vytvoří se čára podobná relacím: tažení myši
Dokončíme náš příklad výběrem pole Město do návrhové mřížky. Zamezit opakování v odpovědi lze nastavením jedinečných hodnot (viz str.21).
Jako procvičení zkuste v návrhovém zobrazení vytvořit náš první dotaz, který jsme původně dělali pomocí průvodce. (Musíte vybrat tabulky Kategorie, Výrobky, Rozpis objednávek, Objednávky a Zákazníci…)
4.7 Přidávání tabulek do návrhu dotazu Často se setkáme se situací, že jsme například pomocí průvodce vytvořili dotaz, a posléze zjistíme, že bychom do něj rádi přidali pole, jež v tabulkách v dotazu není. Pak musíme do dotazu takovou tabulku přidat, a možná ještě předtím zvážit, zda budeme muset přidat ještě nějaké další tabulky kvůli propojení (podle schématu databáze). Přidání tabulky do dotazu děláme po stisknutí tlačítka -Přidat tabulku v dialogu „Zobrazit tabulku“, vybereme tabulku či tabulky, které potřebujeme. Tento dialog nabízí na kartě „Dotazy“ i možnost připojit do stávajícího dotazu nějaký dříve uložený dotaz (jako mezivýsledek). Připojení tabulky či jiného dotazu do stávajícího dotazu automaticky způsobí i přidání odpovídajících propojení ze schématu relací. – Pomocné dotazy „Rozšířené podrobnosti objednávek“ a „Mezisoučty objednávek“ jsme v relacích propojili, protože jsou používány v mnoha dotazech i jinde. Pokud přidávaný dotaz či tabulka nejsou propojeny v relacích , pak budeme muset propojení udělat ručně. – Příkladem může být dotaz „Tržby za výrobky porovnání 1997 a 1998 Q1“, který je ve skupině „Seskupovací“. Tento dotaz využívá mezivýsledků „Tržby za výrobky 1998 Q1“ a „Tržby za výrobky 1997“ (oba patří k seskupovacím dotazům, o nich následující kapitola). – Jiný příklad je „Zákazníci nakupující 10 nejdražších výrobků-lépe“, jenž využívá dotaz „Deset nejdražších výrobků“, což je dotaz importovaný ze vzorové databáze Northwind. (Vysvětlení k tomuto poslednímu příkladu je podáno v kapitole 8 Mezivýsledky.) 25
4.8 Nejvyšší hodnoty Pokud máme v návrhu dotazu zadáno nějaké řazení, a zajímá nás pouze několik prvních řádků z odpovědi, lze pomocí tlačítka Nejvyšší hodnoty v panelu nástrojů zvolit, kolik prvních řádků chceme mít v odpovědi. Počet řádků lze zadat přímo číslem, či procentuelně vůči celkovému počtu všech řádků. Stejné nastavení lze udělat i v dialogu Vlastnosti dotazu, v poli „Nejvyšší hodnoty“. Příkladem je dotaz „Největší nákup výrobku“, který vrací informace o nejvyšším nákupu zvoleného výrobku, a dotaz „Zákazník odkdy“, který vrací nejstarší datum objednávky zvoleného zákazníka. Pomocí seskupovacího dotazu lze tyto informace získat pro všechny zákazníky najednou, viz dotaz „Zákazníci odkdy“ ze skupiny „Seskupovací“. Pro případ odpovídající volbě „Nejvyšší hodnoty = 1“ lze analogického výsledku dosáhnout vždy, byť někdy obtížněji – viz příklad „Statistika počtu zákazníků pro výrobky“, výklad najdete v kapitole 5 Seskupovací dotazy.
4.9 Dotazy s parametry V dotazu mohou být hodnoty v kritériích či ve výrazech zadávány také jako parametry. Po spuštění takového dotazu se pak zobrazí dialogové okno, jež se potupně zeptá na hodnoty všech parametrů dotazu. Stejný dotaz tak dává různé odpovědi podle toho, jak zadáváme různé hodnoty pro parametry. Parametr do dotazu zapisujeme jako text výzvy uzavřený do hranatých závorek, například chceme-li dotaz ze str. 19 modifikovat tak, aby zobrazoval objednávky zvoleného zákazníka, změníme ho:
Pokud chceme zadávat i rok objednávek jako parametr, upravíme ještě
Další příklady jsou v databázi Řešení, ve skupině „Jednoduché“, dotazy „Výrobky prodávané ve zvolené zemi“, „Největší nákup výrobku“, „Zákazník odkdy“. V některých případech (například když by hodnota měla být typu Ano/Ne 32) je potřeba určit datové typy parametrů, pak to uděláme v návrhovém zobrazení dotazu z menu Dotaz-Parametry. V našem druhém příkladu by to bylo jako na obrázku zde (pořadí určuje pořadí, v jakém jsou hodnoty vyžadovány).
32
Nebo když je dotaz křížový nebo když je následně zpracováván křížovým dotazem, nebo je podkladem pro graf, nebo když je dotazem do externí SQL databáze…
26
5 Seskupovací dotazy 5.1 Co jsou seskupovací dotazy? V seskupovacích dotazech se většinou počítají souhrny. Například jednoduchý přehled objednávek z období mezi 1.5.1998 a 7.5.1998 lze rozdělit do skupin podle jednotlivých zaměstnanců: ČísloZam Zaměstnanec ČísloObj DatumObj Mezisoučet 1 Davolio, Nancy 11077 06.05.1998 31 393,02 Kč 1 Davolio, Nancy 11071 05.05.1998 12 112,50 Kč 1 Davolio, Nancy 11069 04.05.1998 9 000,00 Kč 1 Davolio, Nancy 11067 04.05.1998 2 171,25 Kč 1 Davolio, Nancy 11064 01.05.1998 108 260,00 Kč 2 Fuller, Andrew 11073 05.05.1998 7 500,00 Kč 2 Fuller, Andrew 11070 05.05.1998 40 749,37 Kč 4 Peacock, Margaret 11076 06.05.1998 19 818,75 Kč 4 Peacock, Margaret 11072 05.05.1998 130 450,00 Kč 7 King, Robert 11074 06.05.1998 5 802,12 Kč 7 King, Robert 11066 01.05.1998 23 218,75 Kč 8 Callahan, Laura 11075 06.05.1998 12 452,50 Kč 8 Callahan, Laura 11068 04.05.1998 50 677,00 Kč 8 Callahan, Laura 11065 01.05.1998 4 735,50 Kč
Davolio, Nancy
Fuller, Andrew Peacock, Margaret King, Robert Callahan, Laura
V dotaze, který předchozí přehled „seskupí“ podle zaměstnanců, lze pro každého zaměstnance zopakovat pole ČísloZam a Zaměstnanec, a spočítat součet hodnot pole Mezisoučet ze skupiny tohoto zaměstnance: ČísloZam Zaměstnanec SumOfMezisoučet 1 Davolio, Nancy 162 936,77 Kč 2 Fuller, Andrew 48 249,37 Kč 4 Peacock, Margaret 150 268,75 Kč 7 King, Robert 29 020,87 Kč 8 Callahan, Laura 67 865,00 Kč
Specifické detaily o jednotlivých objednávkách, jako jsou hodnoty v poli ČísloObjednávky a v poli DatumObjednávky, ve výsledném souhrnu nemohou být. Můžeme ovšem spočítat nejnižší a nejvyšší datum objednávky u každého zaměstnance: ČísloZam Zaměstnanec SumOfMezisoučet MinOfDatumObj MaxOfDatumObj 1 Davolio, Nancy 162 936,77 Kč 1.5.1998 6.5.1998 2 Fuller, Andrew 48 249,37 Kč 5.5.1998 5.5.1998 4 Peacock, Margaret 150 268,75 Kč 5.5.1998 6.5.1998 7 King, Robert 29 020,87 Kč 1.5.1998 6.5.1998 8 Callahan, Laura 67 865,00 Kč 1.5.1998 6.5.1998
a třeba ještě počet objednávek: ČísloZam Zaměstnanec SumOfMezisoučet MinOfDatumObj MaxOfDatumObj Count 1 Davolio, Nancy 162 936,77 Kč 1.5.1998 6.5.1998 5 2 Fuller, Andrew 48 249,37 Kč 5.5.1998 5.5.1998 2 4 Peacock, Margaret 150 268,75 Kč 5.5.1998 6.5.1998 2 7 King, Robert 29 020,87 Kč 1.5.1998 6.5.1998 2 8 Callahan, Laura 67 865,00 Kč 1.5.1998 6.5.1998 3
27
Původní přehled můžeme rozdělit do skupin také jiným způsobem, podle jednotlivých kalendářních dní: ČísloZam Zaměstnanec DatumObj Mezisoučet 7 King, Robert 01.05.1998 23 218,75 Kč 8 Callahan, Laura 01.05.1998 4 735,50 Kč 1 Davolio, Nancy 01.05.1998 108 260,00 Kč 1 Davolio, Nancy 04.05.1998 9 000,00 Kč 8 Callahan, Laura 04.05.1998 50 677,00 Kč 1 Davolio, Nancy 04.05.1998 2 171,25 Kč 2 Fuller, Andrew 05.05.1998 7 500,00 Kč 4 Peacock, Margaret 05.05.1998 130 450,00 Kč 1 Davolio, Nancy 05.05.1998 12 112,50 Kč 2 Fuller, Andrew 05.05.1998 40 749,37 Kč 1 Davolio, Nancy 06.05.1998 31 393,02 Kč 4 Peacock, Margaret 06.05.1998 19 818,75 Kč 8 Callahan, Laura 06.05.1998 12 452,50 Kč 7 King, Robert 06.05.1998 5 802,12 Kč
1.5.1998 4.5.1998
5.5.1998
6.5.1998
Pak lze seskupit a vypočítat souhrny například: DatumObj SumOfMezisoučet Count 01.05.1998 136 214,25 Kč 3 04.05.1998 61 848,25 Kč 3 05.05.1998 190 811,87 Kč 4 06.05.1998 69 466,39 Kč 4
– tedy jsou tržby a počty objednávek za jednotlivé dny. V databázi Řešení ve skupině „Seskupovací“ je dotaz „Tržby zaměstnanců“. Ten počítá souhrny ze všech objednávek, bez omezení datumu (přehled jednotlivých objednávek by v tomto případě ve skriptech zabral příliš mnoho místa) – v tomto dotaze jsou navíc zobrazeny pole FormálníOslovení a Funkce, ty lze pro každého zaměstnance přečíst z tabulky Zaměstnanci, a k souhrnům je přidat jako další informaci. Jak se vytvářejí seskupovací dotazy? Ačkoli je lze sestavit pomocí průvodce, první seskupovací dotaz vytvoříme tak, abychom pochopili jak seskupovací dotazy fungují.
5.2 První seskupovací dotaz Vytvořte si jednoduchý dotaz, který bude základem pro následující počítání souhrnů. Tento jednoduchý dotaz bude přehledem všech objednávek zobrazující pole DatumObjednávky, dále pole Mezisoučet z pomocného dotazu Mezisoučty objednávek, a pole ČísloZaměstnance, Příjmení, Jméno z tabulky Zaměstnanci.
28
V následujícím dále přeměníme tento jednoduchý dotaz na seskupovací dotaz, jenž bude pro každého zaměstnance počítat jeho celkové tržby za 1. čtvrtletí roku 1998: Do mřížky návrhu dotazu přidáme řádek „Souhrn:“ 33. V řádku „Souhrn:“ nastavíme „Sum“ v poli Mezisoučet, „Kde“ v poli DatumObjednávky, v tomto poli ještě napíšeme kritérium, v ostatních polích v řádku „Souhrn:“ ponecháme volbu „Seskupit“:
(Všimněte si, že po vybrání „Kde“ se automaticky odstranilo zaškrtnutí „Zobrazit“ v tomto sloupci.) 34 Výsledek je hotov, stačí jen pojmenovat pole s vypočtenou tržbou (o přejmenování pole viz str.22). Číslo zaměstnance Příjmení Jméno Tržba 1 Davolio Nancy 1 102 257,90 Kč 2 Fuller Andrew 1 035 407,50 Kč 3 Leverling Janet 1 590 134,32 Kč 4 Peacock Margaret 954 687,00 Kč 5 Buchanan Steven 490 547,37 Kč 6 Suyama Michael 222 430,12 Kč 7 King Robert 474 337,06 Kč 8 Callahan Laura 802 446,06 Kč 9 Dodsworth Anne 790 041,45 Kč
Změnou kritéria v poli DatumObjednávky změníme období, za které se má souhrn počítat, zkuste a porovnejte výsledky. Pokud bychom chtěli pro každého zaměstnance vypočítat tržby za nějaká různá období najednou, například za jednotlivé měsíce roku 1998, je potřeba nejprve vytvořit výraz pro tato období – pro daný příklad vytvoříme do dotazu pole Month(DatumObjednávky), v něm bude „Seskupit“ v řádku „Souhrny:“, a zadáme kritérium omezující dotaz na rok 1998, například takto 35:
Ve výsledku vidíme, že pro každého zaměstnance je vypočítáno více souhrnů – za každý měsíc jeden souhrn (měsíce, ve kterých zaměstnanec neměl žádnou objednávku, ve výsledku nejsou). Můžeme vypočítat tržby i za jednotlivé měsíce ostatních let: přidáme pole s výrazem Year(DatumObjednávky), ponecháme v něm „Seskupit“, a odstraníme předchozí kritérium:
33
Tlačítko
v panelu nástrojů, nebo menu Zobrazit-Souhrny, nebo z lokálního menu nad mřížkou návrhu.
34
Volba „Kde“ v řádku „Souhrn:“ slouží k označení pole, které použijeme pro kritérium omezující výchozí jednoduchý dotaz, přičemž jednotlivé detaily z tohoto pole nemohou být zobrazeny v souhrnném výsledku. 35
Jiná možnost byla vytvořit pole Year(DatumObjednávky) a zadat pro ně kritérium 1998.
29
Vidíme, že seskupování může proběhnout podle více hledisek, v našem případě podle zaměstnanců 36, roků a měsíců.
5.3 Agregační funkce V řádku „Souhrn:“ je nabídka různých agregací pro dané pole. Nejčastěji používané jsou součet (Sum), nejnižší hodnota (Min), nejvyšší hodnota (Max), průměr (Avg). (V úvodní podkapitole této kapitoly by se pro získání druhého z výsledků použily Min a Max v poli DatumObjednávky.) Specifická je agregace Count. K jejímu pochopení vyzkoušíme nejprve průvodce pro souhrnný dotaz.
5.4 Průvodce pro souhrnný dotaz Náš první seskupovací dotaz můžeme sestavit i pomocí průvodce. Je to stejný průvodce, jako pro jednoduché dotazy (v okně Databáze, objekty Dotazy, ikona Vytvořit dotaz pomocí průvodce). V prvním kroku průvodce vyberme z tabulky Zaměstnanci pole ČísloZaměstnace, Příjmení, Jméno, z tabulky Objednávky vybereme DatumObjednávky a z dotazu Mezisoučty objednávek vybereme Mezisoučet. V dalším kroku průvodce přepneme na „Souhrnný“, čímž se zpřístupní tlačítko „Možnosti souhrnu“. Toto tlačítko stiskneme. V následujícím dialogu „Možnosti souhrnu“ se nabízejí pole, která lze agregovat, a výběr agregací pro ně. Zaškrtneme „Součet“ u pole Mezisoučet.
Dále si všimněme možnosti zaškrtnout políčko vpravo dole, u kterého je poněkud méně srozumitelný popis. Toto políčko též zaškrtněme: Potvrdíme a tím se vrátíme do průvodce, zde stiskneme „Další“. Následující krok se v průvodci objevuje v závislosti na tom, zda jsme předtím vybrali nějaké pole typu datum. V našem případě tomu tak bylo, bylo to pole DatumObjednávky. Proto nám nyní průvodce nabízí možnosti seskupení podle nabízených časových intervalů – vyberme „Měsíc“. V posledním kroku již nový dotaz pouze pojmenujte, dokončete průvodce. Porovnejte výsledný dotaz s dotazem, který jsme vytvořili jako náš první seskupovací dotaz. Zásadní rozdíl je v tom, jak je zařízeno seskupování podle kalendářních měsíců – zde to zajišťují 4. a 7. pole v mřížce návrhu dotazu – průvodce zde vytvořil poměrně složité výrazy, jež slouží pro určení kalendářního měsíce z datumu. 4.sloupec, jenž má být zároveň ve výsledku zobrazen, má výhodu toho, že výsledky jsou česky, zato je pro uživatele poměrně složitý. 7. sloupec zde slouží k chronologickému řazení, když u něj zvolíte Řadit: vzestupně (zkuste a podívejte se na výsledek). V databázi Řešení ve skupině Seskupovací porovnáním dotazů Tržby zaměstnanců po čtvrtletích 1 a Tržby zaměstnanců po čtvrtletích 2 zjistíte, jak ještě jinak lze vytvořit výraz pro kalendářní období. Tato druhá podoba má tu výhodu, že chronologické řazení zde odpovídá vzestupnému řazení těchto výrazů. To lze například využít pro křížové dotazy (o nich viz dále).
36
Seskupení podle Příjmení a Jméno ve skutečnosti neproběhne, neboť tyto jsou pro dané ČísloZaměstnance jedinečně určeny. Toto MS Access „zná“ z návrhu tabulky Zaměstnanci, kde ČísloZaměstnance je klíčem.
30
Z hlediska efektivity provádění dotazu databázovým serverem však není ideální ani jedna z těchto dvou možností. Nejlepší je použít SQL funkce (např. Year, Month) jako je to popsáno v podkapitole „První seskupovací dotaz“. Pokud nutně potřebujeme jediné pole pro kalendářní období, můžeme z nich eventuelně v následujícím dotazu vytvořit jediný výraz z polí předchozího mezivýsledku.
Dále si povšimněme pole „Count Of Mezisoučty objednávek“, jež se zde vytvořilo proto , že jsme v průvodci zaškrtli podkapitola.
. V tomto poli je výraz Count(*). O něm viz následující
5.5 Count v seskupovacím dotazu Pokud použijeme průvodce pro souhrnný dotaz, pak v dialogu Možnosti souhrnu je vždy vpravo dole možno zaškrtnout „Počet záznamů v …“ (text záleží na konkrétním dotazu). Vždy se vytvoří výraz Count(*), průvodce ho nějak pojmenuje. Co tento výraz počítá? Count(*) počítá počet řádků v každé skupině výchozího jednoduchého dotazu, jenž je základem pro seskupení. Pro pochopení, co je tedy vlastně počítáno, je nutné vědět, co tvoří řádky jednoduchého dotazu, na kterém je daný seskupovací dotaz založen. V našem příkladu se můžete vrátit k našemu prvnímu seskupovacímu dotazu, odstranit v něm řádek „Souhrn:“ (a tím z něj udělat opět pouze jednoduchý dotaz) a podívat se na výsledek. Pro lepší názornost si můžete zobrazení datového listu uspořádat podle sloupce ČísloZaměstnance. Uvědomte si, že jednotlivé řádky nyní odpovídají jednotlivým objednávkám. Takže počet řádků ve skupině daného zaměstnance je počet jeho objednávek. Neukládejte v této podobě, zavřete. Nyní můžete do návrhu našeho prvního seskupovacího dotazu přidat pole početObjednávek: Count(*) , a v řádku „Souhrn:“ vybrat „Výraz“ 37:
Tento výraz dává stejný výsledek, jako volba funkce Count v řádku „Souhrn:“ v poli, jež je vždy zaručeně neprázdné. Takové pole může být ČísloObjednávky, ale třeba i ČísloZaměstnace. Záleží pouze na vašem vkusu, co je pro vás příhodnější.
V řádku „Souhrn:“ je však možno volit také agregační funkci „Count“ – co tato volba znamená? Agregační funkce Count v poli dotazu počítá počet neprázdných hodnot v tomto poli v příslušné skupině jednoduchého dotazu, na kterém je daný seskupovací dotaz založen. Ukažme si to na příkladu. Využijeme tuto funkci k tomu, abychom v každé zemi spočítali počet zákazníků, kteří mají fax. Vytvořte jednoduchý dotaz s poli Zákazníci-Země a Zákazníci-Fax, pak přidejte řádek „Souhrn:“, u pole Fax vyberte funkci „Count“ a pojmenujte ho PočetFaxů, vytvořte nové pole s výrazem PočetZákazníků:Count(*), nastavte zde „Výraz“. Podívejte se odpověď, porovnejte oba souhrny, prohlédněte si řádek se Švýcarskem.
37
Volba „Výraz“ je určena pro případ, že v řádku „Pole:“ je nějaký výraz s agregací – viz například dotaz „Podíl dopravného a ceny zboží 2“.
31
5.6 Doporučení pro použití průvodce Pro dotaz se souhrny doporučuji spíše postupovat tak, jak jsme to dělali v případě našeho prvního seskupovacího dotazu, a to nejprve vytvořit jednoduchý dotaz, a následně ho upravit v návrhovém zobrazení na dotaz se souhrny. Pro vytvoření základního jednoduchého dotazu můžeme použít průvodce jednoduchým dotazem. Zopakujme a doplňme návod z předchozí kapitoly: V prvním kroku průvodce vybereme pole, •
jež chceme zobrazit do výsledku,
•
jež budeme potřebovat do výrazů,
•
podle kterých chceme řadit nebo seskupit nebo pro ně zadat kritéria Do dotazu můžeme přidat pole, která nemusíme mít zobrazena ve výsledku, ale seskupování podle nich dotaz zefektivní. V našem příkladu to bylo pole ČísloZaměstnance, protože to je klíčem tabulky Zaměstnanci, a pro něj je v databázi vytvořen index. Seskupování pole indexovaného pole je podstatně efektivnější.
Ve druhém kroku se vyhněme vytváření souhrnů průvodcem, a pokročíme nakonec, kdy dotaz pouze pojmenujeme tak, jak se má jmenovat výsledný seskupovací dotaz. V návrhovém zobrazení dotazu následně vytvoříme všechny výrazy, které eventuelně potřebujeme. Pak přidáme řádek „Souhrn:“ 38 , v něm volíme •
agregační funkci (Sum, Min, Max, Avg …) v polích, v nichž se má vypočítat souhrn
•
„Kde“ v polích, jež slouží pro zadání kritéria „před seskupením“, tedy kritéria, jež říká, co se má do souhrnů započítat. V takovém poli nemůže být zaškrtnuto „Zobrazit:“.
•
„Výraz“ v případě, že jde o Count(*) nebo složitější výraz z agregačních funkcí
•
v ostatních polích necháme Seskupit
Dotazy generované v MS Access pomocí návrháře nejsou příliš efektivní, co se týče klauzule GROUP BY v SQL-kódu. Vygenerovaný SQL-kód má v GROUP BY vše, co má „Seskupit“ v řádku „Souhrn:“ v návrhovém zobrazení. Skutečné seskupování je však nutné pouze podle některých z nich, jako v našem příkladu pouze podle ČísloZaměstnace, Year(datumObjednávky), Month(DatumObjednávky). – Pokud chcete vygenerovat vhodnější SQL-kód, zvolte u ostatních polí „Výraz“ místo „Seskupit“ – pak ale takový dotaz nelze spustit jako dotaz MS Access. Jestliže vytváříte předávací SQL dotaz, dejte pozor – seskupování podle výrazů nemusí některé databázové servery umět!
5.7 Omezení počtu řádků výsledku seskupovacího dotazu Někdy chceme ve výsledku pouze ty řádky, v nichž vypočtený souhrn vyhovuje nějakému kritériu. Takové kritérium prostě napíšeme do příslušného pole. Například když chceme vybrat zákazníky, kteří v roce 1998 podali více než 3 objednávky, sestavíme dotaz podle obrázku na následující straně nahoře. Dalším příkladem je dotaz „Zákazníci podle nákupu v roce 1997-výběr1“ ve skupině Seskupovací. Můžeme také podle agregované hodnoty řadit, a omezit počet výstupních řádků volbou „Nejvyšší hodnoty“ (jak, viz str.26). Příkladem jsou dotazy „ Zákazníci podle nákupu v roce 1997-výběr2“ a „Zákazníci podle nákupu v roce 1997-výběr3“.
38
Tlačítko
v panelu nástrojů, nebo menu Zobrazit-Souhrny, nebo z lokálního menu nad mřížkou návrhu.
32
5.8 Křížové dotazy Pokud seskupování probíhá podle dvou hledisek, je možné uspořádat výsledek přehledněji, do tzv. křížové tabulky. Předveďme si to na druhé verzi našeho prvního seskupovacího dotazu, který pro každého zaměstnance vypočítává tržby za jednotlivé měsíce roku 1998 (viz str.29). Výsledek může být uspořádán i takto: ČísloZam Příjmení Jméno 1 2 3 4 5 1 Davolio Nancy 202 884,03 Kč 278 687,62 Kč 620 686,25 Kč 314 680,63 Kč 162 936,77 Kč 2 Fuller Andrew 108 777,75 Kč 578 188,75 Kč 348 441,00 Kč 774 757,00 Kč 48 249,37 Kč 3 Leverling Janet 642 625,19 Kč 538 506,00 Kč 409 003,13 Kč 323 933,88 Kč 4 Peacock Margaret 481 149,25 Kč 266 076,50 Kč 207 461,25 Kč 248 442,75 Kč 150 268,75 Kč 5 Buchanan Steven 296 070,00 Kč 134 426,50 Kč 60 050,87 Kč 5 250,00 Kč 6 Suyama
Michael
7 King Robert 8 Callahan Laura 9 Dodsworth Anne
46 870,75 Kč 48 835,00 Kč 126 724,37 Kč 131 173,75 Kč 161 750,00 Kč 157 928,31 Kč 154 658,75 Kč 714 764,06 Kč 29 020,87 Kč 281 592,81 Kč 2 650,00 Kč 518 203,25 Kč 344 427,50 Kč 67 865,00 Kč 133 832,97 Kč 480 083,50 Kč 176 124,98 Kč 237 537,50 Kč
Zde ČísloZaměstnace, Příjmení, Jméno tvoří hlavičku řádků, měsíce tvoří hlavičky sloupců, a souhrnná tržba je hodnotou, která je vyplňována do tabulky do příslušného řádku a sloupce. Toho docílíme, když nejprve přepneme typ dotazu na „Křížový dotaz“ 39 . Tím se do mřížky návrhu dotazu přidá řádek „Křížová tabulka:“. V něm zvolíme „hlavička řádku“ v polích ČísloZaměstnace, Příjmení a Jméno, „hlavička sloupce“ v poli měsíc, a „hodnota“ v poli Tržba:
Pro tvorbu křížových tabulek existuje v MS Access průvodce křížovým dotazem. Pro jeho použití je nejprve potřeba mít uložen dotaz, na základě kterého bude křížová tabulka vytvořena. Pro nás to může být buď, stejně jako předtím, druhá verze našeho prvního seskupovacího dotazu ze str. 29, nebo dokonce pouhý jednoduchý dotaz jako je následující:
39
Nabídka Typ dotazu
v panelu nástrojů nebo menu Dotaz.
33
Vytvořte tento dotaz a uložte ho (pojmenujte např. Pkt 40)! Nyní jsme připraveni spustit Průvodce křížovým dotazem. Všechny průvodce pro tvorbu dotazů naleznete v seznamu „Nový dotaz“ nabídnutému po stištění tlačítka
nahoře v okně Databáze pro objekty Dotazy:
První krok Průvodce křížovým dotazem se vás zeptá, kde je tabulka či dotaz, jež se má zpracovat do křížové podoby. Pro náš příklad přepněte na a v seznamu nahoře vyberte prve pro naše účely uložený dotaz (možná jste ho pojmenovali Pkt). Stiskněte tlačítko „Další“.
V následujícím kroku vyberete hlavičky řádků, v našem případě to budou
.
Přejdeme na další krok průvodce, v něm vybereme hlavičku sloupců – nyní to bude DatumObjednávky. Následující krok průvodce se objevuje v závislosti na tom, zda jsme prve vybrali nějaké pole typu datum, a to jsme opravdu vybrali – bylo to DatumObjednávky. Podobně jako v průvodci pro souhrnný dotaz se nám nabízí možnosti seskupení podle nabízených časových intervalů – vyberme „Měsíc“. Další krok průvodce se ptá, co se bude vyplňovat jako hodnota do křížové tabulky – vyberme Mezisoučet. Dále zde vybereme agregaci, která se má počítat – vybereme „Součet“. Ještě si všimněte zaškrtnutí vlevo „Ano, zahrnout součty řádků“ – to ponecháme. Dialog nyní vypadá jako obrázek nahoře na následující straně. V posledním kroku pouze dotaz pojmenujte. Porovnejte získaný výsledek s dotazem, jehož návrh jsme sami upravili do podoby křížové tabulky.
40
Jako zkratka za „Pro křížovou tabulku“ – ☺.
34
Nepodstatný rozdíl je výrazu pro měsíc. Další drobný rozdíl je v počtu sloupců – ten je zajištěn vyjmenováním hodnot do „Hlavičky sloupců“ ve vlastnostech dotazu 41. Podstatný rozdíl je ve sloupci, který zajišťuje souhrny pro celé řádky výsledné křížové tabulky. Tento sloupec má pojmenování (průvodce vytvořil pojmenování „Celkem Mezisoučet“), jako pole je vybráno pole, jež se má agregovat, v řádku „Souhrn:“ má vybranou agregační funkci, a v řádku „Křížová tabulka:“ má volbu „Hlavička řádku“. Toto můžeme sami vytvořit ve vlastním návrhu dotazu přidáním dalšího sloupce do mřížky:
V databázi Řešení jsou oba postupy demonstrovány na příkladech dotazů „Tržby zaměstnanců po čtvrtletích 2_křížový dotaz“ a „Tržby zaměstnanců po čtvrtletích 3“.
5.8.1 Pole křížového dotazu Shrňme: křížový dotaz je typ dotazu MS Access. V křížovém dotazu může být více polí jako hlavička řádku. Některé z nich mohou být agregací nějakého pole, ty pak představují souhrn pro celý řádek výsledku. Jedno pole musí být hlavička sloupce, a nesmí jich být víc. Jedno pole musí být „hodnota“, a opět jich nemůže být víc. Kód vygenerovaný pro křížový dotaz není SQL-kódem, nelze ho poslat běžnému databázovému serveru! Pokud chcete zpracovat výsledek dotazu předaného databázovému serveru do podoby křížové tabulky, musíte tento jako mezivýsledek zpracovat dalším, tentokrát MS Access křížovým dotazem, podobně jak to dělá průvodce křížovým dotazem.
5.8.2 Datový list křížového dotazu V datovém listu křížového dotazu je možno dělat úpravy formátu, jako s jinými dotazy. Zejména je zajímavá možnost ukotvení sloupců s hlavičkami řádků, a možnost uspořádat řádky podle nějakých vybraných sloupců.
41
Menu Zobrazit-Vlastnosti, nebo lokální menu, nebo tlačítko aktuálnímu objektu.
35
v panelu nástrojů. Dialog Vlastnosti se přizpůsobuje
5.9 Kontingenční tabulky, kontingenční grafy MS Access 2002 nabízí možnost zpracování výsledku dotazu do podoby kontingenční tabulky nebo kontingenčního grafu. V databázi Řešení jsou některé příklady, vytvořené kontingenční tabulky jsou ve skupině pojmenované „Kontingenční tabulky“, kontingenční grafy jsou vytvořeny pro dotazy, jež mají v popisu poznámku „kontingenční graf“. Nabídka kontingenční tabulky a kontingenčního grafu je v menu Zobrazit (nebo v panelu nástrojů v přepínači zobrazení) pro právě otevřené okno dotazu. Pro první seznámení s kontingenčními tabulkami v MS Access je v databázi Řešení připraven dotaz „Podrobný přehled prodejů“. Importujte si ho (a pokud nemáte, tak ještě „Jména Zaměstnanců“) do svého projektu. Otevřte prve importovaný dotaz „Podrobný přehled prodejů“, přepněte zobrazení na Zobrazení kontingenční tabulky. Zobrazte seznam polí
:
Nyní máte různé možnosti, co zvolit do jednotlivých „dimenzí“ kontingenční tabulky. Můžete například: do oblasti řádků přidat „Země“ a „Firma“, do oblasti sloupců přidat „Datum objednávky po měsících“, do oblasti filtru přidat „Název kategorie“, „Název výrobku“ a „Zaměstnanec“. Do datové oblasti i do podrobných dat přidejte „Výsledná cena“. Seznam polí potom zavřete – můžete ho vždy znovu otevřít – budete mít lepší výhled.
Pokud se vám roky špatně zobrazují, asi takto:
, a klikněte pravým tlačítkem myši na vyberte „Vlastnosti“. V dialogu „Vlastnosti“ přepněte na kartu „Formát“. Zde přepište rrrr na yyyy a pak dialog „Vlastnosti“ zavřete.
Kontingenční tabulka nyní vypadá takto:
36
Nyní můžete zkoušet rozbalovat a sbalovat pomocí a , , , atd.) můžete zkoušet různě upravovat, například takto: nabídky u polí (
nebo Pak bude zobrazen pouze výběr. Zkoušejte také lokální menu na různých objektech (po ukázání myší se vám zobrazí návěští objektu) … Zde naleznete též odebrání pole z kontingenční tabulky (zůstane ovšem v seznamu polí). Kontingenční tabulky v MS Access2002 nabízejí podobné možnosti, jako v MS Excel, uživatelská přívětivost je v MS Access horší, ale možnost zobrazení podrobností i souhrnů je v MS Access2002 navíc. O možnostech v MS Excel viz kapitolu 6 Souhrny pomocí datových kostek.
5.9.1 Kontingenční grafy Data kontingenční tabulky lze zobrazit i do grafu, po přepnutí zobrazení do Zobrazení kontingenčního grafu. V zobrazení kontingenčního grafu můžete měnit typ grafu, zobrazovat či skrýt legendu, volit řady podle sloupců či podle řádků – vše v panelu nástrojů nebo v menu Kontingenční graf
Výběr polí nejlépe upravíte v kontingenční tabulce. Příklad grafu ukazuje obrázek nahoře na následující straně. Další příklady kontingenčních grafů naleznete v databázi Řešení u některých dotazů, u kterých je odpovídající poznámka v popisu.
37
- výběr zemí:
5.10 Zvláštní případy seskupovacích úloh 5.10.1 Dotazy na počet různých hodnot Dotazy, ve kterých chceme zjistit počet různých hodnot vyhovujících nějakým požadavkům, vyžadují v MS Access specifický postup. Tento postup spočívá v tom, že si nejprve připravíme seznam požadovaných hodnot bez opakování, a poté pro tento seznam použijeme agregaci Count. Ukažme si to na příkladu. Představme si, že chceme pro každý druh výrobku spočítat počet zákazníků, kteří tento druh výrobku nakupují. Nejprve si tedy připravíme pro každý výrobek seznam zákazníků, kteří daný výrobek někdy nakoupili, a zamezíme opakování. Toto zajistíme například dotazem:
seznamVýrobkyZákazníci Výsledkem je tabulka: ČísloVýrobku se zobrazuje jako Výrobky.NázevVýrobku s titulkem Výrobek, KódZákazníka jako Zákazníci.Firma s titulkem Zákazník.
Výrobek Chai Chai … Chang Chang
Zákazník Berglunds snabbköp Blondel pere et fils … Antonio Moreno Taquería Around the Horn
…
…
Aniseed Syrup Ana Trujillo Emparedados y helados (Pro vysvětlení viz podkapitolu 2.4 Struktura Aniseed Syrup Berglunds snabbköp … … tabulek v databázi Northwind na str.10 )
38
Výsledek předchozího dotazu nyní seskupíme podle Výrobků, a spočítáme počet řádků v každé skupině – zvolíme Nový dotaz v návrhovém zobrazení, v dialogu Zobrazit tabulku přepneme na Dotazy a vybereme prve vytvořený mezivýsledek, pak v návrhu dotazu vytvoříme souhrn: Výsledek: Výrobky-početZákazníků Výrobek
početZákazníků Chai 31 Chang 28 Aniseed Syrup 10 Chef Anton's Cajun Seasoning 17 Chef Anton's Gumbo Mix 9
...
…
V SQL existuje agregační funkce COUNT DISTINCT, kterou lze použít pro tento typ úloh, avšak MS Access tuto funkci nepodporuje. Úloha zde předvedená by byla řešena SQL-kódem: SELECT R.ČísloVýrobku, COUNT DISTINCT(O.KódZákazníka) AS PočetZákazníků FROM Rozpis_objednávek R INNER JOIN Objednávky O ON R.ČísloObjednávky=O.ČísloObjednávky GROUP BY V.ČísloVýrobku
počet zákazníků
V databázi řešení je další příklad na výpočet počtu různých hodnot, tvoří jej dotazy „Sortiment zákazníků“ a „Velikost sortimentu zákazníků“ ve skupině Seskupovací. Dotaz „Sortiment zákazníků“ ukazuje jinou možnost pro zamezení opakování dvojic (zákazník-výrobek), a to seskupením podle polí (KódZákazníka a ČísloVýrobku). Tento příklad pokračuje ještě o jeden krok dále, pro zjištění statistiky velikosti sortimentu zákazníků (dotaz „Frekvence velikosti sortimentu“): 8 7 6 5 4 3 2 1 0 0
10
20
30
40
50
60
velikost sortimentu
5.10.2 Agregace First, Last MS Access nabízí dvě specifické agregace, a to First a Last. Pro jejich smysluplné použití je třeba zajistit, aby řádky základní tabulky, ve které se takový souhrn počítá, měly žádoucí pořadí. Jedním příkladem je dotaz „Poslední cena“ ve skupině „Seskupovací“. Jeho smysl je zjistit, zda jednotková cena uvedená v tabulce Výrobky odpovídá ceně naposled účtované v objednávkách. Důvěra v pořadí řádků vzniklých spojením tabulek Výrobky a Rozpis objednávek je ovšem založena na znalostech postupů, jak se takové propojení realizuje, což vysoko přesahuje rámec těchto skript. Obecně tedy nelze předpokládat, v jakém pořadí bude MS Access procházet řádky tabulek.
39
Jiným příkladem ukazujícím jak zajistit pořadí pro použití funkcí First nebo Last jsou dotazy „Počty zákazníků nakupujících výrobky v zemích“ (to je vytvářecí dotaz 42, tedy odpověď na něj je ukládána jako skutečná tabulka v databázi – zde je to „Počty_zák_v_zemích_pro_výrobky“) a následující, který zpracuje výsledek předchozího, dotaz „Statistika počtu zákazníků v zemích pro výrobky“. Podobně lze například vytvořit výsledek Nejlepší prodejci měsíců rok měsíc 1996 7 1996 8 1996 9 1996 10 1996 11 1996 12 1997 1 1997 2 … …
nejlepší zaměstnanec jeho/její tržba počet objednávek Peacock, Margaret 296 511,25 Kč 7 Callahan, Laura 206 595,00 Kč 6 Davolio, Nancy 166 275,00 Kč 5 Peacock, Margaret 342 974,25 Kč 8 Peacock, Margaret 282 785,50 Kč 5 Buchanan, Steven 250 770,50 Kč 3 Peacock, Margaret 593 411,62 Kč 8 Peacock, Margaret 303 049,87 Kč 6 … … …
Stejného výsledku však lze dosáhnout i složením několika mezivýsledků. Nejprve:
Potom:
A konečně: 42
Jak pracovat s vytvářecími dotazy viz kapitolu Akční dotazy.
40
Pro dosažení stejného výsledku lze použít též skalární pod-dotazy, o tom viz kapitolu Mezivýsledky. Takové řešení je ale podstatně obtížnější.
41
6 Souhrny pomocí datových kostek V programu MS Excel lze pomocí kontingenčních tabulek a kontingenčních grafů zobrazit a prohlížet data z různých dostupných databází. Tato data lze navíc uspořádat do datových kostek. Datová kostka je vícedimenzionální databázový útvar, v němž jsou data organizována analogicky ke křížovým tabulkám. Zatímco křížové tabulky mají dimenze pouze dvě, datové kostky jich mohou mít více. Při prohlížení si pak můžeme vybrat, které dimenze chceme zobrazit. Navíc, každá dimenze může mít hierarchické členění, umožňující na požádání zobrazit buď detailní data, či jejich souhrny v nějaké úrovni členění dimenze. Například můžeme mít souhrny pro celé kategorie výrobků, nebo detailní data pro jednotlivé výrobky. Vyrobíme si datovou kostku z dat vzorové databáze Northwind, a jejím prohlížením v kontingenční tabulce programu MS Excel získáte představu o tom, co taková datová kostka je a k čemu slouží.
6.1 Datová kostka z dat Northwind V programu MS Excel otevřte nový sešit. V menu zvolte Data-Importovat externí data-Nový databázový dotaz. V následujícím dialogu „Zvolit zdroj dat“ vyberte Databáze MS Access*, stiskněte tlačítko „OK“. V dialogu „Vybrat databázi“ najděte vzorovou databázi Northwind, potvrďte. Spustí se průvodce dotazem, v jehož prvním kroku vyberte sloupce: Kategorie-NázevKategoie Objednávky-DatumObjednávky Rozšířené podrobnosti objednávek-VýslednáCena Výrobky-NátevVýrobku Zákazníci-Firma a Zěmě Zaměstnaci-Příjmení
Další dva kroky průvodce („filtrování dat“ a „pořadí řazení“) přeskočte. V dokončení průvodce vyberte: . Tlačítkem ukončete průvodce dotazem, a začne Průvodce vytvořením datové krychle OLAP. Zde, pokud chcete, můžete vyvolat Nápovědu a přečíst si více o datových kostkách OLAP, nebo můžete postoupit přímo dále.
42
V prvním ze tří kroků průvodce nastavíte: (Toto nastavení pravděpodobně průvodce sám nabídl).
V následujícím, druhém, kroku průvodce vytvoříte strukturu podle obrázku → (Nižší úroveň hierarchie vytvoříte přetažením příslušného pole na symbol příslušného nadřazeného pole, tedy např. přetažením
) Dimenze DatumObjednávky se rozbalí sama na RokČtvrtletí-Měsíc…) ,a V posledním, třetím, kroku průvodce vyberte vyberte umístění pro soubor krychle (nejlépe složku, ve které máte své pokusy k těmto skriptům), soubor krychle pojmenujte například Northwind1. Po stištění tlačítka „Dokončit“ v průvodci jste požádáni o umístění souboru dotazu, jenž byl sestaven předchozím průvodcem. I tento soubor vhodně umístěte (nejlépe na stejné místo, jako soubor krychle), dotaz pojmenujte například Northwind1. Objeví se dialog „Průvodce kontingenční tabulkou a grafem (3/3)“, zde potvrďte umístění na existující list. V listu sešitu MS Excel máte nyní
DatumObjednávky, NázevKategorie, Příjmení a Země jsou „dimenze“, které můžete umístit do oblasti řádků, sloupců, nebo stránek. Například Země do oblasti řádků, DatumObjednávky do oblasti sloupců, NázevKategorie a Příjmení do oblasti stránek:
43
Součet Z VýslednáCena je jediné pole vhodné pro umístění do datové oblasti, umístěte ho. Seznam polí kontingenční tabulky nyní můžete zavřít. Podobně jako v kontingenčních tabulkách programu MS Access nyní můžete zobrazovat detaily nižších úrovní hierarchie dimenzí, nebo nastavovat kritéria výběru, například:
apod. Přepnutím v panelu nástrojů „Kontingenční tabulka“ na listě, tento můžete různě upravovat…
vytvoříte kontingenční graf na novém
Při zavírání programu MS Excel nemusíte sešit ukládat, k opětovnému zobrazení kontingenční tabulky slouží soubor dotazu OLAP (v našem případě Northwind1.oqy 43).
6.2 Jiná kostka z Northwind Pro data ze vzorové databáze Northwind můžeme vytvořit ještě jinou datovou kostku, a to pro Dopravné a s ním související dimenze. Ty mohou být například Přepravci-Firma (kdo zboží převážel), Objednávky-ZeměPříjemce (kam se zboží vezlo), DatumObjednávky (kdy se zboží vezlo). Příslušnou datovou kostku a dotaz OLAP můžete nazvat Northwin2. Můžete například zobrazit kontingenční grafy pro souhrny přepravného do Evropy (výběr více zemí v oblasti stránek), nebo do Severní Ameriky apod.: 450000 400000 350000 300000 1998
250000
1997
200000
1996
150000 100000 50000 0 Federal Shipping
43
Speedy Express
United Package
V tomto souboru je přímo zakódována cesta (tzv. absolutní umístění) ke zdroji dat i k souboru datové krychle.
44
7 Přehledy a souhrny ještě jinak Výrazy a souhrny v tabulkách lze pohodlně počítat také pomocí tabulkového kalkulátoru. Nejsnazší okamžitá cesta je zkopírovat tabulku či výsledek dotazu ze zobrazení datového listu 44 a vložit je do sešitu tabulkového kalkulátoru. Jiný postup, umožňující aktualizaci dat v tabulkovém kalkulátoru, pokud se zdrojová data v databázi změní, je v programu MS Excel přes volbu Data-Importovat externí data, kde volíme Importovat data, pokud chceme načíst data z tabulky či dotazu nějaké databáze, nebo volíme Nový databázový dotaz, pokud žádaný dotaz ve zdrojové databázi není, a pak ho pomocí průvodce můžeme sestavit 45. Běžné výpočty v tabulkovém kalkulátoru čtenář jistě zvládne. Připomeňme, že program MS Excel nabízí v menu Data také některé „databázové“ služby, pro seznámení s nimi existují jiné studijní materiály, než jsou tato skripta. Pro ukázku méně obvyklých výpočtů si můžete prohlédnout sešity „Průběh cen“ a „Opakování nákupů“. V programu MS Access lze zobrazit souhrnná i detailní data také za pomoci tzv. vnořených datových listů. Ve vzorové databázi Northwind jste si prohlédli v datovém listu tabulky Objednávky její vnořený datový list z tabulky Rozpis objednávek (tlačítko se znakem + ve voliči záznamů). Také tabulky Dodavatelé, Kategorie, Přepravci, Výrobky, Zákazníci a Zaměstnanci mají vnořené datové listy (vnořený datový list může sám mít vlastní vnořený datový list, pak jde o kaskádovité vnoření). V databázi Řešení je ukázka ve skupině Seskupovací, dotaz „Souhrny podle zemí“. Ten má vnořený datový list předchozího dotazu „Souhrny zákazníků“, jenž sám má zase vnořený datový list dotazu „Přehled nákupů zákazníků“ ze skupiny „Jednoduché“. Porovnejte sérii obrázků na následující straně. Dotaz „Souhrny zákazníků“ má i pole Země, to proto, aby bylo možno určit, ke kterému řádku ze „Souhrny podle zemí“ patří které řádky ze „Souhrny zákazníků“ – pole Země je v obou společné, tzv. propojovací. Také si všimněte, že ve vnořeném datovém listu v „Souhrny podle zemí“ se propojovací pole Země nezobrazuje, není to nutné. – Podobně je tomu s dotazy „Souhrny zákazníků“ a „Přehled nákupů zákazníků“, ty mají společná pole Země a Firma. Jak se datové listy vkládají? Prohlédněte si dotaz „Souhrny zákazníků“: Pokud zobrazení přepnete na zobrazení datového listu, pak v menu zvolte Vložit-vnořený datový list → Nebo přepněte na návrhové zobrazení, a vyvolejte dialog „Vlastnosti“ dotazu↓
Pokud je více propojovacích polí, musíme jejich 44
Pomocí menu Úpravy-Vybrat všechny záznamy, nebo pokud chceme pouze některé řádky, vyberte je pomocí voliče záznamů v levé části zobrazení datového listu. Potom kopírujte běžnými postupy v MS Windows. 45
Používá se přitom modul programu MS Query.
45
seznam vypsat a oddělit středníky.
společná pole, sloužící k propojení ve vnořeném datovém listu se propojovací pole nezobrazuje
propojovací pole jsou zde Země a Firma
Další možnost zobrazit zároveň detaily i souhrny nabízejí tiskové sestavy, o nich viz kapitolu 12 Sestavy. Pro základní představu o možnostech souhrnů a výpočtů v sestavách si prohlédněte náhledy 46 sestav „Prodeje zaměstnanců v posledním čtvrtletí“, „Přehled nákupů zákazníků“ a „Souhrny podle zemí v jednotlivých letech“ v databázi Řešení ve skupině „O sestavách“.
46
Poklepání nebo Enter na ikonu sestavy nebo tlačítko
v okně Databáze.
46
8 Mezivýsledky Někdy si pro dotaz, který máme na mysli, musíme nejprve vytvořit tabulku, kterou má tento dotaz zpracovat. Zcela výjimečně je potřeba uložit takovou tabulku do databáze, většinou je možné a zcela vyhovuje použít výsledek jiného dotazu. Tak se ten jiný dotaz stává mezivýsledkem k našemu cíli. Všechny dotazy, v nichž jsou použity pomocné dotazy Rozšířené podrobnosti objednávek a Mezisoučty objednávek jako tabulky v návrhu dotazu, jsou příklady takového postupu. Ovšem tyto dva pomocné dotazy byly zahrnuty do relací, a jsou nahlíženy jako skutečné tabulky databáze. Není vždy nutné zahrnovat mezivýsledky do relací, spíše naopak. Pak je ovšem nutno zajistit správné propojení mezivýsledků s ostatními tabulkami v návrhu dotazu.
8.1 Příklad propojení mezivýsledků Představme si, že potřebujeme přehled, ve kterém budeme mít pro každého dodavatele počet výrobků, které tento dodavatel dodává, a celkovou tržbu za výrobky tohoto dodavatele. Pro každého dodavatele tedy potřebujeme spočítat dva souhrny. Chceme je dát nakonec dohromady, ale každý z nich je třeba dělat zvlášť. První z nich bude dosažen dotazem: Druhý dotazem:
(Zkuste, že count(*) v dotazu „tržbyDodavatelů“ dá jiná čísla, než v dotazu „počtyVýrobkůDodavatelů“. Nelze tedy oba souhrny počítat najednou.) Nyní je třeba propojit výsledky obou dotazů, takto: počtyVýrobkůDodavatelů Dodavatel Exotic Liquids New Orleans Cajun Delights Grandma Kelly's Homestead Tokyo Traders Cooperativa de Quesos 'Las Cabras' Mayumi's Pavlova, Ltd. Specialty Biscuits, Ltd. PB Knäckebröd AB …
tržbyDodavatelů
početVýrobků 1 4 3 3 2
Dodavatel Exotic Liquids New Orleans Cajun Delights Grandma Kelly's Homestead Tokyo Traders Cooperativa de Quesos 'Las Cabras' Mayumi's Pavlova, Ltd. Specialty Biscuits, Ltd. PB Knäckebröd AB …
3 5 4 2 …
47
Tržba 408 899,00 Kč 779 199,75 Kč 1 048 832,50 Kč 763 158,50 Kč 628 985,75 Kč 368 418,86 Kč 2 661 494,36 Kč 1 156 099,50 Kč 293 101,50 Kč …
Je tedy potřeba vložit oba předchozí dotazy jako tabulky do dalšího dotazu (jak? – viz podkapitolu 4.7 Přidávání tabulek do návrhu dotazu na str.25), a pak je propojit. Nejprve dostaneme do návrhu dotazu tabulky nepropojeny:
Kdybychom je nechali bez propojení, bude se do výsledku propojovat každý (dodavatel) s každým (dodavatelem), zkuste výsledek: Dodavatel Exotic Liquids New Orleans Cajun Delights Grandma Kelly's Homestead Tokyo Traders Cooperativa de Quesos 'Las Cabras' Mayumi's Pavlova, Ltd. … Exotic Liquids …
Dodavatel Exotic Liquids Exotic Liquids Exotic Liquids Exotic Liquids Exotic Liquids Exotic Liquids Exotic Liquids … Tokyo Traders …
Takže propojení je třeba vytvořit – je však třeba si rozmyslet, jaké propojení (mezi tabulkami výsledků předchozích dotazů) odpovídá našemu cíli: cílem je spojit dohromady řádky, ve kterých je stejný dodavatel, jako je to naznačeno mezi tabulkami na předchozí straně. Pravidlo, které říká, co se má spolu spojovat, je že ČísloDodavatele z „tržbyDodavatelů“ je stejné jako ČísloDodavatele z „počtyVýrobkůDodavatelů“. Spojíme tedy tato pole čarou, podobnu relacím (srovnej str.9), tažením myši: tažení myši
(Porovnejte výsledek nyní!) V mřížce návrhu pak vybereme pole z mezivýsledků do konečného výsledku:
Příkladem v databázi řešení je dotaz „Tržby za výrobky porovnání 1997 a 1998 Q1“, ve kterém je navíc z polí mezivýsledků vypočítáván výraz „podíl“ (všimněte si, že má formát pole „procenta“). Pokud bychom nyní chtěli v našem příkladu přidat ještě nějaké informace o dodavateli, připojíme je tam podobně – 48
– například kontaktní osoba v dodavatelské firmě je v tabulce Dodavatelé, můžeme ji připojit:
Přidejme k našemu přehledu ještě informaci o počtu zákazníků, kteří nakupují výrobky tohoto dodavatele. Ve shodě s postupem vysvětleným v sekci 5.10.1 Dotazy na počet různých hodnot na str. 38 vytvoříme tyto dva další mezivýsledky:
a pak do našeho konečného přehledu připojíme ještě dotaz „počtyZákazníkůDodavatelů“:
49
8.2 Mezivýsledek pro použití v kritériu Někdy si vytvoříme seznam hodnot, které chceme použít v kritériu následujícího dotazu pro porovnání In(seznam_hodnot). Tento seznam může být i výsledkem nějakého předchozího dotazu. Místo toho, abychom jednotlivé hodnoty seznamu opisovali do kritéria, můžeme využít tabulky odpovědi dotazu vytvářejícího ten seznam. Například vybereme pět nejméně prodávaných výrobků, a chceme vědět, kteří zákazníci tyto výrobky kupovali. Nejprve tedy sestavíme dotaz vybírající ty výrobky:
← Dál sestavíme dotaz pro výběr zákazníků, v němž bude pole ČísloVýrobku pro kritérium k vymezení výrobků.
Kritérium vymezující výrobky můžeme nyní napsat takto → To je jistě velmi pracné, navíc předchozí dotaz vytvářející seznam výrobků v odpovědi nezobrazoval čísla výrobků, ale jejich názvy, takže bychom ho museli upravit…
←Také můžeme místo toho napsat SQLkód.
Nejlepší řešení však ukazuje obrázek na následující straně! 50
Skutečně, propojení mezi tabulkami výsledků dotazů „Rozpis objednávek“ a „NejhoršíVýrobky“ realizované prostřednictvím pole ČísloVýrobku omezí výsledek pouze na ty řádky, v nichž ČísloVýrobku je ze seznamu nejhorších výrobků. (Na začátku kapitoly 10 Polospojení je k tomuto podrobný výklad.) Takto získaný seznam můžeme použít dál, například zjistit podíl tržby těchto zákazníků na celkové tržbě, a tím zjistit, jak jsou tito zákazníci pro nás významní…: ← Toto je zvláštní agregační dotaz, kde není ani jedno „Seskupit“. V takovém případě se souhrn počítá pro celou tabulku. Vypočtené číslo se dá opsat do výrazu následujícího dotazu, je však možno využít výsledek i takto, a to bez propojení: ↓
V kapitole 10 Polospojení je ukázáno, jak do našeho přehledu zahrnout všechny zákazníky, a do výsledku přidat informaci, zda do „ZákazníciNejhoršíchVýrobků“ patří nebo nepatří. V databázi Řešení jsou ve skupině „Poddotazy“ k tomuto tématu příklady dotazů „Zákazníci nakupující deset nejdražších výrobků“ a „Tržby za výrobky od největších zákazníků“. Dotaz „Tržby za výrobky od největších zákazníků :( “ je navržen s kritériem in (select…). Takové dotazy jsou v MS Access obecně pomalejší, než jim odpovídající verze s „kritériem propojením“. Tento konkrétní dotaz specielně je v MS Access natolik špatně optimalizován, že jeho provádění trvá řádově minuty. Dotaz „Porovnání tržeb za výrobky“ v databázi Řešení porovnává celkové tržby za jednotlivé výrobky s tržbami od největších zákazníků. Protože u některých výrobků nebyla žádná tržba od největších zákazníků, jsou v návrhu tohoto dotazu tabulky použitých mezivýsledků polospojeny.
51
8.3 Skalární pod-dotazy Tento část výkladu se týká poměrně sofistikovaného a náročného použití „mezivýsledků“. Většinou lze však použít snazší postupy, a ty zde také ukážeme. Postup ukážeme na úloze pro data ze vzorové databáze Northwind: Pro každý výrobek nalezněte zemi, ve které byla za ten výrobek největší tržba! (Obecně jde o úlohu v každé skupině nalézt nejlepšího/nejhoršího …) Vysvětlíme si nejprve snazší postupy. První z nich využije agregační funkci „Fist“, nabízenou programem MS Access. Jak je vysvětleno v kapitole 5 Seskupovací dotazy, podkapitole 5.10 Zvláštní případy seskupovacích úloh, v sekci 5.10.2 Agregace First, Last (zde je odkaz na analogickou úlohu), je potřeba zajistit pořadí zpracovávaných řádků, což většinou znamená mezivýsledek uložit jako tabulku do databáze. Sestavíme tedy vytvářecí dotaz (o vytvářecích dotazech viz kapitolu 13 Akční dotazy):
Výsledek tohoto dotazu uložíme do tabulky „tržbyvýrobky-země“.
Tabulku „tržby-výrobkyzemě“ zpracujeme pomocí agregační funkce „First“:
Nevýhodou tohoto prvního postupu je nutnost ukládat mezivýsledek jako tabulku, dále je to specifičnoust funkce „Fist“, kterou běžné SQL databáze nepodporují.
8.3.1 Alternativa pomocí více mezivýsledků Další alternativa postupu ke stejnému cíli používá více mezivýsledků. První je podobný jako prve, ale není nutno v něm řadit a není nutno výsledek dotazu ukládat jako tabulku (první obrázek na další straně). V následujícím kroku nalezneme pro každý výrobek nejvyšší tržbu v jednotlivé zemi (druhý obrázek). Konečně v posledním kroku porovnáním nalezneme zemi, ve které ta nejvyšší tržba byla (třetí obrázek). 52
8.3.2 SQL SELECT jako výraz pro pole dotazu Poslední alternativa postupu je ukázána v databázi Řešení, ve skupině „Poddotazy“, jako dotaz „Země s největší tržbou pro jednotlivé výrobky“ – toto řešení je však velmi sofistikované, používá SQL-kód v polích návrhu dotazu, odtud název této sekce 47. Jak předchozí výklad ukázal, většinou lze výsledku dosáhnout příjemnější cestou. Podejme vysvětlení k ukázce v databázi Řešení. Dva dotazy „pom1“ a pom2“ jsou pomůckou k vygenerování SQL-kódu, jenž je pak použit v dotazu „Země s největší tržbou pro jednotlivé výrobky“ v polích „Země“ a „Tržba“. Oba pomocné dotazy jsou parametrické, parametry v nich jsou myšleny jako hodnoty z běžných řádků tabulky Výrobky zpracovávané dotazem „Země s největší tržbou pro jednotlivé výrobky“. (Pokud byste chtěli takovýto postup používat obecně, museli byste v některých případech použít i aliasy pro tabulky v pomocných dotazech.)
47
SQL-kód použitý jako výraz pro pole přestavuje pod-dotaz, jenž vrací jedinou hodnotu – tabulku s jediným řádkem a jediným sloupcem. Tento pod-dotaz je tedy „skalární“.
53
9 Dotazy na to co v databázi není Zvláštní skupinu úloh tvoří požadavek zjistit, která fakta daného typu a vlastností v databázi nejsou. Protože formulovat takové dotazy je poměrně obtížné, přestože jsou potřebné, nabízí pro ně program MS Access průvodce. Je to „Průvodce vyhledávacím dotazem na chybějící záznamy“ v okně Databáze pro objekty Dotazy. Před jeho použitím je dostupný po stištění tlačítka obvykle třeba si připravit jeden nebo i dva dotazy jako mezivýsledky. Vysvětlíme si to na příkladu: chceme zjistit, kteří zákazníci v posledních dvou měsících nepodali žádnou objednávku. K této úloze máme v databázi tabulku Zákazníci, v níž jsou všichni zákazníci, tedy i ti, které hledáme. Dále si musíme připravit dotaz, jenž nám poskytne seznam těch zákazníků, kteří naopak v dotyčných dvou měsících nějakou objednávku podali – o tom máme záznamy v tabulce Objednávky:
Poznámka k datu: v databázi Northwind končí čas v květnu 1998…
Pro zvýšení efektivity výsledného dotazu je možno v tomto pomocném dotazu nastavit Jedinečné hodnoty.
Nyní jsme připraveni použít Průvodce vyhledávacím dotazem na chybějící záznamy: V jeho prvním kroku máme vybrat tabulku nebo dotaz, mezi jehož řádky lze nalézt hledané záznamy („kde jsou všichni, které hledáme“), což je v našem případě tabulka Zákazníci. Ve druhém kroku průvodce pak máme vybrat tabulku nebo dotaz, použitelný jako seznam hodnot k vyškrtnutí z předchozí tabulky nebo dotazu (ze „seznamu všech“). Ve třetím kroku se průvodce ptá, co má v předchozích dvou seznamech porovnávat - v našem případě má porovnávat kódy zákazníků v tabulce Zákazníci s kódy zákazníků seznamu „TiCoObjednali“. V následujícím kroku se průvodce ptá, která pole chceme ve výsledku. V našem příkladu vyberte např. KódZákazníka, Firma, Země. V posledním kroku se průvodce pouze zeptá, jak výsledek pojmenovat. Výsledný návrh dotazu: (V odpovědi je 35 zákazníků.) Dva konstrukční prvky návrhu zajišťují výsledek: polospojení mezi tabulkami (o polospojení viz následující kapitolu), a kritérium „Is Null“ (omezuje na řádky, v nichž je hodnota v daném poli prázdná).
54
V SQL databázi, která neumí polospojení, by bylo místo toho nutno použít kritérium not in (select…) Pokud vyzkoušíte tuto verzi v MS Access, možná zpozorujete mírné zpoždění v provádění dotazu!
V databázi Řešení jsou další příklady ve skupině „Co se nestalo“, dotazy „Zákazníci bez objednávky“, „Zákazníci bez objednávky od 1998“, „Výrobky nenakupované největšími zákazníky“, „Výrobky v dané zemi neprodávané“. Obtížnější je případ, kdy si musíme uměle sestavit první seznam k tomuto typu úloh, tj. seznam „kde jsou všichni“. Příklad uvádí dotaz „Výrobky x Země“ ve skupině „Co se nestalo“. Seznam „hodnot k vyškrtnutí“ je v tomto případě dotaz „Výrobky prodávané v zemích –vše“. Průvodce v tomto případě ani nelze použít, neboť polospojení musí být definováno pro dvojice polí (ČísloVýrobku; ZeměPříjemce) (o tom jak to udělat viz další kapitolu). Výsledný dotaz „Výrobky v zemích neprodávané“ nalézá v jednotlivých zemích ty výrobky, které se tam neprodávaly.
9.1 Alternativní řešení Pro většinu takovýchto úloh existují alternativní řešení! Pro náš příklad existuje snadné řešení:
Ale pozor, dva zákazníci, ti co nemají vůbec žádnou objednávku, nám ve výsledku chybí!
Jinou možnost poskytuje využití křížových dotazů. Pro náš příklad:
– takto dostáváme přehled nákupů zákazníků v jednotlivých měsících, a prázdné políčko ve výsledku značí, že zákazník v tom měsíci nenakupoval… I zde chybí oba zákazníci bez objednávky. Prohlédněte si dotazy v databázi Řešení ve skupině „Co se nestalo“: „Prodeje výrobků v zemích“ (posuďte i následující dotaz „Výrobky neprodávané v USA“) a „Nákupy zákazníků podle ceny výrobků“ (tento dotaz používá sofistikovaný výraz pro zaokrouhlení ceny výrobků). 55
10 Polospojení Běžné spojení mezi tabulkami vytváří do výsledku kombinace řádků z obou tabulek, jež vyhovují podmínce propojení. To znamená, že ty řádky v jedné či druhé tabulce, které takto nelze zkombinovat, ve výsledku nejsou. Například propojíme-li tabulky Objednávky a Zákazníci:
– ve výsledku nejsou zákazníci „FISSA Fabrica Inter. Salchichas S.A.“ (KódZákazníka FISSA) a „Paris spécialités“ (KódZákazníka PARIS), ačkoli v tabulce Zákazníci je nalezneme. To proto, že v tabulce Objednávky není žádný řádek s KódZákazníka FISSA nebo PARIS, od těchto dvou zákazníků nemáme žádnou objednávku. Můžeme dosáhnout toho, že tito dva zákazníci ve výsledku budou také – musíme změnit běžné propojení na polospojení 48. Poklepejte na čáru spojující obě tabulky, tím vyvoláte dialog „Vlastnosti spojení“:
V horní části tohoto dialogu je pravidlo propojení. V dolní části je přepínač mezi běžným spojením 49, levým polospojením a pravým polospojením. Přepněte přepínač do polohy 2, tj. na „Zahrnout všechny záznamy z tabulky Zákazníci…“, potvrďte OK. Čára spojující obě tabulky má nyní šipku:
Ve výsledku dotazu najdete nyní i ty dva zákazníky, FISSA a PARIS. Podívejte se, co je ve sloupci ČísloObjednávky u těchto dvou zákazníků – je tam prázdné políčko 50.
48
Někdy též nazývané vnější spojení (OUTER JOIN).
49
Někdy též nazývaným vnitřní spojení (INNER JOIN).
50
Prázdná hodnota, tzv. Null .
56
Ve výsledku polospojení v řádcích, které vznikly polospojením navíc (tj. nejsou kombinací řádků z obou tabulek), jsou ve sloupcích, kde nic nemůže být, prázdná políčka. V dotazech s polospojením lze též počítat souhrny. Zkuste nejprve agregační funkci count:
V řádcích zákazníků FISSA a PARIS vidíme ve sloupci PočetObjednávek číslo 0. Pozor, zde nesmíme použít výraz count(*), ten počítá počet řádků v dotazu před seskupením, a ten je i pro zákazníky FISSA a PARIS nenulový, protože jeden řádek tam pro ně byl! Zkuste (ve čtvrtém sloupci je u FISSA a PARIS číslo 1):
Vyzkoušíme i agregační funkci sum. Nejprve jednodušeji, spočítáme každému zákazníkovi součet dopravného:
Zákazníci FISSA a PARIS mají součet prázdný! 51 Pro počítání tržeb všech zákazníků by bylo nutno připojit dotaz Mezisoučty objednávek, a takovouto kombinaci spojení a polospojení nelze v MS Access udělat, je třeba určit, co se má dělat dříve, zda běžné spojení a pak polospojení, nebo obráceně. Takže nejprve připravíme 51
Součet prázdné množiny hodnot není 0. Stejně je tomu s ostatními agregačními funkcemi, kromě funkce Count.
57
a pak tento výsledek polospojíme s tabulkou Zákazníci:
V databázi Řešení jsou příklady dotazů s polospojením ve skupině „Jiné propojení“. Dotaz „Zákazníci a dodavatelé výrobků podle měst“ propojuje ke smluvním partnerům dodávané výrobky. Dotaz „Porovnání tržeb za výrobky“ spojuje dva předchozí mezivýsledky polospojením (povšimněte si výrobku „Laughing Lumberjack Lager“). V kapitole 8 Mezivýsledky v podkapitole 8.2 Mezivýsledek pro použití v kritériu je řešen příklad pomocí série mezivýsledků, a na konci je poznámka, že pomocí polospojení můžeme výsledek ještě vylepšit. Ukažme tedy jak. Konečný výsledek ze zmíněné subkapitoly upravíme:
a polospojíme ho s dotazem „zákazníciNejhoršíchVýrobků“ (obrázek nahoře na následující straně). Následující tabulka ukazuje výsledek (poslední pole má titulek). Ve skupině „Tendence nákupů“ v dotaze „Počet objednávek výrobků“ je použit lépe vyhovující výraz pro takovýto účel.
58
Zákazník QUICK-Stop Ernst Handel Save-a-lot Markets Rattlesnake Canyon Grocery
Tržba 2 756 932,62 Kč 2 621 874,45 Kč 2 609 048,74 Kč 1 277 445,02 Kč
podíl nakupuje také nejhorší výrobky? 8,71% QUICK-Stop 8,29% Ernst Handel 8,25% Save-a-lot Markets 4,04%
Hungry Owl All-Night Grocers 1 249 497,63 Kč 3,95% Hanari Carnes
821 034,25 Kč 2,59%
Königlich Essen Folk och fä HB
772 709,59 Kč 2,44% Königlich Essen 739 189,05 Kč 2,34%
Mere Paillarde White Clover Markets Frankenversand Queen Cozinha Berglunds snabbköp
721 804,74 Kč 684 090,12 Kč 666 413,97 Kč 642 937,44 Kč 623 189,43 Kč
…
…
2,28% Mere Paillarde 2,16% White Clover Markets 2,11% Frankenversand 2,03% Queen Cozinha 1,97% ……
V případě, že chceme polospojit vztahy nezávislých objektů, potřebujeme vytvořit polospojení přes více polí, jako v dotazu „Výrobky v zemích neprodávané“ ve skupině „Co se nestalo“. V návrhovém zobrazení je pak třeba nastavit přepínač v dialogu „Vlastnosti spojení“ u obou čar znázorňujících propojení.
59
11 Self join Při řešení některých úloh zjistíme, že potřebujeme do dotazu dát nějakou tabulku dvakrát, a propojit ji nějakým způsobem samu se sebou. Například můžeme chtít zjistit, zda u některého výrobku někdy došlo ke zlevnění. Záznamy o cenách výrobků máme v tabulce Rozpis objednávek, pro jednoduchost budeme předpokládat, že čísla objednávek ukazují, která objednávka byla dřív, a která později. Hledáme tedy dvě objednávky, jednu s nižším číslem a druhou s vyšším číslem, v nichž byl objednán tentýž výrobek, poprvé s vyšší cenou než podruhé. Protože číslo objednávky je v tabulce Rozpis objednávek také, tabulku Objednávky k ničemu nepotřebujeme. Po programu tedy chceme, aby hledal dvojice řádků z tabulky Rozpis objednávek, ve kterých je stejné ČísloVýrobku, přičemž ČísloObjednávky v prvním řádku je nižší než ve druhém řádku a JednotkováCena v prvním řádku je vyšší než ve druhém řádku. Obecně, hledáme dvojice řádků ze stejné tabulky splňující nějakou podmínku. V takovém případě musíme do návrhu dotazu dát tuto tabulku dvakrát:
Druhý exemplář stejné tabulky v návrhu dotazu MS Access přejmenuje – přidá „_“ a číslo 1 52. Pokud některá z podmínek, jež mají být mezi hledanými řádky splněny, je rovnost mezi poli řádků těchto tabulek, jako v našem případě rovnost polí ČísloVýrobku, můžeme ji v návrhu vytvořit jako propojení:
pozor na přejmenování druhé tabulky
Ostatní podmínky je třeba zadat jako kritéria:
V odpovědi vidíme jistou redundanci informace, objednávka č. 10308 je zde opakovaně. V databázi Řešení ve skupině „Jiné propojení“ je dotaz „Zlevnění výrobku“, ve kterém není použit náš zjednodušující předpoklad o stoupajících číslech objednávek v průběhu času. V jeho návrhu uvidíte, že i tabulka Objednávky je tam dvakrát, oba exempláře jsou připojeny ke „svému“ exempláři tabulky Rozpis objednávek. Mnoho úloh podobného typu lze ovšem řešit i jednodušeji, a to pokud všechny podmínky na dvojice hledaných řádků jsou rovnosti stejných polí. Například dotaz „Objednávky téhož zákazníka v jednom dni“ ve skupině „Jiné propojení“ má jednodušší řešení: seskupení tabulky Objednávek podle KódZákazníka a DatumObjednávky a kritériem count(*)>1. Pokud chceme ještě informaci, které objednávky to byly, můžeme do odpovědi vnořit datový list tabulky Objednávky: 52
Další by měly číslo 2, 3, … (V SQL-kódu jde o aliasy tabulek.)
60
(O vnořených datových listech viz kapitolu 7 Přehledy a souhrny ještě jinak.)
Dotaz „Zaměstnanci pohled“ ve skupině „Jiné propojení“ ukazuje propojení tabulky Zaměstnanci samé se sebou, a to propojením polí Nadřízený a ČísloZaměstnance. Exemplář tabulky Zaměstnanci, ze kterého propojujeme pole Nadřízený, je přejmenován 53 na „Podřízení“ kvůli přehlednosti. Hodnota tohoto dotazu je zvýšena vnořením datového listu tohoto dotazu do sebe!:
Zbavit se v tomto pohledu zaměstnanců nižší úrovně je obtížné, jak ukazuje následující dotaz „Pro organizační schéma“. To proto, že relační databáze jsou pro prozkoumávání hierarchií dosti nešikovné (srovnej Dotaz „Šéfové druhé úrovně“). Obdržet skutečně hierarchický pohled (sestava Organizační schéma) dá mnoho práce.
53
Takové přejmenování je třeba udělat úpravou aliasu v SQL-kódu. Jde čistě o pohodlí, MS Access generuje dostatečně korektní strojové aliasy. Jiný způsob „přejmenování“ by byl dotaz jiného jména zobrazující pole tabulky.
61
12 Sestavy Sestava je jakousi šablonou, podle níž se má upravit pro tisk tabulka či výsledek dotazu. Ukázky sestav si můžete prohlédnout ve vzorové databázi Northwind a v databázi Řešení. Nejjednodušší cestou k vytvoření sestavy je volba „automatická sestava“, sestavy takto vytvořené jsou však velmi jednoduché. Náročnější sestavy lze nejlépe vytvořit pomocí průvodce, jenž za vás udělá mnoho rutinní práce, a vzniklý návrh sestavy můžete eventuelně následně upravit. Také je možno vytvořit celou sestavu rovnou v návrhovém zobrazení, to je však zbytečně pracné.
12.1 Automatické sestavy MS Access nabízí dva druhy automatických sestav – sloupcové a tabelární. Vyzkoušejte automatickou sloupcovou sestavu pro tabulku Kategorie, a automatickou tabelární sestavu pro dotaz „Souhrny podle zemí v jednotlivých letech“ 54: Protože sestava je vždy šita na míru nějaké tabulce nebo dotazu, můžete tuto tabulku nebo dotaz nejprve vyznačit v seznamu v okně databáze, a pak požádat o novou sestavu nad nimi 55. Nebo můžete požádat o novou sestavu 56, a hned v prvním následném kroku vybrat zdroj záznamů ze seznamu tabulek a dotazů v databázi.
Sestava vytvořená pro tabulku Kategorie obsahuje i obrázky z pole Obrázek. Sloupcová sestava má pole z jednoho záznamu pod sebou, jednotlivé záznamy následují po sobě. Tabelární sestava má pole z jednoho záznamu vedle sebe – tabulka je vytištěna jako tabulka. Výsledky pokusů z této sekce nemusíte ukládat.
12.2 Průvodce sestavou Pokud použijete průvodce sestavou, a to lze doporučit v každém případě, nemusíte dokonce ani mít vytvořen dotaz, na jehož základě má být sestava navržena, pokud se jedná o jednoduchý dotaz – na začátku průvodce sestavou se zobrazí dialog známý z průvodce jednoduchým dotazem, v němž lze vybrat pole z více tabulek či dotazů. Průvodce sám sestaví příslušný jednoduchý dotaz jako zdroj záznamů budoucí sestavy. Vyzkoušejte to na příkladě, vyberte: z tabulky Zákazníci - KódZákazníka, Firma, Země 54
Tento dotaz si importujte z databáze Řešení.
55
V panelu nástrojů tlačítko Nový objekt
56
V okně Databáze přepnout na objekty Sestavy, a tlačítkem Nový
, pak vybrat volbu Sestava.
62
požádat o novou sestavu.
z tabulky Objednávky - ČísloObjednávky, DatumObjednávky, DatumOdeslání, Přeprava, Dopravné z tabulky Výrobky - ČísloVýrobku, NázevVýrobku z dotazu Rozšířené podrobnosti objednávek - JednotkováCena, Množství, Sleva, VýslednáCena. Pořadí polí ve výběru ovlivní pořadí v budoucí sestavě!
V dalším kroku, eventuelně, navrhne průvodce sestavou možné hierarchické členění sestavy na základě relací v databázi. Tento krok „Jak chcete prohlížet data?“ se vůbec neobjeví, pokud průvodce žádné možnosti hierarchického členění nenajde. Pokud chcete tuto službu co nejlépe využít, sestavte svůj dotaz tak, aby v něm byly relace z návrhu relací v databázi použity, pokud to dává smysl pro daný účel 57. Přepínačem vlevo lze vybírat některé další možnosti uspořádání. Odstranit pouze některé z nabízených úrovní však nelze (nelze například prohlížet podle zákazníků a vynechat hierarchickou úroveň objednávek). V našem příkladu ponechte původní nastavení „podle Zákazníci“:
návrh hierarchického členění sestavy
V dalším kroku „Chcete přidat úrovně seskupení?“ můžete návrh hierarchického členění sestavy upravit přidáním dalších úrovní (celkem mohou být maximálně 4). V našem příkladu vyberte pole Země ze seznamu vlevo:
V dalším kroku nastavujete dvě věci: řazení v nejnižší úrovni hierarchie a souhrny. Řazení nastavte podle NázevVýrobku
Pomocí tlačítka zavolejte dialog, ve kterém zadáte požadavky na výpočet souhrnů v sestavě. Zadejte „Součet“ u „VýslednáCena“. Povšimněte si možnosti „Jen souhrnně“ – jejím vybráním bychom v sestavě neměli detaily o jednotlivých výrobkách – ponechte „Podrobně a
57
Kódy entit vybírejte raději z tabulek těchto entit, pokud chcete podle těchto entit mít úroveň hierarchie. Když propojujete mezivýsledky, pak co lze propojit z tabulek schématu až v posledním kroku, to tak udělejte.
63
souhrnně“!. Dále si povšimněte možnosti výpočtu procent z celku – ani toto v našem příkladu nevyužijeme. Tedy pouze zaškrtneme:
Potvrzením OK se vrátíme do předchozího dialogu, přejdeme na další krok. V tomto kroku vybíráte celkové rozložení na stránce, pro náš příklad vyberte Hierarchické1. I zde si prohlédněte zbytek dialogu. V dalším kroku vybíráte jeden z automatických formátů sestavy, do určité míry lze tento seznam v siné situaci doplnit. Pro náš příklad vyberte „Světle šedý“. Naposled vybraný automatický formát v průvodci sestavou se používá v automatických sestavách! V posledním kroku sestavu pojmenujte, například Cvičná sestava. Po dokončení průvodce chvíli probíhá generování sestavy, nakonec vidíte náhled
hlavičky úrovní hierarchie, kromě nejnižší úrovně
v nejnižší úrovni řazeno podle NázevVýrobku vypočtený souhrn pro objednávku vygenerované popisné texty pro souhrn
grafické prvky a formáty textů odpovídají vybranému automatickému formátu
zápatí stránky
Listováním pomocí naleznete na stránce 2 nenápadný souhrn pro prvního zákazníka, na stránce 5 naleznete stejně nenápadný souhrn pro první zemi. 64
Úpravy se dělají v návrhovém zobrazení, přepněte na něj: volič celé sestavy místo pro tažení myší pro změnu šířky sestavy
voliče sekcí sestavy sekce Záhlaví KódZákazníka
obdélník pole se seznamem čára popisek textové pole dolní okraj sekce – místo pro tažení myší pro změnu velikosti sekce
Ukážeme si základní úpravy, o dalším viz nápovědu programu MS Access. V návrhu sestavy jsou grafické objekty: popisky, textová pole či pole se seznamem, čáry a obdélníky. Každý grafický objekt, každá sekce i celá sestava mají vlastnosti nastavitelné v okně Vlastnosti 58, některé vlastnosti lze ovlivnit volbou v panelu nástrojů, velikosti a polohu lze ovlivňovat tažením myší. Okno Vlastnosti ve svém titulku sděluje, jaký typ objektu jste vyznačili. Ke grafické úpravě návrhu sestavy lze použít standardní postupy známé z MS Office. Navíc je zde menu Formát. Přesun objektů v rámci sekce je možný tažením myši uchopením za větší čtvereček vlevo nahoře v okraji vyznačeného objektu, nebo pokud ukazatel myši vypadá jako otevřená dlaň. Přesun či kopírování do jiné sekce je možný pouze přes schránku MS Windows – před vložením ukážeme na oddělovač cílové sekce. Přesouvání v rámci sekce je též možné pomocí kláves Ctrl+šipky. Změna velikosti objektu pomocí Shift+šipky. Popiskem k textovému poli je možno pohybovat samostatně pouze uchopením za větší čtvereček vlevo nahoře v okraji objektu. Text popisků lze změnit přímo umístěním kurzoru do textu v objektu popisku a následným editováním. 58
Menu Zobrazit-Vlastnosti či tlačítko Vlastnosti
v panelu nástrojů.
65
Nepotřebné objekty lze též vyznačit a smazat klávesou Delete. (Smažte popisné texty v sekcích zápatí kromě polí „=sum(VýslednáCena)“.) Textová pole v návrhu sestavy jsou dvojí: ty, co zobrazují obsah polí zdroje záznamů sestavy, a výrazy. Výrazy začínají „=“. Co textové pole zobrazí v tisku je vyjádřeno ve vlastnosti „Zdroj ovládacího prvku“:
u políčka vlastnosti představuje možnost zavolat tvůrce pro tvorbu hodnoty této Tlačítko vlastnosti. – Souhrny jsou počítány pomocí agregační funkcí, jejich argumenty mohou být pouze výrazy z polí zdroje záznamů. Souhrny lze přemístit ze zápatí do záhlaví stejné úrovně hierarchie: přemístěte souhrny pro Země a KódZákazníka ze zápatí do záhlaví! Vlastnost Název na kartě Jiné obsahuje jméno objektu. Pokud chceme vytvořit výraz z hodnot textových polí či polí se seznamem, která již jsou v návrhu sestavy, odkazujeme se na ně jejich názvy. Zkuste vytvořit nové pole pro součet celkové částky za výrobky v objednávce a dopravného: V panelu nástrojů „Souprava nástrojů“ vyberte Textové pole , vyznačte v sestavě místo v zápatí ČísloObjednávky, kde nové pole chcete. Do návrhu sestavy se přidala dvojice popisek – textové pole s výrazem „nevázaný“. Místo stávajícího textu popisku „Text54:“ napište „Celkem“, do výrazu textového pole sestavte (tvůrce výrazů v první nabídce nabízí všechny objekty aktuální sestavy): = [Sum Of VýslednáCena] + [Dopravné] Zde [Sum Of VýslednáCena] je jméno objektu textového pole se souhrnem pro ČísloObjednávky (přesvědčte se o tom!). Nové textové pole by mělo mít správný formát čísla – ten je na kartě Formát v okně Vlastnosti jako první volba. Do našeho nového pole můžete zkopírovat masku formátu pole Sum Of VýslednáCena, již vytvořit průvodce sestavou. Jinou možností pro náš příklad je volba „měna“. Pro náš příklad přemístěte pole Dopravné i s jeho popiskem ze záhlaví do zápatí ČísloObjednávky, upravte celkový vzhled a formáty objektů jako na obrázku na následující straně. Vždy máte kontrolu v zobrazení náhledu sestavy. V náhledu si prohlédněte, jak se zalamují stránky. Nepříjemné je, že nový zákazník může začínat na stránce dole, dále je nepříjemné, že někdy dochází k lámání stránky uprostřed objednávky. Oba nedostatky napravíme. V návrhovém zobrazení klepněte na sekci zápatí KódZákazníka, v jejích vlastnostech na kartě Formát jako první je „Ostránkovat“. Vyberte „za sekcí“. Nyní bude za každým zákazníkem ostránkováno. Pokud chcete opakovat záhlaví zákazníků na každé nové stránce, zvolte vlastnost sekce záhlaví na kartě Formátové „Opakovat sekci ano“. Dále si zjistíme, jak je to se samotnou existencí sekcí sestavy. Zobrazíme Řazení a seskupování 59. Zobrazí se dialog Řazení a seskupování, kde jsou úrovně hierarchie vytvořené průvodcem – zde je možno vše změnit, přidat… vyberte pole ČísloObjednávky, ve spodní části dialogu „Vlastnosti
59
Menu Zobrazit – Řazení a seskupování, nebo v panelu nástrojů stejné tlačítko
66
.
skupiny“ nastavte „Udržovat pohromadě celou skupinu“. Potvrďte, nyní budou celé objednávky na jedné straně, pokud se na jednu stránku vejdou (objednávka 11077 na str. přibližně 254 se na stránku vejde dosti těsně).
Další možnosti ovlivnění vzhledu sestavy vycházejí z volby v menu Soubor-Vzhled stránky. (Sestava „Prodeje zaměstnanců v posledním čtvrtletí“ v databázi Řešení má více sloupců, formátová vlastnost „Nový řádek nebo sloupec“ sekce Zápatí ČísloZaměstnance zajišťuje zalamování sloupců.) V databázi Řešení jsou ukázky sestav vytvořených pomocí průvodce sestavou „Prodeje zaměstnanců v posledním čtvrtletí“ a „Přehled nákupů zákazníků“, vytvořené podle stejnojmenných dotazů. Chcete-li zjistit, co je zdrojem záznamů nějaké sestavy, podívejte se v návrhovém zobrazení na datovou vlastnost „Zdroj záznamů“ celé sestavy. (Kde je volič celé sestavy viz obrázek na str.65.) Další sub-kapitoly v této kapitole objasňují některé náročnější možnosti při vytváření sestav.
12.3 Podsestavy Sestava „Města se zákazníky a zaměstnanci“ v databázi Řešení má tzv. podsestavy – detaily vnořené v hlavní sestavě. Tyto detaily mají vlastní návrh, v případě zmíněné sestavy jde o sestavy „Adresy zákazníků“ a „Adresy zaměstnanců“. Všimněte si, že tyto dvě sestavy zobrazují všechny zákazníky resp. zaměstnance, kdežto v sestavě „Města se zákazníky a zaměstnanci“ se pod příslušným městem zobrazují pouze zákazníci a zaměstnanci z tohoto města. Dále si všimněte, že v sestavách „Adresy zákazníků“ a „Adresy zaměstnanců“ nejsou textová pole s městem, tyto sestavy město nezobrazují. Ale je podstatné a nutné, že pole „město“ je ve zdroji záznamů obou sestav „Adresy zákazníků“ a „Adresy zaměstnanců“. Záležitost s podsestavami je podobná jako s vnořenými datovými listy. Podsestavu vytvoříme volbou v panelu nástrojů „Souprava nástrojů“ tlačítkem Podformulář či podsestava , když následně v sestavě vymezíme místo, kam chceme podsestavu umístit. Pak se spustí průvodce, který 67
se vás na vše zeptá, včetně výběru řídících a dceřiných polí 60. Sestavu, kterou chceme vnořit jako podsestavu do naší sestavy, musíme mít ale nejprve vytvořenou. Pro její návrh je dobré se snažit o co nejkompaktnější podobu sestavy. Pokud chcete použít nadpisy z návrhu podsestavy do hlavní sestavy, vložte je do záhlaví sestavy. (Viz v databázi Řešení sestavu „Katalog-podsestava“, která je vložena jako podsestava do sestavy „Katalog“.) Šířka objektu podsestavy v hlavní sestavě se automaticky upraví podle skutečné šířky návrhu podsestavy. Výšku nedělejte příliš velikou, při tisku se místo zvětší podle aktuální potřeby. Poněkud specificky je řešena vazba podsestavy „Nejlepší zákazníci zaměstnanců“ a sestavy „Top 10 zaměstnanců“. O tom viz sub-kapitolu 12.4 Parametry.
12.3.1 Grafy Data detailů vnořených v hlavní sestavě mohou být zobrazena i v grafu. Sestava „Prodeje podle kategorií“ ze vzorové databáze Northwind zobrazuje detailní data o prodejích výrobků jak v podsestavě, tak v grafu. Princip je podobný jako s podsestavami, musíte mít nejprve připraven zdroj záznamů pro graf, v němž bude „navíc“ pole sloužící k propojení do hlavní sestavy. Vložení grafu do sestavy provedete volbou z menu Vložit-Graf, a pak je postup podobný jako s podsestavou. Průvodce se vás na vše vyptá: vyberete pole, jež mají být do grafu zobrazena, typ grafu, navrhnete rozvržení dat v grafu (poklepáním na políčko u číselné osy dostanete nabídku způsobů sumarizace, je zde i volba „žádný“), vyberete pole k propojení zdroje řádků hlavní sestavy se zdrojem řádků grafu, nakonec vyberete nadpis grafu. Na rozdíl od podsestav, velikost grafu v tisku se nepřizpůsobuje skutečným datům datového zdroje grafu, je tedy třeba v návrhu sestavy velikost grafu upravit tak, aby stačila pro všechny tištěné grafy. Úpravy grafu děláte po poklepání na objekt grafu v návrhu sestavy, čímž se zavolá aplikace MS Graph. Postupy známými z tohoto modulu MS Office lze graf upravovat. Program MS Access 2002 se při práci s grafy občas dopouští chyb, v takovém případě většinu pomůže zkusit stejnou akci znova.
V databázi Řešení je sestava „Průběh cen“, která zobrazuje grafy časového průběhu cen jednotlivých výrobků. Zdrojem záznamů grafu je dotaz „Průběh cen“, v němž jsou časově řazené ceny výrobků zjištěné z tabulky Rozpis objednávek. Zajímavostí grafů v této sestavě je, že časová osa všech jednotlivých tištěných grafů je stejná.
12.4 Parametry Zdroj záznamů sestavy může být parametrický dotaz (o parametrech v dotazu viz str.26). Při náhledu tisku či při tisku samotném jsme pak vyzváni k zadání hodnoty parametru. Hodnota parametru může být i vytištěna – návrh sestavy může v záhlaví sestavy obsahovat textové pole s výrazem odkazujícím se na parametry zdrojového dotazu. Sestava „Přehled objednávek 2“ je parametrickou verzí sestavy „Přehled objednávek“ (v prvním parametru Zákazník je očekáván začátek kódu zákazníka, druhé dva parametry nemusíte zadat – pak jsou limity počátek 20.století a aktuální datum). Pokud chceme hodnotu parametru předat do podsestavy, je situace náročnější. O tom je výklad v následující sekci.
60
V poněkud nesrozumitelném kroku průvodce, ve kterém se nabízí seznam propojení hlavní sestavy s podsestavou, zvolte „Definovat vlastní“.
68
12.4.1 Parametry v podsestavách Sestava „Prodeje podle kategorií2“ má parametr Rok:, a ten dokonce není ani parametrem jejího zdroje záznamů (jejím zdrojem záznamů je tabulka Kategorie) 61. Tento parametr je předáván podsestavě a grafu způsobem vysvětleným dále. Tisk podsestav probíhá tak, že je vždy znova vyhodnocován dotaz, který je zdrojem záznamů podsestavy; pak je zformátován a zobrazen výsledek podsestavy s konkrétními daty vyhodnoceného dotazu. Hodnota je však známa pouze při tisku záhlaví hlavní sestavy, později není proměnná s touto hodnotou známa. Do dalších sekcí je hodnotu parametru hlavní sestavy možno předávat tak, že v záhlaví hlavní sestavy umístíme textové pole se „zdrojem ovládacího prvku“ odkazujícím na parametr (viz sestavu „Prodeje podle kategorií2“). Vlastnost „Název“ tohoto textového pole cíleně nastavíme na jméno, odkazované v dalších sekcích hlavní sestavy (zdrojový dotaz podsestavy „Prodeje podle kategorií podsestava2“ má parametr Rok, je tedy mírně odlišný od parametru hlavní sestavy). Parametr je tedy předáván prostřednictvím jména objektu ze záhlaví sestavy. Sestava „Prodeje podle kategorií 3“ má jiný zdroj záznamů než sestava „Prodeje podle kategorií2“, tímto zdrojem záznamů je parametrický dotaz „Tržby v kategoriích“. – Tato sestava bylo upravena ze sestavy „Prodeje podle kategorií2“ následujícím způsobem: změnil se zdroj záznamů, změněn byl zdroj objektu „SumOfProdejeVýrobků“ v záhlaví NázevKategorie, a přidáno bylo textové pole pro souhrn „=Max([SumOfVýslednáCena])“ v záhlaví sestavy 62. Sestava „Top 10 zaměstnanců“ má ještě sofistikovanější využití parametrů. Její podsestava „Největší zákazníci zaměstnanců“ má parametr cislozam ve zdrojovém dotazu. V těle hlavní sestavy je textové pole pro ČísloZaměstnace, jež má vlastnost Název rovnu cislozam (toto textové pole má navíc formátovou vlastnost Zobrazit rovnu „ne“, není tedy tištěno). Mezi podsestavou a hlavní sestavou v tomto případě není propojení pomocí řídících a dceřinných polí!
12.5 Obrázky v sestavě Jak jste viděli v sestavě pro tabulku Kategorií, obrázky z pole typu „objekt OLE“ se bez problému dostanou do návrhu sestavy. Pokud je však v poli tabulky pouze text se jménem souboru, jako je tomu v tabulce Zaměstnanci, je situace složitější. Obrázky v takovém případě lze do sestavy umístit pouze programátorsky. Příklad naleznete v sestavě „Top 10 zaměstnanců“. V takovýchto případech je nutno do návrhu sestavy umístit eventuelně nezobrazované textové pole se jménem souboru s obrázkem (pole Foto z tabulky Zaměstnanci), a dále jakýkoli obrázek (v panelu nástrojů „Souprava nástrojů“ tlačítko Obrázek, vymezíme místo v návrhu sestavy, v dialogu pro nalezení souboru vybereme jakýkoli existující obrázek). Ve formátové vlastnosti „Obrázek“ nového objektu obrázku následně vymažeme hodnotu, tj. jméno souboru s cestou. Objekt obrázek v sestavě je nyní nevázaný. Dále změňte formátovou vlastnost „Typ obrázku“ na „propojený“. Skutečné hodnoty vlastnosti „Obrázek“ musí být dosazovány až při tisku, což lze zajistit událostní vlastností sekce sestavy. V sestavě „Top 10 zaměstnanců“ v událostní vlastnosti „Při tisku“ sekce Tělo najdete proceduru, která aktualizaci obrázku zajistí, příslušný příkaz je: Me![Obrázek20].Picture = "C:/Program Files/Microsoft Office/Office10/Samples/" & Me![Foto] jméno objektu s obrázkem v návrhu sestavy
cesta ke všem obrázkům
název textového pole v sestavě obsahujícího jméno souboru obrázku
61
Jako parametr je vyhodnoceno vše, co není jménem pole zdroje záznamů či známým jménem proměnné.
62
Účel této sestavy je zobrazit maximum součtů, což nelze jinak, než nejprve spočítat součty, a pak z nich maximum.
69
13 Akční dotazy V kapitole Seskupovací dotazy v sekci Agregace First, Last jsou vyloženy specifické důvody pro použití vytvářecího dotazu k uložení mezivýsledku jako tabulky do databáze. Ve snaze získávat informace z databází mohou existovat i jiné důvody k tomu, abychom vytvářeli nové tabulky či v již existujících tabulkách měnili data.
13.1 Vytvářecí dotazy Vytvářecí dotazy slouží k tomu, aby výsledek dotazu byl uložen jako tabulka v databázi. Můžeme je použít například tehdy, když chceme pro své účely upravit a uložit data z nějakého externího zdroje, k nimž máme přístup nějakým způsobem ztížený. Z běžného výběrového dotazu uděláme vytvářecí dotaz tím, že ho přepneme na typ „Vytvářecí dotaz“ 63. Po tomto se hned objeví dialog „Vytvořit tabulku“, v němž zadáme název tabulky, kterou má dotaz vytvořit. Návrh dotazu děláme stejně jako u běžných dotazů, přepnutí zobrazení na Zobrazení datového listu nám ukáže náhled výsledku. Skutečnou akci, tj. vytvoření nové tabulky, spustíme volbou „Spustit“ 64 . Vytvořme si například pomocnou tabulku zemí, využijme pole Země z tabulky Zákazníci:
13.2 Přidávací dotazy Přidávací dotazy slouží k tomu, aby výsledek dotazu byl připojen do již existující tabulky jako nové řádky. Postup je stejný jako v předchozím případě, pouze typ dotazu zvolíme „Přidávací dotaz“, a vybereme tabulku, do níž se má výsledek připojit. Pokračujme v našem příkladu, a přidejme do tabulky Země další země z pole Země z tabulky Dodavatelé, které v tabulce Země ještě nejsou (srovnej eventuelně kapitolu 9 Dotazy na to co v databázi není) – viz obrázek nahoře na další straně. Přidávací akční dotazy se provedou volbou „Spustit“ náhled nových přidávaných řádků.
, zobrazení datového listu ukazuje pouze
63
Menu Dotaz nebo přepínač Typ dotazu v panelu nástrojů Návrh dotazu.
64
Menu Dotaz nebo tlačítko Spustit
v panelu nástrojů Návrh dotazu.
70
Dokončeme náš příklad tím, že otevřeme návrh tabulky Země a přidáme pole Kontinent typu text délky 15:
a na kartě Vyhledávání tohoto pole vyjmenujeme možné hodnoty:
Nakonec otevřeme datový list tabulky Země a vybereme správný kontinent pro každou Zemi: Země Argentina Belgie Brazílie Dánsko Finsko Francie Irsko Itálie Kanada Mexiko Německo Norsko Polsko
Kontinent Jižní Amerika Evropa Jižní Amerika Evropa Evropa Evropa Evropa Evropa Severní Amerika Severní Amerika Evropa Evropa Evropa
Země Kontinent Portugalsko Evropa Rakousko Evropa Španělsko Evropa Švédsko Evropa Švýcarsko Evropa USA Severní Amerika Velká Británie Evropa Venezuela Jižní Amerika Austrálie Austrálie Japonsko Asie Nizozemí Evropa Singapur Asie
71
Tabulku Země můžeme použít pro počítání souhrnů obchodu v jednotlivých kontinentech… Můžeme ji v budoucnu příležitostně aktualizovat přidávacím dotazem podobným našemu příkladu, když přibudou nové země ve zdrojové databázi. Můžete chtít tabulku Země uspořádat abecedně podle zemí. Pokud to uděláte v datovém listu, změní se pouze váš pohled na data. Můžete také podle polí Země a Kontinent indexovat – v návrhu tabulky zadat u těchto polí „indexovat“. Pokud chcete vytvořit uspořádanou kopii tabulky, nejprve v okně Databáze tuto tabulku zkopírujte a v dialogu při kopírování nastavte „Jen struktura“. Pak přidávacím dotazem se zadaným řazením do nové tabulky přidejte řádky z tabulky Země… Všechny tyto možnosti ovlivňují efektivitu databáze, kterážto problematika vysoce přesahuje rámec těchto skript.
13.3 Odstraňovací dotazy Odstraňování jednotlivých záznamů je možné v datovém listu tabulky, když tyto záznamy vyznačíme a klávesou Delete požádáme o jejich odstranění, následující potvrzující dialog potvrdíme. Toto je nejbezpečnější způsob, neboť mažeme to, co vidíme. Smazané záznamy již nelze obnovit zpět, data jsou nenávratně ztracena! Odstraňovat záznamy lze i hromadně, byť je tato záležitost nebezpečná a vyžaduje vysokou ostražitost. K tomuto účelu slouží odstraňovací dotazy. Ukažme si to na příkladu odstranění starých dat. Zálohujte si vzorovou databázi Northwind, odstraňte atribut jen pro čtení. Ve svém projektu vytvořte dotaz typu „Odstraňovací dotaz“: Spusťte
tento dotaz, potvrďte varující dialog.
Podívejte se následně do tabulky Objednávky: jsou zde záznamy pouze z roku 1998, záznamy začínají objednávkou č. 10808. Podívejte se i do tabulky Rozpis objednávek, i zde začínají záznamy číslem objednávky 10808! To je způsobeno vlastností relace mezi těmito tabulkami „Odstranění souvisejících polí v kaskádě“, která je ve vašem projektu je nepřístupná, ale můžete si ji prohlédnout přímo v databázi Northwind.
Vidíte, že jste z projektu připojeného k databázi odstranili v této databázi data, dokonce více dat, než jste nejspíše zamýšleli. Po tomto pokusu vraťte databázi Northwind do původního stavu pomocí záložní kopie. Ukažme si ještě jeden příklad, výrazně odstrašující. Vytvořte si takovýto „archiv“ (kód je dostupný též na nb.vse.cz/~palovska/uzida/): SELECT Výrobky.ČísloDodavatele, Dodavatelé.Firma, Dodavatelé.KontaktníOsoba, Dodavatelé.Funkce, Dodavatelé.Adresa, Dodavatelé.Město, Dodavatelé.Oblast, Dodavatelé.PSČ, Dodavatelé.Země, Dodavatelé.Telefon, Dodavatelé.Fax, Dodavatelé.DomovskáStránka, [Rozšířené podrobnosti objednávek].ČísloVýrobku, Výrobky.NázevVýrobku, Výrobky.ČísloKategorie, Výrobky.MnožstvíVJednotce, Výrobky.JednotkováCena, Výrobky.JednotkyNaSkladě, Výrobky.ObjednánoJednotek, Výrobky.MinimálníÚroveň, Výrobky.NákupUkončen, [Rozšířené podrobnosti objednávek].JednotkováCena, [Rozšířené podrobnosti objednávek].Množství, [Rozšířené podrobnosti objednávek].Sleva, [Rozšířené podrobnosti objednávek].VýslednáCena, [Rozšířené podrobnosti objednávek].ČísloObjednávky, Objednávky.ČísloZaměstnance, Objednávky.DatumObjednávky, Objednávky.DodatDne, Objednávky.DatumOdeslání, Objednávky.Přeprava, Objednávky.Dopravné, Objednávky.JménoPříjemce, Objednávky.AdresaPříjemce, Objednávky.MěstoPříjemce, Objednávky.[Oblast příjemce], Objednávky.PSČPříjemce, Objednávky.ZeměPříjemce, Objednávky.KódZákazníka, Zákazníci.Firma,
72
Zákazníci.KontaktníOsoba, Zákazníci.Funkce, Zákazníci.Adresa, Zákazníci.Město, Zákazníci.Oblast, Zákazníci.PSČ, Zákazníci.Země, Zákazníci.Telefon, Zákazníci.Fax INTO archiv FROM Zákazníci INNER JOIN (Dodavatelé INNER JOIN (([Rozšířené podrobnosti objednávek] INNER JOIN Výrobky ON [Rozšířené podrobnosti objednávek].ČísloVýrobku = Výrobky.ČísloVýrobku) INNER JOIN Objednávky ON [Rozšířené podrobnosti objednávek].ČísloObjednávky = Objednávky.ČísloObjednávky) ON Dodavatelé.ČísloDodavatele = Výrobky.ČísloDodavatele) ON Zákazníci.KódZákazníka = Objednávky.KódZákazníka;
Tento archiv obsahuje téměř všechna data z databáze Northwind, pouze data o zaměstnancích a přepravcích jsme ponechali v samostatných tabulkách. Odstraníme nyní z „archivu“ stará data: Podívejte se nyní do archivu, zkusme nalézt výrobky „Genen Shouyu“ nebo „Tofu“! Nejsou tam, všechny informace o nich jsme ztratili! Zkuste nalézt zákazníky „Centro comercial Moctezuma“, „Familia Arquibaldo“, „FISSA Fabrica Inter. Salchichas S.A.“, „Folies gourmandes“, „GROSELLA-Restaurante“, „Hungry Coyote Import Store“, „Lazy K Kountry Store“, „Mere Paillarde“, „Paris spécialités“, „Vins et alcools Chevalier“ – ani ty tam nenajdeme! Vidíme, že jsme ztratili více dat, než jsme asi zamýšleli. Kdybychom odstranili ještě mladší data, do konce března 1998, ztratili bychom dokonce data o dodavateli „Zaanse Snoepfabriek“! Tyto nehody byly zapříčiněny špatným návrhem „archivu“. Protože je někdy skutečně potřeba stará či mylná data odstranit, bývá toto často řešeno pomocí umělého pole typu např. ano/ne, které říká, zda je záznam ještě platný, či nikoli (viz např. pole NákupUkončen v tabulce Výrobky). Jiná možnost řešení je archivovat stará data někam jinam.
13.4 Aktualizační dotazy Poslední z akčních dotazů jsou aktualizační dotazy. Ty slouží k hromadné úpravě či opravě dat v nějaké tabulce. I zde je třeba varovat, že neuváženým použitím takového nástroje můžete nenávratně ztratit data. Ukažme si komplexní příklad, ve kterém použijeme některé postupy dříve vyložené, i aktualizační dotaz. Představte si, že jste z externího zdroje získali data v textovém formátu (uložte si někam soubor nb.vse.cz/~palovska/uzida/exdata.txt). Podívejte se, že jednotlivá pole jsou v našem případě oddělena tabelátory, záznamy jsou ukončeny znaky konce řádků. Z těchto dat si chcete vytvořit tabulku databáze MS Access. Nejprve do projektu MS Access tato externí data propojíme – v menu zvolte Soubor-Načíst externí data-Propojit tabulky, v dialogu „Propojit“ nastavte „Soubory typu“ na „textové soubory“ a nalezněte soubor s daty. Spustí se průvodce propojením textu, který vám vždy ukazuje náhled dat po vaší volbě. V prvním kroku nastavte formát „s oddělovači“, v dalším kroku vyberte oddělovač „Tabelátor“ (data v našem příkladu neobsahují názvy polí, texty nejsou ničím ohraničeny), v dalším kroku pojmenujte jednotlivá pole (vždy vyberte pole v náhledu dole a v políčku Název pole vepište jméno pole) : Příjmení, Jméno, DatumNarození, Adresa, Město, Oblast, PSČ, Země, Telefon. V posledním kroku pojmenujte propojenou tabulku např. Exdata. Jste upozorněni, že propojení proběhlo úspěšně. Na propojenou tabulku se nyní podívejte. Podívejte se i na návrh této tabulky – všechna textová pole mají délku 255 znaků. Takový návrh nemusí vyhovovat, navíc můžeme chtít přidat ještě jiná pole, například Mobil, TelefonDoPráce, Poznámka. Nechte návrh tabulky Exdata otevřený, a v okně Databáze zvolte „Vytvořit tabulku v návrhovém zobrazení“. 73
V okně návrhu tabulky Exdata vyznačte všechna pole (například volbou v menu Úpravy-Vybrat vše) a zkopírujte do schránky (Ctrl+C), přejděte do okna návrhu nové tabulky, a vložte ze schránky (Ctrl+V). Opravte návrh polí takto (všimněte si změny jmen na česká): Příjmení, text , délka 20 Jméno, text, délka 10 DatumNarození, datum a čas, formát datum (krátké) Adresa, text, délka 60 Město, text, délka 15, indexovat ano (duplicita povolena) Oblast, text, délka 15 PSČ, text, délka 10 Země, text, délka 15, indexovat ano (duplicita povolena) TelefonDomů, text, délka 24 Přidejte další pole: Mobil, text, délka 24 TelefonDoPráce, text, délka 24 Poznámka, memo Uložte novou tabulku pod jménem Osoby, dialog, který vás upozorní na chybějící primární klíč, potvrďte Ano – do návrhu vaší tabulky je nyní přidáno pole ID. Zavřete okna návrhu tabulek Osoby i Exdata. Do nové tabulky Osoby přidáme data z externího zdroje přidávacím dotazem (všimněte si přejmenování polí):
Podívejte se pak do tabulky Osoby. V polích, která v přidávacím dotazu nebyla, jsou prázdná místa, pouze primární klíč ID se automaticky vyplnil. Pokud příště dostaneme další externí data ve stejném formátu, stačí je uložit na stejné místo pod stejným jménem jako původní soubor. Propojená tabulka Exdata ve vašem projektu bude ukazovat nová data. Můžete tato nová data připojit do tabulky Osoby, ale nebudeme chtít mít duplicitní záznamy. Usoudíme například, že pokud je stejné Příjmení, Jméno a DatumNarození, jedná se o stejnou osobu. Upravte přidávací dotaz tak, že bude sloužit pro všechny další případy:
74
Nakonec si všimneme, že telefony máme bez předvolby státu. Opravíme to aktualizačními dotazy:
nová hodnota v tomto poli v tomto poli se žádná změna dělat nebude kritéria výběru řádků, ve kterých se má provést změna
(Jako všechny akční dotazy, i aktualizační se provedou volbou „Spustit“
.)
Oprava TelefonDomů do Velké Británie: Ostatní telefony budou opraveny analogicky, pro každou zemi zvlášť. (Bylo by možno vytvořit i jeden komplexní aktualizační dotaz s velmi složitými výrazy.)
Ukažme si nakonec ještě jeden příklad užití aktualizačního dotazu, aplikovaného na odstrašující „archiv“ (viz předchozí sekce 13.3 Odstraňovací dotazy). Představme si, že zákazník „Save-a-lot Markets“ změnil telefon. Tuto změnu nyní musíme provést v celém archivu. Můžeme použít aktualizační dotaz:
sem vepíšete nový telefon
Kdybyste v tomto dotazu udělali chybu a spustili ho, data budou nenávratně změněna. Tento příklad ukazuje, že v nevhodně strukturované databázi jsme pro běžnou aktualizaci údajů nuceni použít zbytečně náročnou a nebezpečnou akci.
75
14 Cvičení V této kapitole jsou uvedeny některé úlohy vhodné k procvičování jednotlivých základních technik. Tématicky zaměřené úlohy integrující znalosti všech technik jsou pak předmětem další kapitoly. Pro kontrolu je za každým zadáním uvedeno, jaký má být výsledek. Řešení ve formě SQL-kódů naleznete na konci této kapitoly.
14.1 Jednoduché dotazy Cvičení 1. Ve kterých zemích se prodávají výrobky dodavatele Zaanse Snoepfabriek? Země příjemce (12 zemí): Brazílie, Francie, Itálie, Kanada, Mexiko, Německo, Portugalsko, Rakousko, Španělsko, Švédsko, USA, Velká Británie Cvičení 2. Kteří zákazníci požadovali v roce 1998 lhůtu dodání 14 dní? Firma (21firem): Cactus Comidas para llevar, Rancho grande, Comércio Mineiro, Vaffeljernet, Bon app', Bottom-Dollar Markets, Drachenblut Delikatessen, Frankenversand, Königlich Essen, QUICK-Stop, Santé Gourmet, Wolski Zajazd, Bólido Comidas preparadas, Richter Supermarkt, Rattlesnake Canyon Grocery, Save-a-lot Markets, Split Rail Beer & Ale, Around the Horn, B's Beverages, Eastern Connection, HILARIÓN-Abastos Cvičení 3. Které výrobky se prodávaly v posledním měsíci (květen 1998)? NázevVýrobku (40 výrobků): Chai, Chang, Aniseed Syrup, Chef Anton's Cajun Seasoning, Grandma's Boysenberry Spread, Uncle Bob's Organic Dried Pears, Northwoods Cranberry Sauce, Ikura, Queso Cabrales, Queso Manchego La Pastora, Konbu, Tofu, Pavlova, Alice Mutton, Teatime Chocolate Biscuits, Sir Rodney's Marmalade, Tunnbröd, Guaraná Fantástica, Rössle Sauerkraut, Nord-Ost Matjeshering, Gorgonzola Telino, Mascarpone Fabioli, Sasquatch Ale, Chartreuse verte, Jack's New England Clam Chowder, Ipoh Coffee, Spegesild, Valkoinen suklaa, Filo Mix, Perth Pasties, Tourtiere, Pâté chinois, Camembert Pierrot, Wimmers gute Semmelknödel, Louisiana Hot , Spiced Okra, Scottish Longbreads, Röd Kaviar, Rhönbräu Klosterbier, Lakkalikööri, Original Frankfurter grüne Soße
14.2 Dotazy se souhrny Cvičení 4. Spočítejte celkovou tržbu za zboží jednotlivých dodavatelů! Dodavatel Aux joyeux ecclésiastiques
Celkem tržba 4 161 984,37 Kč
Bigfoot Breweries
559 780,00 Kč
Cooperativa de Quesos 'Las Cabras'
628 985,75 Kč
Escargots Nouveaux
147 041,87 Kč
Exotic Liquids
408 899,00 Kč
…
…
Cvičení 5. Spočítejte celkovou tržbu za zboží jednotlivých dodavatelů v roce 1998! Dodavatel Aux joyeux ecclésiastiques
Celkem tržba 1 947 008,75 Kč
Bigfoot Breweries
232 952,50 Kč
Cooperativa de Quesos 'Las Cabras'
198 988,75 Kč
Escargots Nouveaux
60 685,00 Kč
Exotic Liquids
157 486,25 Kč
…
…
76
14.2.1 Křížové dotazy Cvičení 6. Spočítejte tržby za zboží jednotlivých dodavatelů v jednotlivých letech! Dodavatel
1996
Exotic Liquids
1997
1998
75 449,00 Kč 175 963,75 Kč 157 486,25 Kč
New Orleans Cajun Delights
166 613,00 Kč 447 992,24 Kč 164 594,51 Kč
Grandma Kelly's Homestead
129 800,00 Kč 398 657,50 Kč 520 375,00 Kč
Tokyo Traders
74 036,00 Kč 446 787,50 Kč 242 335,00 Kč
Cooperativa de Quesos 'Las Cabras' 48 816,00 Kč 381 181,00 Kč 198 988,75 Kč …
…
…
…
Cvičení 7. Pro každého zaměstnance spočítejte tržbu v jednotlivých letech! Zaměstnanec
1996
1997
1998
Davolio, Nancy
894 112,87 Kč 2 328 701,90 Kč 1 579 875,30 Kč
Fuller, Andrew
543 926,50 Kč 1 761 103,50 Kč 1 858 413,87 Kč
Leverling, Janet
455 599,00 Kč 2 700 653,87 Kč 1 914 068,20 Kč
Peacock, Margaret 1 248 627,88 Kč 3 220 244,77 Kč 1 353 398,50 Kč Buchanan, Steven
458 285,50 Kč
767 911,66 Kč
495 797,37 Kč
Suyama, Michael
416 065,12 Kč 1 078 159,23 Kč
353 603,87 Kč
King, Robert
380 321,50 Kč 1 511 779,87 Kč 1 218 121,99 Kč
Callahan, Laura
556 003,00 Kč 1 400 815,36 Kč 1 214 738,56 Kč
Dodsworth, Anne
247 362,87 Kč
657 759,81 Kč 1 027 578,95 Kč
Cvičení 8. Spočítejte celkové tržby v jednotlivých zemích (ZeměPříjemce) rozdělené podle kategorií! Země příjemce
Celkem
Cukrovinky
Koření
Argentina
202 977,50 53 377,50 Kč Kč
22 675,00 Kč
Belgie
845 621,37 Kč
61 369,87 Kč
Brazílie
2 673 144,41 Kč
186 989,50 Kč
Maso / Drůbež
Mléčné výrobky
Mořské produkty
Obilné výrobky
Nápoje
28 587,50 Kč 15 162,50 Kč
Plodiny
44 950,00 9 750,00 Kč Kč
53 462,50 Kč
216 500,00 30 352,50 Kč 135 717,00 Kč Kč
285 902,56 279 519,00 189 077,85 Kč Kč Kč
392 011,25 322 178,38 Kč 929 836,12 Kč Kč
80 650,00 Kč
28 475,00 Kč 80 580,00 Kč
153 038,75 121 580,50 Kč Kč
Dánsko
816 525,56 64 786,50 Kč 108 128,75 Kč Kč
79 062,50 66 107,50 Kč 96 431,56 Kč 300 642,50 2 493,75 Kč Kč Kč
98 872,50 Kč
Finsko
470 251,31 25 626,25 Kč Kč
46 825,00 Kč
83 519,50 Kč
…
…
…
…
…
138 952,75 31 182,50 Kč Kč …
54 560,00 Kč
61 925,00 Kč
27 660,31 Kč
…
…
…
…
Cvičení 9. Pro každého zákazníka spočítejte jeho nákupy v jednotlivých kategoriích výrobků v roce 1997! Firma
Celkem
Cukrovinky Koření
Maso / Drůbež
Mléčné výrobky
Mořské produkty
Nápoje
Obilné výrobky
Ana Trujillo Emparedados y helados
28 243,75 Kč
1 500,00 Kč
16 500,00 Kč
6 750,00 1 750,00 Kč Kč
Antonio Moreno Taquería
149 019,43 Kč
23 973,18 1 700,00 38 050,00 Kč Kč Kč
44 487,50 Kč
4 140,00 Kč 34 475,00 2 193,75 Kč Kč
Around the Horn
160 9 380,00 Kč 172,50 Kč
Berglunds snabbköp
346 225,37 Kč
Blauer See Delikatessen
26 995,00 2 000,00 Kč Kč
Blondel pere et fils
195 447,00 Kč
34 475,00 Kč
…
…
…
27 360,00 Kč
14 049,00 5 940,00 23 652,00 Kč Kč Kč
34 825,00 12 482,50 Kč 21 375,00 Kč Kč
…
…
77
1 743,75 Kč
28 250,00 26 500,00 Kč Kč
41 400,00 39 210,00 Kč 185 4 375,00 Kč 32 100,00 Kč 499,37 Kč Kč
3 725,00 7 350,00 Kč Kč 10 260,00 Kč
Plodiny
8 550,00 1 950,00 Kč Kč
46 962,50 36 536,50 Kč 19 098,00 Kč Kč …
…
…
3 420,00 Kč
48 115,00 Kč …
…
14.2.2 Výběr podle vypočteného souhrnu Cvičení 10. Vypište zaměstnance, kteří měli v posledním roce malou tržbu! (Nalezněte a vyberte vhodný limit.) (Limit např. 1 000 000,-Kč): Michael Suyama, Steven Buchanan Cvičení 11. Vyberte zákazníky, kteří v roce 1997 utratili více než 300 000 Kč! Firma (12 firem):, QUICK-Stop, Save-a-lot Markets, Ernst Handel, Mere Paillarde, Hungry Owl All-Night Grocers, Rattlesnake Canyon Grocery, Simons bistro, Berglunds snabbköp, HILARIÓN-Abastos, Folk och fä HB, Lehmanns Marktstand, Wartian Herkku Cvičení 12. Najděte výrobek roku 1997! (Za který byla největší tržba) NázevVýrobku: Côte de Blaye
14.2.3 Souhrny pro celou tabulku Cvičení 13. Jaká je celková hodnota zboží objednaného do skladu? (Počítejte souhrn JednotkováCena*JednotkyNaSkladě 65.) 359 437,50 Kč Cvičení 14. Jaká je průměrná jednotková cena výrobků? 721,66 Kč Cvičení 15. Jaká byla průměrná výše objednávky v roce 1997? 37 811,59 Kč Cvičení 16. Jaký byl průměrný výkon zaměstnanců v roce 1997? 1 714 125,55 Kč
14.2.4 Spojení několika souhrnů Cvičení 17. Jaký byl podíl celkového součtu dopravného a celkového součtu ceny zboží pro jednotlivé země (ZeměPříjemce)? Země příjemce SumOfDopravné SumOfMezisoučet podíl Argentina
14 964,50 Kč
202 977,50 Kč 7,37%
Švédsko
80 940,00 Kč
1 362 378,48 Kč 5,94%
184 787,50 Kč
3 200 095,95 Kč 5,77%
6 728,50 Kč
119 936,25 Kč 5,61%
16 088,25 Kč
286 809,06 Kč 5,61%
Rakousko Polsko Portugalsko …
…
…
řazeno sestupně podle podílu
…
Cvičení 18. Porovnejte tržby zaměstnanců v Severní Americe a ve zbytku světa! Zaměstnanec
bydlí v
v Severní Americe ve zbytku světa podíl
Fuller, Andrew
USA
861 387,50 Kč 3 302 056,37 Kč 26,09%
Callahan, Laura
USA
700 567,06 Kč 2 470 989,86 Kč 28,35%
Dodsworth, Anne
Velká Británie
454 787,87 Kč 1 477 913,76 Kč 30,77%
Buchanan, Steven Velká Británie Leverling, Janet
65
410 528,75 Kč 1 311 465,78 Kč 31,30%
USA
1 219 952,07 Kč 3 850 369,00 Kč 31,68%
Peacock, Margaret USA
1 456 174,62 Kč 4 366 096,53 Kč 33,35%
Suyama, Michael
Velká Británie
Davolio, Nancy
USA
King, Robert
Velká Británie
530 744,49 Kč 1 317 083,73 Kč 40,30% 1 442 755,25 Kč 3 359 934,82 Kč 42,94% 944 344,31 Kč 2 165 879,05 Kč 43,60%
V databázi Northwind není nákupní cena výrobků, takže počítáme prodejní cenu.
78
14.2.5 Agregační funkce pro čas Cvičení 19. Pro každého zákazníka nalezněte datum posledního nákupu! Kód zákazníka
Firma
PosledníNákup
ALFKI
Alfreds Futterkiste
ANATR
Ana Trujillo Emparedados y helados
9.4.1998
ANTON
Antonio Moreno Taquería
28.1.1998
AROUT
Around the Horn
10.4.1998
BERGS
Berglunds snabbköp
BLAUS
Blauer See Delikatessen
…
…
4.3.1998
4.3.1998 29.4.1998 …
Cvičení 20. Kteří zákazníci v posledním čtvrtletí (tj. od 1.4.1998) nic nenakoupili ? Firma (32 firem):, Ana Trujillo Emparedados y helados, Antonio Moreno Taquería, Berglunds snabbköp, Blondel pere et fils, Bólido Comidas preparadas, Centro comercial Moctezuma, Consolidated Holdings, Du monde entier, Familia Arquibaldo, Folies gourmandes, France restauration, Furia Bacalhau e Frutos do Mar, Galería del gastrónomo, GROSELLA-Restaurante, Hungry Coyote Import Store, Island Trading, La corne d'abondance, Laughing Bacchus Wine Cellars, Lazy K Kountry Store, Let's Stop N Shop, Magazzini Alimentari Riuniti, Mere Paillarde, Morgenstern Gesundkost, Océano Atlántico Ltda., Seven Seas Imports, Split Rail Beer & Ale, Toms Spezialitäten, Tradiçăo Hipermercados, Trail's Head Gourmet Provisioners, Victuailles en stock, Vins et alcools Chevalier, Wellington Importadora
14.2.6 Počet různých hodnot Cvičení 21. V kolika různých zemích letos (1998) jednotliví zaměstnanci prodávali? Zaměstnanec
PočetZemí
Davolio, Nancy
17
Fuller, Andrew
17
Leverling, Janet
16
Peacock, Margaret
16
Buchanan, Steven
9
Suyama, Michael
9
King, Robert
15
Callahan, Laura
13
Dodsworth, Anne
13
14.3 Kritérium propojením Cvičení 22. Ve kterých zemích se v roce 1997 prodával výrobek tohoto roku? Země příjemce Brazílie Dánsko Kanada Mexiko Německo Švédsko USA
79
14.4 Řešení cvičení Zde naleznete SQL-kódy pro cvičení z této kapitoly. Tyto kódy jsou dostupné též na internetové adrese nb.vse.cz/~palovska/uzida/ Odtud je můžete kopírovat do Zobrazení SQL okna dotazů. Cvičení 1. SELECT DISTINCT Objednávky.ZeměPříjemce FROM (Dodavatelé INNER JOIN Výrobky ON Dodavatelé.ČísloDodavatele = Výrobky.ČísloDodavatele) INNER JOIN (Objednávky INNER JOIN [Rozpis objednávek] ON Objednávky.ČísloObjednávky = [Rozpis objednávek].ČísloObjednávky) ON Výrobky.ČísloVýrobku = [Rozpis objednávek].ČísloVýrobku WHERE (((Dodavatelé.Firma)="Zaanse Snoepfabriek"));
Cvičení 2. SELECT DISTINCT Zákazníci.Země, Zákazníci.Firma FROM Zákazníci INNER JOIN Objednávky ON Zákazníci.KódZákazníka = Objednávky.KódZákazníka WHERE (((Year([DatumObjednávky]))=1998) AND (([DodatDne]-[DatumObjednávky])=14)) ORDER BY Zákazníci.Země, Zákazníci.Firma;
Cvičení 3. SELECT DISTINCT Výrobky.ČísloVýrobku, Výrobky.NázevVýrobku FROM Výrobky INNER JOIN (Objednávky INNER JOIN [Rozpis objednávek] ON Objednávky.ČísloObjednávky = [Rozpis objednávek].ČísloObjednávky) ON Výrobky.ČísloVýrobku = [Rozpis objednávek].ČísloVýrobku WHERE (((Objednávky.DatumObjednávky) Between #5/1/1998# And #5/31/1998#));
Cvičení 4. SELECT Dodavatelé.Firma AS Dodavatel, Sum([Rozšířené podrobnosti objednávek].VýslednáCena) AS SumVýslednáCena FROM Dodavatelé INNER JOIN (Výrobky INNER JOIN [Rozšířené podrobnosti objednávek] ON Výrobky.ČísloVýrobku = [Rozšířené podrobnosti objednávek].ČísloVýrobku) ON Dodavatelé.ČísloDodavatele = Výrobky.ČísloDodavatele GROUP BY Dodavatelé.Firma, Dodavatelé.ČísloDodavatele;
Cvičení 5. SELECT Dodavatelé.Firma AS Dodavatel, Sum([Rozšířené podrobnosti objednávek].VýslednáCena) AS SumVýslednáCena FROM Dodavatelé INNER JOIN ((Výrobky INNER JOIN [Rozšířené podrobnosti objednávek] ON Výrobky.ČísloVýrobku = [Rozšířené podrobnosti objednávek].ČísloVýrobku) INNER JOIN Objednávky ON [Rozšířené podrobnosti objednávek].ČísloObjednávky = Objednávky.ČísloObjednávky) ON Dodavatelé.ČísloDodavatele = Výrobky.ČísloDodavatele WHERE (((Objednávky.DatumObjednávky) Between #1/1/1998# And #12/31/1998#) AND ((Year([DatumObjednávky]))=1998)) GROUP BY Dodavatelé.Firma, Dodavatelé.ČísloDodavatele;
Cvičení 6. TRANSFORM Sum([Rozšířené podrobnosti objednávek].VýslednáCena) AS SumVýslednáCena SELECT Dodavatelé.ČísloDodavatele, Dodavatelé.Firma AS Dodavatel FROM Dodavatelé INNER JOIN ((Výrobky INNER JOIN [Rozšířené podrobnosti objednávek] ON Výrobky.ČísloVýrobku = [Rozšířené podrobnosti objednávek].ČísloVýrobku) INNER JOIN Objednávky ON [Rozšířené podrobnosti objednávek].ČísloObjednávky = Objednávky.ČísloObjednávky) ON Dodavatelé.ČísloDodavatele = Výrobky.ČísloDodavatele
80
GROUP BY Dodavatelé.ČísloDodavatele, Dodavatelé.Firma PIVOT Year([DatumObjednávky]);
Cvičení 7. TRANSFORM Sum([Mezisoučty objednávek].Mezisoučet) AS [Sum Of Mezisoučet] SELECT Objednávky.ČísloZaměstnance FROM Objednávky INNER JOIN [Mezisoučty objednávek] ON Objednávky.ČísloObjednávky = [Mezisoučty objednávek].ČísloObjednávky GROUP BY Objednávky.ČísloZaměstnance PIVOT Year([DatumObjednávky]);
Cvičení 8. Řešeno dvěma kroky, kvůli rychlosti seskupování podle ČísloKategorie. (Samozřejmě je možno řešit i jediným křížovým dotazem.) Mezivýsledek, uložit jako cvičení08a: SELECT Kategorie.ČísloKategorie, Kategorie.NázevKategorie, Objednávky.ZeměPříjemce, Sum([Rozšířené podrobnosti objednávek].VýslednáCena) AS SumVyslednaCena FROM Kategorie INNER JOIN (Výrobky INNER JOIN (Objednávky INNER JOIN [Rozšířené podrobnosti objednávek] ON Objednávky.ČísloObjednávky=[Rozšířené podrobnosti objednávek].ČísloObjednávky) ON Výrobky.ČísloVýrobku=[Rozšířené podrobnosti objednávek].ČísloVýrobku) ON Kategorie.ČísloKategorie=Výrobky.ČísloKategorie GROUP BY Kategorie.ČísloKategorie, Kategorie.NázevKategorie, Objednávky.ZeměPříjemce;
a pak další krok: TRANSFORM Sum(cvičení08a.SumVyslednaCena) AS SumOfSumVyslednaCena SELECT cvičení08a.ZeměPříjemce AS Výraz1, Sum(cvičení08a.SumVyslednaCena) AS Celkem FROM cvičení08a GROUP BY cvičení08a.ZeměPříjemce PIVOT cvičení08a.NázevKategorie;
Cvičení 9. Oproti předchozímu příkladu je tento řešen v jediném kroku. Toto snadnější řešení je někdy pomalejší, někdy ho nelze vůbec použít. (Sloupec KódZákazníka se v datovém listu následně skryje.) TRANSFORM Sum([Rozšířené podrobnosti objednávek].VýslednáCena) AS SumOfVýslednáCena SELECT Zákazníci.KódZákazníka, Zákazníci.Firma, Sum([Rozšířené podrobnosti objednávek].VýslednáCena) AS Celkem FROM Zákazníci INNER JOIN (Kategorie INNER JOIN (([Rozšířené podrobnosti objednávek] INNER JOIN Objednávky ON [Rozšířené podrobnosti objednávek].ČísloObjednávky = Objednávky.ČísloObjednávky) INNER JOIN Výrobky ON [Rozšířené podrobnosti objednávek].ČísloVýrobku = Výrobky.ČísloVýrobku) ON Kategorie.ČísloKategorie = Výrobky.ČísloKategorie) ON Zákazníci.KódZákazníka = Objednávky.KódZákazníka WHERE (((Year([DatumObjednávky]))=1997)) GROUP BY Zákazníci.KódZákazníka, Zákazníci.Firma PIVOT Kategorie.NázevKategorie;
Cvičení 10. SELECT Objednávky.ČísloZaměstnance, Zaměstnanci.Funkce, Sum([Mezisoučty objednávek].Mezisoučet) AS CelkemTržba FROM Zaměstnanci INNER JOIN (Objednávky INNER JOIN [Mezisoučty objednávek] ON Objednávky.ČísloObjednávky = [Mezisoučty objednávek].ČísloObjednávky) ON Zaměstnanci.ČísloZaměstnance = Objednávky.ČísloZaměstnance WHERE (((Year([DatumObjednávky]))=1998))
81
GROUP BY Objednávky.ČísloZaměstnance, Zaměstnanci.Funkce HAVING (((Sum([Mezisoučty objednávek].Mezisoučet))<1000000)) ORDER BY Sum([Mezisoučty objednávek].Mezisoučet) DESC;
Cvičení 11. SELECT Zákazníci.KódZákazníka, Zákazníci.Firma, Sum([Mezisoučty objednávek].Mezisoučet) AS Nákup1997 FROM Zákazníci INNER JOIN (Objednávky INNER JOIN [Mezisoučty objednávek] ON Objednávky.ČísloObjednávky = [Mezisoučty objednávek].ČísloObjednávky) ON Zákazníci.KódZákazníka = Objednávky.KódZákazníka WHERE (((Year([DatumObjednávky]))=1997)) GROUP BY Zákazníci.KódZákazníka, Zákazníci.Firma HAVING (((Sum([Mezisoučty objednávek].Mezisoučet))>300000)) ORDER BY Sum([Mezisoučty objednávek].Mezisoučet) DESC;
Cvičení 12. SELECT TOP 1 Výrobky.ČísloVýrobku, Výrobky.NázevVýrobku, Sum([Rozšířené podrobnosti objednávek].VýslednáCena) AS SumOfVýslednáCena FROM Výrobky INNER JOIN (Objednávky INNER JOIN [Rozšířené podrobnosti objednávek] ON Objednávky.ČísloObjednávky = [Rozšířené podrobnosti objednávek].ČísloObjednávky) ON Výrobky.ČísloVýrobku = [Rozšířené podrobnosti objednávek].ČísloVýrobku WHERE (((Year([DatumObjednávky]))=1997)) GROUP BY Výrobky.ČísloVýrobku, Výrobky.NázevVýrobku ORDER BY Sum([Rozšířené podrobnosti objednávek].VýslednáCena) DESC;
Cvičení 13. SELECT Sum([JednotkováCena]*[ObjednánoJednotek]) AS objednáno FROM Výrobky;
Cvičení 14. SELECT Avg(Výrobky.JednotkováCena) AS průměrnáCena FROM Výrobky;
Cvičení 15. SELECT Avg([Mezisoučty objednávek].Mezisoučet) AS průměrnáObjednávka FROM [Mezisoučty objednávek] INNER JOIN Objednávky ON [Mezisoučty objednávek].ČísloObjednávky = Objednávky.ČísloObjednávky WHERE (((Year([DatumObjednávky]))=1997));
Cvičení 16. Řešeno dvěma kroky. První uložit jako cvičení16a: SELECT Sum([Mezisoučty objednávek].Mezisoučet) AS Tržba FROM Objednávky INNER JOIN [Mezisoučty objednávek] ON Objednávky.ČísloObjednávky = [Mezisoučty objednávek].ČísloObjednávky WHERE (((Year([DatumObjednávky]))=1997)) GROUP BY Objednávky.ČísloZaměstnance;
Druhý krok: SELECT Avg([cvičení16a].Tržba) AS AvgOfTržba FROM cvičení16a;
82
Cvičení 17. Tři kroky, první uložit jako cvičení17a: SELECT Objednávky.ZeměPříjemce, Sum(Objednávky.Dopravné) AS DopravnéCelkem FROM Objednávky GROUP BY Objednávky.ZeměPříjemce;
Druhý krok, uložit jako cviční17b: SELECT Objednávky.ZeměPříjemce, Sum([Rozšířené podrobnosti objednávek].VýslednáCena) AS TržbaCelkem FROM [Rozšířené podrobnosti objednávek] INNER JOIN Objednávky ON [Rozšířené podrobnosti objednávek].ČísloObjednávky = Objednávky.ČísloObjednávky GROUP BY Objednávky.ZeměPříjemce;
Třetí krok: SELECT cvičení17a.ZeměPříjemce, cvičení17a.DopravnéCelkem, cvičení17b.TržbaCelkem, [DopravnéCelkem]/[TržbaCelkem] AS podíl FROM cvičení17a INNER JOIN cvičení17b ON cvičení17a.ZeměPříjemce = cvičení17b.ZeměPříjemce ORDER BY [DopravnéCelkem]/[TržbaCelkem] DESC;
Je možné řešení i jediným krokem: SELECT Objednávky.ZeměPříjemce, Sum(Objednávky.Dopravné) AS SumOfDopravné, Sum([Mezisoučty objednávek].Mezisoučet) AS SumOfMezisoučet, Sum([Dopravné])/Sum([Mezisoučet]) AS podíl FROM [Mezisoučty objednávek] INNER JOIN Objednávky ON [Mezisoučty objednávek].ČísloObjednávky = Objednávky.ČísloObjednávky GROUP BY Objednávky.ZeměPříjemce ORDER BY Sum([Dopravné])/Sum([Mezisoučet]) DESC;
Ovšem takto by to bylo špatně!: SELECT Zákazníci.KódZákazníka, Zákazníci.Firma, Sum(Objednávky.Dopravné) AS SumOfDopravné, Sum([Rozšířené podrobnosti objednávek].VýslednáCena) AS SumOfVýslednáCena FROM Zákazníci INNER JOIN ([Rozšířené podrobnosti objednávek] INNER JOIN Objednávky ON [Rozšířené podrobnosti objednávek].ČísloObjednávky = Objednávky.ČísloObjednávky) ON Zákazníci.KódZákazníka = Objednávky.KódZákazníka GROUP BY Zákazníci.KódZákazníka, Zákazníci.Firma;
Cvičení 18. Řešeno třemi kroky, první uložit jako cvičení18a: SELECT Objednávky.ČísloZaměstnance, Sum([Mezisoučty objednávek].Mezisoučet) AS SeverníAmerika FROM [Mezisoučty objednávek] INNER JOIN Objednávky ON [Mezisoučty objednávek].ČísloObjednávky = Objednávky.ČísloObjednávky WHERE (((Objednávky.ZeměPříjemce) In ("USA","Kanada","Mexiko"))) GROUP BY Objednávky.ČísloZaměstnance;
Druhý krok, uložit jako cvičení18b: SELECT Objednávky.ČísloZaměstnance, Sum([Mezisoučty objednávek].Mezisoučet) AS ZbytekSvěta FROM [Mezisoučty objednávek] INNER JOIN Objednávky ON [Mezisoučty objednávek].ČísloObjednávky = Objednávky.ČísloObjednávky WHERE (((Objednávky.ZeměPříjemce) Not In ("USA","Kanada","Mexiko"))) GROUP BY Objednávky.ČísloZaměstnance;
Třetí krok: SELECT cvičení18a.ČísloZaměstnance, Zaměstnanci.Země, cvičení18a.SeverníAmerika, cvičení18b.ZbytekSvěta, [SeverníAmerika]/[ZbytekSvěta] AS podíl
83
FROM (cvičení18a INNER JOIN cvičení18b ON cvičení18a.ČísloZaměstnance = cvičení18b.ČísloZaměstnance) INNER JOIN Zaměstnanci ON cvičení18a.ČísloZaměstnance = Zaměstnanci.ČísloZaměstnance ORDER BY [SeverníAmerika]/[ZbytekSvěta];
Cvičení 19. SELECT Zákazníci.KódZákazníka, Zákazníci.Firma, Max(Objednávky.DatumObjednávky) AS PosledníNákup FROM Zákazníci INNER JOIN Objednávky ON Zákazníci.KódZákazníka = Objednávky.KódZákazníka GROUP BY Zákazníci.KódZákazníka, Zákazníci.Firma;
Cvičení 20. SELECT Zákazníci.KódZákazníka, Zákazníci.Firma, Max(Objednávky.DatumObjednávky) AS PosledníNákup FROM Zákazníci INNER JOIN Objednávky ON Zákazníci.KódZákazníka = Objednávky.KódZákazníka GROUP BY Zákazníci.KódZákazníka, Zákazníci.Firma HAVING (((Max(Objednávky.DatumObjednávky))<#3/31/1998#));
Jiná možnost je řešení je pomocí křížového dotazu, kde řádky tvoří zákazníci, sloupce čtvrtletí, hodnoty jsou tržby. Ve sloupci s posledním čtvrtletím pak hledáme prázdná políčka. Cvičení 21. Dva kroky, první uložit jako cvičení21a: SELECT DISTINCT Objednávky.ČísloZaměstnance, Zákazníci.Země FROM Zákazníci INNER JOIN Objednávky ON Zákazníci.KódZákazníka = Objednávky.KódZákazníka WHERE (((Year([DatumObjednávky]))=1998));
Druhý krok: SELECT cvičení21a.ČísloZaměstnance, Count(*) AS PočetZemí, Count(cvičení21a.Země) AS CountOfZemě FROM cvičení21a GROUP BY cvičení21a.ČísloZaměstnance;
Cvičení 22. Využije se cvičení12: SELECT DISTINCT Objednávky.ZeměPříjemce FROM Objednávky INNER JOIN ([Rozpis objednávek] INNER JOIN cvičení12 ON [Rozpis objednávek].ČísloVýrobku = cvičení12.ČísloVýrobku) ON Objednávky.ČísloObjednávky = [Rozpis objednávek].ČísloObjednávky WHERE (((Year([DatumObjednávky]))=1997));
84
15 Tématicky zaměřené úlohy V této kapitole jsou naznačeny postupy řešení některých problémových okruhů, pro něž jsou v databázi Řešení samostatné skupiny „Výjimky a závislosti“, „Zaměření zaměstnanců“, „Tendence nákupů“, „Průběh cen“, „Porovnání charakteristik“.
15.1 Výjimky a závislosti Tato skupina se dotýká tématiky hledání závislostí a nalézání výjimek. Toto je předmětem samostatné disciplíny nazývané dobývání znalostí z databází (data-mining), my zde pouze naznačíme uživatelské možnosti při použití dotazů do databáze. Máme-li nějakou domněnku o statistické závislosti hodnot v nějakých polích na hodnotách v jiných polích, nejlépe je vytvořit dotaz zobrazující všechna dotyčná pole, a podle polí, jež se domníváme, že ovlivňují ta druhá, odpověď seřadíme. Pak prohlížením výsledku posoudíme, zda závislost „vidíme“ či nikoli. V případě že se jedná o dvojici polí, může pomoci zobrazit výsledek do grafu. Můžeme také vypočítat statistický průměr hodnot jednoho pole ve skupině dané hodnotou v jiném poli. Výjimky se hledají ještě nesnadněji, musíme mít nejprve představu o pravidle, z něhož hledáme výjimku. Dotaz „Počty výrobků v objednávce“ je jediným ukázaným příkladem. Další skupiny úloh jsou zaměřeny konkrétně.
15.2 Zaměření zaměstnanců Tato skupina se zabývá otázkou, zde zákazníci mají „svého“ kontaktního zaměstnance, či zda naopak je kontakt konkrétního zaměstnance se zákazníkem čistě náhodný. Výsledek ukazuje dotaz „Počty zaměstnanců u zákazníků2“.
15.3 Tendence nákupů V této skupině je pojata hypotéza, že pokud nějaký zákazník opakuje nákup nějakého druhu výrobku, potom si ho oblíbil. Zajímá nás otázka, zda výrobky někým oblíbené jsou všeobecně více prodávány. – Dotazy 1-1 až 1-5. Další dotazy v této skupině přibírají do úvahy intervaly mezi jednotlivými nákupy stejného výrobku jedním zákazníkem. Ukázány jsou možnosti dotazovacího jazyka databází (1-6 až 1-9), a také je ukázáno, jak může být výhodné použít zpracování dat v tabulkovém kalkulátoru, a propojení výsledků zpět do databáze – výsledek dotazu 2-1 je načten do sešitu MS Excel „Opakování nákupů“, kde jsou vypočteny intervaly mezi nákupy. Tabulka „Intervaly“ propojuje vypočtená data z listu 2 v sešitu „Opakování nákupů“. Sešit „Opakování nákupů“ ve svém prvním listě ukazuje možnost filtrování dat v prostředí MS Excel. Toto poněkud omezuje možnost automatického aktualizování propojených dat, lépe by bylo filtrovat data až v databázovém programu (jak je tomu v případě podobné techniky ve skupině „Průběh cen“).
Dotaz 2-3 ukazuje stejný výsledek jako dotaz 1-8, jeho formulace je mnohem snazší. Navíc není problém doplnit další statistické hodnoty, jako průměr, odchylku… Dotaz 2-7 ukazuje prostřednictvím vnořeného datového listu nejucelenější pohled na problematiku opakování nákupů stejného druhu výrobku.
15.4 Průběh cen Tato skupina se zabývá průběhem cen výrobků v čase. Dotaz 1-1 vytváří uspořádaný pohled na průběh cen výrobků zjištěný z jednotlivých objednávek. Ukazuje se i možnost zobrazení průběhu 85
cen jednotlivého výrobku v grafu. Výsledek tohoto dotazu je načten do sešitu MS Excel „Průběh cen“. V sešitu „Průběh cen“ je pomocí průběžného porovnávání cen jednotlivého výrobku nalezeno vždy první a polední datum, kdy platila stejná cena (sloupce „Od“, „Do“). Řádky, v nichž je „Do“ nenulové, jsou ty, v nichž nalezneme potřebnou informaci o začátku a konci platnosti ceny. Celá tabulka ze sešitu „Průběh cen“ je propojena do databáze MS Access jako tabulka Ceny, jsou v ní tedy i „nepotřebné“ řádky. Ty jsou odfiltrovány kritériem v dotazu 1-8. Dotazy 1-2 a 1-3 ověřují informaci, že v jednom dni vždy platila jedna cena výrobku. Dotazy 1-4 a 1-5 ukazují, odkdy platí aktuální cena výrobků. Oba předchozí dotazy jsou založeny na předpokladu, že nedošlo k výkyvu ceny a s následným návratem k původní hodnotě. Dotaz 1-6 ukazuje, že u výrobku „Zaanse koeken“ to byl mylný předpoklad. Nejucelenější pohled na průběh cen výrobků ukazuje sestava „Průběh cen“, v níž se v grafech přizpůsobuje měřítko na svislé ose skutečným hodnotám cen, ale vodorovná osa času je pro všechny výrobky stejná.
15.5 Porovnání charakteristik Toto je nejsofistikovanější skupina, užití postupů v ní ukázaných připadá v úvahu ve velmi specifických úlohách. Obecně jde o to, že ze záznamů o činnostech entit můžeme odvodit určitou charakteristiku těchto entit, a tu pak s něčím porovnávat. Ve všech případech se využijí křížové dotazy. V dotazech 1-1 až 1-5 se zjišťuje, o jaké kategorie výrobků projevují zákazníci zájem. Dotaz 1-2 je pomocný pro dotaz 1-4, jenž vytváří pro každého zákazníka pole s binárním kódem jeho charakteristiky. Výsledek tohoto dotazuje uložen do tabulky char_zák, aby bylo možno vnořit jeho datový list do dotazu 1-5. Dotazy 2-1 až 2-3 se zabývají podobnou tématikou, tentokrát jde o to, se kterými zákazníky v USA přicházeli zaměstnanci do styku. Dotaz 2-1 na rozdíl od dotazu 1-2 připouští prázdné hodnoty ve výsledku, z čehož vyplynou následně problémy. Zatímco seskupení podle prázdných hodnot (v dotazu 2-2) nedělá problémy, porovnání dvou prázdných hodnot (dotaz 2-3) nedává výsledek „pravda“, ale „neznámý“, takže dva řádky s prázdnou hodnotou v propojovacích polích by nebyly propojeny! Pamatujte si: Null porovnáno s čímkoli dává neznámý výsledek, nikdy „pravda“, ani v případě Null=Null. Null hodnoty ve výrazech způsobí Null hodnotu výsledku (viz výrazy v sestavě „Souhrny podle zemí v jednotlivých letech“). Null hodnoty v agregovaných polích souhrnných dotazů nejsou problémem, agregace jsou počítány pouze z neprázdných hodnot.
86
Rejstřík agregační funkce, 30
propojení mezivýsledků, 47
datová kostka, 42
propojovací pole datových listů, 45
datový list, 10
přejmenování pole v dotazu, 22
datový typ, 6
přesný číselný typ, 11
jedinečné hodnoty, 21
relace, 9, 13
klíč tabulky, 6, 10
relační databáze, 6
kritérium Is Null, 54
řazení v datovém listu, 19
křížová tabulka, 33
schéma databáze, 9, 14
lupa, 23
SQL-kód, 19
mřížka dotazu, 17
titulek, 10
nepřesný číselný typ, 11
titulek pole, 22
odpověď na dotaz, 16
vlastnosti pole, 22
parametr dotazu, 26
vnořený datový list, 45
pole, 6
vzorová databáze Northwind, 5, 7
polospojení, 56
záznam, 6
pravidlo propojení, 56
zdroj záznamů sestavy, 62
87
