3. hodina, Trochu blíže rocket science
Datum: 3. října 2019
Hlavní cíl hodiny: ​Agregační funkce, pokročilejší práce s textem
Soubor s databází:
https://is.muni.cz/auth/el/phil/podzim2019/ISKB56/um/03_prednaska/books_02_03.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í a titulek nejmladší knihy která je starší než dnes - 10 roků.
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. Sprá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 title, year, DATE("now", "-10 year") AS "tehdy" FROM books WHERE year <
tehdy ORDER BY year DESC
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";