/*
Ötödik találkozás az MS SQLSERVER 2008 R2-vel

SELECT-ek a SzinhPortal adatbazisban
1. ora - 7. het - 2020.11.19.

Csütörtök - 11:30-13:05
*/

use SzinhPortal;
go

--calculator

select 2*5;
select SIN(60);
select SIN(30);


select * 
	from dbo.SZINHAZ;
	
select SZ_H_CIM_HELYSEG,SZINHAZNEV,SZ_H_WWW_CIM,SZ_H_EMAIL,SZ_H_AMATOR
	from dbo.SZINHAZ;
	
select SZ_H_CIM_HELYSEG as "Varos",
		SZINHAZNEV as "A szinhaz neve",
		SZ_H_WWW_CIM as"Honlap cime",
		SZ_H_EMAIL as "Mail-cim"
	from dbo.SZINHAZ;
	
select SZ_H_CIM_HELYSEG as "Varos",
		SZ_H_CIM_IRANYITOSZAM*2 as "Ketszeres iranyitoszam",
		SZINHAZNEV as "A szinhaz neve"
	from dbo.SZINHAZ;



select SZ_H_CIM_HELYSEG as "Varos",
		' melynek iranyitoszama ' as " Magyarazat ",
		SZ_H_CIM_IRANYITOSZAM*2 as "Ketszeres iranyitoszam",
		SZINHAZNEV as "A szinhaz neve"
	from dbo.SZINHAZ;


select SZ_H_CIM_HELYSEG as "Varos",
		' melynek iranyitoszama ' as " Magyarazat ",
		SZ_H_CIM_IRANYITOSZAM as "Iranyitoszam",
		SZINHAZNEV as "A szinhaz neve"
	from dbo.SZINHAZ
	where SZ_H_CIM_IRANYITOSZAM<20000;	


select SZ_H_CIM_HELYSEG as "Varos",
		' melynek iranyitoszama ' as " Magyarazat ",
		SZ_H_CIM_IRANYITOSZAM as "Iranyitoszam",
		SZINHAZNEV as "A szinhaz neve"
	from dbo.SZINHAZ
	group by SZ_H_CIM_HELYSEG;			

/*
Msg 8120, Level 16, State 1, Line 3
Column 'dbo.SZINHAZ.SZ_H_CIM_IRANYITOSZAM' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
*/

select SZ_H_CIM_HELYSEG as "Varos",
		' melynek iranyitoszama ' as " Magyarazat ",
		SZ_H_CIM_IRANYITOSZAM as "Iranyitoszam",
		SZINHAZNEV as "A szinhaz neve",
		COUNT(SZINHAZ_AZ)
	from dbo.SZINHAZ
	group by SZ_H_CIM_HELYSEG;	

/*
Msg 8120, Level 16, State 1, Line 3
Column 'dbo.SZINHAZ.SZ_H_CIM_IRANYITOSZAM' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
*/

select SZ_H_CIM_HELYSEG as "Varos",
		SZINHAZNEV as "A szinhaz neve",
		COUNT(SZINHAZ_AZ)
	from dbo.SZINHAZ
	group by SZ_H_CIM_HELYSEG;

/*
Msg 8120, Level 16, State 1, Line 3
Column 'dbo.SZINHAZ.SZINHAZNEV' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
*/

select SZ_H_CIM_HELYSEG as "Varos",
		SZINHAZNEV as "A szinhaz neve",
		COUNT(SZINHAZ_AZ)
	from dbo.SZINHAZ
	group by SZ_H_CIM_HELYSEG, SZINHAZNEV;
	

select SZ_H_CIM_HELYSEG as "Varos",
		' melynek iranyitoszama ' as " Magyarazat ",
		SZ_H_CIM_IRANYITOSZAM as "Iranyitoszam",
		SZINHAZNEV as "A szinhaz neve"
	from dbo.SZINHAZ
	order by SZ_H_CIM_HELYSEG;	
	
-- Itt a vege fuss el vele




/*
Hatodik találkozás az MS SQLSERVER 2008 R2-vel

SELECT-ek a SzinhPortal adatbazisban
2. ora - 8. het - 2020.11.26.

Csütörtök - 11:30-13:05
*/

use SzinhPortal;

-- getdate()+1..., CAST(expression AS datatype(length))
-- agregacios fuggvenyek group by-jal es anelkul
-- fuggvenyek: left, right, substring, lower, upper, len
-- fuggvenyek: year(), mounth(), day(),
-- fuggvenyek: dateadd(), datediff, datepart()

/*
 -- DATEADD()
If you want to add or subtract an amount of time to a column or a variable, then display a new value
in a rowset or set a variable with that new value, DATEADD() will do this. The syntax for DATEADD() is
DATEADD(datepart, number, date)
The datepart option applies to all of the date functions and details what you want to add from
milliseconds to years. These are defined as reserved words and therefore are not surrounded by
quotation marks. There are a number of possible values, as detailed in Table 11-1.
Taking the second option of the datepart function, to add the value, make the number positive,
and to subtract a number, make it negative. Moving to the final option of the datepart function, this
can be either a value, a variable, or a column date type holding the date and time you wish to change.
Table 11-1. Potential Values for datepart
datepart Definition Meaning
isowk, isoww ISOWeek is a numbering system used to give every week in the calendar
a unique, ascending number. An ISO week starts on a Monday, and
Week 1 is the week containing the first Thursday of that year. For example,
in 2008, the first Thursday occurred on January 3, so Week 1 ran from
December 31, 2007, through January 6, 2008.
tz Timezone offset
ns Nanosecond
mcs Microsecond
ms Millisecond
ss, s Second
mi, n Minute
hh Hour
dw, w Weekday
wk, ww Week
dd, d Day
dy, y Day of year
mm, n Month
qq, q Quarter
yy, yyyy Year
*/

select GETDATE() as "Datum (ido)", GETDATE()+2 as "Ket nappal kesobbi datum (ido)"

select GETDATE() as "Datum (ido)", GETDATE()+2 as "Ket nappal kesobbi datum (ido)",
		DATEADD(wk,1,getdate())
		
select GETDATE() as "Datum (ido)", GETDATE()+2 as "Ket nappal kesobbi datum (ido)",
		DATEADD(dd,2,getdate())as "Ket nappal kesobbi datum (ido)",
		DATEADD(wk,1,getdate()) as "Egy hettel kesobbi datum (ido)"

select GETDATE() as "Datum (ido)", year(GETDATE()+2) as "Ket nappal kesobbi datum (ido) evszama",
		month(GETDATE()+2) as "Ket nappal kesobbi datum (ido) honapja",
		day(GETDATE()+2) as "Ket nappal kesobbi datum (ido) napsyamja a honapban"

/*
-- DATEPART()
If you wish to achieve returning part of a date from a date variable, column, or value, you can use
DATEPART() within a SELECT statement.
As you may be expecting by now, the syntax has datepart as the first option, and then the
datetoinspect as the second option, which returns the numerical day of the week from the date
inspected.
DATEPART(datepart, datetoinspect)
1. We need to set only one local variable to a date and time. After that, we find the day of the month.
DECLARE @WhatsTheDay datetime
SET @WhatsTheDay = '24 March 2008 3:00 PM'
SELECT DATEPART(dd, @WhatsTheDay)
Figure 11-24 shows the results after executing this code.
Figure 11-24. Finding part of a date
2. To produce a more pleasing date and time for a statement, we can combine DATEPART() and DATENAME() to
have a meaningful output. The function CAST(), which we will look at in detail shortly, is needed here, as it is a
data type conversion function.
DECLARE @WhatsTheDay datetime
SET @WhatsTheDay = '24 March 2008 3:00 PM'
SELECT DATENAME(dw, @WhatsTheDay) + ', ' +
CAST(DATEPART(dd,@WhatsTheDay) AS varchar(2)) + ' ' +
DATENAME(mm,@WhatsTheDay) + ' ' +
CAST(DATEPART(yyyy,@WhatsTheDay) AS char(4))
3. When this is executed, it will produce the more meaningful date shown in Figure 11-25.
Figure 11-25. Finding and concatenating to provide a useful date


*/
		
select GETDATE() as "Datum (ido)", datepart(dd,GETDATE()) as "Datum (ido) evszama",
		datepart(mm,GETDATE()) as "Datum (ido) honapja",
		datepart(dd,GETDATE()) as "Datum (ido) napszamja a honapban"
	
select GETDATE() as "Datum (ido)", datepart(dd,GETDATE()) as "Datum (ido) evszama",
		datepart(mm,GETDATE()) as "Datum (ido) honapja",
		datepart(dd,GETDATE()) as "Datum (ido) napszamja a honapban",
		datepart(dd,GETDATE()+2) as "Datum + 2 nap (ido) napszamja a honapban"
		

select getdate() as "Datum",
		len(getdate()) as "Datumhossz",
		LEFT(getdate(),5) as "Datum elso 5 jele", 
		RIGHT(getdate(),5) as "Datum utolso 5 jele",
		SUBSTRING(getdate(),1,5) as "Datum elso 5 jele-substring", 
		SUBSTRING(getdate(),12,5) as "Datum utolso? 5 jele-substring"
		
/*
Msg 8116, Level 16, State 1, Line 1
Argument data type datetime is invalid for argument 1 of substring function.
*/

select getdate() as "Datum",
		len(getdate()) as "Datumhossz",
		LEFT(getdate(),5) as "Datum elso 5 jele", 
		RIGHT(getdate(),5) as "Datum utolso 5 jele"

/*
2020-11-26 12:10:20.657	19	Nov 2	:10PM
*/

/*
CAST(expression AS datatype(length))
Parameter Values
Value	Description
expression	Required. The value to convert
	datatype	Required. The datatype to convert expression to. Can be one of the following: bigint, int, 
	smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, 
	char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image
(length)	Optional. The length of the resulting data type (for char, varchar, nchar, nvarchar, 
	binary and varbinary)
*/


select getdate() as "Datum",
		len(getdate()) as "Datumhossz",
		LEFT(getdate(),5) as "Datum elso 5 jele", 
		RIGHT(getdate(),5) as "Datum utolso 5 jele",
		SUBSTRING(cast(getdate() as character),1,5) as "Datum elso 5 jele-substring", 
		SUBSTRING(cast(getdate() as character),12,5) as "Datum utolso? 5 jele-substring"
		
/*
2020-11-26 12:13:14.470	19	Nov 2	:13PM	Nov 2	 12:1
*/
		
select getdate() as "Datum", cast(getdate() as character) as "getdate() karakterekke alakitva",
		len(getdate()) as "Datumhossz",
		LEFT(getdate(),5) as "Datum elso 5 jele", 
		RIGHT(getdate(),5) as "Datum utolso 5 jele",
		SUBSTRING(cast(getdate() as character),1,5) as "Datum elso 5 jele-substring", 
		SUBSTRING(cast(getdate() as character),12,5) as "Datum utolso? 5 jele-substring"


/*
2020-11-26 12:16:56.403	Nov 26 2020 12:16PM           	19	Nov 2	:16PM	Nov 2	 12:1
*/

select getdate() as "Datum", cast(getdate() as character) as "getdate() karakterekke alakitva",
		len(getdate()) as "Datumhossz",
		LEFT(getdate(),5) as "Datum elso 5 jele", 
		RIGHT(getdate(),5) as "Datum utolso 5 jele",
		SUBSTRING(cast(getdate() as character),1,5) as "Datum elso 5 jele-substring", 
		SUBSTRING(cast(getdate() as character),12,5) as "Datum utolso? 5 jele-substring",
		SUBSTRING(cast(getdate() as character),len(getdate())- 5,5) as "Datum utolso? 5 jele-substring",
		SUBSTRING(cast(getdate() as character),len(getdate())- 4,5) as "Datum utolso? 5 jele-substring"
	
/*
2020-11-26 12:24:18.283	Nov 26 2020 12:24PM           	19	Nov 2	:24PM	Nov 2	 12:2	2:24P	:24PM
*/


select COUNT(*)
	from dbo.SZEMELY;
	
select COUNT(SZEMELY_AZ)
	from dbo.SZEMELY;
	
select COUNT(SZE_ELETRAJZ)
	
	from dbo.SZEMELY
	where SZE_ELETRAJZ is null;

select * from dbo.SZEMELY

update dbo.SZEMELY
	set SZE_ELETRAJZ = null
	where SZE_ELETRAJZ='???'
	

select COUNT(SZE_ELETRAJZ)
	from dbo.SZEMELY	
	
select COUNT(*), AVG(HELYISEG_AZ),SUM(SZEMELY_AZ)
	from dbo.SZEMELY

/*
19	7	190
*/

select COUNT(*) as 'Szemelyek szama', 
		AVG(HELYISEG_AZ) as 'Atlagos helysegazonosito',
		SUM(SZEMELY_AZ) as 'Szemeazonositok osszege'
	from dbo.SZEMELY
	
	
select SZE_VAJDASAGI,SZE_AMATOR,
		COUNT(*) as 'Szemelyek szama', 
		AVG(HELYISEG_AZ) as 'Atlagos helysegazonosito',
		SUM(SZEMELY_AZ) as 'Szemeazonositok osszege'
	from dbo.SZEMELY
	group by SZE_VAJDASAGI,SZE_AMATOR

/*
0	0	17	7	176
1	0	2	2	14
*/


select SZE_VAJDASAGI,SZE_AMATOR,SZE_AKTIV,
		COUNT(*) as 'Szemelyek szama', 
		AVG(HELYISEG_AZ) as 'Atlagos helysegazonosito',
		SUM(SZEMELY_AZ) as 'Szemeazonositok osszege'
	from dbo.SZEMELY
	group by SZE_VAJDASAGI,SZE_AMATOR, SZE_AKTIV

/*
0	0	0	2	7	9
0	0	1	15	7	167
1	0	1	2	2	14
*/

select * from dbo.SZEMELY

select SZE_VEZETEKNEV, SZE_NEV, SZE_VAJDASAGI
	from dbo.SZEMELY
	where SZE_NEV in ('Attila', 'Bernadett')
	
/*
Keresztes	Attila	0
Dolhai	Attila	0
Vágó	Bernadett	0
Németh	Attila	0
Vágó	Bernadett	0
*/	

select SZE_VEZETEKNEV, SZE_NEV, SZE_VAJDASAGI
	from dbo.SZEMELY
	where SZE_NEV like 'B%'


/*
Vágó	Bernadett	0
Vágó	Bernadett	0
Angler	Balázs	0
*/
	

select SZE_VEZETEKNEV, SZE_NEV, SZE_VAJDASAGI
	from dbo.SZEMELY
	where SZE_NEV like 'B%' or SZE_NEV like 'A%'
	

/*
Keresztes	Attila	0
Dolhai	Attila	0
Vágó	Bernadett	0
Németh	Attila	0
Vágó	Bernadett	0
Angler	Balázs	0
*/



/*
Hetedik találkozás az MS SQLSERVER 2008 R2-vel

SELECT-ek a SzinhPortal adatbazisban
3. ora - 9. het - 2020.12.03.

Csütörtök - 11:30-13:05
*/

-- having, order by
-- tobbtablas lekerdezesek

use SzinhPortal;


/*
Listazzuk a bemutato evet, az eloadashosszakat percekben, szindarabok szamat, 
a sorok szamat es a kitoltott REPERTOARON mezok szamat bemutato ev(ek)re
es eloadashosszokra csoportositva. Csak azokat a sorokat kell bekapcsolni
a szamlalasba, amelyekben a tartalomleiras kitoltott (nem NULL ereteku).
Ne jelenjenek meg a 2014-ben bemutatott eloadasokrol szolo osszegzesek.
Rendezzuk az eredmenytablat a bemutato-evek szerint novekvo sorrendbe!
*/

--1-1
select YEAR(EL_BEMUTATO_DATUM) as "Bemutato eve",
		EL_HOSSZA_PERC, COUNT(SZINDARAB_AZ) as "Szindarabok szama",
		COUNT(*) as "Sorok szama", count(REPERTOARON) as "Szindarabszam a repertoaron"
	from dbo.ELOADAS_PROJEKT
	where EL_TARTALOM is not null
	group by YEAR(EL_BEMUTATO_DATUM), EL_HOSSZA_PERC
	having YEAR(EL_BEMUTATO_DATUM)<>2014
	order by YEAR(EL_BEMUTATO_DATUM);
go

--2
select YEAR(EL_BEMUTATO_DATUM) as "Bemutato eve",
		EL_HOSSZA_PERC, COUNT(SZINDARAB_AZ) as "Szindarabok szama",
		COUNT(*) as "Sorok szama", count(REPERTOARON) as "Szindarabszam a repertoaron"
	from dbo.ELOADAS_PROJEKT
--	where EL_TARTALOM is not null
	group by YEAR(EL_BEMUTATO_DATUM), EL_HOSSZA_PERC
	having YEAR(EL_BEMUTATO_DATUM)<>2014
	order by YEAR(EL_BEMUTATO_DATUM);
go


--3
select YEAR(EL_BEMUTATO_DATUM) as "Bemutato eve",
		EL_HOSSZA_PERC, COUNT(SZINDARAB_AZ) as "Szindarabok szama",
		COUNT(*) as "Sorok szama", count(REPERTOARON) as "Szindarabszam a repertoaron"
	from dbo.ELOADAS_PROJEKT
--	where EL_TARTALOM is not null
	group by YEAR(EL_BEMUTATO_DATUM), EL_HOSSZA_PERC
--	having YEAR(EL_BEMUTATO_DATUM)<>2014
	order by YEAR(EL_BEMUTATO_DATUM);
go

--1-2
select YEAR(EL_BEMUTATO_DATUM) as "Bemutato eve",
		EL_HOSSZA_PERC, COUNT(SZINDARAB_AZ) as "Szindarabok szama",
		COUNT(*) as "Sorok szama", count(REPERTOARON) as "Szindarabszam a repertoaron"
	from dbo.ELOADAS_PROJEKT
	where EL_TARTALOM is not null and YEAR(EL_BEMUTATO_DATUM)<>2014
	group by YEAR(EL_BEMUTATO_DATUM), EL_HOSSZA_PERC
--	having YEAR(EL_BEMUTATO_DATUM)<>2014
	order by YEAR(EL_BEMUTATO_DATUM);
go

--4

select YEAR(EL_BEMUTATO_DATUM) as "Bemutato eve",
		EL_HOSSZA_PERC, COUNT(SZINDARAB_AZ) as "Szindarabok szama",
		COUNT(*) as "Sorok szama", count(REPERTOARON) as "Szindarabszam a repertoaron"
	from dbo.ELOADAS_PROJEKT
	where EL_TARTALOM is not null and YEAR(EL_BEMUTATO_DATUM)<>2014 and
			COUNT(SZINDARAB_AZ)=1
	group by YEAR(EL_BEMUTATO_DATUM), EL_HOSSZA_PERC
	having YEAR(EL_BEMUTATO_DATUM)<>2014
	order by YEAR(EL_BEMUTATO_DATUM);
go

/*
Msg 147, Level 15, State 1, Line 6
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
*/

--5

select YEAR(EL_BEMUTATO_DATUM) as "Bemutato eve",
		EL_HOSSZA_PERC, COUNT(SZINDARAB_AZ) as "Szindarabok szama",
		COUNT(*) as "Sorok szama", count(REPERTOARON) as "Szindarabszam a repertoaron"
	from dbo.ELOADAS_PROJEKT
	where 1>2
	group by YEAR(EL_BEMUTATO_DATUM), EL_HOSSZA_PERC
	having YEAR(EL_BEMUTATO_DATUM)<>2014
	order by YEAR(EL_BEMUTATO_DATUM);
go

--6

select YEAR(EL_BEMUTATO_DATUM) as "Bemutato eve",
		EL_HOSSZA_PERC, COUNT(SZINDARAB_AZ) as "Szindarabok szama",
		COUNT(*) as "Sorok szama", count(REPERTOARON) as "Szindarabszam a repertoaron"
	from dbo.ELOADAS_PROJEKT
	where EL_TARTALOM is not null
	group by YEAR(EL_BEMUTATO_DATUM), EL_HOSSZA_PERC
--	having YEAR(EL_BEMUTATO_DATUM)<>2014
	order by YEAR(EL_BEMUTATO_DATUM) desc;
go

--

select ORSZAGNEV, HELYISEGNEV
	from dbo.ORSZAG
	join dbo.HELYISEG on ORSZAG_AZ=ORSZAG_AZ
	
/*Msg 209, Level 16, State 1, Line 3
Ambiguous column name 'ORSZAG_AZ'.
Msg 209, Level 16, State 1, Line 3
Ambiguous column name 'ORSZAG_AZ'.
*/

select ORSZAGNEV, HELYISEGNEV
	from dbo.ORSZAG o
	join dbo.HELYISEG h on h.ORSZAG_AZ=o.ORSZAG_AZ
	

select ORSZAGNEV, HELYISEGNEV
	from dbo.ORSZAG o
	join dbo.HELYISEG h on h.ORSZAG_AZ=o.ORSZAG_AZ
	
	
------
select ORSZAGNEV, HELYISEGNEV, SZINHAZNEV
	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
	order by ORSZAGNEV,  HELYISEGNEV, SZ_H_CIM_IRANYITOSZAM
	
select ORSZAGNEV, HELYISEGNEV, SZINHAZNEV, SZ_H_CIM_IRANYITOSZAM
	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
	order by SZ_H_CIM_IRANYITOSZAM
	
select ORSZAGNEV, HELYISEGNEV, SZINHAZNEV, SZ_H_CIM_IRANYITOSZAM
	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 HELYISEGNEV like 'B%'
	order by SZ_H_CIM_IRANYITOSZAM


select ORSZAGNEV, HELYISEGNEV, SZINHAZNEV, SZ_H_CIM_IRANYITOSZAM
	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 HELYISEGNEV not like 'B%'
	order by SZ_H_CIM_IRANYITOSZAM
	
select ORSZAGNEV, HELYISEGNEV, SZINHAZNEV, SZ_H_CIM_IRANYITOSZAM
	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 HELYISEGNEV in ('Szeged','Újvidék')
	order by SZ_H_CIM_IRANYITOSZAM
	
select ORSZAGNEV, HELYISEGNEV, SZINHAZNEV, SZE_VEZETEKNEV
	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
	join dbo.SZEMELY sze on sze.HELYISEG_AZ=h.HELYISEG_AZ
--	where HELYISEGNEV in ('Szeged','Újvidék')
--	order by SZ_H_CIM_IRANYITOSZAM
	

/*
Listazzuk a budapesti es szabadkai szemelyeket, akik aktivak es amatorok
, es az eletrajzuk nem hosszabb, mint 7 jel.
*/
select * from dbo.HELYISEG
select * from dbo.SZEMELY

select SZE_VEZETEKNEV, SZE_NEV, HELYISEGNEV, len(SZE_ELETRAJZ),
		SZE_AKTIV,SZE_AMATOR
	from dbo.SZEMELY sz
	join dbo.HELYISEG h on h.HELYISEG_AZ=sz.HELYISEG_AZ
	where (HELYISEGNEV='Budapest' or HELYISEGNEV='Szabadka') and  SZE_AKTIV=1
		and SZE_AMATOR='false' 
		and (len(SZE_ELETRAJZ)<=7 or LEN(SZE_ELETRAJZ) is null)

--

select SZE_VEZETEKNEV, SZE_NEV, HELYISEGNEV, len(SZE_ELETRAJZ),
		SZE_AKTIV,SZE_AMATOR
	from dbo.SZEMELY 
--	join dbo.HELYISEG h on h.HELYISEG_AZ=sz.HELYISEG_AZ
	where SZE_AKTIV=1 and SZE_AMATOR='false' 
		and (len(SZE_ELETRAJZ)<=7 or LEN(SZE_ELETRAJZ) is null)
		and HELYISEG_AZ in (select HELYISEG_AZ
								from dbo.HELYISEG
								where HELYISEGNEV in ('Budapest','Szabadka'))
/*
Msg 207, Level 16, State 1, Line 1
Invalid column name 'HELYISEGNEV'.
*/

select SZE_VEZETEKNEV, SZE_NEV, len(SZE_ELETRAJZ),
		SZE_AKTIV,SZE_AMATOR
	from dbo.SZEMELY 
--	join dbo.HELYISEG h on h.HELYISEG_AZ=sz.HELYISEG_AZ
	where SZE_AKTIV=1 and SZE_AMATOR='false' 
		and (len(SZE_ELETRAJZ)<=7 or LEN(SZE_ELETRAJZ) is null)
		and HELYISEG_AZ in (select HELYISEG_AZ
								from dbo.HELYISEG
								where HELYISEGNEV in ('Budapest','Szabadka'))
								
								


/*
Nyolcadik találkozás az MS SQLSERVER 2008 R2-vel

SELECT-ek a SzinhPortal adatbazisban
4. ora - 10. het - 2020.12.10.

Csütörtök - 11:30-13:05
*/

-- tovabbi tobbtablas lekerdezesek
-- tovabbi alselect-ek a whereben es a havingben
-- tobbszor vegrehajtodo alselectek
-- alselectek a fromban

use SzinhPortal;

/*
Melyik orszagbol valo es hogy hivjak a Romeo es Julia szindarab szerzojet
*/
--JOIN

select ORSZAGNEV,HELYISEGNEV, SZE_VEZETEKNEV, SZE_NEV, SZINDARAB_CIME
	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
	join dbo.SZERZO sze on sze.SZEMELY_AZ=sz.SZEMELY_AZ
	join dbo.SZINDARAB szi on szi.SZINDARAB_AZ=sze.SZINDARAB_AZ
	where SZINDARAB_CIME='Rómeó és Júlia';
go

--alSELECT

select ORSZAGNEV,HELYISEGNEV, SZE_VEZETEKNEV, SZE_NEV, SZINDARAB_CIME
	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
	join dbo.SZERZO sze on sze.SZEMELY_AZ=sz.SZEMELY_AZ
	where sze.SZINDARAB_AZ=(select SZINDARAB_AZ
							from SZINDARAB
							where SZINDARAB_CIME='Rómeó és Júlia');
/*
Msg 207, Level 16, State 1, Line 1
Invalid column name 'SZINDARAB_CIME'.
*/

select ORSZAGNEV,HELYISEGNEV, SZE_VEZETEKNEV, SZE_NEV
	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
	join dbo.SZERZO sze on sze.SZEMELY_AZ=sz.SZEMELY_AZ
	where sze.SZINDARAB_AZ=(select SZINDARAB_AZ
							from SZINDARAB
							where SZINDARAB_CIME='Rómeó és Júlia');


select ORSZAGNEV,HELYISEGNEV, SZE_VEZETEKNEV, SZE_NEV
	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 sz.SZEMELY_AZ=(select SZEMELY_AZ
						from SZERZO
						where SZINDARAB_AZ=(select SZINDARAB_AZ
							from SZINDARAB
							where SZINDARAB_CIME='Rómeó és Júlia'));


select ORSZAGNEV,HELYISEGNEV, SZE_VEZETEKNEV, SZE_NEV
	from dbo.ORSZAG o
	join dbo.HELYISEG h on h.ORSZAG_AZ=o.ORSZAG_AZ
	where h.HELYISEG_AZ=(select HELYISEG_AZ
						from SZEMELY
						where SZEMELY_AZ=(select SZEMELY_AZ
						                  from SZERZO
						                  where SZINDARAB_AZ=(select SZINDARAB_AZ
							                                  from SZINDARAB
							                                  where SZINDARAB_CIME='Rómeó és Júlia')));

/*
Msg 207, Level 16, State 1, Line 1
Invalid column name 'SZE_VEZETEKNEV'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'SZE_NEV'.
*/

select ORSZAGNEV
	from dbo.ORSZAG
	where ORSZAG_AZ=(select ORSZAG_AZ
					from HELYISEG
					where HELYISEG_AZ=(select HELYISEG_AZ
						               from SZEMELY
						               where SZEMELY_AZ=(select SZEMELY_AZ
						                                 from SZERZO
						                                 where SZINDARAB_AZ=(select SZINDARAB_AZ
							                                                 from SZINDARAB
							                                                 where SZINDARAB_CIME='Rómeó és Júlia'))));
/*
Nev szerint melyek azok a mufajok, amelyekhez nem tartozik szindarab
*/

--alSELECT

select MUFAJ_AZON, MUFAJ_NEVE
	from dbo.MUFAJ
	where MUFAJ_AZON not in (select MUFAJ_AZON
								from dbo.SZINDARAB)

-- outer join

select m.MUFAJ_AZON, MUFAJ_NEVE   --, SZINDARAB_CIME
	from dbo.MUFAJ m
	left join dbo.SZINDARAB sz on sz.MUFAJ_AZON=m.MUFAJ_AZON
	where SZINDARAB_CIME is null
							                                                 

/*
Helyisegenkent melyek a legnagyobb iranyitoszamok a Helyiseg tablaban?
*/

select 	hk.HELYISEGNEV, hk.H_IRANYITOSZAM
	from dbo.HELYISEG hk						            
	where HELYISEG_AZ=(select MAX(H_IRANYITOSZAM)
						from dbo.HELYISEG hb 
						where hb.HELYISEG_AZ=hk.HELYISEG_AZ)
						
select 	hk.HELYISEGNEV, hk.H_IRANYITOSZAM
	from dbo.HELYISEG hk						            
	where hk.H_IRANYITOSZAM=(select MAX(H_IRANYITOSZAM)
						from dbo.HELYISEG hb 
						where hb.HELYISEG_AZ=hk.HELYISEG_AZ)

select 	hk.HELYISEGNEV, hk.H_IRANYITOSZAM
	from dbo.HELYISEG hk						            
	where hk.H_IRANYITOSZAM=(select MAX(H_IRANYITOSZAM)
						from dbo.HELYISEG hb 
						where hb.HELYISEGNEV=hk.HELYISEGNEV)


--Atlagosan hany szinhaz van helysegenkent

select AVG(Q.szinhazszam)
	from (select SZ_H_CIM_HELYSEG, count(SZINHAZ_AZ) as "szinhazszam"
			from dbo.SZINHAZ
			group by SZ_H_CIM_HELYSEG) Q;
			
-- a belso SELECT eredmenytablaja
select SZ_H_CIM_HELYSEG, count(SZINHAZ_AZ) as "szinhazszam"
			from dbo.SZINHAZ
			group by SZ_H_CIM_HELYSEG


select AVG(szinhazszam)
	from (select SZ_H_CIM_HELYSEG, count(SZINHAZ_AZ) as "szinhazszam"
			from dbo.SZINHAZ
			group by SZ_H_CIM_HELYSEG) s;


select AVG(cast(szinhazszam as decimal))
	from (select SZ_H_CIM_HELYSEG, count(SZINHAZ_AZ) as "szinhazszam"
			from dbo.SZINHAZ
			group by SZ_H_CIM_HELYSEG) s;


--Atlagosan hany szemely tartozkodik helysegenkent

select AVG(a.szemelyek_szama)
	from (select HELYISEG_AZ, COUNT(SZEMELY_AZ) as "szemelyek_szama"
			from dbo.SZEMELY
			group by HELYISEG_AZ) a
			

-- a CAST() fuggveny alakitja at az integert numeric-ka 
 			
select AVG(cast(a.szemelyek_szama as numeric))
	from (select HELYISEG_AZ, COUNT(SZEMELY_AZ) as "szemelyek_szama"
			from dbo.SZEMELY
			group by HELYISEG_AZ) a