Databáze:
https://is.muni.cz/auth/el/1421/podzim2018/VIKBB68/um/07_prednaska/books_2.sqlite
Dotazy:
1. Počet knih VS primární klíč. Souhlasí ?
2. Vypiš všechny knihy s vydavetelem.
3. Vypiš všechny knihy s jazykem i vydavatelem-
4. Vypiš vydatele a počet jím vydaných knih. Seřaď od nejčastějšího vydavatele.
5. Vypiš jazyk a počet knih v něm napsaných. Seřaď od nejčastějšího jazyka.
6. Vypiš počet všech knih.
7. Vypiš všechny knihy. Vypiš název knihy a jejich vydavatele.
8. Vypiš všechny knihy. Vypiš jejich vydavatele a jazyk v němž jsou napsány.
(název knihy, jméno jazyka, jméno vydavatele)
9. Vypiš všechny vydavatele knihy a počet knih jež vydal. Seřaď od vydavatele s
nejméně knihami.
10.Vypiš vydavatele knihy obsahující ‘​a’ ​a počet knih vydaných tímto
vydavatelstvím. Vydaných knih od tohoto vydavatelství musí být mezi 100 - 300.
11.Vypiš počet vydavatelů, jež nemají v databázi žádnou knihu.
12.Vypiš počet vydavatelů, jež mají v databázi knihu.
Řešení:
1. Počet knih v tabulce je <= nejvyššímu primárnímu klíči.
2. SELECT b.title, b.year, p.publisher FROM books b INNER JOIN publishers p ON
(b.publisher_id = p.id);
3. SELECT b.title, b.year, p.publisher, l.language FROM books b INNER JOIN
publishers p ON (b.publisher_id = p.id) INNER JOIN languages l ON
(b.language_id = l.id);
4. SELECT p.publisher, COUNT(b.id) AS pocet_knih FROM publishers p INNER
JOIN books b ON (p.id = b.publisher_id) GROUP BY p.publisher ORDER BY
pocet_knih DESC​;
Velice podobná otázka na které si můžeme ukázat, že nezáleží na pořadí tabulek v
INNER JOIN ani na pořadí porovnávání klíčů v ON (...)
5. SELECT l.language, COUNT(b.id) AS pocet_knih FROM books b INNER JOIN
languages l ON (l.id = b.language_id) GROUP BY l.language ORDER BY
pocet_knih DESC
a druhá možnost je GROUP BY l.id
SELECT l.language, COUNT(b.id) AS pocet_knih FROM books b INNER JOIN
languages l ON (l.id = b.language_id) GROUP BY b.language_id ORDER BY
pocet_knih DESC
6. SELECT COUNT(*) FROM books;
7. SELECT b.title, p.publisher FROM books b INNER JOIN publishers p ON
b.publisher_id = p.id;
Tímto dotazem S INNER JOIN se připravíme o knihy bez vydavatele!
Správně:
SELECT b.title, p.publisher FROM books b LEFT JOIN publishers p ON
b.publisher_id = p.id;
8. SELECT b.title, p.publisher, l.language FROM books b LEFT JOIN publishers p
ON b.publisher_id = p.id LEFT JOIN languages l ON (b.language_id = l.id)
9. Všechny knihy a potencionální vydavatele.
SELECT p.publisher, COUNT(b.id) AS pocet_knih FROM books b LEFT JOIN
publishers p ON (b.publisher_id = p.id) GROUP BY p.id ORDER BY pocet_knih
ASC
Všechny knihy a povinně i vydavatele.
SELECT p.publisher, COUNT(b.id) AS pocet_knih FROM books b INNER JOIN
publishers p ON (b.publisher_id = p.id) GROUP BY p.idd
Protože máme na LEVÉ STRANĚ publishers, chceme všechny vydavatele a
k nim potencionální knihu. To je správné řešení dotazu 9.
SELECT p.publisher, COUNT(b.id) as pocet FROM publishers p LEFT JOIN
books b ON b.publisher_id = p.id GROUP BY p.id ORDER BY pocet ASC
10. SELECT p.publisher, COUNT(b.id) AS pocet_knih FROM books b INNER JOIN
publishers p ON (b.publisher_id = p.id) WHERE publisher LIKE "%a%" GROUP
BY p.id HAVING pocet_knih BETWEEN 100 AND 300;
11. SELECT COUNT(*) FROM publishers where id NOT IN (SELECT DISTINCT
b.publisher_id FROM books b WHERE publisher_id IS NOT NULL);
12. SELECT COUNT(*) FROM publishers where id IN (SELECT DISTINCT
b.publisher_id FROM books b WHERE publisher_id IS NOT NULL);