S db ​https://is.muni.cz/auth/el/phil/podzim2020/ISKB56/um/09_prednaska/eshop.sqlite
(diagram ​https://is.muni.cz/auth/el/phil/podzim2020/ISKB56/um/09_prednaska/eshop.png​)
1) Vypište počet knih, které si nikdo nikdy neobjednal
2) Kolik hodnocení (tabulka “hodnoceni”) uděleli zákazníci s příjmením na B
3) 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í.
4) Vypište seznam autorů (abecedně dle příjmení), u každého vypiště, kolik od něj
máme celkem knížek a jaký je nejmladší a nejstarší rok vydání. Vše v jednom
dotazu.
5) Jaká je nejlépe hodnocená knížka?
+ V prezentaci
https://is.muni.cz/auth/el/phil/podzim2020/ISKB56/um/09_prednaska/db-09-2019.pdf
příklady na slajdech 20-23 (řešení je tam rovnou uvedeno)
S db ​https://is.muni.cz/auth/el/phil/podzim2020/ISKB56/um/09_prednaska/osoby.db
Pozn: počet dat je schválně takto malý, abyste si mohli řešení rozmyslet/zkontrolovat na
papíře.
6) Vypište uzavřené “životní úseky” (tj ty, kde je datum ​od​ i ​do​), u každého vypište
jméno osoby, název města a počet let, jak dlouho úsek trval.
7) Kolik lidí žilo v roce 2001
a) (za předpokladu, že žádná z osob nezemřela)
8) Kolik lidí bydlelo v roce 2016 v jednotlivých městech?
Řešení
1. SELECT count(*) FROM knihy
LEFT JOIN polozky_objednavky ON knihy.id = polozky_objednavky.id_kniha
WHERE polozky_objednavky.id_polozka IS NULL
2. SELECT count(*) FROM zakaznici
INNER JOIN hodnoceni ON hodnoceni.id_zakaznik = zakaznici.id_zakaznik
WHERE prijmeni LIKE "b%"
3. SELECT pocet_hvezdicek, count(*) FROM zakaznici
INNER JOIN hodnoceni ON hodnoceni.id_zakaznik = zakaznici.id_zakaznik
WHERE prijmeni LIKE "b%"
GROUP BY pocet_hvezdicek
4. 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
5. 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. Svoje řešení pak ukážu na začátku příští
přednášky
6. SELECT jmeno, nazev, (do - od) as delka FROM Osoby
LEFT JOIN OsobaMesto ON Osoby.ID = OsobaMesto.osoba_id
LEFT JOIN Mesta ON Mesta.ID = OsobaMesto.mesto_id
WHERE do IS NOT NULL
Pozn: může být i inner join
7. SELECT count(DISTINCT Osoby.id) FROM Osoby
LEFT JOIN OsobaMesto ON Osoby.ID = OsobaMesto.osoba_id
WHERE od < 2001
Hledáme počet úseků, které začaly před 2001. Protože jedna osoba jich
může mít víc, musíme přidat DISTINCT.
8. SELECT nazev, count(*) FROM Osoby
INNER JOIN OsobaMesto ON Osoby.ID = OsobaMesto.osoba_id
INNER JOIN Mesta ON Mesta.ID = OsobaMesto.mesto_id
WHERE od <= 2016 AND (do >= 2016 OR do IS NULL)