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

/*
1.	Készítsen listát azokról a helyiségekről (Helyiségnév), amelyek 
	vajdaságiak és a nevük B betűvel kezdődik vagy a nevük N betűvel 
	kezdődik, az azonosítójuk pedig nagyobb, mint az országazonosítójuk 
	duplája, de vajdaságon kívüliek! Valamennyi listázott helységnévre 
	igaz kell, hogy legyen, hogy a helyiségnév utolsó betűje nem lehet c! 
	Rendezze az eredménytáblát a helyiségnevek szerint ABC sorrendbe, 
	majd azon belül a helyiségnév-hosszok szerint csökkenő sorrendbe! 
	(10 pont) 
*/

use Szinhportal;
go

select HELYISEGNEV
	from dbo.HELYISEG
	where right(HELYISEGNEV,1)<>'c' and
		((H_IRANYITOSZAM between 21000 and 25999 and left(HELYISEGNEV,1)='B') or
		 (LEFT(HELYISEGNEV,1)='N' and  HELYISEG_AZ>2*ORSZAG_AZ and
			H_IRANYITOSZAM not between 21000 and 25999))
	order by HELYISEGNEV,LEN(HELYISEGNEV) desc;
go

/*
3.	Listázza azokat az országokat (Országnév) és helyiségeit (Helyiségnév), 
	amelyek ugyanazzal a betűvel kezdődnek, ahol a helyiségnevek 
	hosszabbak az országnévnél, és amely helyiségekben legalább öt 
	személy tartózkodik és legalább két profi színház működik. 
	A rendezés helyiségnevenként (ABC sorrendben) és színházszámonként 
	(csökkenő sorrenben) történjék. - INFORMATIKUSOKNAK  
	(a managementeseknek nem jár pont a megoldásért!!!) (10 pont)
 */
	
select ORSZAGNEV, HELYISEGNEV
	from dbo.HELYISEG h 
	join dbo.ORSZAG o on o.ORSZAG_AZ=h.ORSZAG_AZ
	where left(ORSZAGNEV,1)=left(HELYISEGNEV,1) and
		len(HELYISEGNEV)<len(ORSZAGNEV) and 
		(select COUNT(SZEMELY_AZ)
			from dbo.SZEMELY sz
			where sz.HELYISEG_AZ=h.HELYISEG_AZ
			group by HELYISEG_AZ)>=5 and
		(select count(SZINHAZ_AZ)
			from dbo.SZINHAZ szi
			where szi.HELYISEG_AZ=h.HELYISEG_AZ and
				Sz_h_amator='false'
			group by HELYISEG_AZ)>=2
	order by HELYISEGNEV, count(SZINHAZ_AZ) desc;
go

select ORSZAGNEV, HELYISEGNEV,COUNT(distinct SZEMELY_AZ),
		count(distinct SZINHAZ_AZ)
	from dbo.HELYISEG h 
	join dbo.ORSZAG o on o.ORSZAG_AZ=h.ORSZAG_AZ
	join dbo.SZEMELY sz on sz.HELYISEG_AZ=h.HELYISEG_AZ
	join dbo.SZINHAZ szi on szi.HELYISEG_AZ=h.HELYISEG_AZ
	where left(ORSZAGNEV,1)=left(HELYISEGNEV,1) and
		len(HELYISEGNEV)<len(ORSZAGNEV) and
		Sz_h_amator='false'
	group by ORSZAGNEV, HELYISEGNEV
	having COUNT(SZEMELY_AZ)>=5 and count(SZINHAZ_AZ)>=2
	order by HELYISEGNEV, count(SZINHAZ_AZ) desc;
go



/*
4.	A Színház táblát használva számlálja meg a színházak számát a 
	Vajdaságon kívüli szerbiai helyiségekben (Sz_h_cím_helység), 
	azon belül pedig irányítószámonként. Csak az internet címmel 
	rendelkező profi színházakat kell megszámlálni, amelyek email 
	címmel és telefonnal is rendelkeznek. Csak azok a sorok maradjanak 
	az eredménytáblában, amelyekben a színházak szána 5-től 10-ig terjed! 
	Az eredménytáblát rendezze a színházak száma szerint csökkenő 
	sorrendbe! (10 pont)
*/


select SZ_H_CIM_HELYSEG,SZ_H_CIM_IRANYITOSZAM,COUNT(SZINHAZ_AZ) as "Szinhazak szamA"
	from dbo.SZINHAZ
	where SZ_H_CIM_IRANYITOSZAM not between 21000 and 25999 and
		right(SZ_H_WWW_CIM,2)='rs' and
		SZ_H_AMATOR='false' and SZ_H_WWW_CIM is not null and
		SZ_H_EMAIL is not null and SZ_H_TELEFON is not null
	group by SZ_H_CIM_HELYSEG,SZ_H_CIM_IRANYITOSZAM
	having COUNT(SZINHAZ_AZ) between 1 and 10
	order by COUNT(SZINHAZ_AZ) desc;
go


/*
5.	A hárombetűs országjelzésű országok (Országnév) hány olyan különböző 
	irányítószámmal rendelkeznek, amelyekben amatőr színházak működnek, 
	de amely helyiségekben nem tartózkodik P vezetéknévvel rendelkező 
	amatőr személy a nyilvántartásunk szerint? Az eredménytáblába csak 
	az S betűvel kezdődő országjelzésekhez tartozó országok kerüljenek, 
	amelyekre a megszámlált különböző irányítószámok száma meghaladja 
	a négyet. Az eredménytáblát az irányítószámok száma alapján 
	(csökkenő sorrendbe) kell rendezni! (10 pont)
*/

select ORSZAGNEV,COUNT(distinct H_IRANYITOSZAM) as "Iranyitoszamok szama"
	from dbo.ORSZAG o
		join dbo.HELYISEG h on h.ORSZAG_AZ=o.ORSZAG_AZ
		join dbo.SZINHAZ szi on szi.HELYISEG_AZ=h.HELYISEG_AZ
	where len(ORSZAG_JELZES)=3 and SZ_H_AMATOR='true' and
		LEFT(ORSZAG_JELZES,1)='S' and
		h.HELYISEG_AZ not in (select HELYISEG_AZ
								from dbo.SZEMELY
								where SZE_AMATOR='true' and
									LEFT(SZE_VEZETEKNEV,1)='P')
	group by ORSZAGNEV
	having COUNT(distinct H_IRANYITOSZAM)>4
	order by COUNT(distinct H_IRANYITOSZAM) desc;
go