/*
Adatbázis-vizsga -Januar-2-1 - 2018.01.29. megoldása
*/

/*
1.	Készítsen listát azokról a vajdasági személyekről (családi név 
	és keresztnév), akiknek (1) a vezeték- és keresztneve ugyanazzal 
	a betűvel kezdődik, nem aktívak és nincs életrajzuk vagy 
	(2) profik, aktívak, és az életrajzuk hosszabb 100 jelnél
	(karakter). Rendezze az eredménytáblát a vezetéknevek, majd azon 
	belül a keresztnevek szerint ABC sorrendbe! (10 pont) 
*/

use Szinhportal;
go


select SZE_VEZETEKNEV,SZE_NEV
	from dbo.SZEMELY
	where SZE_VAJDASAGI='true' and 
		((LEFT(SZE_VEZETEKNEV,1)=substring(SZE_NEV,1,1) and
			SZE_AKTIV='false' and SZE_ELETRAJZ is null) or
		(SZE_AMATOR='false' and SZE_AKTIV='false' and
			LEN(SZE_ELETRAJZ)>100))
	order by SZE_VEZETEKNEV,SZE_NEV;
go



/*
3.	Kik azok a személyek és melyek a tartózkodási helyeik 
	(családi név, keresztnév és helyiségnév), akik vajdaságiként 
	vannak nyilvántartva, de a szerbiai tartózkodási helyük 
	irányítószáma Vajdaságon kívüli helyiségre utal 
	(a vajdasági helyiségnevek irányítószámainak értékhalmaza 
	21000 és 25999 közötti értékeket tartalmazza)? 
	A rendezés helyiségnevenként, családi nevenként, majd 
	keresztnevenként történjen ABC sorrendben. (10 pont) 
	- INFORMATIKUSOKNAK (a managementeseknek nem jár pont a 
	megoldásért!!!)
 */
	
select SZE_VEZETEKNEV,SZE_NEV,HELYISEGNEV
	from dbo.SZEMELY sz
	join dbo.HELYISEG h on h.HELYISEG_AZ=sz.HELYISEG_AZ
	join dbo.ORSZAG o on o.ORSZAG_AZ=h.ORSZAG_AZ
	where SZE_VAJDASAGI='true' and ORSZAGNEV='Szerbia' and
		H_IRANYITOSZAM not between 21000 and 25999
	order by HELYISEGNEV,SZE_VEZETEKNEV,SZE_NEV;
go

/*
4.	Mutassuk ki, hogy országonként (Országnév) hány olyan helyiség 
	van, ahol működik amatőr színház, de ahol a helyiségben szereplő 
	helyiségnév vagy a helyiség irányítószáma nem egyezik meg a
	színház táblában található megfelelő oszlopértékkel! 
	Az eredménytáblát rendezze a helyiségek száma szerint csökkenő 
	sorrendbe! (10 pont)
*/

select ORSZAGNEV,COUNT(h.HELYISEG_AZ) as "HelysegszamAholAmatorszinhazakVannak"
	from dbo.ORSZAG o
	join dbo.HELYISEG h on h.ORSZAG_AZ=o.ORSZAG_AZ
	join dbo.SZINHAZ sz on sz.HELYISEG_AZ=h.HELYISEG_AZ
	where SZ_H_AMATOR='false' and 
		(HELYISEGNEV <> SZ_H_CIM_HELYSEG or
			H_IRANYITOSZAM<>SZ_H_CIM_IRANYITOSZAM)
	group by ORSZAGNEV
	order by COUNT(h.HELYISEG_AZ);
go


/*
5.	Hány nyilvántartott professzionális, aktív személy tartózkodik 
	országonként (Országnév) az egyes olyan helyiségekben 
	(Helyiségnév), amelyekben nem működik színház? 
	(Országonként, azon belül helyiségenként hány személynek kell 
	utaznia a munkahelyére?) Az eredménytáblákba csak azok a sorok 
	kerüljenek, amelyekben a személyek száma nagyobb, mint 3. 
	Az eredménytáblát országnevenként (ABC sorrenbe), azon belül a 
	személyek száma alapján (csökkenő sorrendbe) kell rendezni! 
	(10 pont)
*/

select ORSZAGNEV,HELYISEGNEV,
		COUNT(SZEMELY_AZ) as "Szem.szamaHelysAholNincsSzinh."
	from dbo.ORSZAG o
		join dbo.HELYISEG h on h.ORSZAG_AZ=o.ORSZAG_AZ
		join dbo.SZEMELY sz on sz.HELYISEG_AZ=h.HELYISEG_AZ
		left join dbo.SZINHAZ szi on szi.HELYISEG_AZ=h.HELYISEG_AZ
	where SZE_AMATOR='false' and SZE_AKTIV='true' and
		SZINHAZNEV is null
	group by ORSZAGNEV,HELYISEGNEV
	having COUNT(SZEMELY_AZ)>0
	order by ORSZAGNEV,COUNT(SZEMELY_AZ) desc;
go


select ORSZAGNEV,HELYISEGNEV,
		COUNT(SZEMELY_AZ) as "Szem.szamaHelysAholNincsSzinh."
	from dbo.ORSZAG o
		join dbo.HELYISEG h on h.ORSZAG_AZ=o.ORSZAG_AZ
		join dbo.SZEMELY sz on sz.HELYISEG_AZ=h.HELYISEG_AZ
	where SZE_AMATOR='false' and SZE_AKTIV='true' and
		h.HELYISEG_AZ not in (select HELYISEG_AZ
								from dbo.SZINHAZ)
	group by ORSZAGNEV,HELYISEGNEV
	having COUNT(SZEMELY_AZ)>0
	order by ORSZAGNEV,COUNT(SZEMELY_AZ) desc;
go