DBS - 3. individuální úkol - lichý týden

Datový model z 1. úkolu lze převést do relačního modelu následovně.

Plavidlo (IDP, Kapacita, Nosnost, Barva)
Balon (IDP, Nadrz, Pisek)
Vzducholod (IDP, Baterie)
Zakaznik (IDZ, Jmeno, Adresa, Telefon)
Zamestnanec (IDZAM, Jmeno)
Kontrola (IDP, Datum, Opravy)
Vypujcka (IDV, Pujceno, Vraceno, IDP, IDZ, Vydal, Prevzal)

IO:

Balon [IDP] ⊆ Plavidlo [IDP]
Vzducholod [IDP] ⊆ Plavidlo [IDP]
Kontrola [IDP] ⊆ Plavidlo [IDP]
Vypujcka [IDP] ⊆ Plavidlo [IDP]
Vypujcka [IDZ] ⊆ Zakaznik [IDZ]
Vypujcka [vydal] ⊆ Zamestnanec [IDZAM]
Vypujcka [prevzal] ⊆ Zamestnanec [IDZAM]

Pozor na názvy atributů při přirozeném spojení :!:.

Zadání

Navrhněte dotazy v SQL. Rozhodně nechci, abyste všechny dotazy odladili na nějakých datech, překlepy mě nezajímají, jde o myšlenky:

  1. Vypište seznam výpůjček zákazníka jménem Jan Novák, položka seznamu bude obsahovat datum výpůjčky, inventární číslo plavidla a barvu.
    SELECT v.pujceno, p.IDP, p.barva
        FROM zakaznik z
          JOIN vypujcka v ON (z.IDZ = v.IDZ) 
          JOIN plavidlo p ON (v.IDP = p.IDP)
        WHERE z.jmeno = 'Jan Novak';
  2. Vypište seznam všech výpůjček. V každém řádku bude datum výpůjčky, ID plavidla, jméno zaměstnance, který předával, a jméno zaměstnance, který přebíral.
    SELECT v.pujceno, v.idp, z1.jmeno, z2.jmeno 
       FROM vypujcka v
       JOIN zamestnanec z1 ON (v.vydal = z.idz)
       JOIN zamestnanec z2 ON (v.prevzal = z.idz)
  3. Vypište zákazníky, kteří si ještě nepůjčili vzducholoď.
    SELECT *
       FROM zakaznik z
       WHERE z.idz NOT IN (
           SELECT idz FROM vypujcka NATURAL JOIN vzducholod
       );
  4. Vypište zákazníky, kteří si půjčili pouze (a jenom) vzducholodě.
    SELECT idz, jmeno 
       FROM zakaznik NATURAL JOIN vypujcka NATURAL JOIN vzducholod
          MINUS -- nebo EXCEPT
    SELECT idz, jmeno
       FROM zakaznik NATURAL JOIN vypujcka NATURAL JOIN balon;
  5. Vypište seznam zákazníků a u nich počet jejich výpůjček. Chceme i zákazníky, kteří si ještě nic nepůjčili (tedy budou mít v daném sloupci 0).
    SELECT z.jmeno, COUNT(v.idv) 
      FROM zakaznik z
      LEFT JOIN vypujcka v ON (z.idz = v.idz)
      GROUP BY z.idz, z.jmeno;
  6. Vypište seznam zaměstnanců, kteří již předali (půjčili) aspoň 10 výpůjček.
    SELECT z.jmeno, COUNT(v.idv) 
    FROM zamestnanec z
    LEFT JOIN vypujcka v ON (z.idzam = v.vydal)
    GROUP BY z.idzam, z.jmeno 
    HAVING COUNT(v.idv) > 10;