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í .

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;