DB:
http://is.muni.cz/el/1421/podzim2016/VIKBB68/um/65558679/eshop.sqlite
1. Vypište zákazníky, kteří si nic neobjednali pomocí IN/NOT IN
2. Vypište počet nakladatelství bez prodané knihy.
3. Vypište nakladatelství s prodanými alespoň 150 knihami a počet prodaných knih.
4. Vypiš knihy začínající na j a pod to knihy začínající na l pomocí UNION
Procvičování
1. Vypiš jazyk a počet knih. Knihy jsou napsány v jazyce obsahujícím písmeno “č”. Počet
knih musí být menší, nebo rovný 270.
2. Kolik knih napsal autor se jménem Nasim nebo Alfonso? Kniha nesmí mít v názvu
písmeno “L” a vyřaďte autory, kteří napsali více než 10 knih. Vypište jméno autora,
příjmení autora a počet jím napsaných knih.
3. Jaké knihy ​nenapsané​ v češtině mají nejhorší průměrné hodnocení? Vyřaďte knihy s
průměrným hodnocením 0. (průměrné hodnocení vychází z počtu hvězdiček). Vypište
název knihy, jazyk a průměrné hodnocení a vypište 7 nejhorších knih.
4. Vypište jméno žánru a celkový počet stran které mají knihy tohoto žánru. Délka jména
žánru musí být delší než 6 a celkový počet stran všech knih musí být menší než 100
000.
Řešení
1. BEZ SUBQUERY
SELECT z.id_zakaznik, z.jmeno, z.prijmeni FROM zakaznici z LEFT JOIN objednavky o
ON (z.id_zakaznik = o.id_zakaznik) WHERE o.id IS NULL
NEBO
SELECT z.jmeno FROM zakaznici z LEFT JOIN objednavky o ON (o.id_zakaznik =
z.id_zakaznik) GROUP BY z.id_zakaznik HAVING COUNT(o.id) = 0
S POMOCÍ SUBQUERY
SELECT z.id_zakaznik, z.jmeno, z.prijmeni FROM zakaznici z WHERE z.id_zakaznik
NOT IN (SELECT DISTINCT id_zakaznik FROM objednavky)
2. SELECT COUNT(id) FROM nakladatelstvi WHERE id NOT IN (SELECT DISTINCT
knihy.nakladatelstvi FROM knihy INNER JOIN polozky_objednavky po ON (knihy.id =
po.id_kniha))
3. Bez IN/NOT IN
SELECT n.nazev FROM knihy k INNER JOIN polozky_objednavky po ON (k.id =
po.id_kniha) INNER JOIN nakladatelstvi n ON (k.nakladatelstvi = n.id) GROUP BY
k.nakladatelstvi HAVING COUNT(po.id_polozka) > 150
S IN/NOT IN
SELECT nazev FROM nakladatelstvi WHERE id IN (SELECT k.nakladatelstvi FROM
knihy k INNER JOIN polozky_objednavky po ON (k.id = po.id_kniha) GROUP BY
k.nakladatelstvi HAVING COUNT(po.id_polozka) > 150)
4. SELECT nazev FROM knihy WHERE nazev LIKE "j%" UNION SELECT nazev FROM
knihy WHERE nazev LIKE "l%"
Řešení procvičování
1. SELECT j.nazev, COUNT(k.id) AS pocet_knih FROM jazyky_vydani j INNER JOIN knihy
k ON j.id = k.jazyk WHERE j.nazev LIKE '%č%' GROUP BY j.id HAVING pocet_knih <=
270;
2. SELECT jmeno, prijmeni, COUNT(k.id) pocet FROM autori a INNER JOIN autori_knihy
ak ON (a.id = ak.autori_id) INNER JOIN knihy k ON (k.id = ak.knihy_id) WHERE (jmeno
LIKE 'nasim' OR jmeno LIKE 'alfonso') AND k.nazev NOT LIKE "%l%" GROUP BY a.id
HAVING pocet < 10 ORDER BY pocet DESC;
3. SELECT k.nazev, j.nazev, AVG(pocet_hvezdicek) prumerne FROM hodnoceni h INNER
JOIN knihy k ON (k.id = h.id_kniha) INNER JOIN jazyky_vydani j ON k.jazyk = j.id
WHERE j.nazev NOT LIKE 'čeština' GROUP BY id_kniha HAVING prumerne > 0
ORDER BY prumerne ASC LIMIT 7;
4. SELECT z.zanr, SUM(pocet_stran) sum_pocet_stran FROM zanry z INNER JOIN
knihy_zanry kz ON (kz.zanry_id = z.id) INNER JOIN knihy k ON (k.id = kz.zanry_id)
WHERE LENGTH(z.zanr) > 6 GROUP BY z.id HAVING sum_pocet_stran < 100000;