6.9. Funkce SQL

6.9.1. Příprava / opakování

1 Stáhněte si přes FTP z fileserveru pomocí účtu anonym soubor db07.zip a extrahujte jej tak, aby rozbalené soubory byly ve složce c:\temp\db07 FileZilla
2 Otevřte si v Excelu sešit odpovědi.xls, do kterého budete zapisovat výsledky hledání žlutých řádků podle zadání níže Excel
3 Otevřte v textovém editoru soubor citaty.sql a doplňte příkazy pro vytvoření tabulky citaty a indexu index_autor. Všechny příkazy navíc obalte do transakce (viz vzor.sql). PSPad
4 Spusťte příkazový řádek a přejděte v něm do složky db07 cmd, cd
5 Vytvořte ve složce db07 novou databázi db07.sqlite spuštěním:
c:\programy\sqlite\sqlite3 db07.sqlite
cmd, sqlite
6 Proveďte připravené SQL příkazy načtením souboru citaty.sql .read
7 Ověřte funkčnost databáze - najděte citáty od Rooseveltů (použijte operátor like) select ...
8 Omezte výsledek na prvních 10 citátů ... limit ...
9 Uspořádajte výsledky podle času (sloupce datum) ... order by ...

6.9.2. Matematické funkce

count() - vrací počet hodnot

10 Vypište celkový počet řádků tabulky citaty příkazem select count(*) from citaty;  
11 Vypište počet citátů s uvedeným rokem 1995 (... datum like ...) xlsx
12 Vypište počet autorů citátů (... count(distinct autor) ...) xlsx

random() - vrací náhodné celé číslo

13 Vypište náhodný citát příkazem select * from citaty order by random() limit 1;  

sum() - součet

14 Vypočítejte celkový součet hodnocení citátů u autora Goethe:
select sum(hodnoceni) from citaty where ...;
xlsx
15 Vypočítejte průměrné hodnocení citátů u stejného autora (sum/count) - všimněte si zaokrouhlení (výsledek je celé číslo)  

avg() - aritmetický průměr

round(x,y) - zaokrouhlení čísla x na y desetinných míst

16 Vypočítejte průměrné hodnocení citátů u stejného autora pomocí funkce avg a výsledek zaokrouhlete na dvě desetinná místa xlsx

min(), max() - minimum, maximum

6.9.3. Alias - AS

Cvičení
17 Nastavte si výpis ve sloupcích včetně záhlaví (.headers on, .mode column) a zopakujte poslední prováděný příkaz - v názvu sloupce je použitá funkce  
18 Vypisovaný sloupec pomocí aliasu nazvěte "prumer":
select round(avg(hodnoceni),2) as prumer from citaty ...;
 

6.9.4. Seskupování řádků - GROUP BY

19 Vypište témata citátů (bez duplicit - použijte distinct)  
20 Vypište počet témat (pomocí count) xlsx
21 Vypište témata včetně počtu citátů u každého tématu:
select tema, count(tema) ... group by tema;
 
22 Ověřte nastavení parametrů sqlite příkazem .show (musí být zapnuto zobrazování ve sloupcích - columns - a zobrazování záhlaví - headers)  
23 Zjistěte pomocí příkazu .help jak se nastavuje šířka zobrazených sloupců a nastavte šířku prvního sloupce na 20 znaků. Znovu proveďte poslední select a ověřte, že témata jsou vypsána bez zkrácení  
24 K výpisu přidejte další sloupec s průměrným hodnocením každého tématu zaokrouhleným na 2 desetinná místa
... round(avg(...),2) ...
 
25 Pro poslední sloupec si nadefinujte alias prumer
... as prumer ...
 
26 Stejný výpis uspořádejte od nejlepšího hodnocení k nejhoršímu (sestupně)
... order by prumer desc;
 

6.9.5. Filtrování po seskupení - HAVING

27 Naposled použitý výpis omezte (filtrujte) jen na průměry větší než 5:
... group by tema having prumer > 5 ...
 
28 Zapněte si výpis příkazů (.echo on) a výstup do souboru temata.txt (.output temata.txt) a proveďte znovu předchozí příkaz pro výpis do souboru. Poté si přesměrujte výstup zpátky na obrazovku (.output stdout)  

6.9.6. Funkce pro datum a čas

29 Vypište aktuální čas:
select time('now');
 
30 Výpis je třeba opravit podle časového pásma:
select time('now', 'localtime');
 
31 Vypište datum za 1 měsíc:
select date('now','+1 month');
 
32 Podobně vypište datum za 250 dnů xlsx
33 Počet dnů mezi dvěma daty (zde od vypuknutí 2. sv. války):
select julianday('now')-julianday('1939-09-01');
 
34 Vypište zaokrouhlený počet dnů do prvního máje xlsx
35 Vypište dnešní datum v běžně používané podobě:
select strftime('%d.%m.%Y','now','localtime');
 

6.9.7. Textové funkce

upper() - vrací velká písmena (jen ASCII), lower() - malá písmena

substr(x,y,z) - podřetězec vstupního x od písmena y (první písmeno=1) v délce z

length() - délka řetězce

36 Zapněte si režim výpisu seznam (.mode list)  
37 Vypište z tabulky citáty nejkratší citát:
select citat from citaty order by length(citat) limit 1;
 
38 Vypište z tabulky citáty nejdelší citát a do Excelu zapište délku tohoto citátu xlsx
39 Vypište autora s nejkratším jménem  
40 Zapište do Excelu největší délku jména autora xlsx
41 Vypište z tabulky citáty rok nejstaršího citátu (použijte funkci substr) a zapište jej do Excelu xlsx
42 Uložte a zavřete otevřené dokumenty a celou složku db07 (se soubory db07.sqlite, citaty.sql, odpovědi.xls, temata.txt) uložte na fileserver Filezilla