--2harmadik
SELECT ev, gyar, modell, pont FROM `auto` 
WHERE helyezes = 3 AND pont > 200;

--3japan
SELECT ev, gyar, modell, helyezes FROM `auto`
WHERE gyar IN ("Honda", "Mazda", "Nissan", "Toyota")
ORDER BY helyezes;

--4Egyszer
SELECT gyar FROM `auto` 
GROUP BY gyar
HAVING COUNT(azon) = 1;

--5Elsok
SELECT gyar, COUNT(azon) FROM `auto`
GROUP BY gyar, helyezes
HAVING helyezes = 1
ORDER BY COUNT(azon) DESC
LIMIT 3;

--6Evek
SELECT ev FROM `auto`
WHERE 
gyar = (
    SELECT gyar FROM `auto`
    GROUP BY gyar, modell
    ORDER BY COUNT(azon) DESC
    LIMIT 1)
AND modell = (
    SELECT modell FROM `auto`   
    GROUP BY gyar, modell
    ORDER BY COUNT(azon) DESC
    LIMIT 1);

--7Fiesta
SELECT gyar, modell FROM `auto` 
GROUP BY gyar, modell
HAVING gyar = (
    SELECT DISTINCT gyar FROM auto
    WHERE modell = "Fiesta"
) AND modell <> "Fiesta";

--8Elsomasodik
SELECT gyar, modell, ev FROM `auto`
WHERE helyezes = 2
GROUP BY gyar, modell, ev
HAVING modell IN (
	SELECT modell FROM `auto` 
	WHERE helyezes = 1
	GROUP BY gyar, modell
);

--9Szorosnyeres
SELECT a1.ev, a1.gyar, a1.modell, a2.gyar, a2.modell
FROM `auto` AS a1 INNER JOIN `auto` As a2
WHERE a1.ev = a2.ev 
AND a1.helyezes = 1 AND a2.helyezes = 2 
AND ABS(a1.pont - a2.pont) <= 5;



--10Eremtabla
SELECT gyar,
    SUM(CASE WHEN helyezes = 1 THEN 1 ELSE 0 END) AS "1. helyezés",
    SUM(CASE WHEN helyezes = 2 THEN 1 ELSE 0 END) AS "2. helyezés",
    SUM(CASE WHEN helyezes = 3 THEN 1 ELSE 0 END) AS "3. helyezés"
FROM `auto`
GROUP BY gyar
ORDER BY gyar;

