3. hodina, 6.10. 2014, Trochu blíže rocket science 
Datum: 6. října 2014 
Hlavní cíl hodiny: Agregační funkce, pokročilejší práce s textem 
Soubor s databází: http://is.muni.cz/el/1421/podzim2014/VIKMB44/um/50892092/books.sqlite 
Vysvětlené části: 
1. Aliasy 
2. NULL 
3. Agregační funkce 
4. LIKE 
5. Pokročilejší práce s řetězci.  
6. Práce s datumem 
 
DOMÁCÍ ÚKOL z 2. přednášky: Základní dotazování 
1. Vypiš jméno, datum a originální jazyk knihy, která je nejstarší, napsaná v originále v 
jazyce “English“, nebo v “German “a je přeložena do jazyka “Croatian“. 
2. Vypiš jméno, datum a jazyk knihy, která je nejstarší, napsaná v originále v jazyce 
“English“, nebo v “German“. Napsáne byla mezi roky 1950 a 2000 včetně. 
3. Vypiš knihy vydavatele “Ikar”. Originální jazyk knih je “French” a zároveň country 
“Slovakia”. Nebo je originální jazyk “English” a country “Czech Republic”. Vydání knih je v 
roce 2000 nebo v roce 2012.  
 
1. SELECT title, year, language_original FROM books WHERE (language_original = 
"English" OR language_original = "German") AND language_translation = 
"Croatian" ORDER BY year ASC LIMIT 1 
2. SELECT title, year, language_original FROM books WHERE (language_original = 
"English" OR language_original = "German") AND year BETWEEN 1950 AND 2000 
ORDER BY year ASC LIMIT 1 
3. SELECT * FROM books WHERE ((language_original = "French" AND country = 
"Slovakia") OR (language_original = "English" AND country = "Czech Republic")) 
AND (year = 2000 OR year = 2012) AND publisher = "Ikar" 
 
 
 
 
 
 
 
 
 
 
 
 
 
SQL dotazy: 
 
NULL, NOT NULL 
1. Najdi mi nejstarší knihu. 
2. Najdi mi nejstarší knihu, která má vyplněné datum vydání. 
3. Najdi mi knihy bez data vydání. 
4. Najdi mi knihy bez originálního jazyka. 
 
LIKE ­ používáme primárně pro vyhledávání v textu 
 
5. Najdi mi knihy, které napsal author “Drábek, Jan” 
6. Najdi mi knihy, které napsal author, se jménem končícím na “jan” 
7. Najdi mi knihy, které napsal author  jehož jméno neobsahuje “jan” 
8. Najdi mi knihy, které napsal author  jehož jméno začíná na “anderson” 
 
AGREGAČNÍ FUNKCE 
https://www.sqlite.org/lang_aggfunc.html 
 
9. Počet knih v databázi, jejichž autor obsahuje “jan”. 
10. Rok vydání nejstarší knihy. 
11. Průměrný věk vydání knihy zaokrouhleno na celá čísla a počet takových to knih v 
databázi,. Knihy byly napsány v originálním jazyce “czech” ale jazyk přeložení není 
“czech”. 
12. Součet let, počet knih, průměrný věk knih. Originální jazyk “English” a vydání mezi 
rokem 1980 a 2000. Knihy byly přeloženy do jazyka začínající na “czech” nebo do 
jazyka “Slovakia”. Knihy mají nějakého autora. 
 
PRÁCE S TEXTEM a DATEM a ALIAS = AS 
https://www.sqlite.org/lang_corefunc.html 
https://www.sqlite.org/lang_datefunc.html 
 
13. Najdi mi knihy seřazené podle délky názvu. Vypiš i s délkou názvů. 
14. Najdi mi titulek knihy, autora, délku názvu. Knihy jsou seřazené podle délky názvu. 
Vypiš jich 10 nejdelších. 
15. Najdi mi titulek knihy, autora, délku názvu. Knihy jsou seřazené podle délky názvu a 
jejich délka názvu je mezi 2 a 3. Vypiš jich 10 nejkratších. 
 
16. Najdi mi titulek knihy, autory, délku názvu knihy a spojené informace o zemi vydání, 
originálním jazyce, jazyce přeložení JAKO "země, originální jazyk, jazyk přeložení". 
Knihy jsou seřazeny podle délky názvu od nejdelšího. Vypiš nejdelších 10. 
17. Najdi mi počet knih starší pět let. 
18. Najdi mi autora a odstraň z jeho jména čárky, jako “Autoři” a prvních 10 znaků názvu 
knihy jako “Zkrácený titulek”, rok vydání knihy jako “rok vydání”. Knihy napsal author 
začínající na “Drábek”, seřaď je podle data vydání od nejstarší knihy. 
 
19. Rok vydání nejmladší knihy která je vydána dříve než je dnešní datum a vypiš dnešní 
datum.  
 
 
TAKOVÉ TO DOMÁCÍ PROCVIČOVÁNÍ 
1. Vypiš: Součet počtu znaků názvu, součet počtu znaků originálního názvu, počet knih, 
průměrný věk knih (zaokroulený a nezaokroulený­spočítaný).  
Najdi knihy, kde originální jazyk “French” a vydání mezi rokem 1980 a zároveň kniha 
musí být více než 14 let stará (od dnešního dne). Knihy byly přeloženy do jazyka 
začínající na “czech” nebo do jazyka “slovakia” a musí mít vyplněného autora. 
Vydavatel knihy nesmí obsahovat znak “a” nebo znak “e”. Délka originálního názvu 
musí být kratší než délka přeloženého názvu. 
 
 
ŘEŠENÍ 
1. SELECT * FROM books ORDER BY year ASC LIMIT 1 
U tohoto dotazu je problém, protože vrácený výsledek nemá informaci o roku 
vydání knihy. Srrávně by to tedy mělo být jako dotaz 2. 
2. SELECT * FROM books WHERE year IS NOT NULL ORDER BY year ASC LIMIT 1 
3. SELECT * FROM books WHERE year IS NULL; 
4. SELECT * FROM books WHERE language_original IS NULL; 
 
LIKE ­ používáme primárně pro vyhledávání v textu, nemusíme pak  např. řešit velká a 
malá písmena v textu 
 
5. SELECT * FROM books WHERE authors = "drábek, jan"; 
vyzkoušejte obě varianty, druhá je správná 
SELECT * FROM books WHERE authors LIKE "drábek, jan"; 
6. SELECT * FROM books WHERE authors LIKE "%jan"; 
7. SELECT * FROM books WHERE authors NOT LIKE "%jan%"; 
8. SELECT * FROM books WHERE authors LIKE "anderson%"; 
 
 
AGREGAČNÍ FUNKCE 
https://www.sqlite.org/lang_aggfunc.html 
 
9. SELECT count(*) FROM books WHERE authors LIKE "%jan%"; 
10. SELECT MIN(year) FROM books; 
11. SELECT ROUND(AVG(year), 0), COUNT(*) FROM books WHERE language_original 
LIKE "czech" AND language_translation NOT LIKE "czech"; 
12. SELECT SUM(year), COUNT(*), ROUND(AVG(year), 0) FROM books WHERE 
language_original LIKE "english" AND year BETWEEN 1980 AND 2000 AND 
(language_translation LIKE "czech%" OR language_translation LIKE "slovakia%") AND 
authors IS NOT NULL; 
 
 
PRÁCE S TEXTEM a DATEM a ALIAS = AS 
https://www.sqlite.org/lang_corefunc.html 
https://www.sqlite.org/lang_datefunc.html 
13. SELECT *,  LENGTH(title) FROM books ORDER BY LENGTH(title) DESC; 
14. SELECT title, authors, LENGTH(title) AS delka FROM books ORDER BY title 
DESC LIMIT 10. 
Běžný ORDER nám tu nestačí, protože se snaží seřazovat podle abecedy. 
Využijeme tedy funkci LENGTH a usnadníme si práci pomocí AS. Správné 
řešení: 
SELECT title, authors, LENGTH(title) AS delka FROM books ORDER BY delka 
DESC LIMIT 10. 
15. SELECT title, authors, LENGTH(title) AS delka FROM books WHERE delka BETWEEN 
2 AND 3 ORDER BY delka ASC LIMIT 10. 
16. SELECT title, authors, LENGTH(title) AS delka, (country || ' ' || language_original || ' ' || 
language_translation) AS "země, originální jazyk, jazyk přeložení" FROM books ORDER 
BY delka DESC LIMIT 10 
17. SELECT COUNT(*) FROM books WHERE year < date('now','­5 years'); 
18. SELECT REPLACE(authors,",", " ") AS "Autoři", SUBSTR(title, 0, 9) AS "Zkrácený 
titulek", year AS "Rok vydání"  FROM books WHERE authors LIKE "drábek%" ORDER 
BY year ASC 
19. SELECT DATE("now") AS "dnes", MIN(year) AS "year" FROM books WHERE year < 
dnes