-- 1 ----------------------------------------------------------------
SELECT
	vyrobek.jmeno, SUM(koupil.cena) AS [suma]
FROM
	vyrobek
	LEFT JOIN koupil ON vyrobek.vID = koupil.vID
GROUP BY
	vyrobek.vID, vyrobek.jmeno

-- 2 ----------------------------------------------------------------
SELECT
	vyrobek.jmeno, COUNT(koupil.vID) AS [pocet]
FROM
	vyrobek
	LEFT JOIN koupil ON vyrobek.vID = koupil.vID
GROUP BY
	vyrobek.vID, vyrobek.jmeno
ORDER BY
	pocet
	
-- 3 ----------------------------------------------------------------
SELECT
	vyrobek.jmeno, COUNT(uzivatel.login) AS [pocet]
FROM
	vyrobek
	LEFT JOIN koupil ON vyrobek.vID = koupil.vID
	LEFT JOIN uzivatel ON koupil.login = uzivatel.login AND uzivatel.rok_narozeni BETWEEN 1980 AND 1995
GROUP BY
	vyrobek.vID, vyrobek.jmeno
	
-- 4 ----------------------------------------------------------------
SELECT
	uzivatel.login, COUNT(k1.vID) AS [poceet]
FROM
	uzivatel
	LEFT JOIN koupil k1 ON uzivatel.login = k1.login AND NOT EXISTS(
		SELECT *
		FROM koupil k2
		WHERE k2.vID = k1.vID AND k2.cena < k1.cena
	)
GROUP BY
	uzivatel.login
	
SELECT
	uzivatel.login, COUNT(k1.vID)
FROM
	uzivatel
	LEFT JOIN koupil k1 ON uzivatel.login = k1.login AND k1.cena = (SELECT MIN(cena) FROM koupil k2 WHERE k2.vID = k1.vID)
GROUP BY
	uzivatel.login
	
-- 5 ----------------------------------------------------------------
SELECT
	vyrobek.jmeno, COUNT(koupil.vID) AS [pocet]
FROM
	vyrobek
	LEFT JOIN koupil ON vyrobek.vID = koupil.vID
GROUP BY
	vyrobek.vID, vyrobek.jmeno
HAVING
	COUNT(koupil.vID) = 
	(
		SELECT TOP 1 COUNT(koupil.vID) AS [pocet]
		FROM vyrobek LEFT JOIN koupil ON vyrobek.vID = koupil.vID
		GROUP BY vyrobek.vID
		ORDER BY pocet DESC
	)


-- 6 ----------------------------------------------------------------

SELECT
	vyrobek.jmeno
FROM
	vyrobek
	LEFT JOIN koupil ON vyrobek.vID = koupil.vID
GROUP BY
	vyrobek.vID, vyrobek.jmeno
HAVING
	MAX(cena) - MIN(cena) =
	(SELECT
		MAX(rozptyl)
	FROM
		(SELECT
			MAX(cena) - MIN(cena) AS rozptyl
		FROM
			vyrobek
			LEFT JOIN koupil ON vyrobek.vID = koupil.vID
		GROUP BY
			vyrobek.vID) v)

SELECT
	vyrobek.jmeno
FROM
	vyrobek
	LEFT JOIN koupil ON vyrobek.vID = koupil.vID
GROUP BY
	vyrobek.vID, vyrobek.jmeno
HAVING
	MAX(cena) - MIN(cena) =
	(SELECT TOP 1
		MAX(cena) - MIN(cena) AS rozptyl
	FROM
		vyrobek
		LEFT JOIN koupil ON vyrobek.vID = koupil.vID
	GROUP BY
		vyrobek.vID
	ORDER BY rozptyl DESC)