S databází eshopu:
https://is.muni.cz/auth/el/phil/podzim2020/ISKB56/um/09_prednaska/eshop.sqlite
1) (rozehřívací) Vypište kolik objednávek zaslaných do Brna nebo Ostravy bylo
zaplaceno kartou, kolik dobírkou a kolik převodem. Výsledky seřaďte od
nejčastějšího způsobu.
2) Vypiště zákazníky seřazené abecedně dle příjmení a u každého z nich vypište datum
nejstarší a nejnovější objednávky - tyto sloupečky se budou jmenovat “nejstarší” a
“nejnovější”. Nevypisujte zákazníky, kteří zatím žádnou objednávku nemají.
3) Stejné zadání jak předchozá příklad, ale vypište jen ty zákazníky, kteří mají alespoň
2 objednávky. U každého řádku pak vypište ještě celkový počet objednávek daného
zákazníka.
4) Stejné zadání jako bod 3, ale vypište jen ty zákazníky, kterým příjmení začíná na “R”
a kteří udělali nejstarší objednávku v lednu 2014 a nejnovější v prosinci 2014.
5) Vypište autory seřazené dle počtu knih žánru “Pro děti”, které napsali. Zobrazte jen
ty, kteří nějakou napsali a seřaďte je od nejvyššího počtu napsaných.
a) Pozn: připomínám, že na tyhle dotazy je dobré jít postupně. Začít třeba
“vypište počet autorů spolu s počtem knížek, které napsali”, pak připojit další
tabulku, pak další
6) Vypište tržby e-shopu za jednotlivé měsíce (dle data objednávek). U každého měsíce
vypište kolik peněz utržili a kolik kusů knih prodali.
a) Pozn: pro zjednodušení můžete počítat s tím, že všechny data jsou z jednoho
roku, tedy nemusíte řešit stejný měsíc v různých letech.
S databází ​https://is.muni.cz/auth/el/phil/podzim2020/ISKB56/um/10_prednaska/akce.db​ (její
vysvětlení viz video z 10. přednášky, ale určitě ji pochopíte i bez něj), jde o velmi
zjednodušený kalendář akcí města Karlovy vary
7) Přijíždíte do Varů na týden 3. března 2014 - 9. března 2014 (​jde o starší dataset,
takže předpokládejme, že máte stroj času) ​a chcete zjistit, jaké všechny akce můžete
stihnout.
Řešení
1. SELECT zpusob_platby, count(*) FROM objednavky WHERE adresa_mesto LIKE
"Brno" OR adresa_mesto LIKE "Ostrava" GROUP BY zpusob_platby ORDER BY
count(*) DESC
2. SELECT jmeno, prijmeni, min(vytvoreno) AS nejstarší, max(vytvoreno) AS nejnovější
FROM zakaznici
INNER JOIN objednavky ON objednavky.id_zakaznik = zakaznici.id_zakaznik
GROUP BY zakaznici.id_zakaznik
ORDER BY prijmeni
3. SELECT jmeno, prijmeni, count(*) AS počet, min(vytvoreno) AS nejstarší,
max(vytvoreno) AS nejnovější
FROM zakaznici
INNER JOIN objednavky ON objednavky.id_zakaznik = zakaznici.id_zakaznik
GROUP BY zakaznici.id_zakaznik
HAVING count(*) > 1
ORDER BY prijmeni
4. SELECT jmeno, prijmeni, count(*) AS počet, min(vytvoreno) AS nejstarší,
max(vytvoreno) AS nejnovější
FROM zakaznici
INNER JOIN objednavky ON objednavky.id_zakaznik = zakaznici.id_zakaznik
WHERE prijmeni LIKE "r%"
GROUP BY zakaznici.id_zakaznik
HAVING
min(vytvoreno) >= '2014-01-01' AND min(vytvoreno) < '2014-02-01'
AND max(vytvoreno) >= '2014-12-01' AND max(vytvoreno) < '2015-01-01'
ORDER BY prijmeni
Pozn: způsobů, jakým omezit data je více
5. SELECT jmeno, prijmeni, count(knihy.id) FROM autori
INNER JOIN autori_knihy ON autori.id = autori_knihy.autori_id
INNER JOIN knihy ON autori_knihy.knihy_id = knihy.id
INNER JOIN knihy_zanry ON knihy_zanry.knihy_id = knihy.id
INNER JOIN zanry ON knihy_zanry.zanry_id = zanry.id
WHERE zanry.zanr = 'Pro děti'
GROUP BY autori.id
ORDER BY count(knihy.id) DESC
6. SELECT strftime("%m", vytvoreno) as mesic, SUM(pocet), SUM(pocet *
cena_za_kus) FROM objednavky
INNER JOIN polozky_objednavky ON objednavky.id =
polozky_objednavky.id_objednavka
GROUP BY strftime("%m", vytvoreno)
ORDER BY strftime("%m", vytvoreno)
Pozn.: měsíc je možné “vypreparovat” třeba i pomocí SUBSTR
7. Mělo by vám vyjít 53 výsledků, vaše řešení mi pošlete emailem/teams (včetně
nějakého krátkého textového komentáře), jsem zvědavý, jak jste k tomu přistupovali.