-- 2hianyos
SELECT cim, kocka
FROM film
WHERE kiadasiev IS NULL
ORDER BY cim;

-- 3mazsola
SELECT cim, kocka, kiadasev
FROM film
WHERE cim LIKE "%mazsola%";

-- 4tobb
SELECT film.cim
FROM film, kapcsolo
WHERE film.id = kapcsolo.filmid
GROUP BY film.cim, film.id
HAVING Count(kapcsolo.kiadoid)>1;

-- 5nepszeru
SELECT film.cim, COUNT(kapcsolo.kiadoid) AS kiadasok_szama
FROM film, kapcsolo
WHERE film.id = kapcsolo.filmid
GROUP BY film.id
ORDER BY kiadasok_szama DESC
LIMIT 1;

-- 6idodb
SELECT kiadasiev, COUNT(*) AS darabszam
FROM film
WHERE kiadasev IS NOT NULL
GROUP BY kiadasev
ORDER BY darabszam DESC;

-- 7egyszerre
SELECT f1.cim, f1.kiadasev
FROM film AS f1, film AS f2
WHERE f1.cim = f2.cim AND f1.kiadasev = f2.kiadasev AND f1.szinese <> f2.szinese
GROUP BY f1.cim, f1.kiadasev;

-- 8mono
SELECT SUM(if(szinese=0,1,0))/COUNT(id)*100 AS fekete_feher_szazalek
FROM film;
vagy
SELECT (COUNT(szinese)-SUM(szinese)) / COUNT(szinese) * 100 FROM film;
vagy
SELECT CONCAT((SELECT COUNT(film.id)
	           FROM film
	           WHERE film.szinese = 0) / COUNT(film.id) * 100, '%')
FROM film;


-- 9aranyfalu
SELECT film.cim, film.kiadasev, film.kocka
FROM film, kapcsolo
WHERE film.id = kapcsolo.filmid
WHERE kapcsolo.kiadoid IN (
    SELECT kapcsolo.kiadoid
    FROM kapcsolo, film
    WHERE kapcsolo.filmid = film.id
    WHERE film.cim = "Aranyfalu"
) AND film.cim <> "Aranyfalu"
GROUP BY film.cim, film.kiadasev, film.kocka;

vagy

SELECT cim, kiadasiev, kocka
FROM film inner join kapcsolo on id=filmid 
WHERE (SELECT kiadoid FROM kapcsolo inner join film on id=filmid where cim='Aranyfalu') = kiadoid and cim!='Aranyfalu' ORDER BY cim;