S databází books_complex (​https://is.muni.cz/auth/go/ykykbx​)
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.
Ř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.