--1
SELECT Orszag
FROM Szallito
WHERE Orszag IN (SELECT Orszag
FROM Vevo)
GROUP BY Orszag


--2
SELECT Szallito.CegNev AS 'Szállító neve'
FROM Szallito
WHERE Fax IS NULL OR Fax=''
GROUP BY Szallito.CegNev
HAVING Szallito.CegNev IN (

SELECT Szallito.CegNev
FROM Szallito
join Termek AS t ON SzallitoID = Szallito.ID
WHERE EgysegAr > 20
)

--3

--a
SELECT *
FROM Termek
WHERE ID NOT IN (
SELECT TermekID
FROM MegrendelesSor
)

--b
SELECT *
FROM Termek
left join MegrendelesSor AS m ON Termek.ID= TermekID
WHERE MegrendelesID IS NULL

--4
SELECT top 1 Datum
FROM Megrendeles
WHERE CAST(getdate()-Datum AS REAL) > (5*365)
ORDER BY Datum DESC


--5
SELECT Telepules ,SUM(m.Osszeg) AS [Összeg]
FROM MegrendelesSor
join Megrendeles AS m ON MegrendelesSor.MegrendelesID =m.ID
join Vevo AS v ON m.VevoID=v.ID
WHERE TermekID IN (
SELECT ID
FROM Termek
WHERE Kifutott = 1
)
GROUP BY Telepules
ORDER BY [Összeg]


--6
SELECT Orszag,COUNT(Orszag) AS db
FROM Vevo
GROUP BY Orszag
HAVING COUNT(Orszag) = (
SELECT top 1 COUNT(Orszag)
FROM Vevo
GROUP BY Orszag
ORDER BY COUNT(Orszag) DESC
)
--7

SELECT Megnevezes,EgysegAr
FROM Termek
WHERE ID IN (
SELECT TermekID
FROM MegrendelesSor
join Megrendeles ON Megrendeles.ID = MegrendelesID
WHERE YEAR(Datum)=2012
)

--8
SELECT Megrendeles.ID,(EgysegAr*Mennyiseg) AS 'valós összeg'--,Osszeg
FROM Megrendeles
join MegrendelesSor ON Megrendeles.ID = MegrendelesSor.MegrendelesID
WHERE (EgysegAr*Mennyiseg) != Osszeg

--9

SELECT (KeresztNev + ' ' + VezetekNev) AS 'Vevő neve'
FROM Vevo
WHERE ID IN (

SELECT VevoID
FROM Megrendeles
WHERE Datum >= '2012-08-01'
GROUP BY VevoID
HAVING COUNT(VevoID) >= 2
)
ORDER BY [Vevő neve]

--10


SELECT Szallito.CegNev AS 'Szallító', vevo.Orszag AS 'Rendelő ország' ,SUM(Mennyiseg) AS 'Darabszám'
FROM Megrendeles
join MegrendelesSor ON MegrendelesSor.MegrendelesID = Megrendeles.ID
join Termek ON Termek.ID=TermekID
join Szallito ON Szallito.ID=SzallitoID
join Vevo ON VevoID = Vevo.ID
WHERE vevo.Orszag IN ('France','Germany')
GROUP BY vevo.Orszag , Szallito.CegNev