Majme databázu s relaciami:

Lubi(ochmelka, pivo)
Capuju(krcma, pivo)
Navstivil(idn, ochmelka, krcma, den, cas_od, cas_do)
Vypil(idn, pivo, mnozstvo)

Niektore dolezite skutocnosti. Krcmy su v prevadzke od 10:00 dopoludnia do polnoci (24:00). Jeden ochmelka moze za den 
navstivit aj viac krciem. Kazdy ochmelka lubi aspon jedenu znacku piva.

Sformulujte nasledujúce dotazy v SQL:

1. Najdite vsetky krcmy, kde sa stretli ochmelkovia Fero Mrkvicka a Jozo Kapusta.

2. Zistite a vypiste dvojice znaciek piva, ktore pri týchto stretnutiach pili.

3. Vypiste piva, ktore obaja ochmelkovia lubia.

4. Najdite ochmelkov - statlivcov, ktori navstivili iba krcmy, kde capujú aspon jednu znacku piva, ktoru lubia.

5. Najdite ochmelkov - smoliarov, ktori navstivili iba krcmy, kde capuju iba piva, ktore nelubia.

6. Najdite ochmelkov, ktori lubia vsetky znacky piva, co lubi Jozo Kapusta.

7. Najdite znacky piva, ktore lubia aspon dvaja ochmelkovia. 
a) Bez pouzitia agregacnej funkcie.
b) S pouzitim agregacnej funkcie.

8. Najdite znacky piva, ktore capuju v prave jednej krcme. 
a) Bez pouzitia agregacnej funkcie.
b) S pouzitim agregacnej funkcie.

9. Zistite, kolko roznych ochmelkov navstivilo krcmu "U troch volov".

10. Zistite, kolko ktorej znacky piva sa vypilo v krcme "U troch volov".

11. Pre kazdu znacku piva, najdite krcmu, kde sa z danej znacky vypilo najviac, vypiste aj kolko sa jej vypilo. (V pripade, 
ze maximum sa dosiahlo vo viacerych krcmach, vypiste vsetky.)

--------------------------------------------------------------------------------------------------------------------

RIESENIA: 

1.

SELECT n1.krcma from navstivil n1, navstivil n2
WHERE n1.ochmelka='Fero Mrkvicka' AND n2.ochmelka='Jozo Kapusta' 
AND n1.krcma=n2.krcma 
AND n1.den=n2.den
AND (   n1.cas_od >= n2.cas_od AND n1.cas_od <= n2.cas_do 
     OR n2.cas_od >= n1.cas_od AND n2.cas_od <= n1.cas_do);

SELECT n1.krcma from navstivil n1, navstivil n2
WHERE n1.ochmelka='Fero Mrkvicka' AND n2.ochmelka='Jozo Kapusta' 
AND n1.krcma=n2.krcma 
AND n1.den=n2.den
AND n1.cas_od <= n2.cas_do AND n2.cas_od <= n1.cas_do;

----

2.

SELECT v1.pivo, v2.pivo 
FROM navstivil n1, navstivil n2, vypil v1, vypil v2
WHERE n1.ochmelka='Fero Mrkvicka' AND n2.ochmelka='Jozo Kapusta' 
AND n1.krcma=n2.krcma AND n1.den=n2.den
AND (   n1.cas_od >= n2.cas_od AND n1.cas_od <= n2.cas_do
     OR n2.cas_od >= n1.cas_od AND n2.cas_od <= n1.cas_do)
AND v1.idn=n1.idn AND v2.idn=n2.idn;

SELECT v1.pivo, v2.pivo 
FROM navstivil n1, navstivil n2, vypil v1, vypil v2
WHERE n1.ochmelka='Fero Mrkvicka' AND n2.ochmelka='Jozo Kapusta' 
AND n1.krcma=n2.krcma AND n1.den=n2.den
AND n1.cas_od <= n2.cas_do AND n2.cas_od <= n1.cas_do
AND v1.idn=n1.idn AND v2.idn=n2.idn;

SELECT v1.pivo, v2.pivo 
FROM vypil v1, vypil v2,
     (SELECT n1.idn as idn1, n2.idn as idn2 from navstivil n1, navstivil n2
      WHERE n1.ochmelka='Fero Mrkvicka' AND n2.ochmelka='Jozo Kapusta'
      AND n1.krcma=n2.krcma
      AND n1.den=n2.den
      AND n1.cas_od <= n2.cas_do AND n2.cas_od <= n1.cas_do) n
WHERE n.idn1 = v1.idn AND n.idn2 = v2.idn;

-----

3.

SELECT pivo 
FROM lubi
WHERE ochmelka='Fero Mrkvicka' 
AND pivo IN (SELECT pivo 
              FROM lubi 
              WHERE ochmelka='Jozo Kapusta'); 

SELECT l1.pivo
FROM lubi l1, lubi l2
WHERE l1.ochmelka = 'Fero Mrkvicka'
AND l2.ochmelka = 'Jozo Kapusta'
AND l1.pivo = l2.pivo;

-----

4.

SELECT DISTINCT ochmelka 
FROM navstivil
WHERE ochmelka NOT IN
         (SELECT n.ochmelka 
          FROM navstivil n
          WHERE NOT EXISTS
             (SELECT * 
              FROM lubi NATURAL JOIN capuju
              WHERE capuju.krcma=n.krcma AND lubi.ochmelka=n.ochmelka)); 


SELECT ochmelka 
FROM lubi
EXCEPT
SELECT ochmelka
FROM navstivil n
WHERE NOT EXISTS
   (SELECT * 
    FROM lubi l, capuju c
    WHERE l.ochmelka = n.ochmelka
    AND   c.krcma = n.krcma
    AND   l.pivo = c.pivo);


-----

5.

SELECT ochmelka 
FROM lubi 
WHERE ochmelka NOT IN
        (SELECT n.ochmelka 
         FROM navstivil n
         WHERE EXISTS
               (SELECT * 
                FROM navstivil NATURAL JOIN capuju NATURAL JOIN lubi
                WHERE ochmelka=n.ochmelka)); 

6.
SELECT DISTINCT ochmelka FROM Lubi L 
WHERE NOT EXISTS
((SELECT pivo FROM Lubi 
WHERE ochmelka='Jozo Kapusta')
EXCEPT
(SELECT pivo FROM Lubi
WHERE ochmelka=L.ochmelka));

7.
a)
SELECT DISTINCT l1.pivo FROM lubi l1, Lubi l2
WHERE l1.pivo=l2.pivo AND l1.ochmelka!=l2.ochmelka; 

b)
SELECT pivo FROM lubi
GROUP BY pivo
HAVING count(*)>=2;

8.
a)
SELECT pivo FROM capuju 
WHERE pivo NOT IN
(SELECT c1.pivo FROM Capuju c1, Capuju c2
WHERE c1.pivo=c2.pivo AND c1.krcma!=c2.krcma); 

b)
SELECT pivo FROM capuju
GROUP BY pivo
HAVING count(*)=1;

9.
SELECT COUNT (DISTINCT ochmelka) FROM navstivil
WHERE krcma='U troch volov'; 

10.
SELECT pivo, SUM(mnozstvo)
FROM navstivil NATURAL JOIN vypil
WHERE krcma='U troch volov'
GROUP BY pivo;

SELECT  pivo, SUM(mnozstvo) 
FROM navstivil n, vypil v 
WHERE n.idn = v.idn AND n.krcma = 'U troch volov'
GROUP BY pivo;

11.
SELECT pivo, krcma, SUM(mnozstvo)
FROM Navstivil NATURAL JOIN Vypil V
GROUP BY pivo, krcma 
HAVING SUM(mnozstvo)>=ALL
(SELECT SUM(mnozstvo)
FROM Navstivil NATURAL JOIN Vypil
WHERE pivo=V.pivo
GROUP BY krcma);