Hodina: 22.11. 2018
Databáze:
https://is.muni.cz/auth/el/1421/podzim2018/VIKBB68/um/09_prednaska/books_complex.sqlite
1. Vypiš vydavatele knihy jejichž jméno obsahu “a” a počet knih vydaných tímto
vydavatelstvím. Délka názvu knih musí být delší než 9 a vydaných knih od tohoto
vydavatelství musí být mezi 100 - 300.
2. LEFT JOIN a pořadí tabulek
a. Najdi všechny knihy a spočítej používané jazyky (i knihy bez jazyku).
Vypiš jazyk a počet knih v něm napsaných.
b. Najdi všechny knihy s vyplněným jazykem. Vypiš jazyk a počet knih v něm
napsaných.
c. Vypiš všechny jazyky a k nim spočítej poček knih v něm napsaných.
3. Vypiš knihu a autory knihy.
4. Vypiš knihy, autory knihy a počet autorů knihy. Seřaď podle počtu autorů
sestupně.
5. Vypiš počet unikátních autorů v databázi pomocí tabulky books_authors.
6. Vypiš autory s nejvíce napsanými knihami a počet jejich knih. Seřaď podle počtu
knih sestupně. Vyřaď duplicity podle názvu knihy! (můžete vyzkoušet i podle id
knihy)
7. Vypiš knihy autora s id 3563.
8. Vypiš unikátní knihy autora se jménem “Jaroslav Peprník”.
9. Vypiš vydavatele a počet jeho knih a počet autorů knih kde: Jméno vydavatele je
kratší než 10 znaků a název knihy začíná nebo končí na “1” a počet autoru je
více než 3. Seřaď podle počtu knih sestupně.
1. SELECT p.publisher, COUNT(b.id) pocet_knih FROM publishers p LEFT JOIN
books b ON b.publisher_id = p.id WHERE p.publisher like '%a%' AND
LENGTH(b.title) > 9 GROUP BY p.id HAVING pocet_knih BETWEEN 100 AND
300 ORDER BY pocet_knih DESC;
2. LEFT JOIN a pořadí tabulek
a. SELECT l.language, COUNT(b.id) AS pocet_knih FROM books b LEFT
JOIN languages l ON (b.language_id = l.id) GROUP BY l.id ORDER BY
pocet_knih DESC
Vidíme i velké množství knih bez jazyku, nevidíme jazyk srp
b. SELECT l.language, COUNT(b.id) AS pocet_knih FROM books b INNER
JOIN languages l ON (b.language_id = l.id) GROUP BY l.id ORDER BY
pocet_knih DESC
Vidíme pouze knihy s jazykem
c. SELECT l.language, COUNT(b.id) AS pocet_knih FROM languages l
LEFT JOIN books b ON (b.language_id = l.id) GROUP BY l.id ORDER
BY pocet_knih ASC
Vidíme všechny jazyky a k nim počet knih (jazyk srp nemá žádné
knihy)
3. SELECT b.title, a.name FROM books b INNER JOIN books_authors b_a ON
(b.id = b_a.book_id) INNER JOIN authors a ON (a.id = b_a.author_id)
Spojení záznamů z vazební tabulky do jednoho sloupce.
SELECT b.title, GROUP_CONCAT(a.name) FROM books b INNER JOIN
books_authors b_a ON (b.id = b_a.book_id) INNER JOIN authors a ON (a.id =
b_a.author_id) GROUP BY b.id;
4. SELECT b.id, b.title, GROUP_CONCAT(a.name), COUNT(a.id) AS pocet_autoru
FROM books b INNER JOIN books_authors b_a ON (b.id = b_a.book_id) INNER
JOIN authors a ON (a.id = b_a.author_id) GROUP BY b.id ORDER BY
pocet_autoru DESC;
5. DISTINCT
SELECT COUNT(author_id) FROM books_authors;
VS
SELECT COUNT(DISTINCT author_id) FROM books_authors;
6. SELECT a.id AS author_id, a.name, COUNT(DISTINCT b.title) AS pocet_knih
FROM books_authors ba INNER JOIN authors a ON (a.id = ba.author_id) INNER
JOIN books b ON (b.id = ba.book_id) GROUP BY a.id ORDER BY pocet_knih
DESC;
Některé knihy mají stejný název (ale jiné id knihy, např. může jít o jiné
vydání etc), takže takto bychom jejich počet nespočetli správně. Pokud
můžeme, počítáme optimálně pomocí primárního klíče.
SELECT a.id AS author_id, a.name, COUNT(DISTINCT b.id) AS pocet_knih
FROM books_authors ba INNER JOIN authors a ON (a.id = ba.author_id) INNER
JOIN books b ON (b.id = ba.book_id) GROUP BY a.id ORDER BY pocet_knih
DESC;
7. Jednotlivé svazky v knihovně
SELECT b.title FROM books b INNER JOIN books_authors ba ON (b.id =
ba.book_id) WHERE author_id = 3563;
Odstranění duplicit
SELECT DISTINCT b.title FROM books b INNER JOIN books_authors ba ON
(b.id = ba.book_id) WHERE author_id = 3563;
8. SELECT DISTINCT b.title FROM books b INNER JOIN books_authors ba ON
(b.id = ba.book_id) INNER JOIN authors a ON (a.id = ba.author_id) WHERE
a.name LIKE "Jaroslav Peprník";
9. SELECT p.id, p.publisher, COUNT(DISTINCT b.id) pocet_knih,
COUNT(DISTINCT a.id) pocet_autoru FROM publishers p LEFT JOIN books b
ON b.publisher_id = p.id LEFT JOIN books_authors ba ON ba.book_id = b.id
LEFT JOIN authors a ON a.id = ba.author_id WHERE LENGTH(p.publisher) < 10
AND (b.title LIKE '1%' OR b.title LIKE '%1') GROUP BY p.id HAVING
pocet_autoru > 3 ORDER BY pocet_knih DESC
pokud zapomeneme na DISTINCT(b.id) každá kniha se nám v počtu
zduplikuje za každého autora knihy
Procvičování:
1. Vypište autory jejichž jméno začíná na “jiří” kteří napsalí více než 5 knih.