-- 1. feladat
SELECT Orszag
FROM Szallito
INTERSECT
SELECT Orszag
FROM Vevo
-- 2. feladat
SELECT CegNev AS 'Szallito neve'
FROM Szallito sz
JOIN Termek t ON t.SzallitoID=sz.ID
WHERE Fax IS NULL AND EgysegAr > 20
-- 3. feladat
SELECT *
FROM Termek
WHERE ID NOT IN (SELECT TermekID
                    FROM MegrendelesSor)
SELECT t.*
FROM Termek t
LEFT JOIN MegrendelesSor ms ON t.ID = ms.TermekID
WHERE ms.ID IS NULL
-- 4. feladat
SELECT TOP 1 Datum -- vagy min(datum)
FROM Megrendeles
WHERE Osszeg > 5000 AND GETDATE() - Datum > 1825 -- 365*5
ORDER BY Datum
-- 5. feladat
SELECT Telepules, SUM(ms.EgysegAr*Mennyiseg) -- AS 'Valami'
FROM Termek t
JOIN MegrendelesSor ms ON t.ID = ms.TermekID
JOIN Megrendeles m ON ms.MegrendelesID = m.ID
JOIN Vevo v ON m.VevoID = v.ID
WHERE Kifutott = 1
GROUP BY Telepules
ORDER BY SUM(ms.EgysegAr*Mennyiseg) -- 'Valami'
-- 6. feladat
SELECT Orszag
FROM Vevo
GROUP BY Orszag
HAVING COUNT(*) = (SELECT TOP 1 COUNT(*)
                    FROM Vevo
                    GROUP BY Orszag
                    ORDER BY COUNT(*) DESC)
-- 7. feladat
SELECT DISTINCT Megnevezes, t.EgysegAr
FROM Termek t
WHERE t.ID NOT IN (SELECT TermekID
                    FROM MegrendelesSor ms
                    JOIN Megrendeles m ON ms.MegrendelesID = m.ID
                    WHERE YEAR(Datum) = 2012)
    -- EXCEPT-tel is megoldható
-- 8. feladat
SELECT ID, Osszeg
FROM Megrendeles
EXCEPT
SELECT MegrendelesID, SUM(EgysegAr*Mennyiseg)
FROM MegrendelesSor
GROUP BY MegrendelesID