


-- 3 - Prunik, rozdil, sjednoceni
-- 1 ------------------------------------------
(
	SELECT
		vyrobek.jmeno
	FROM
		uzivatel, koupil, vyrobek
	WHERE
		uzivatel.login = koupil.login AND
		koupil.vID = vyrobek.vID AND
		uzivatel.login = 'vinetu'
)
INTERSECT
(
	SELECT
		vyrobek.jmeno
	FROM
		uzivatel, koupil, vyrobek
	WHERE
		uzivatel.login = koupil.login AND
		koupil.vID = vyrobek.vID AND
		uzivatel.login = 'pepik'
)

SELECT jmeno
FROM vyrobek
WHERE
	vID IN 
	(
		SELECT vID
		FROM koupil
		WHERE login = 'vinetu'
	)
	AND	vID IN 
	(
		SELECT vID
		FROM koupil
		WHERE login = 'pepik'
	)
	
-- 2 ------------------------------------------
(
	SELECT
		vyrobek.jmeno
	FROM
		uzivatel, koupil, vyrobek
	WHERE
		uzivatel.login = koupil.login AND
		koupil.vID = vyrobek.vID AND
		uzivatel.login = 'vinetu'
)
EXCEPT
(
	SELECT
		vyrobek.jmeno
	FROM
		uzivatel, koupil, vyrobek
	WHERE
		uzivatel.login = koupil.login AND
		koupil.vID = vyrobek.vID AND
		uzivatel.login = 'pepik'
)

SELECT jmeno
FROM vyrobek
WHERE
	vID IN 
	(
		SELECT vID
		FROM koupil
		WHERE login = 'vinetu'
	)
	AND vID NOT IN 
	(
		SELECT vID
		FROM koupil
		WHERE login = 'pepik'
	)
	
-- 3 ------------------------------------------
(
	SELECT
		vyrobek.vID, vyrobek.jmeno
	FROM
		vyrobek
)
EXCEPT
(
	SELECT
		koupil.vID, vyrobek.jmeno
	FROM
		koupil, vyrobek
	WHERE
		koupil.vID = vyrobek.vID
)

SELECT vID
FROM vyrobek
WHERE vID NOT IN (
	SELECT vID
	FROM koupil
)

-- 4 ------------------------------------------
(
	SELECT
		vyrobek.jmeno
	FROM
		uzivatel, koupil, vyrobek
	WHERE
		uzivatel.login = koupil.login AND
		koupil.vID = vyrobek.vID AND
		(uzivatel.login = 'vinetu' OR uzivatel.login = 'pepik')
)
EXCEPT
(
	(
		SELECT
			vyrobek.jmeno
		FROM
			uzivatel, koupil, vyrobek
		WHERE
			uzivatel.login = koupil.login AND
			koupil.vID = vyrobek.vID AND
			uzivatel.login = 'vinetu'
	)
	INTERSECT
	(
		SELECT
			vyrobek.jmeno
		FROM
			uzivatel, koupil, vyrobek
		WHERE
			uzivatel.login = koupil.login AND
			koupil.vID = vyrobek.vID AND
			uzivatel.login = 'pepik'
	)
)

SELECT vyrobek.vID, vyrobek.jmeno
FROM vyrobek
WHERE
	(
		vID IN (SELECT vID FROM koupil WHERE login = 'vinetu') AND
		vID NOT IN (SELECT vID FROM koupil WHERE login = 'pepik')
	)
	OR
	(
		vID IN (SELECT vID FROM koupil WHERE login = 'pepik') AND
		vID NOT IN (SELECT vID FROM koupil WHERE login = 'vinetu')
	)
		
-- 5 ------------------------------------------
SELECT DISTINCT vyrobek.jmeno
FROM vyrobek, koupil k1
WHERE
	vyrobek.vID = k1.vID AND EXISTS
	(
		SELECT *
		FROM koupil k2
		WHERE k1.vID = k2.vID AND k1.login = k2.login AND k1.rok != k2.rok
	)

-- 6 ------------------------------------------
SELECT jmeno
FROM vyrobek
WHERE
	aktualni_cena > ALL
	(
		SELECT cena
		FROM koupil
		WHERE koupil.vID = vyrobek.vID
	)
	
-- 7 ------------------------------------------
SELECT DISTINCT login
FROM koupil k1
WHERE
	NOT EXISTS
	(
		SELECT *
		FROM koupil k2
		WHERE k1.login = k2.login AND (k1.vID != k2.vID OR k1.rok != k2.rok)
	)

-- 8 ------------------------------------------
SELECT DISTINCT login
FROM koupil k1
WHERE
	NOT EXISTS
	(
		SELECT *
		FROM koupil k2
		WHERE k1.login = k2.login AND k1.vID != k2.vID 
	)

-- 9 ------------------------------------------
SELECT DISTINCT jmeno
FROM koupil k1, vyrobek
WHERE
	k1.vID = vyrobek.vID AND
	NOT EXISTS
	(
		SELECT *
		FROM koupil k2
		WHERE k1.login = k2.login AND k1.vID != k2.vID 
	)

-- 10 -----------------------------------------
SELECT DISTINCT login
FROM koupil k1
WHERE
	NOT EXISTS
	(
		SELECT *
		FROM koupil k2
		WHERE k1.vID = k2.vID AND k1.cena > k2.cena
	)

