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/jaro2016/VIKBB68/um/62039345/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 knihy bez originálního jazyka. 
 
BETWEEN 
3. Najdi mi knihy napsané mezi roky 1950 ­ 2000, včetně. 
IN 
4. Najdi mi kniny napsané v roce 1968 a 1989. 
 
 
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 “Slovak”. 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.Rok vydání nejmladší knihy která je vydána dříve než je dnešní datum a vypiš 
dnešní datum.  
15.Najdi mi titulek knihy, autora, délku názvu. Knihy jsou seřazené podle délky 
názvu. Vypiš jich 10 nejdelších. 
16.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. 
 
17.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. 
18.Najdi mi počet knih starší pět let. 
19.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. 
 
 
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. 
SELECT * FROM books WHERE year IS NOT NULL ORDER BY year ASC LIMIT 1 
2. SELECT * FROM books WHERE language_original IS NULL; 
 
BETWEEN 
3. SELECT * FROM books WHERE year BETWEEN 1950 AND 2000; 
IN 
4. SELECT * FROM books WHERE year IN (1968,1989); 
 
 
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 "slovak") 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; 
s ALIASEM 
SELECT *,  LENGTH(title) AS delka FROM books ORDER BY delka DESC; 
14.SELECT DATE("now") AS "dnes", MAX(year) AS "year" FROM books WHERE year < 
dnes; 
15. 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. 
16. SELECT title, authors, LENGTH(title) AS delka FROM books WHERE delka BETWEEN 
2 AND 3 ORDER BY delka ASC LIMIT 10; 
17. 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; 
18. SELECT COUNT(*) FROM books WHERE year < date('now','­5 years'); 
19. 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 
20.  
Řešení domácího úkolu: 
SELECT SUM(LENGTH(title)), SUM(LENGTH(original_title)), COUNT(*), 
ROUND(AVG(year)), SUM(year)/COUNT(year) 
FROM books WHERE  
year BETWEEN 1980 AND date('now', "­14 year") AND (language_translation LIKE 
"czech%" OR language_translation LIKE "slovakia") AND authors IS NOT NULL AND 
publisher NOT LIKE "%a%" AND publisher NOT LIKE "%e%" AND LENGTH(title) > 
LENGTH(original_title) AND language_original LIKE "french";