Příručka aplikace Base 7.3

Kapitola 5
Dotazy

 

Obecné informace o dotazech

Dotazy do databáze jsou nejmocnějším nástrojem, který máme k dispozici pro praktické využití databází. Mohou spojit data z různých tabulek, v případě potřeby vypočítat výsledky a rychle vyfiltrovat konkrétní záznam z velkého množství dat. Rozsáhlé internetové databáze, které lidé denně využívají, existují především proto, aby na základě promyšleného výběru klíčových slov poskytly uživateli rychlý a praktický výsledek z obrovského množství informací – včetně reklamy související s vyhledáváním, která lidi vybízí k nákupu.

Base používá jazyk SQL (Structured Query Language), vyslovovaný jako "sequel", který je v podporovaných databázích podobný. Dotazy lze zadávat jak v grafickém uživatelském rozhraní, tak přímo jako kód SQL. V obou případech se otevře okno, ve kterém můžeme dotaz vytvořit a v případě potřeby také opravit.

Vytváření dotazů pomocí dialogového okna Návrh dotazu

Vytváření dotazů pomocí Průvodce je stručně popsáno v kapitole 8 příručky Začínáme s LibreOffice, Začínáme s aplikací Base. Zde vysvětlíme přímé vytváření dotazů v Režimu návrhu.

V této kapitole budeme nadále používat ukázkovou databázi Media_without_Macros.odb. V hlavním okně databáze klepneme v části Databáze na ikonu Dotazy a poté v části Úlohy klepneme na Vytvořit dotaz v zobrazení návrhu. Zobrazí se dvě dialogová okna. Jedno z nich poskytuje základ pro vytvoření návrhu pohledu na dotaz; druhý slouží k přidání tabulek, pohledů nebo dotazů k aktuálnímu dotazu.

Protože náš jednoduchý formulář odkazuje na tabulku Loan, vysvětlíme si nejprve vytvoření dotazu pomocí této tabulky.

Z dostupných tabulek vybereme tabulku Úvěr. Toto okno umožňuje kombinovat více tabulek (a také pohledů a dotazů). Chceme-li vybrat tabulku, klepneme na její název a poté na tlačítko Přidat. Nebo dvakrát klepneme na název tabulky. Obě metody přidají tabulku do grafické oblasti dialogového okna Návrh dotazu (obrázek 2).

Po výběru všech potřebných tabulek klepneme na tlačítko Zavřít. V případě potřeby lze později přidat další tabulky a dotazy. Žádný dotaz však nelze vytvořit bez alespoň jedné tabulky, proto je třeba provést výběr na začátku.

Obrázek 2 zobrazuje základní rozdělení dialogového okna Návrh dotazu: v grafické oblasti jsou zobrazeny tabulky, které mají být s dotazem propojeny. Mohou být také zobrazeny jejich vzájemné relace ve vztahu k dotazu. Tabulková oblast slouží k výběru polí pro zobrazení nebo k nastavení podmínek týkajících se těchto polí.

Klepnutím na pole v prvním sloupci v tabulkové oblasti se zobrazí šipka dolů, jak je znázorněno na obrázku 3. Klepnutím na tuto šipku otevřeme rozevírací seznam dostupných polí. Formát je Název_tabulky.Název_pole – proto zde všechny názvy polí začínají slovem Loan.

Označení vybraného pole Loan.* má zvláštní význam. Zde můžeme jedním klepnutím přidat do dotazu všechna pole ze zdrojové tabulky. Použijeme-li toto označení polí se zástupným znakem * pro všechna pole, dotaz se od tabulky nerozezná.

Tip

Chceme-li rychle přenést všechna pole tabulky do dotazu, stačí klepnout na zobrazení tabulky v grafickém rozhraní (Loan.* výše).

Dvojitým klepnutím na pole se toto pole vloží do tabulkové oblasti na nejbližší volnou pozici.

Prvních pět polí tabulky Loan je vybráno podle obrázku 4. Dotazy v režimu návrhu lze vždy spustit jako testy klepnutím na tlačítko Spustit dotaz. Tím se nad grafickým zobrazením tabulky Loan se seznamem polí zobrazí tabulka s daty. Před uložením dotazu je vždy vhodné provést jeho testovací spuštění, aby bylo jasné, zda dotaz skutečně dosáhne svého cíle. Logická chyba často zabrání tomu, aby dotaz získal vůbec nějaká data. V jiných případech se může stát, že se zobrazí právě ty záznamy, které jsme chtěli vyloučit.

Dotaz, který v základní databázi vyvolá chybové hlášení, nelze v zásadě uložit, dokud není chyba opravena.

Ve výše uvedeném testu věnujeme zvláštní pozornost prvnímu sloupci výsledku dotazu. Značka aktivního záznamu (zelená šipka) se vždy zobrazuje na levé straně tabulky, zde ukazuje na první záznam jako na aktivní záznam. Zatímco první pole prvního záznamu na obrázku 5  je zvýrazněno, odpovídající pole na obrázku 6  zobrazuje pouze čárkovaný okraj. Zvýraznění označuje, že toto pole lze upravit. Jinými slovy, záznamy jsou editovatelné. Čárkovaný rámeček označuje, že toto pole nelze upravovat. Obrázek 5 obsahuje také další řádek pro zadání nového záznamu, přičemž pole ID je již označeno jako <AutoField>. To také ukazuje, že je možné zadávat nové položky.

Tip

Základním pravidlem je, že pokud není v dotazu uveden primární klíč dotazované tabulky, není možné provést žádný nový záznam.

Pole Loan_Date a Return_Date mají aliasy, jak je znázorněno na obrázku 7. Tím nedojde k jejich přejmenování, ale pouze k tomu, že se uživateli dotazu zobrazí pod těmito názvy.

Zobrazení tabulky na obrázku 8  ukazuje, jak aliasy nahrazují skutečné názvy polí.

Pole Return_Date zobrazené na obrázku 9 je rovněž opatřeno vyhledávacím kritériem, které způsobí, že se zobrazí pouze ty záznamy, u nichž je pole Return_Date prázdné. (Do řádku Kritérium v poli Return_Date zadáme IS EMPTY.) Toto vylučovací kritérium způsobí, že se zobrazí pouze ty záznamy, které se týkají médií, která ještě nebyla vrácena.

Občasné přepínání mezi režimem návrhu a režimem SQL pomocí nástroje na obrázku 10 může pomoci při učení jazyka SQL.

Zde je na obrázku 11 zobrazen vzorec SQL vytvořený našimi předchozími volbami. Pro snazší čtení byly zařazeny některá zalomení řádků. Editor tyto zalomení řádků neukládá, takže při opětovném vyvolání dotazu se zobrazí jako jediný souvislý řádek zalomený na okraji okna.

SELECT začíná výběrová kritéria. AS určuje aliasy polí, které se mají použít. FROM zobrazuje tabulku, která má být použita jako zdroj dotazu. WHERE udává podmínky dotazu, konkrétně, že pole Return_Date má být prázdné (IS NULL). ORDER BY definuje kritéria řazení, konkrétně vzestupné pořadí (ASC – ascending) pro dvě pole Reader_ID a Loan_Date. Tato specifikace třídění ilustruje, jak lze alias pole Loan_Date použít v samotném dotazu.

Tip

Při práci v režimu zobrazení návrhu můžeme pomocí příkazu IS EMPTY požadovat, aby bylo pole prázdné. Při práci v režimu SQL použijeme IS NULL, což vyžaduje jazyk SQL (Structured Query Language).

Pokud chceme řadit sestupně pomocí jazyka SQL, použijeme místo ASC volbu DESC.

Pole Media_ID a Reader_ID jsou zatím viditelná pouze jako číselná pole. Jména čtenářů jsou nejasná. Chceme-li je zobrazit v dotazu, musí být do dotazu zahrnuta tabulka Reader. Za tímto účelem se vrátíme do režimu návrhu. Poté lze do zobrazení Návrh přidat novou tabulku pomocí nástroje na obrázku 12.

Zde lze následně přidat další tabulky nebo dotazy a zviditelnit je v grafickém uživatelském rozhraní. Pokud byly vazby mezi tabulkami deklarovány při jejich vytváření (viz kapitola 3, Tabulky), pak jsou tyto tabulky zobrazeny s příslušnými přímými vazbami na obrázku 13.

Pokud odkaz chybí, lze jej v tomto okamžiku vytvořit přetažením myší z položky "Loan". "Reader_ID" na položku "Reader". "ID".

Poznámka

Propojení tabulek funguje pouze v interní databázi nebo v externích relačních databázích. Například tabulky z tabulkového procesoru propojit nelze. Nejprve je třeba je importovat do interní databáze.

K vytvoření propojení mezi tabulkami stačí prostý import bez dodatečného vytvoření primárního klíče.

Nyní lze do tabulkové oblasti zadat pole z tabulky Reader. Pole jsou zpočátku přidána na konec dotazu, jak je znázorněno na obrázku 14.

Polohu polí lze v tabulkové oblasti editoru opravit pomocí myši. Takže například pole FirstName bylo na obrázku 15 přetaženo na pozici přímo před pole Loan_Date.

Nyní jsou jména viditelná. Pole Reader_ID se stalo nadbytečným. Také řazení podle LastName a FirstName dává větší smysl než řazení podle Reader_ID.

Tento dotaz již není vhodný pro použití jako dotaz, který umožňuje zadávat nové záznamy do výsledné tabulky, protože v něm chybí primární klíč pro přidanou tabulku Reader. Teprve pokud je tento primární klíč zabudován, je dotaz opět editovatelný. Ve skutečnosti je pak zcela upravitelný, takže lze měnit i jména čtenářů. Z tohoto důvodu je možnost upravovat výsledky dotazů možností, kterou je třeba používat s nejvyšší opatrností, v případě potřeby pod kontrolou formuláře.

Upozornění

Problémy může způsobit dotaz, který můžeme upravovat. Úpravou dat v dotazu se upravují také data v podkladové tabulce a záznamy v ní obsažené. Údaje nemusí mít stejný význam. Například změníme jméno čtenáře, a tím změníme i to, jaké knihy si čtenář vypůjčil a vrátil.

Pokud musíme data upravovat, provádějme to ve formuláři, abychom viděli účinky úprav dat.

I když lze dotaz dále upravovat, není tak snadné jej používat jako formulář s poli se seznamem, který zobrazuje  jména čtenářů, ale obsahuje Reader_ID z tabulky. Pole se seznamem nelze přidat do dotazu; lze je použít pouze ve formulářích.

Pokud se nyní přepneme zpět do zobrazení SQL, vidíme na obrázku 16 , že všechna pole jsou nyní zobrazena v dvojitých uvozovkách: "Název_tabulky". "Název_pole" oddělená tečkou. To je nutné, aby databáze věděla, ze které tabulky pocházejí dříve vybraná pole. Vždyť pole v různých tabulkách mohou mít snadno stejné názvy. Ve výše uvedené struktuře tabulky to platí zejména pro pole ID.

Poznámka

Následující dotaz funguje bez uvedení názvů tabulek před názvy polí:

SELECT "ID", "Number", "Price" FROM "Stock", "Dispatch" WHERE "Dispatch"."stockID" = "Stock"."ID"

Zde je ID převzato z tabulky, která je v definici FROM na prvním místě. Definice tabulky ve vzorci WHERE je také zbytečná, protože stockID se vyskytuje pouze jednou (v tabulce Dispatch) a ID bylo jednoznačně převzato z tabulky Stock (z pozice tabulky v dotazu).

Pokud má pole v dotazu alias, lze na něj odkazovat – například při třídění – pomocí tohoto aliasu, aniž by byl uveden název tabulky. Třídění se provádí v grafickém uživatelském rozhraní podle pořadí polí v tabulkovém zobrazení. Pokud chceme místo toho řadit nejprve podle "Loan_Date" a poté podle "Loan". "Reader_ID", lze to provést, pokud:

• Změní se pořadí polí v oblasti tabulky grafického uživatelského rozhraní (přetáhneme "Loan_Date" vlevo od "Loan". "Reader_ID" nebo "Loan_ID").

• Přidá se další pole, nastavené jako neviditelné, pouze pro třídění (editor jej však zaregistruje pouze dočasně, pokud pro něj nebyl definován alias) [přidáme další pole "Loan_Date" těsně před "Loan". "Reader_ID" nebo přidáme další pole "Loan". "Reader_ID" těsně za "Loan_Date"], nebo

• Text příkazu ORDER BY v SQL editoru se odpovídajícím způsobem změní (ORDER BY "Loan_Date", "Loan". "Reader_ID").

Tip

Dotaz může vyžadovat pole, které není součástí výstupu dotazu. V grafice v následující části je příklad dotazu Return_Date. Tento dotaz vyhledává záznamy, které neobsahují datum vrácení. Toto pole poskytuje kritérium pro dotaz, ale neobsahuje žádné užitečné viditelné údaje.

Použití funkcí v dotazu

Použití funkcí umožňuje dotazu poskytnout více než jen filtrovaný pohled na data v jedné nebo více tabulkách. Dotaz na obrázku 17 vypočítá, kolik médií bylo vypůjčeno v závislosti na identifikátoru Reader_ID.

Pro ID tabulky Loan je vybrána funkce Count. V zásadě je jedno, které pole tabulky je pro tento účel vybráno. Jedinou podmínkou je, že pole nesmí být v žádném ze záznamů prázdné. Z tohoto důvodu je nejvhodnější volbou pole primárního klíče, které není nikdy prázdné. Započítávají se všechna pole s jiným obsahem než NULL.

Pro identifikátor Reader_ID, který umožňuje přístup k informacím o čtenáři, je zvolena funkce Seskupení. Tímto způsobem jsou záznamy se stejným Reader_ID seskupeny dohromady. Výsledek na obrázku 18 ukazuje počet záznamů pro každé Reader_ID.

Jako kritérium vyhledávání je Return_Date nastaveno na "IS EMPTY", stejně jako v předchozím příkladu. (Níže je uveden následující SQL příkaz WHERE "Return_Date" IS NULL.)

Výsledek dotazu ukazuje, že Reader_ID '0' má celkem 3 vypůjčená média. Kdyby byla funkce Count přiřazena k Return_Date místo k ID, měl by každý Reader_ID na výpůjčce médium '0', protože Return_date je předdefinován jako NULL.

Odpovídající vzorec v kódu SQL je rovněž uveden na obrázku 18.

Celkově grafické uživatelské rozhraní poskytuje funkce uvedené vpravo, které odpovídají funkcím v základní databázi HSQLDB.

Vysvětlení funkcí nalezneme v části „Vylepšení dotazu pomocí režimu SQL“ na straně 1.

Pokud je jedno pole v dotazu spojeno s funkcí, musí být všechna zbývající pole uvedená v dotazu také spojena s funkcemi, pokud mají být zobrazena. Pokud to není zajištěno, zobrazí se chybové hlášení na obrázku 19:

Poněkud volný překlad by zněl: Následující výraz neobsahuje žádnou agregační funkci ani seskupení.

Tip

Při použití režimu zobrazení návrhu je pole viditelné pouze tehdy, pokud je v řádku Viditelné zaškrtnuto. Při použití SQL režimu je pole viditelné pouze tehdy, když následuje za klíčovým slovem SELECT.

Poznámka

Pokud pole není spojeno s funkcí, je počet řádků ve výstupu dotazu určen podmínkami vyhledávání. Pokud je pole spojeno s funkcí, je počet řádků ve výstupu dotazu určen tím, zda je či není provedeno seskupení. Pokud nedojde k seskupení, je ve výstupu dotazu pouze jeden řádek. Pokud existuje seskupení, počet řádků odpovídá počtu různých hodnot, které má pole se zvolenou funkcí seskupení. Všechna viditelná pole tedy musí být buď spojena s funkcí, nebo musí být spojena s příkazem seskupení, aby se zabránilo tomuto konfliktu ve výstupu dotazu.

Poté je v chybové zprávě uveden celý dotaz, ale bez konkrétního názvu chybného pole. V tomto případě bylo pole Return_Date přidáno jako zobrazované pole. Toto pole nemá žádnou přidruženou funkci a není zahrnuto ani v příkazu pro seskupení.

Informace poskytnuté pomocí tlačítka Více zahrnují kód chyby SQL, ale pro běžného uživatele databáze nemusí být příliš poučné.

Chceme-li chybu opravit, odstraníme zaškrtnutí v řádku Viditelné u pole Return_Date. Jeho vyhledávací podmínka (Kritérium) je použita při spuštění dotazu, ale není viditelná ve výstupu dotazu.

Pomocí grafického uživatelského rozhraní lze provádět základní výpočty a používat další funkce.

Předpokládejme, že knihovna nevydává upomínky, když má být položka vrácena, ale vydává upomínky za zpoždění v případech, kdy výpůjční lhůta uplynula a položka nebyla vrácena. To je běžná praxe ve školních a veřejných knihovnách, které půjčují pouze na krátkou, pevně stanovenou dobu. V tomto případě vydání oznámení o prodlení automaticky znamená, že musí být zaplacena pokuta. Jak tyto pokuty vypočítáme?

V dotazu na obrázku 20 jsou tabulky Loan a Recalls dotazovány společně. Z počtu datových záznamů v tabulce Recalls se zjistí celkový počet oznámení o vrácení. Pokuta za zpožděná média je v dotazu stanovena na 2,00 USD. Místo názvu pole je označení pole uvedeno jako Count(Recalls.Date)*2. Grafické uživatelské rozhraní přidá uvozovky a převede výraz „count“ na příslušný příkaz SQL.

Upozornění

Pouze pro ty, kteří používají čárku jako oddělovač desetinných míst:

Pokud chceme zadávat čísla s desetinnými místy pomocí grafického uživatelského rozhraní, musíme zajistit, aby se v konečném příkazu SQL použila jako oddělovač desetinných míst desetinná tečka, nikoli čárka. Jako oddělovače polí se používají čárky, takže pro desetinnou část se vytvoří nové pole dotazu.

Položka s čárkou v zobrazení SQL vždy vede k dalšímu poli obsahujícímu číselnou hodnotu desetinné části, jak je znázorněno na obrázku 21.

Dotaz nyní pro každé médium, které je stále zapůjčeno, zobrazuje pokuty, které narostly na základě vydaných oznámení o vrácení, a pole pro dodatečnou multiplikaci. Struktura dotazu na obrázku 22 bude užitečná i pro výpočet dlužných pokut od jednotlivých uživatelů.

Pole "Loan". "ID" a "Loan". "Media_ID" byla odstraněna. Byly použity v předchozím dotazu k vytvoření samostatného záznamu pro každé médium. Nyní budeme seskupovat pouze podle čtenáře. Výsledek dotazu je zobrazen na obrázku 23:

Namísto samostatného výpisu médií pro Reader_ID = 0 byla započítána všechna pole "Recalls". "Date" a celková částka 8,00 USD byla zadána jako dlužná pokuta.

Definice relace v dotazu

Při vyhledávání dat v tabulkách nebo formulářích je vyhledávání obvykle omezeno na jednu tabulku nebo jeden formulář. Dokonce ani cesta z hlavního formuláře do podformuláře není navigovatelná pomocí vestavěné vyhledávací funkce. Takové údaje je lepší shromažďovat pomocí dotazu.

Jednoduchý dotaz na pole Title z tabulky Media na obrázku 24  zobrazuje testovací záznamy pro tuto tabulku, celkem 9 záznamů. Pokud však do dotazovací tabulky zadáte položku Subtitle, obsah záznamu v tabulce Media se zmenší na pouhé 2 tituly. Pouze pro tyto dva Titles (tituly, pozn. překl.) jsou v tabulce uvedeny také Subtitles (podtituly, pozn. překl.), jak ukazuje obrázek 25. U všech ostatních titulů podtituly neexistují. To odpovídá podmínce spojení uvedené na obrázku 26 , že se mají zobrazit pouze ty záznamy, u nichž se pole Media_ID v tabulce Subtitles rovná poli ID v tabulce Media. Všechny ostatní záznamy jsou vyloučeny.

Aby se zobrazily všechny požadované záznamy, je třeba otevřít podmínky spojení pro úpravy. V tomto případě nemáme na mysli spojení mezi tabulkami v rámci návrhu relací, ale spojení v rámci dotazů.

Ve výchozím nastavení jsou relace nastaveny jako vnitřní spojení, což znamená, že dotaz vrátí pouze záznamy, jejichž obsah souvisejících polí obou tabulek je totožný. Na obrázku 27  je zobrazeno okno s informacemi o tom, jak tento typ spojení funguje v praxi.

Dvě dříve vybrané tabulky jsou uvedeny v seznamu Použité tabulky. Zde je nelze vybrat. Příslušná pole z obou tabulek se načtou z definic tabulek. Pokud není v definici tabulky zadán žádný vztah, lze jej v tomto okamžiku pro dotaz vytvořit. Pokud jsme však databázi naplánovali přehledně pomocí HSQLDB, nemělo by být nutné tato pole měnit.

Nejdůležitějším nastavením je možnost Join. Zde lze relace zvolit tak, aby byly vybrány všechny záznamy z tabulky Subtitles, ale pouze ty záznamy z Media, které mají v tabulce Subtitles zadaný titulek.

Nebo můžeme zvolit opačný postup: že se v každém případě zobrazí všechny záznamy z tabulky Media bez ohledu na to, zda mají podtitul.

Volba Přirozené určuje, že propojená pole v tabulkách jsou považována za rovnocenná. Tomuto nastavení se můžeme vyhnout také správným definováním relací na samém začátku plánování databáze.

Pro typ Pravý spoj popis ukazuje, že se zobrazí všechny záznamy z tabulky Media  (Subtitle RIGHT JOIN Media). Vzhledem k tomu, že v Media neexistuje žádný Subtitle, kterému by chyběl název, ale v Media určitě existují Titles, kterým chybí Subtitles, je tato volba správná, jak ukazuje obrázek 28.

Po potvrzení Pravého spoje vypadají výsledky dotazu tak, jak jsme chtěli. Title a Subtitle se zobrazují společně v jednom dotazu, který je znázorněn na obrázku 29. Samozřejmě se tituly objevují vícekrát, stejně jako u předchozí relace. Pokud se však nepočítají shody, lze tento dotaz dále použít jako základ vyhledávací funkce. Viz fragmenty kódu v této kapitole, v kapitole 8, Úlohy databáze, a v kapitole 9, Makra.

Definování vlastností dotazu

Od LibreOffice verze 4.1 je možné v editoru dotazů definovat další vlastnosti.

Vedle tlačítka pro otevření okna Vlastnosti dotazu se nachází pole se seznamem pro regulaci počtu zobrazených záznamů a tlačítko Rozlišit hodnoty (viz obrázek 30). Tyto funkce se opakují v dialogovém okně na obrázku 31:

Nastavení Jedinečné hodnoty určuje, zda má dotaz potlačit duplicitní záznamy.

Předpokládejme, že se provede dotaz na čtenáře, kteří mají stále vypůjčené knihy. Jejich názvy se zobrazí, pokud je pole pro datum vrácení prázdné. Pro čtenáře, kteří mají vypůjčeno více položek, se názvy zobrazují vícekrát, jak je znázorněno na obrázku 32.

Pokud zvolíme možnost Odlišné hodnoty, zmizí záznamy se stejným obsahem, jak je znázorněno na obrázku 33.

Dotaz pak vypadá takto:

Původní dotaz:

SELECT "Reader"."FirstName", "Reader"."LastName" …

Přidáním DISTINCT do dotazu se duplicitní záznamy potlačí.

SELECT DISTINCT "Reader"."FirstName", "Reader"."LastName" …

V dřívějších verzích bylo pro zadání jedinečných záznamů nutné přepnout z režimu návrhu do režimu SQL a vložit kvalifikátor DISTINCT. Tato vlastnost je zpětně kompatibilní s předchozími verzemi LO a nezpůsobuje žádné problémy.

Nastavení Limit určuje, kolik záznamů se má v dotazu zobrazit. Zobrazen je pouze omezený počet záznamů.

Všechny záznamy v tabulce Media jsou zobrazeny na obrázku 34. Dotaz je upravitelný, protože obsahuje primární klíč.

Nastavením limitu uvedeného na obrázku 35 se zobrazí pouze prvních pět záznamů (ID 0-4). Nebylo požadováno řazení, proto je použito výchozí řazení podle primárního klíče. I přes omezení výstupu lze dotaz dále upravovat. Tím se vstup v grafickém rozhraní liší od toho, co bylo v předchozích verzích dostupné pouze pomocí jazyka SQL.

Do původního dotazu bylo jednoduše přidáno „LIMIT 5“. Velikost limitu může být libovolná.

Upozornění

Nastavení limitů v grafickém rozhraní není zpětně kompatibilní. Ve všech verzích LO před verzí 4.1 bylo možné limit nastavit pouze v režimu přímého SQL. Omezení pak vyžadovalo třídění (ORDER BY ...) nebo podmínku (WHERE ...).

Vylepšení dotazu pomocí režimu SQL

Pokud při grafickém zadávání vypneme zobrazení návrhu pomocí Zobrazit > Režim návrhu zap/vyp, zobrazí se příkaz SQL pro to, co se dříve zobrazovalo v zobrazení návrhu. To může začátečníkům pomoci naučit se standardní dotazovací jazyk (Standard Query Language) pro databáze. Někdy je to také jediný způsob, jak zadat dotaz do databáze, pokud grafické uživatelské rozhraní nedokáže převést vaše požadavky na potřebné příkazy SQL.

SELECT * FROM "Table_name"

Zobrazí se vše, co je v tabulce Table_name. Znak "*" představuje všechna pole tabulky.

SELECT * FROM "Table_name" WHERE "Field_name" = 'Karl'

Zde existuje významné omezení. Zobrazí se pouze ty záznamy, u nichž pole Field_name obsahuje výraz „Karl“ – přesný výraz, nikoli například „Karl Egon“.

Někdy nelze dotazy v aplikaci Base provádět pomocí grafického uživatelského rozhraní, protože určité příkazy nemusí být rozpoznány. V takových případech je nutné vypnout zobrazení návrhu a pro přímý přístup k databázi použít příkaz Úpravy > Spustit SQL příkaz přímo. Tato metoda má tu nevýhodu, že s dotazem lze pracovat pouze v režimu SQL.

Přímé použití příkazů SQL je dostupné také pomocí grafického uživatelského rozhraní, jak ukazuje obrázek 36. Klepnutím na zvýrazněnou ikonu (Spustit SQL příkaz přímo) vypneme ikonu Režim návrhu zap/vyp.. Nyní se po klepnutí na ikonu Spustit, spustí přímo příkazy SQL.

Tvorba dotazů v režimu SQL

Zde je příklad rozsáhlých možností, které jsou k dispozici pro zadávání dotazů do databáze a určování typu požadovaného výsledku:

SELECT [{LIMIT <offset> <limit> | TOP <limit>}][ALL | DISTINCT]
{ <Select-Formulation> | "Table_name".* | * } [, ...]
[INTO [CACHED | TEMP  | TEXT] "new_Table"]
FROM "Table_list"
[WHERE SQL-Expression]
[GROUP BY SQL-Expression [, ...]]
[HAVING SQL-Expression]
[{ UNION [ALL | DISTINCT] | {MINUS [DISTINCT] | EXCEPT [DISTINCT] } |
INTERSECT [DISTINCT] } Query statement]
[ORDER BY Order-Expression [, ...]]
[LIMIT <limit> [OFFSET <offset>]];

[{LIMIT <offset> <limit> | TOP <limit>}]:

Tím se omezí počet zobrazovaných záznamů. LIMIT 10 20 začíná od 11. záznamu a zobrazí následujících 20 záznamů. TOP 10 zobrazí vždy prvních 10 záznamů. Všechny zadané hodnoty musí být celá čísla. To je stejné jako LIMIT 0 10. LIMIT 10 0 vynechá prvních 10 záznamů a zobrazí všechny záznamy počínaje 11. záznamem.

[ALL | DISTINCT]

SELECT ALL je výchozí nastavení. Zobrazí se všechny záznamy, které splňují podmínky vyhledávání. Příklad: SELECT ALL "Name" FROM "Table_name" zobrazí všechna jména; pokud se v tabulce vyskytuje třikrát "Peter" a čtyřikrát "Egon", zobrazí se tato jména třikrát, resp. čtyřikrát. SELECT DISTINCT "Name" FROM "Table_name" potlačí výsledky dotazu, které mají stejný obsah. V tomto případě se slova „Peter“ a „Egon“ vyskytují pouze jednou. DISTINCT se vztahuje k celému záznamu, ke kterému dotaz přistupuje. Pokud je tedy například požadováno i příjmení, budou záznamy pro „Peter Müller“ a „Peter Maier“ považovány za odlišné. I když zadáme podmínku DISTINCT, zobrazí se obě.

<Select-Formulation>

{ Expression | COUNT(*) |
{ COUNT | MIN | MAX | SUM | AVG | SOME | EVERY | VAR_POP | VAR_SAMP | STDDEV_POP | STDDEV_SAMP }
([ALL | DISTINCT]] Expression) } [[AS] "display_name"]

Názvy polí, výpočty, součty záznamů jsou všechny možné položky.

Poznámka

Výpočty v databázi někdy vedou k neočekávaným výsledkům. Předpokládejme, že databáze obsahuje známky udělené za nějakou práci ve třídě a že chceme vypočítat průměrnou známku.

Nejprve musíme sečíst všechny známky. Předpokládejme, že součet je 80. Nyní je třeba tuto hodnotu vydělit počtem žáků (řekněme 30). Výsledkem dotazu je 2.

K tomu dochází, protože pracujeme s poli typu INTEGER. Výsledkem výpočtu proto musí být celočíselná hodnota. Ve výpočtu musíme mít alespoň jednu hodnotu typu DECIMAL. Toho můžeme dosáhnout buď pomocí převodní funkce HSQLDB nebo (jednodušeji) můžeme dělit číslem 30,0 namísto 30. Tím získáme výsledek s jedním desetinným místem. Dělením 30.00 získáme dvě desetinná místa. Dbáme na používání desetinných míst v anglickém stylu. Použití čárek v dotazech je vyhrazeno pro oddělovače polí.

Kromě toho jsou pro zobrazené pole k dispozici různé funkce. Kromě funkce COUNT(*) (která spočítá všechny záznamy) žádná z těchto funkcí nepřistupuje k polím NULL.

COUNT | MIN | MAX | SUM | AVG | SOME | EVERY | VAR_POP | VAR_SAMP | STDDEV_POP | STDDEV_SAMP

COUNT("Name") spočítá všechny záznamy pro pole Name.
MIN("Name") zobrazí první jméno v abecedním pořadí. Výsledek této funkce je vždy formátován tak, jak se vyskytuje v poli. Text se zobrazuje jako text, celá čísla jako celá čísla, desetinná čísla jako desetinná čísla atd.
MAX("Name") zobrazí příjmení v abecedním pořadí.
SUMA("Number") může sčítat pouze hodnoty v číselných polích. Funkce selhává v případě polí s datem.

Tip

Tato funkce selhává také v polích pro čas. Zde mohou být užitečné následující informace:

SELECT (SUM( HOUR("Time") )*3600 + SUM( MINUTE("Time") ))*60 + SUM( SECOND("Time") ) AS "seconds" FROM "Table"

Zobrazený součet se skládá z jednotlivých hodin, minut a sekund. Poté se upraví tak, aby se získal celkový součet v jedné jednotce, v tomto případě v sekundách. Zde se pak funkcí sum sčítají pouze celá čísla. Pokud nyní použijeme:

SELECT ((SUM( HOUR("Time") )*3600 + SUM( MINUTE("Time") ))*60 + SUM( SECOND("Time") )) / 3600.0000 AS "hours" FROM "Table"

získáme čas v hodinách s minutami a sekundami jako desetinnými místy. Při vhodném formátování můžeme tuto hodnotu v dotazu nebo formuláři změnit zpět na normální časovou hodnotu.

AVG("Number") zobrazí průměr obsahu sloupce. I tato funkce je omezena na číselná pole.
SOME("Field_Name"), EVERY("Field_Name"): Pole používaná těmito funkcemi musí mít typ pole Yes/No[BOOLEAN] (obsahuje pouze 0 nebo 1). Kromě toho vytvářejí přehled obsahu pole, na které jsou aplikovány.

SOME vrací TRUE (nebo 1), pokud je alespoň jedna položka pole 1, a vrací FALSE (nebo 0) pouze tehdy, pokud jsou všechny položky 0.  EVERY vrací 1 pouze v případě, že každá položka pole je 1, a vrací FALSE, pokud je alespoň jedna položka 0.

Tip

Typ pole logického typu je Yes/No[BOOLEAN]. Toto pole však obsahuje pouze 0 nebo 1. V podmínkách vyhledávání dotazu použijeme buď TRUE, 1, FALSE nebo 0. Pro podmínku Yes můžeme použít hodnotu TRUE nebo 1. Pro podmínku No použijeme buď FALSE, nebo 0. Pokud se místo toho pokusíme použít „Yes“ nebo „No“, zobrazí se chybová zpráva. Pak budeme muset svou chybu opravit.

Příklad:

SELECT "Class", EVERY("Swimmer")
FROM "Table1"
GROUP BY "Class";

Třída obsahuje názvy plavecké třídy. Swimmer je logické pole popisující, zda student umí plavat (1 nebo 0). Students obsahuje jména studentů. Table1 obsahuje tato pole: primární klíč, Class, Swimmer a Students. Pro tento dotaz jsou potřeba pouze údaje Class a Swimmer.

Protože je dotaz seskupen podle záznamů pole Class, vrátí EVERY pro každou třídu hodnotu pole Swimmer. Když každý účastník plavecké třídy umí plavat, EVERY vrací TRUE. V opačném případě EVERY vrátí FALSE, protože alespoň jeden žák třídy neumí plavat. Vzhledem k tomu, že výstupem pro pole Swimmer je zaškrtávací políčko, značka zaškrtnutí znamená TRUE a žádná značka znamená FALSE.

VAR_POP | VAR_SAMP | STDDEV_POP | STDDEV_SAMP jsou statistické funkce a ovlivňují pouze celočíselná a desetinná pole.
Všechny tyto funkce vracejí hodnotu 0, pokud jsou všechny hodnoty ve skupině stejné.

Statistické funkce neumožňují použít omezení DISTINCT. V podstatě počítají se všemi hodnotami, na které se dotaz vztahuje, zatímco DISTINCT vylučuje ze zobrazení záznamy se stejnými hodnotami.

[AS] "display_name": Pole mohou mít v rámci dotazu jiné označení (alias).

"Table_name".* | * [, ...]

Symbol „*“ použijeme pro zobrazení celkového obsahu tabulky, místo abychom zadávali všechna pole jednotlivě. Pokud se výsledky vztahují pouze na jednu tabulku, je zbytečné používat název tabulky. Pokud však dotaz obsahuje všechna pole jedné tabulky a alespoň jedno pole z druhé tabulky, použijeme:
"Název_tabulky 1".*, "Název_tabulky 2". "Název_pole".

[INTO [CACHED | TEMP | TEXT] "new_table"]

Výsledek tohoto dotazu se zapíše přímo do nové tabulky, která je zde pojmenována. Vlastnosti polí pro novou tabulku jsou definovány z definic polí obsažených v dotazu. Zápis do nové tabulky nefunguje v režimu SQL, protože ten zpracovává pouze zobrazitelné výsledky. Místo toho musíme použít Nástroje > SQL. Výsledná tabulka není zpočátku editovatelná, protože v ní chybí primární klíč.

FROM <Table_list>

"Table_name 1" [{CROSS | INNER | LEFT OUTER | RIGHT OUTER} JOIN "Table_name 2" ON Expression] [, ...]

Tabulky, které mají být společně prohledávány, jsou obvykle v seznamu odděleném čárkami. Vzájemný vztah tabulek je pak dodatečně definován klíčovým slovem WHERE.

Pokud jsou tabulky svázány pomocí JOIN, nikoli čárkou, je jejich vztah definován výrazem začínajícím ON, který se vyskytuje přímo za druhou tabulkou.

Jednoduché JOIN má za následek, že se zobrazí pouze ty záznamy, pro které platí podmínky v obou tabulkách.

Příklad:

SELECT "Table1"."Name", "Table2"."Class"
FROM "Table1", "Table2"
WHERE "Table1"."ClassID" = "Table2"."ID"

je shodné s:

SELECT "Table1"."Name", "Table2"."Class"
FROM "Table1"
  JOIN "Table2"
ON "Table1"."ClassID" = "Table2"."ID"

Zde jsou zobrazeny jména a odpovídající třídy. Pokud není u jména uvedena žádná třída, není toto jméno do zobrazení zahrnuto. Pokud třída nemá žádná jména, také se nezobrazí. Přidání INNER na tom nic nemění.

SELECT "Table1"."Name", "Table2"."Class"
FROM "Table1"
  LEFT JOIN "Table2"
ON "Table1"."ClassID" = "Table2"."ID"

Pokud je přidáno klíčové slovo LEFT, zobrazí se všechna Jména z Table1, i když nemají žádnou hodnotu v poli Class. Pokud je naopak přidáno klíčové slovo RIGHT, zobrazí se všechny Třídy, i když v nich nejsou žádná jména. Použití klíčového slova OUTER zde není třeba uvádět. (Pravý vnější spoj je totéž co Pravý spoj; Levý vnější spoj je totéž co Levý spoj.)

SELECT "Table1"."Player1", "Table2"."Player2"
FROM "Table1" AS "Table1"
  CROSS JOIN "Table2" AS "Table1"
WHERE "Table1"."Player1" <> "Table2"."Player2"

Křížové spojení CROSS JOIN vyžaduje, aby byla tabulka opatřena aliasem, ale přidání výrazu ON není vždy nutné. Všechny záznamy z první tabulky jsou spárovány se všemi záznamy z druhé tabulky. Výše uvedený dotaz tedy poskytne všechny možné dvojice záznamů z první tabulky se záznamy z druhé tabulky s výjimkou dvojic mezi záznamy pro stejného hráče. V případě CROSS JOIN nesmí podmínka obsahovat vazbu mezi tabulkami uvedenými v termínu ON. Místo toho lze zadat podmínky WHERE. Pokud jsou podmínky formulovány přesně jako v případě jednoduchého spojení JOIN, získáme stejný výsledek:

SELECT "Table1"."Name", "Table2"."Class"
FROM "Table1"
  JOIN "Table2"
ON "Table1"."ClassID" = "Table2"."ID"

dává stejný výsledek jako

SELECT "Table1"."Name", "Table2"."Class"
FROM "Table1" AS "Table1"
  CROSS JOIN "Table2" AS "Table2"
WHERE "Table1"."ClassID" = "Table2"."ID"

[WHERE SQL-Expression]

Standardní úvod pro podmínky, které vyžadují přesnější filtrování dat. I zde se obvykle definují relace mezi tabulkami, pokud nejsou propojeny pomocí JOIN.

[GROUP BY SQL-Expression [, …]]

Tuto funkci použijeme, pokud chceme data dotazu rozdělit do skupin a teprve poté použít funkce na každou skupinu zvlášť. Rozdělení je založeno na hodnotách pole nebo polí obsažených ve výrazu GROUP BY.

Příklad:

SELECT "Name", SUM("Input"-"Output") AS "Balance"
FROM "Table1"
GROUP BY "Name";

Záznamy se stejným názvem jsou sečteny. Ve výsledku dotazu je pro každou osobu uveden součet Vstup – Výstup (Input - Output). Toto pole se nazývá Balance. Každý řádek výsledku dotazu obsahuje hodnotu z tabulky Name a vypočtený zůstatek pro tuto konkrétní hodnotu.

Tip

Pokud jsou pole zpracována pomocí určité funkce (například COUNT, SUM ...), všechna pole, která nejsou zpracována pomocí funkce, ale mají být zobrazena, jsou seskupena pomocí GROUP BY.

[HAVING SQL-Expression]

Vzorec HAVING se velmi podobá vzorci WHERE. Rozdíl je v tom, že vzorec WHERE se vztahuje na hodnoty vybraných polí v dotazu. Vzorec HAVING se vztahuje na vybrané vypočtené hodnoty. Konkrétně vzorec WHERE nemůže jako součást vyhledávací podmínky použít agregační funkci, vzorec HAVING ano.

Vzorec HAVING slouží ke dvěma účelům, jak je uvedeno ve dvou příkladech níže. V prvním případě podmínka vyhledávání vyžaduje, aby minimální doba běhu byla kratší než 40 minut. V druhém příkladu podmínka hledání vyžaduje, aby zůstatek jednotlivce byl kladný.

Ve výsledcích prvního dotazu jsou uvedena jména lidí, jejichž doba běhu byla alespoň jednou kratší než 40 minut, a minimální doba běhu. Lidé, jejichž doba běhu byla delší než 40 minut, uvedeni nejsou.

Ve výsledcích druhého dotazu jsou uvedena jména osob, jejichž celkový výstup je vyšší než vstup, a jejich zůstatek. Lidé, jejichž zůstatek je 0 nebo méně, nejsou uvedeni.

Příklady:

SELECT "Name", "Runtime"
FROM "Table1"
GROUP BY "Name", "Runtime"

HAVING MIN("Runtime") < '00:40:00';
SELECT "Name", SUM("Input"-"Output") AS "Balance"
FROM "Table1"
GROUP BY "Name"
HAVING SUM("Input"-"Output") > 0;

[SQL Expression]

Výrazy SQL se kombinují podle následujícího schématu:

[NOT] podmínka [{ OR | AND } podmínka]

Příklad:

SELECT *
FROM "Table_name"
WHERE NOT "Return_date" IS NULL AND "ReaderID" = 2;

Z tabulky se načtou záznamy, u nichž bylo zadáno datum vrácení (Return_date) a hodnota RecordID je 2. V praxi to znamená, že získáme všechna média zapůjčená konkrétní osobě a vrácená zpět. Podmínky jsou spojeny pouze pomocí AND. NOT se vztahuje pouze na první podmínku.

SELECT *
FROM "Table_name"
WHERE NOT ("Return_date" IS NULL AND "ReaderID" = 2);

Závorky kolem podmínky, mimo které je uvedeno NOT, zobrazí pouze ty záznamy, které podmínku v závorce zcela nesplňují. To by se týkalo všech záznamů kromě záznamů pro ReaderID číslo 2, které dosud nebyly vráceny.

[SQL Expression]: podmínky

{ hodnota [|| hodnota]

Hodnota může být buď jedna nebo několik hodnot spojených dvěma svislými čarami ||. To samozřejmě platí i pro obsah polí.

SELECT "Surname" || ', ' || "First_name" AS "Name"
FROM "Table_name"

Obsah polí Surname a First_name se zobrazí společně v poli s názvem Name. Všimneme si, že mezi hodnoty Surname a First_name se vkládá čárka a mezera.

| hodnota { = | < | <= | > | >= | <> |!= } hodnota

Tyto znaky odpovídají známým matematickým operátorům:
{ Rovná se | Menší než | Menší nebo rovno | Větší než | Větší nebo rovno | Není rovno | Není rovno }

| hodnota IS [NOT] NULL

Odpovídající pole nemá žádný obsah, protože do něj nebylo nic zapsáno. To nelze v grafickém uživatelském rozhraní jednoznačně určit, protože vizuálně prázdné textové pole neznamená, že je zcela bez obsahu. Ve výchozím nastavení databáze Base jsou však prázdná pole v databázi nastavena na hodnotu NULL.

| EXISTS(Query_result)

Příklad:

SELECT "Name"
FROM "Table1"
WHERE EXISTS
  (SELECT "First_name"
  FROM "Table2"
  WHERE "Table2"."First_name" = "Table1"."Name")

Zobrazí se jména z Table1, jejichž křestní jména jsou uvedena v Table2.

| Hodnota BETWEEN Hodnota AND Hodnota

BETWEEN hodnota1 AND hodnota2 zobrazí všechny hodnoty od hodnota1 až po hodnota2 včetně. Pokud jsou hodnoty tvořeny písmeny, použije se abecední řazení, při kterém mají malá písmena stejnou hodnotu jako odpovídající velká písmena.

SELECT "Name"
FROM "Table_name"
WHERE "Name" BETWEEN 'A' AND 'E';

Výsledkem tohoto dotazu budou všechna jména začínající na A, B, C nebo D (a také na odpovídající malá písmena). Jelikož je E nastaveno jako horní hranice, nejsou zahrnuta jména začínající na E. Samotné písmeno E se vyskytuje těsně před jmény začínajícími na E.

| hodnota [NOT] IN ( {hodnota [, ...] | výsledek dotazu } )

To vyžaduje buď seznam hodnot, nebo dotaz. Podmínka je splněna, pokud je hodnota obsažena v seznamu hodnot nebo ve výsledku dotazu.

| hodnota [NOT] LIKE hodnota [ESCAPE] hodnota }

Operátor LIKE je operátor, který je potřebný v mnoha jednoduchých vyhledávacích funkcích. Hodnota se zadává podle následujícího vzoru:
'%' znamená libovolný počet znaků (včetně 0),
'_' nahrazuje přesně jeden znak.

Chceme-li vyhledat samotný znak '%' nebo '_', musí tyto znaky následovat bezprostředně za jiným znakem definovaným jako ESCAPE.

SELECT "Name"
FROM "Table_name"
WHERE "Name" LIKE '\_%' ESCAPE '\'

Tento dotaz zobrazí všechna jména začínající podtržítkem.  '\' je zde definováno jako znak ESCAPE.

[SQL Expression]: hodnoty

[+ | –] { Výraz [{ + | – | * | / | | } Výraz]

Hodnoty mohou mít předchozí znaménko. Je povoleno sčítání, odčítání, násobení, dělení a spojování výrazů. Příklad spojování:

SELECT "Surname"||', '||"First_name"
FROM "Table"

Tímto způsobem se v dotazu zobrazí záznamy s polem obsahujícím "Surname, First_name". Operátor spojování lze kvalifikovat pomocí následujících výrazů.

| ( Podmínka )

Viz předchozí oddíl.

| Funkce ( [Parametr] [,...] )

Viz oddíl Funkce v příloze.

Následující dotazy se také označují jako poddotazy (podvýběry).

| Výsledek dotazu, který dává přesně jednu odpověď

Protože záznam může mít v každém poli pouze jednu hodnotu,  pouze dotaz, který dává právě jednu hodnotu, může být zobrazen celý.

| {ANY|ALL} (Výsledek dotazu, který dává přesně jednu odpověď z celého sloupce)

Často se vyskytuje podmínka, která porovnává výraz s celou skupinou hodnot.

V kombinaci s ANY to znamená, že se výraz musí ve skupině vyskytovat alespoň jednou. To lze zadat také pomocí podmínky IN. = ANY vede ke stejnému výsledku jako IN.

V kombinaci s ALL znamená, že všechny hodnoty skupiny musí odpovídat jednomu výrazu.

[SQL Expression]: Výraz

{ 'Text' | Celé číslo | Číslo s plovoucí desetinnou čárkou
| ["Table".]"Field" | TRUE | FALSE | NULL }

Hodnoty v zásadě slouží jako argumenty pro různé výrazy v závislosti na zdrojovém formátu. Chceme-li vyhledat obsah textových polí, umístíme obsah do uvozovek. Celá čísla se zapisují bez uvozovek, stejně jako čísla s pohyblivou čárkou.
Pole znamenají hodnoty, které se v tabulce vyskytují v daných polích. Obvykle se pole porovnávají buď mezi sebou, nebo s určitými hodnotami. V jazyce SQL by měly být názvy polí umístěny do dvojitých uvozovek, protože jinak nemusí být správně rozpoznány. SQL obvykle předpokládá, že text bez dvojitých uvozovek je bez speciálních znaků, tj. jedno slovo bez mezer a s velkými písmeny. Pokud je v dotazu obsaženo více tabulek, musí být kromě názvu pole uveden i název tabulky, oddělený od názvu pole tečkou.
Hodnoty TRUE a FALSE se obvykle odvozují od polí Yes/No.
Hodnota NULL znamená žádný obsah. Není to totéž co 0, ale spíše odpovídá „prázdnému“.

UNION [ALL | DISTINCT] Query_result

Tím se dotazy propojí tak, že obsah druhého dotazu je zapsán pod prvním dotazem. Aby to fungovalo, musí se všechna pole v obou dotazech shodovat v typu. Toto propojení několika dotazů funguje pouze v režimu přímého příkazu SQL.

SELECT "First_name"
FROM "Table1"
UNION DISTINCT
  SELECT "First_name"
  FROM "Table2";

Tento dotaz zobrazí všechna křestní jména z Table1 a Table2; doplňkový výraz DISTINCT znamená, že se nezobrazí žádná duplicitní křestní jména. DISTINCT je v tomto kontextu výchozí hodnota. Ve výchozím nastavení jsou křestní jména řazena vzestupně podle abecedy. ALL způsobí, že se zobrazí všechna křestní jména v Table1 a za nimi křestní jméno v Table2. V tomto případě je výchozí třídění podle primárního klíče.

Použití této techniky dotazování umožňuje vypsat hodnoty ze záznamu přímo ve sloupci pod sebe. Předpokládejme, že máme tabulku s názvem Stock, ve které jsou pole Sales_price, Rebate_price_1 a Rebate_price_2. Z těchto údajů chceme vypočítat kombinační pole, ve kterém budou tyto ceny uvedeny přímo pod sebou.

SELECT
  "Sales_price"
FROM "Stock" WHERE "Stock_ID" = 1
  UNION
  SELECT
     "Rebate_price_1"
  FROM "Stock" WHERE "Stock_ID" = 1
  UNION
  SELECT
     "Rebate_price_2"
  FROM "Stock" WHERE "Stock_ID" = 1;

Primární klíč tabulky Stock musí být samozřejmě nastaven pomocí makra, protože pole kombinace bude mít odpovídající položku.

MINUS [DISTINCT] | EXCEPT [DISTINCT] Query_result

SELECT "First_name"
FROM "Table1"
EXCEPT
  SELECT "First_name"
  FROM "Table2";

Zobrazí všechna křestní jména z tabulky Table1 s výjimkou křestních jmen obsažených v tabulce Table2. MINUS a EXCEPT vedou ke stejnému výsledku. Řazení je abecední.

INTERSECT [DISTINCT] Query_result

SELECT "First_name"
FROM "Table1"
INTERSECT
  SELECT "First_name"
  FROM "Table2";

Zobrazí se křestní jména, která se vyskytují v obou tabulkách. Řazení je opět abecední. V současné době to funguje pouze v režimu přímého příkazu SQL.

[ORDER BY Ordering-Expression [, …]]

Výrazem může být název pole, číslo sloupce (počínaje 1 zleva), alias (formulovaný například pomocí AS) nebo složený výraz hodnoty (viz [SQL Expression]: hodnoty). Řazení je obvykle vzestupné (ASC). Pokud chceme sestupné třídění, musíme výslovně zadat DESC.

SELECT "First_name", "Surname" AS "Name"
FROM "Table1"
ORDER BY "Surname";

je totožný s

SELECT "First_name", "Surname" AS "Name"
FROM "Table1"
ORDER BY 2;

je totožný s

SELECT "First_name", "Surname" AS "Name"
FROM "Table1"
ORDER BY "Name";

[LIMIT <limit> [OFFSET <offset>]]:

LIMIT 10 zobrazí pouze 10 záznamů. Přidání OFFSET 20 způsobí, že zobrazení začne od 21. záznamu.  Tato poslední forma omezení zobrazení vyžaduje buď instrukci pro řazení (ORDER BY...), nebo podmínku (WHERE...).

Všechny zadané hodnoty limitu musí být celá čísla. Není možné nahradit záznam poddotazem tak, aby se například pokaždé zobrazilo pět posledních záznamů řady.

Použití aliasu v dotazu

Dotazy mohou reprodukovat pole se změněnými názvy.

SELECT "First_name", "Surname" AS "Name"
FROM "Table1"

Pole Surname se v zobrazení nazývá Name.

Pokud dotaz zahrnuje dvě tabulky, musí být před názvem každého pole uveden název tabulky:

SELECT "Table1"."First_name", "Table1"."Surname" AS "Name", "Table2"."Class"
FROM "Table1", "Table2"
WHERE "Table1"."Class_ID" = "Table2"."ID"

Názvu tabulky lze také přidělit alias, ale tato změna se projeví pouze v dotazu a neprojeví se v zobrazení tabulky. Pokud je takový alias nastaven, je třeba odpovídajícím způsobem změnit všechny názvy tabulek v dotazu:

SELECT "a"."First_name", "a"."Surname" AS "Name", "b"."Class"
FROM "Table1" AS "a", "Table2" AS "b"
WHERE "a"."Class_ID" = "b"."ID"

Přiřazení aliasu tabulce lze provést stručněji bez použití termínu AS:

SELECT "a"."First_name", "a"."Surname" "Name", "b"."Class"
FROM "Table1" "a", "Table2" "b"
WHERE "a"."Class_ID" = "b"."ID"

Tím se však kód stává méně čitelným. Z tohoto důvodu by se zkrácený formulář měl používat pouze ve výjimečných případech.

Poznámka

Kód editoru dotazů v grafickém uživatelském rozhraní byl nedávno změněn tak, aby se označení aliasů vytvářela bez použití předpony AS. Je to proto, že při použití externích databází, jejichž způsob zadávání aliasů nelze předvídat, vedlo zahrnutí AS k chybovým hlášením.

Pokud je kód dotazu otevřen k úpravám nikoli přímo v SQL, ale pomocí grafického uživatelského rozhraní, stávající aliasy ztratí předponu AS. Pokud je to pro nás důležité, musíme dotazy vždy upravovat v režimu SQL.

Aliasový název také umožňuje použít tabulku s odpovídajícím filtrováním více než jednou v rámci dotazu:

SELECT "KasseAccount"."Balance", "Account"."Date",
  "a"."Balance" AS "Actual",
  "b"."Balance" AS "Desired"
FROM "Account"
  LEFT JOIN "Account" AS "a"
  ON "Account"."ID" = "a"."ID" AND "a"."Balance" >= 0
  LEFT JOIN "Account" AS "b"
  ON "Account"."ID" = "b"."ID" AND "b"."Balance" < 0

Dotazy na vytvoření polí seznamu

V polích se seznamem se zobrazuje hodnota, která neodpovídá obsahu podkladové tabulky. Používají se k zobrazení hodnoty, kterou uživatel přiřadil cizímu klíči, a nikoli samotného klíče. Hodnota, která je nakonec uložena ve formuláři, se nesmí vyskytovat na první pozici pole seznamu.

SELECT "FirstName", "ID"
FROM "Table1";

Tento dotaz by zobrazil všechna křestní jména a hodnoty primárního klíče "ID", které poskytuje základní tabulka formuláře. Samozřejmě to ještě není optimální. Křestní jména nejsou seřazena a v případě stejných křestních jmen nelze určit, o kterou osobu se jedná.

SELECT "FirstName"||' '||"LastName", "ID"
FROM "Table1"
ORDER BY "FirstName"||' '||"LastName";

Nyní se zobrazí jméno i příjmení oddělené mezerou. Jména se stanou rozlišitelnými a jsou také seřazena. Řazení se však řídí obvyklou logikou, kdy se začíná prvním písmenem řetězce, takže se řadí podle jména a teprve potom podle příjmení. Jiné pořadí řazení, než v jakém jsou pole zobrazena, by bylo pouze matoucí.

SELECT "LastName"||', '||"FirstName", "ID"
FROM "Table1"
ORDER BY "LastName"||', '||"FirstName";

To nyní vede k řazení, které lépe odpovídá běžným zvyklostem. Členové rodiny se objevují společně, jeden pod druhým; různé rodiny se stejným příjmením se však prolínají. Abychom je mohli rozlišit, museli bychom je v tabulce seskupit jinak.

Je tu ještě jeden problém: pokud mají dva lidé stejné příjmení a jméno, stejně je nelze rozlišit. Jedním z řešení by mohlo být použití přípony jména. Ale představme si, jak by to vypadalo, kdyby na pozdravu stálo Pan "Müller II"!

SELECT "LastName"||', '||"FirstName"||' - ID:'||"ID", "ID"
FROM "Table1"
ORDER BY "LastName"||', '||"FirstName"||"ID";

Zde jsou všechny záznamy rozlišitelné. Ve skutečnosti se zobrazí "LastName, FirstName – ID:ID hodnota". Například osoba jménem Heinrich Müller s ID 1 se zobrazí jako „Müller, Heinrich – ID: 1“.

Ve formuláři pro výpůjčky se v seznamu zobrazí pouze média, která ještě nebyla vypůjčena. Vytváří se pomocí následujícího SQL příkazu:

SELECT "Title" || ' - Nr. ' || "ID", "ID"
FROM "Media"
WHERE "ID" NOT IN
  (SELECT "Media_ID"
  FROM  "Loan"
  WHERE "Return_Date" IS  NULL)
ORDER BY  "Title" || ' - Nr. ' || "ID"  ASC

Je důležité, aby toto pole se seznamem bylo vždy aktualizováno, pokud je médium v něm zapůjčeno.

Následující pole se seznamem na obrázku 37 zobrazuje obsah několika polí v tabulkové podobě tak, že prvky, které patří k sobě, jsou přímo pod sebou.

Aby takové zobrazení fungovalo, musíme nejprve zvolit vhodné písmo s pevnou šířkou. Zde lze použít Courier nebo jiné bezpatkové písmo, například Liberation Mono. Tabulkový formulář vytvoříte pomocí kódu SQL:

SELECT
  LEFT("Stock"||SPACE(25),25) || ' - ' ||
     RIGHT(SPACE(8)||"Price",8) || ' €',
  "ID"
FROM "Stock"
ORDER BY ("Stock" || ' - ' || "Price" || ' $') ASC

Obsah pole Stock byl doplněn mezerami tak, aby celý řetězec měl minimální délku 25 znaků. Poté se tam umístí prvních 25 písmen a přebytek se odstřihne.

Situace se komplikuje, pokud obsah pole seznamu obsahuje netisknutelné znaky, jako jsou nové řádky. Pak je třeba kód upravit takto:

SELECT
  LEFT(REPLACE("Stock",CHAR(10),' ')||SPACE(25), 25) || ' - ' || ...

Tím se v systému Linux nahradí nový řádek mezerou. V systému Windows musíme navíc odstranit návrat kurzoru na začátek řádku (CHAR(13)).

Počet potřebných mezer lze také určit na základě jednotlivých dotazů. Tím se zabrání náhodnému zkrácení hodnoty „Stock“.

SELECT
  LEFT("Stock"||SPACE((SELECT MAX(LENGTH("Stock")) FROM "Stock")),            (SELECT MAX(LENGTH("Stock")) FROM "Stock"))|| ' - ' ||
     RIGHT('    '||"Price",8) || ' €',
  "ID"
FROM "Stock"
ORDER BY ("Stock" || ' - ' || "Price" || ' $') ASC

Protože se cena má zobrazovat vpravo, je vlevo doplněna mezerami a umístěna maximálně osm znaků zprava. Vybrané zobrazení bude fungovat pro všechny ceny do  $ 99999,99.

Pokud chceme v jazyce SQL nahradit desetinnou tečku čárkou, budeme potřebovat další kód:

REPLACE(RIGHT('    '||"Price",8),'.',',')

Dotazy jako základ pro další informace ve formulářích

Pokud chceme, aby formulář zobrazoval další informace, které by jinak nebyly viditelné, máme k dispozici různé možnosti dotazů. Nejjednodušší je získat tyto informace pomocí nezávislých dotazů a výsledky vložit do formuláře. Nevýhodou této metody je, že změny v záznamech mohou ovlivnit výsledek dotazu, ale tyto změny se automaticky nezobrazují.

Tabulka 1 je příkladem z oblasti skladového hospodářství pro jednoduchou pokladnu.

Tabulka pokladny obsahuje součty a cizí klíče pro skladové položky a číslo příjemky. Zákazník má jen velmi málo informací, pokud na účtence není vytištěn žádný další výsledek dotazu. Koneckonců, položky jsou identifikovány pouze načtením čárového kódu. Bez dotazu se zobrazí pouze formulář:

Tabulka 1: Počet položek pro každý čárový kód

Celkem	Čárový kód
3	17
2	24

To, co se skrývá za čísly, nelze zviditelnit pomocí pole se seznamem, protože cizí klíč se zadává přímo pomocí čárového kódu. Stejně tak není možné použít pole se seznamem vedle položky, aby se zobrazovala alespoň jednotková cena.

Zde může pomoci dotaz.

SELECT "Checkout"."Receipt_ID", "Checkout"."Total", "Stock"."Item", "Stock"."Unit_Price", "Checkout"."Total"*"Stock"."Unit_price" AS "Total_Price"
FROM "Checkout", "Stock"
WHERE "Stock"."ID" = "Checkout"."Item_ID";

Nyní po zadání informací alespoň víme, kolik je třeba zaplatit za 3 * Item'17'. Kromě toho je třeba ve formuláři filtrovat pouze informace relevantní pro příslušné Receipt_ID. Stále chybí to, co musí zákazník celkově zaplatit.

SELECT "Checkout"."Receipt_ID", SUM("Checkout"."Total"*"Stock"."Unit_price") AS "Sum"
FROM "Checkout", "Stock"
WHERE "Stock"."ID" = "Checkout"."Item_ID"
GROUP BY "Checkout"."Receipt_ID";

Formulář navrhneme tak, aby zobrazoval vždy jeden záznam dotazu.  Protože je dotaz seskupen podle Receipt_ID, formulář zobrazuje informace vždy o jednom zákazníkovi.

Tip

Pokud formulář potřebuje zobrazit hodnoty data, které závisí na jiném datu (například výpůjční doba média může být 21 dní, takže jaké je datum vrácení?), nelze použít vestavěné funkce HSQLDB. Funkce „DATEADD“ neexistuje.

Dotaz
SELECT "Date", DATEDIFF('dd','1899-12-30',"Date")+21 AS "ReturnDate" FROM "Table"
poskytne správné cílové datum pro vrácení ve formuláři. Tento dotaz počítá dny od 30.12.1899. Jedná se o výchozí datum, které Calc používá také jako nulovou hodnotu.

Vrácená hodnota je však pouze číslo, nikoli datum, které lze použít v dalším dotazu.

Vrácené číslo není vhodné pro použití v dotazech, protože formátování dotazů se neukládá. Místo toho je třeba vytvořit zobrazení.

Možnosti zadávání dat v rámci dotazů

Abychom mohli provádět záznamy do dotazu, který vyvolává pouze jednu tabulku, musí být přítomen primární klíč tabulky, která je základem dotazu, jak je znázorněno na obrázku 38.

Při půjčování médií nemá smysl vystavovat předměty, které již byly před časem vráceny.

SELECT "ID", "Reader_ID", "Media_ID", "Loan_Date"
FROM "Loan"
WHERE "Return_Date" IS NULL;

Formulář tak může v rámci kontrolního pole tabulky zobrazit vše, co si konkrétní čtenář v průběhu času vypůjčil. I zde musí dotaz filtrovat pomocí příslušné struktury formuláře (čtenář v hlavním formuláři, dotaz v podformuláři), aby se zobrazovala pouze média, která jsou skutečně vypůjčena. Dotaz je vhodný pro zadávání dat, protože je v něm obsažen primární klíč.

Pokud se dotaz skládá z více než jedné tabulky, je možné jej upravovat, pokud jsou přítomny primární klíče všech tabulek a k tabulkám se nepřistupuje prostřednictvím aliasu.

SELECT "Media"."ID", "Media"."Title", "Media"."Category_ID", "Category"."ID" AS "katID", "Category"."Category"
FROM "Media", "Category"
WHERE "Media"."Category_ID" = "Category"."ID";

Dotaz na obrázku 39 je upravitelný, protože jsou v něm zahrnuty oba primární klíče a lze k nim přistupovat v tabulkách bez použití aliasu.  Dotaz, který spojuje několik tabulek dohromady, rovněž vyžaduje přítomnost všech primárních klíčů.

V dotazu, který zahrnuje několik tabulek, není možné změnit pole cizího klíče v jedné tabulce, které odkazuje na záznam v jiné tabulce. V záznamu na obrázku 40 byl proveden pokus o změnu kategorie pro titul „The Little Hobbit“. Pole Category_ID bylo změněno z 0 na 2. Zdálo se, že změna proběhla a nová kategorie se objevila v záznamu. Ukázalo se však, že ji nelze uložit.

Je však možné upravit obsah příslušného záznamu kategorie, například nahradit „Fantasie“ za „Fantasy“. Název kategorie se pak změní pro všechny záznamy, které jsou s touto kategorií propojeny.

SELECT "m"."ID", "m"."Title", "Category"."Category", "Category"."ID" AS "katID"
FROM "Media" AS "m", "Category"
WHERE "m"."Category_ID" = "Category"."ID";

V tomto dotazu se k tabulce Media přistupuje pomocí aliasu. Dotaz nelze upravovat. V tomto případě je jedno, zda je primární klíč v dotazu přítomen, nebo ne.

Ve výše uvedeném příkladu se tomuto problému snadno vyhneme. Pokud však použijeme korelovaný poddotaz (viz strana 1), musíme použít alias tabulky. Dotaz je v tomto případě upravitelný pouze tehdy, pokud obsahuje pouze jednu tabulku v hlavním dotazu.

V režimu zobrazení na obrázku 41 se zobrazí pouze jedna tabulka. Tabulce Media je přidělen alias, aby bylo možné přistupovat k obsahu pole Category_ID pomocí souvisejícího poddotazu.

V takovém dotazu je nyní možné změnit pole cizího klíče Category_ID na jinou kategorii. Ve výše uvedeném příkladu se pole Category_ID změní z 0 na 2. Titul „The Little Hobbit“ je tedy zařazen do kategorie „Songwriter“.

Není však již možné změnit hodnotu v poli, které získalo svůj obsah prostřednictvím souvisejícího poddotazu. Pokus o změnu kategorie z „Fantasy“ na „Fantastic“ je znázorněn na obrázku 42. Tato změna není zaregistrována a nelze ji ani uložit. V tabulce, která se zobrazí v zobrazení návrhu, není pole Category přítomno.

Použití parametrů v dotazech

Pokud často používáme stejný základní dotaz, ale pokaždé s jinými hodnotami, můžeme použít dotazy s parametry. Dotazy s parametry fungují v zásadě stejně jako dotazy pro dílčí formulář:

SELECT "ID", "Reader_ID", "Media_ID", "Loan_Date"
FROM "Loan"
WHERE "Return_Date" IS NULL AND "Reader_ID"=2;

Tento dotaz zobrazuje pouze média půjčená čtenáři s číslem 2.

SELECT "ID", "Reader_ID", "Media_ID", "Loan_Date"
FROM "Loan"
WHERE "Return_Date" IS NULL AND "Reader_ID" =: Readernumber;

Nyní se při spuštění dotazu zobrazí vstupní pole. Vyzve nás k zadání čísla čtenáře. Ať už zde zadáme jakoukoli hodnotu, zobrazí se média, která jsou pro daného čtenáře aktuálně vypůjčena.

SELECT
  "Loan"."ID",
  "Reader"."LastName"||', '||"Reader"."FirstName",
  "Loan"."Media_ID",
  "Loan"."Loan_date"
FROM "Loan", "Reader"
WHERE "Loan"."Return_Date" IS NULL
  AND "Reader"."ID" = "Loan"."Reader_ID"
  AND "Reader"."LastName" LIKE '%' ||: Readername || '%'
ORDER BY "Reader"."LastName"||', '||"Reader"."FirstName" ASC;

Tento dotaz je zjevně uživatelsky přívětivější než předchozí. Číslo čtenáře již není nutné znát. Stačí zadat část příjmení a zobrazí se všechna média, která jsou půjčena odpovídajícím čtenářům.

Pokud vyměníme

"Reader"."LastName" LIKE '%' || :Readername || '%'

za

LOWER("Reader"."LastName") LIKE '%' || LOWER(: Readername) || '%'

Již nezáleží na tom, zda je jméno zadáno velkými nebo malými písmeny.

Pokud by parametr ve výše uvedeném dotazu zůstal prázdný, všechny verze LibreOffice až do verze 4.4 by zobrazily všechny čtenáře, protože pouze ve verzi 4.4 se prázdné pole parametru čte jako NULL, nikoli jako prázdný řetězec. Pokud toto chování nechceme, musíme mu zabránit pomocí triku:

LOWER ("Reader"."LastName") LIKE '%' || IFNULL(NULLIF (LOWER (:Readername), ''), '§§' ) || '%'

Pole prázdného parametru vrací do dotazu prázdný řetězec, nikoli NULL. Proto musí být prázdnému poli parametru přiřazena vlastnost NULL pomocí NULLIF. Pak, protože položka parametru nyní dává hodnotu NULL, lze ji resetovat na hodnotu, která se normálně nevyskytuje v žádném záznamu. Ve výše uvedeném příkladu je to '$$'. Tuto hodnotu samozřejmě ve vyhledávání nenajdeme.

Od verze 4.4 jsou nutné úpravy této techniky dotazování:

LOWER ("Reader"."LastName") LIKE '%' || LOWER (:Readername) || '%'

musí v případě neexistence záznamu nevyhnutelně vést ke kombinaci:

 '%' || LOWER (:Readername) || '%' a the NULL value.

Abychom tomu zabránili, přidáme další podmínku, že pro prázdné pole se skutečně zobrazí všechny hodnoty:

(LOWER ("Reader"."LastName") LIKE '%' || LOWER (:Readername) || '%' OR: Readername IS NULL)

Celou věc je třeba dát do závorek. Pak se buď hledá jméno, nebo pokud je pole prázdné (NULL z LibreOffice 4.4), použije se druhá podmínka.

Při použití formulářů lze parametr předat z hlavního formuláře do podformuláře. Někdy se však stává, že se dotazy s parametry v dílčích formulářích neaktualizují, pokud jsou data změněna nebo nově zadána.

Často by bylo vhodné měnit obsah polí se seznamem pomocí nastavení v hlavním formuláři. Mohli bychom například zabránit půjčování médií v knihovně osobám, které mají v současné době zakázáno si média půjčovat. Ovládání nastavení seznamu tímto personalizovaným způsobem pomocí parametrů není možné.

Poddotazy

Poddotazy zabudované do polí mohou vždy vrátit pouze jeden záznam. Pole může také vracet pouze jednu hodnotu.

SELECT "ID", "Income", "Expenditure",
  ( SELECT SUM( "Income" ) - SUM( "Expenditure" )
     FROM "Checkout") AS "Balance"
FROM "Checkout";

Tento dotaz umožňuje zadávání dat (včetně primárního klíče). Dílčí dotaz poskytuje přesně jedna hodnotu, a to celkový zůstatek. To umožňuje po každém zadání odečíst zůstatek na pokladně. To ještě není srovnatelné s formulářem pokladny supermarketu popsaným v části „Dotazy jako základ pro další informace ve formulářích“ na straně 1. Samozřejmě chybí jednotlivé výpočty Total * Unit_price, ale také přítomnost čísla účtenky. Uvádí se pouze celkový součet. Pomocí parametru dotazu lze uvést alespoň číslo účtenky:

SELECT "ID", "Income", "Expenditure",
  ( SELECT SUM( "Income" ) - SUM( "Expenditure" )
     FROM "Checkout"
     WHERE "Receipt_ID" = :Receipt_Number) AS "Balance"
FROM "Checkout" WHERE "Receipt_ID" =: Receipt_Number;

V dotazu s parametry musí být parametr v obou příkazech dotazu stejný, pokud má být rozpoznán jako parametr.

U podformulářů lze tyto parametry zahrnout. Podformulář pak místo názvu pole obdrží odpovídající název parametru. Tento odkaz lze zadat pouze ve vlastnostech podformuláře, nikoli při použití Průvodce.

Poznámka

Dílčí formuláře založené na dotazech nejsou automaticky aktualizovány na základě jejich parametrů. Vhodnější je předávat parametr přímo z hlavního formuláře.

Související poddotazy

Pomocí ještě dokonalejšího dotazu, editovatelného dotazu, můžeme dokonce přenášet průběžný zůstatek pokladny:

SELECT "ID", "Income", "Expenditure",
( SELECT SUM( "Income" ) - SUM( "Expenditure" )
  FROM "Checkout"
  WHERE "ID" <= "a"."ID" ) AS "Balance"
FROM "Checkout" AS "a"
ORDER BY "ID" ASC

Tabulka Checkout je stejná jako tabulka "a". "a" však poskytuje pouze vztah k aktuálním hodnotám v tomto záznamu. Tímto způsobem lze v rámci poddotazu vyhodnotit aktuální hodnotu ID z vnějšího dotazu. V závislosti na ID se tedy určí předchozí zůstatek v příslušném čase, pokud vycházíme z toho, že ID, které je automatická hodnota, se zvyšuje samo o sobě.

Poznámka

Pokud má být obsah poddotazu filtrován pomocí funkce filtru editoru dotazů, funguje to v současné době pouze tehdy, pokud na začátku a na konci poddotazu použijeme dvojité závorky místo jednoduchých: ((SELECT ....)) AS "Saldo"

Dotazy jako zdrojové tabulky pro dotazy

Dotazem je nutné nastavit zámek proti všem čtenářům, kteří obdrželi třetí oznámení o zpoždění pro médium.

SELECT "Loan"."Reader_ID", '3rd Overdue – the reader is blacklisted' AS "Lock"
FROM
  (SELECT COUNT( "Date" ) AS "Total_Count", "Loan_ID"
     FROM "Recalls" GROUP BY "Loan_ID") AS "a",
  "Loan"
WHERE "a"."Loan_ID" = "Loan"."ID" AND "a"."Total_Count" > 2

Nejprve prozkoumejme vnitřní dotaz, ke kterému se vnější dotaz vztahuje. V tomto dotazu se zjišťuje počet záznamů data seskupených podle cizího klíče Loan_ID. To nesmí být závislé na Reader_ID, protože by se tak započítávala nejen tři oznámení o zpoždění pro jedno médium, ale také tři média, každé s jedním oznámením o zpoždění. Vnitřnímu dotazu je přidělen alias, aby mohl být propojen s identifikátorem Reader_ID z vnějšího dotazu.

Vnější dotaz se v tomto případě vztahuje pouze k podmíněnému vzorci z vnitřního dotazu. Zobrazuje pouze Reader_ID a text pro pole Lock, pokud se "Loan". "ID" a "a". "Loan_ID" rovnají a "a". "Total_Count" > 2.

V zásadě jsou všechna pole vnitřního dotazu dostupná vnějšímu dotazu. Takže například součet "a". "Total_Count" může být sloučen do vnějšího dotazu, aby se získal skutečný součet pokut.

V dialogovém okně Návrh dotazu se však může stát, že po takové konstrukci již nebude režim zobrazení návrhu fungovat. Pokud se pokusíme dotaz znovu otevřít pro úpravy, zobrazí se upozornění na obrázku 43:

Pokud pak otevřeme dotaz k úpravám v režimu SQL a pokusíme se z něj přepnout do režimu Návrh, zobrazí se chybová zpráva na obrázku 44:

V režimu zobrazení návrhu nelze najít pole obsažené ve vnitřním dotazu "Loan_ID", kterým se řídí vztah mezi vnitřním a vnějším dotazem.

Při spuštění dotazu v režimu SQL se odpovídající obsah poddotazu reprodukuje bez chyby. Proto v tomto případě nemusíme používat přímý režim SQL.

Vnější dotaz použil výsledky vnitřního dotazu k vytvoření konečných výsledků. Jedná se o seznam hodnot "Loan_ID", které by měly být uzamčeny a proč. Pokud chceme konečné výsledky dále omezit, použijeme funkce řazení a filtrování v grafickém uživatelském rozhraní.

Následující snímky obrazovky ukazují, jakými různými způsoby lze dojít k výsledku dotazu s poddotazy. Zde se dotaz do skladové databáze snaží zjistit, co má zákazník zaplatit u pokladny. Jednotlivé ceny se vynásobí počtem zakoupených položek a získá se mezisoučet. Pak je třeba určit součet těchto dílčích součtů. To vše musí být možné upravovat, aby bylo možné dotaz použít jako základ formuláře.

Dotaz znázorněný na obrázku 45 používá dvě tabulky (sale and articles). Aby bylo možné dotaz upravovat, musí být zahrnuty oba primární klíče.

Poznámka

Kvůli chybě 61871 aplikace Base neaktualizuje dílčí výsledek automaticky.

Na obrázku 46 je tabulka articles přesunuta do poddotazu, který je vytvořen v oblasti tabulky (za výrazem „FROM“) a je mu přidělen alias. Nyní již není primární klíč tabulky articles nezbytně nutný k tomu, aby byl dotaz editovatelný.

Na obrázku 47 se v dotazu musí objevit vypočtená částka. Již jednoduchý dotaz pro výpočet součtu není editovatelný, takže je zde seskupen a sečten.

Na obrázku 48 se druhým poddotazem stane zdánlivě nemožné možným. Předchozí dotaz je vložen jako poddotaz do definice tabulky hlavního dotazu (za „FROM“). Celý dotaz tak zůstává upravitelný. V tomto případě je možné provádět záznamy pouze ve sloupcích „Sum“ a „WarID“. To je následně jasně uvedeno ve formuláři dotazu.

Shrnutí dat pomocí dotazů

Při vyhledávání dat v celé databázi vede použití jednoduchých formulářových funkcí často k problémům. Formulář se koneckonců vztahuje pouze k jedné tabulce a vyhledávací funkce prochází pouze podkladové záznamy tohoto formuláře.

Získání všech dat je jednodušší, pokud používáme dotazy, které mohou poskytnout obraz o všech záznamech. V části „Definice relace v dotazu“ je navržena taková konstrukce dotazu. Ten je pro ukázkovou databázi vytvořen takto:

SELECT "Media"."Title", "Subtitle"."Subtitle", "Author"."Author"
FROM "Media"
  LEFT JOIN "Subtitle"
     ON "Media"."ID" = "Subtitle"."Media_ID"
  LEFT JOIN "rel_Media_Author"
     ON "Media"."ID" = "rel_Media_Author"."Media_ID"
  LEFT JOIN "Author"
     ON "rel_Media_Author"."Author_ID" = "Author"."ID"

Zde jsou všechny tituly (Titles), podtituly (Subtitles) a autoři (Authors) zobrazeni společně.

Tabulka Media obsahuje celkem 9 titulů. U dvou z těchto titulů je k dispozici celkem 8 podtitulů. Bez spojení LEFT JOIN se v obou tabulkách zobrazí dohromady pouze 8 záznamů. Pro každý podtitul se vyhledá odpovídající název a tím dotaz končí. Tituly bez podtitulů se nezobrazují.

Nyní se zobrazí všechna média včetně těch bez podtitulů: hodnota pole Media je na levé straně zadání, Subtitles na pravé straně. LEFT JOIN zobrazí všechny hodnoty Title z tabulky Media, ale hodnoty Subtitle pouze pro ty, které mají hodnotu Title. Tabulka Media se stává rozhodující tabulkou pro určení, které záznamy se mají zobrazit. To bylo plánováno již při sestavování tabulky (viz kapitola 3, Tabulky). Protože pro dva z devíti titulů existují podtituly, dotaz nyní zobrazí 9 + 8 – 2 = 15 záznamů.

Poznámka

Běžné propojení tabulek po vypsání všech tabulek následuje po klíčovém slově WHERE.

V případě spojení LEFT JOIN nebo RIGHT JOIN se přiřazení definuje přímo za názvy obou tabulek pomocí ON. Pořadí je tedy vždy
Tabulka1 LEFT JOIN Tabulka2 ON Tabulka1.Pole1 = Tabulka2.Pole1 LEFT JOIN Tabulka3 ON Tabulka2.Pole1 = Tabulka3.Pole1...

Dva tituly v tabulce Media zatím nemají položku Author nebo Subtitle. Zároveň má jeden titul celkem tři autory. Pokud je tabulka Author propojena bez spojení LEFT JOIN, nezobrazí se dvě Media bez autora. Protože však jedno médium má tři autory místo jednoho, celkový počet zobrazených záznamů bude stále 15.

Pouze pomocí spojení LEFT JOIN bude dotaz instruován, aby použil tabulku Media k určení záznamů, které se mají zobrazit. Nyní se opět objeví záznamy bez hodnoty Subtitle nebo Author, celkem tedy 17 záznamů.

Použití vhodných spojů obvykle zvyšuje množství zobrazených dat. Tento rozšířený soubor dat lze však snadno skenovat, protože kromě názvů se zobrazují i autoři a podtituly. V ukázkové databázi lze přistupovat ke všem tabulkám závislým na médiích.

Rychlejší přístup k dotazům pomocí zobrazení tabulek

Pohledy v SQL jsou rychlejší než dotazy, zejména pro externí databáze, protože jsou ukotveny přímo v databázi a server vrací pouze výsledky. Naproti tomu dotazy jsou nejprve odeslány na server a tam zpracovány.

Pokud se nový dotaz vztahuje k jinému dotazu, režim SQL v databázi Base vytvoří z jiného dotazu tabulku. Pokud z něj vytvoříte View, uvidíte, že ve skutečnosti pracujete s poddotazem (Select použitý v rámci jiného Selectu). Z tohoto důvodu nelze Query 2, který se vztahuje k jinému Query 1, spustit přímo pomocí příkazu Edit > Spustit SQL, protože o Query 1 ví pouze grafické uživatelské rozhraní, a nikoli samotná databáze.

Databáze neumožňuje přímý přístup k dotazům. To platí i pro přístup pomocí maker. Naproti tomu k pohledům lze přistupovat jak z maker, tak z tabulek. V zobrazení však nelze upravovat žádné záznamy. (Musí být upraveny v tabulce nebo formuláři.)

Tip

Dotaz vytvořený pomocí funkce Vytvořit dotaz v režimu SQL má tu nevýhodu, že jej nelze řadit ani filtrovat pomocí grafického uživatelského rozhraní. Jeho použití je proto omezeno.

Naproti tomu Pohledy lze spravovat v aplikaci Base stejně jako běžnou tabulku – s tím rozdílem, že není možné měnit data. Zde jsou tedy i v přímých příkazech SQL k dispozici všechny možnosti řazení a filtrování.

Kromě toho se formátování sloupců v pohledu zachovává i po uzavření databáze, na rozdíl od sloupců v dotazu.

Pohledy jsou řešením mnoha dotazů, pokud chceme vůbec získat nějaké výsledky. Pokud se má například na výsledky dotazu použít podvýběr, vytvořte Pohled, který vám tyto výsledky poskytne. Poté použijeme dílčí výběr v okně Pohled. Odpovídající příklady najdeme v kapitole 8, Úlohy databáze.

Vytvoření pohledu z dotazu je poměrně snadné a přímočaré.

1. Klepneme na objekt Tabulka v části Databáze.

2. Klepneme na Vytvořit pohled.

3. Zavřeme dialogové okno Přidat tabulku.

4. Klepneme na ikonu Režim návrh zap/vyp. (Jedná se o režim SQL pro Pohled.)

5. Získání SQL pro Pohled:

1. Upravíme dotaz v režimu SQL.

2. Pomocí Control+A zvýrazníme SQL dotaz.

3. Pomocí Control+C zkopírujeme SQL.

1. V zobrazení režimu SQL použijeme klávesy Control+V pro vložení SQL.

2. Pohled zavřeme, uložíme a pojmenujeme.

Chyby ve výpočtech v dotazech

K výpočtu hodnot se používají také dotazy. Někdy interní databáze HSQLDB vykazuje zdánlivé chyby, které se při bližším zkoumání ukáží jako logicky správné interpretace dat. Objevují se také problémy se zaokrouhlováním, které mohou snadno způsobit zmatek.

Časy v HSQLDB jsou správně formátovány pouze do rozdílu 23:59:59 hodin. Pokud je třeba sečíst více časů, například pro výpočet odpracovaných hodin, je třeba najít jiný způsob. Zde existuje několik složitých přístupů:

• Čas se přímo vyjadřuje pouze v součtu minut nebo dokonce sekund. Výhoda: hodnoty umožňují následný bezproblémový výpočet.

• Čas je rozdělen na části hodiny, minuty a sekundy a znovu sestaven jako text s použitím znaku ':' jako oddělovače. Výhoda: text se v dotazech od začátku zobrazuje jako správně naformátovaný čas.

• Čas je vytvořen jako desetinné číslo. Den je 1, hodina je 1/24 atd. Výhoda: hodnoty lze následně v dotazu přeformátovat na čas a prezentovat je jako formátovatelné formulářové pole.

Funkce DATEDIFF umožňuje určit časové intervaly. Výslovně se ptá na rozdíl, který má být určen. V příkladu na obrázku 49 nebyly požadovány minuty, ale jsou zohledněny všechny prvky, které jsou delší než minuta. Rozdíl mezi 8:59 a 9:00 je tedy jedna hodina (!). Naproti tomu rozdíl dat se počítá jako časový rozdíl v hodinách. Pokud je například pole Date_time_end nastaveno na 2.10.14 09:00, je to počítáno jako 25 hodin.

Pokud se místo toho časový interval vypočítá v minutách a poté se vydělí 60, časový rozdíl v hodinách se stane nulovým, jak je znázorněno na obrázku 50. Tohle vypadá spíš jako správná hodnota, až na to, kam se poděla ta jedna minuta?

Na obrázku 51 je časový interval zadán jako celé číslo. Celé číslo bylo děleno celým číslem. Výsledkem dotazu musí být i v těchto případech celé, nikoli desetinné číslo. To lze snadno opravit. Časový rozdíl v minutách se vydělí desetinným číslem se dvěma desetinnými místy (60,00). Výsledek má rovněž dvě desetinná místa. 0,02 hodiny stále není přesně jedna minuta, ale je jí mnohem blíže než dříve. Počet desetinných míst by bylo možné zvýšit použitím více nul. Perioda 0,016 je ještě bližší přiblížení, ale pozdější chyby ve výpočtu nelze vždy vyloučit.

Místo toho, abychom museli pracovat se spoustou přidaných nul, lze datový typ DATEDIFF ovlivnit přímo. Pomocí (CONVERT(DATEDIFF('mi', "Date_time_start", "Date_time_end"),DECIMAL(50,49)))/60 lze dosáhnout přesnosti 49 desetinných míst.

Při používání výpočetních funkcí si musíme vždy uvědomit, že datové typy v HSQLDB mají pouze omezenou přesnost. Ať už použijeme jakýkoli počet desetinných míst, faktem zůstává, že mezilehlé výsledky zahrnující počítání času lze pro další výpočty použít pouze v omezené míře.

Pokud má být časová hodnota následně použita ve formuláři nebo sestavě jako formátovaný čas, musíme zajistit, aby byl den platný jako základ pro formátování času.

Na obrázku 52 je rozdíl vypočítán v minutách. Výsledek se udává jako zlomek dne. Jeden den má 60 * 24 minut. Pokud bychom jednoduše vydělili 1440, výsledek by byl nula, takže opět musíme explicitně uvést desetinná místa. Pak se zobrazí jako formátovaný čas 0 hodin a 1 minuta.

Kód formátu pro čas delší než jeden den je [HH]:MM. Pokud použijeme nesprávný formát, může se časový rozdíl 1 den a 1 minuta zobrazit pouze jako 1 minuta.

Chybový ďábel znovu udeřil! Časový rozdíl 10 minut by se při správném formátování podle obrázku 53 neměl zobrazit jako 9 minut. Abychom zjistili, v čem spočívá problém, musíme se zabývat tím, jak přesně se výpočet provádí:

1. 10/1440 = 0.00694. Výsledek je zaokrouhlen na 0,0069, protože byla zadána pouze čtyři desetinná místa.

2. 0,0069 * 1440 = 9,936 minuty, což je 9 minut 56,16 sekundy. A sekundy se nezobrazují ve zvoleném formátu!

Konečně na obrázku 54  prodloužení dělitele o jedno desetinné místo (z 1440,0000 na 1440,00000) tuto chybu vyřeší. Nyní se zaokrouhluje na 0,00694. 0,00694*1440 dává 9,9936, což je 59,616 sekundy. Počet vteřin se zaokrouhluje na 60 vteřin, takže k 9 minutám se přičte 1 minuta, celkem tedy 10 minut.

I zde se mohou vyskytnout další problémy. Mohou existovat další desetinná místa, která při formátování nedávají 1 minutu? K vyřešení tohoto problému může pomoci krátký výpočet pomocí programu Calc s podobně zaokrouhlenými čísly. Sloupec A obsahuje posloupnost čísel od 1 (pro minuty). Sloupec B obsahuje vzorec =ROUND(A1/1440;4) a je formátován tak, aby zobrazoval hodiny a minuty. Pokud budeme pokračovat směrem dolů, uvidíme vedle 10 minut ve sloupci A hodnotu 00:09 ve sloupci B. Podobně pro 28 minut atd. Pokud zaokrouhlíme na 5 míst, tyto chyby zmizí.

Jakkoli by bylo příjemné mít ve formuláři vhodně naformátované zobrazení, je třeba si uvědomit, že se jedná o zaokrouhlené hodnoty, které nejsou vhodné pro další použití při slepých mechanických výpočtech. Pokud je třeba hodnotu použít pro další výpočet, je lepší použít pouze vyjádření časového rozdílu v nejmenších dostupných jednotkách, v tomto případě v minutách.