S databází books_complex -
https://is.muni.cz/auth/el/phil/podzim2024/ISKB56/um/02/books_complex.sqlite
1) Vypište knihy z devadesátých let, které nemají uvedený jazyk vydání.
a) Pozor, nejde o chyták, opravdu je to tak jednoduchý dotaz
2) Vypište jazyky, ve kterých nemáme v databází žádnou vydanou knihu
3) Vypište knihy, které v názvu obsahuji “a” a název je kratší než 10 znaků a u každé
vypište počet autorů, kteří ji napsali. Seřazeno dle roku vydání od nejvyššího po
nejnižší.
4) Stejné zadání jako v příkladě 3, ale zobrazte jen ty knihy, které napsali 2-3 autoři
5) Vypište autory začínající na “i” spolu s počtem knih, které napsali. Seřazeno dle
počtu napsaných knih od nejvyššího po nejnižší
6) Vypište jména autorů, kteří začínají na A a vyšla jim alespoň jedna knížka po roce
2002.
7) Vypište názvy a ID knih, které nemají uvedené autory, ani jazyk vydání. U každé
knihy vypište i název nakladatele. Výsledek bude seřazený abecedně dle názvu
nakladatele.
S databází eshop -
https://is.muni.cz/auth/el/phil/podzim2024/ISKB56/um/02/eshop.sqlite
8) Vypište počet knih, které si nikdo nikdy neobjednal
9) Kolik hodnocení (tabulka “hodnoceni”) udělili zákazníci s příjmením na B
10) Modifikujte předchozí dotaz tak, aby ukázal, kolik zákaznici na B udělili hodnocení
jednou/dvěma/.../pěti hvězdičkami. Ve výsledku bude vždy počet hvězdiček a počet
hodnocení.
11) Vypište seznam autorů (abecedně dle příjmení), u každého vypište, kolik od něj
máme celkem knížek a jaký je nejmladší a nejstarší rok vydání. Vše v jednom
dotazu.
12) Jaká je nejlépe hodnocená knížka?
Řešení:
1)
SELECT * FROM books
WHERE year >= 1990 AND year <= 1999 AND language_id IS NULL
2)
SELECT l.language FROM languages AS l
LEFT JOIN books AS b on l.id = b.language_id
WHERE b.language_id IS NULL
Pozn: ukázka použití krátkých aliasů, doporučuji spíše pro rychlé ad hoc dotazy
3)
SELECT title, year, count(author_id) FROM books
LEFT JOIN books_authors ba on books.id = ba.book_id
WHERE title LIKE "%a%" AND length(title) < 10
GROUP BY books.id
ORDER BY year DESC
4)
SELECT title, year, count(author_id) FROM books
LEFT JOIN books_authors ba on books.id = ba.book_id
WHERE title LIKE "%a%" AND length(title) < 10
GROUP BY books.id
HAVING count(author_id) > 1 AND count(author_id) < 4
ORDER BY year DESC
Pozn.: having se zde dá napsat několika různými způsoby - dájí se využít nerovnosti <=, >=,
operátor IN, nebo BETWEEN. Co zvolíte je na vás.
5)
SELECT name, count(books_authors.book_id)
FROM authors
LEFT JOIN books_authors on authors.id = books_authors.author_id
WHERE name LIKE "i%"
GROUP BY authors.id
ORDER BY count(books_authors.book_id) DESC
Pozn: jak vidíte, pro získání této informace není nutné připojovat tabulku books, protože z ní
nepotřebujeme žádné informace. Pokud byste ji však i tak připojili (další LEFT JOIN), tak by
to chyba nebyla, výsledek bude stejný.
6)
SELECT authors.name FROM
authors
INNER JOIN books_authors ba on authors.id = ba.author_id
INNER JOIN books b on ba.book_id = b.id
WHERE YEAR > 2002 AND name LIKE "a%"
Pozn.: zde můžu používat INNER JOIN, protože mi nevadí, že “příjdu” o autory, kteří
nevydali žádnou knihu. Takové totiž ve výsledcích stejně nechci.
7)
SELECT books.id, title, publishers.publisher FROM books
LEFT JOIN books_authors ba on books.id = ba.book_id
LEFT JOIN publishers on books.publisher_id = publishers.id
WHERE ba.book_id IS NULL AND language_id IS NULL
ORDER BY publishers.publisher
Pozn.: ID jsem nechal vypsat schválně, abyste viděli, že v db může být více knih se stejným
názvem, ale různým ID. To může znamenat buď různé knihy se stejným názvem (proto není
dobré používat title v GROUP BY), nebo chyby v datech, kdy byla stejná kniha do db zadána
2x.
8)
SELECT count(*) FROM knihy
LEFT JOIN polozky_objednavky ON knihy.id = polozky_objednavky.id_kniha
WHERE polozky_objednavky.id_polozka IS NULL
9)
SELECT count(*) FROM zakaznici
INNER JOIN hodnoceni ON hodnoceni.id_zakaznik = zakaznici.id_zakaznik
WHERE prijmeni LIKE "b%"
10)
SELECT pocet_hvezdicek, count(*) FROM zakaznici
INNER JOIN hodnoceni ON hodnoceni.id_zakaznik = zakaznici.id_zakaznik
WHERE prijmeni LIKE "b%"
GROUP BY pocet_hvezdicek
11)
SELECT jmeno, prijmeni, count(*), min(rok_vydani), max(rok_vydani) FROM autori
INNER JOIN autori_knihy ON autori.id = autori_knihy.autori_id
INNER JOIN knihy ON knihy.id = autori_knihy.knihy_id
GROUP BY autori.id
ORDER BY prijmeni ASC
12)
Zde řešení schválně neuvádím, zkuste nad ním sami popřemýšlet. Pokud byste ho chtěli
zkonzultovat, tak mi napište (email či via teams). Řešení (resp. různé možnosti) pak ukážu
na začátku příštího bloku.