/* SQL dotazy */

/*
  Q: Vyber vsechny uzivatele bydlici na pokoji 30.
  A: Vsichni uzivatele (200)
  (A)
*/

SELECT * 
FROM UZIVATEL
WHERE pokoj=30;

/*
  Q: Vyber vsechny uzivatele, kteri jsou v relaci pocitac.
  A: Cca 3/4 uzivatelu (podle generovani i%4 > 0)
  (B)
*/

SELECT jmeno,prijmeni 
FROM UZIVATEL join POCITAC on(uzivatel.id=pocitac.uzivatel_id);

SELECT jmeno,prijmeni
FROM UZIVATEL
WHERE ID IN 
    (SELECT DISTINCT UZIVATEL_ID
     FROM POCITAC);

SELECT (SELECT lpad(jmeno,15) || rpad(prijmeni,20) FROM UZIVATEL WHERE ID=P.SITOVE_ZARIZENI_ID) AS JMENO
FROM POCITAC P;

/*
  Q:Vyber vsechny uzivatele, kteri nejsou v relaci pocitac.
  A:Cca 1/4 uzivatelu (podle generovani i%4 = 0)
  (C)
*/
SELECT jmeno,prijmeni
FROM UZIVATEL
WHERE ID NOT IN 
    (SELECT DISTINCT UZIVATEL_ID
     FROM POCITAC);

/*
  Q: Zjisti, kolik zadali vsichni administratori trestu.
  A: Melo by to byt po 5-ti.

*/

SELECT jmeno,prijmeni,POCET_TRESTU
FROM UZIVATEL NATURAL JOIN 
    (SELECT ADMIN_ID AS ID, count(ID) POCET_TRESTU
     FROM TREST
     GROUP BY TREST.ADMIN_ID);

SELECT jmeno,prijmeni,POCET_TRESTU
FROM UZIVATEL JOIN 
    (SELECT ADMIN_ID AS ID, count(ID) POCET_TRESTU
     FROM TREST
     GROUP BY TREST.ADMIN_ID) USING(ID);

/*
  Q: Vyber vsechny sluzby, ktere nemaji prirazeneho admina(spravce).
  A: Melo by jich par byt, protoze navazujeme jen 3 sluzby na admina a admini jsou 3.
     Celkovy pocet sluzeb je 20.

*/

SELECT SLUZBA.NAZEV 
FROM SLUZBA LEFT OUTER JOIN ADMIN_has_SLUZBA ON(SLUZBA.id=ADMIN_has_SLUZBA.sluzba_id)
WHERE ADMIN_has_SLUZBA.admin_id IS NULL
ORDER BY NAZEV;

/*
  Q: Vyber vsechny servery, na kterych bezi alespon jedna sluzba, ale nemaji prirazeneho admina.
  A: Mely by nejake byt, protoze pocet serveru>pocet sluzeb>pocet vazeb admin<->server

*/
SELECT OSTATNI_SZ.SITOVE_ZARIZENI_ID, OSTATNI_SZ.TYP
FROM ADMIN_has_OSTATNI RIGHT OUTER JOIN OSTATNI_SZ ON(ADMIN_has_OSTATNI.OSTATNI_SZ_SITOVE_ZARIZENI_ID=OSTATNI_SZ.SITOVE_ZARIZENI_ID)
WHERE ADMIN_has_OSTATNI.ADMIN_ID IS NULL 
      AND OSTATNI_SZ.SITOVE_ZARIZENI_ID NOT IN
       (SELECT SITOVE_ZARIZENI_ID
        FROM OSTATNI_SZ,SLUZBA
        WHERE OSTATNI_SZ.SITOVE_ZARIZENI_ID = SLUZBA.OSTATNI_SZ_SITOVE_ZARIZENI_ID)
GROUP BY OSTATNI_SZ.SITOVE_ZARIZENI_ID, OSTATNI_SZ.TYP
HAVING OSTATNI_SZ.TYP = 'server';

/*
  Q: Kolik bezi sluzeb na kterem serveru.
  A: Melo by 0 - 1.

*/
SELECT HOSTNAME, count(S.ID) AS POCET_SLUZEB
FROM SITOVE_ZARIZENI SZ JOIN OSTATNI_SZ OSZ ON(SZ.ID=OSZ.SITOVE_ZARIZENI_ID)
     LEFT OUTER JOIN SLUZBA S ON (OSZ.SITOVE_ZARIZENI_ID=S.OSTATNI_SZ_SITOVE_ZARIZENI_ID)
WHERE OSZ.TYP = 'server'
GROUP BY HOSTNAME;


