SELECT-ek a SzinhPortal adatbazisban 
1. ora - 6. het - 2017.11.01.

Harmadik csoport

*/


use SzinhPortal;
go

-- Listazzuk az orszag tablat 
select *
	from dbo.ORSZAG;
go

-- Listazzuk az orszagazonositot, annak negyzetet 'Azonosito negyzete'
--  fejlecnevvel, az orszagjelzest es az orszagnevet

select ORSZAG_AZ, ORSZAG_AZ*ORSZAG_AZ as "Azonosito negyzete",
		ORSZAG_JELZES, ORSZAGNEV
	from dbo.ORSZAG;
go

-- Listazzuk az orszagazonositot, annak negyzetet 'Azonosito negyzete'
--  fejlecnevvel, az orszagnevet es az orszagjelzest.
-- Szurjunk be az e-tablaba egy uj oszlopot, melynek tartalma "felsegjelzese"
-- az oszlopneve pedig "Szoveg"

select ORSZAG_AZ, ORSZAG_AZ*ORSZAG_AZ as "Azonosito negyzete",
		ORSZAGNEV, 'felsegjelzese' as "szoveg", ORSZAG_JELZES
	from dbo.ORSZAG;
go

-- Listazzuk az orszagazonositot, annak negyzetet 'Azonosito negyzete'
--  fejlecnevvel, az orszagnevet es az orszagjelzest.
-- Szurjunk be az e-tablaba egy uj oszlopot, melynek tartalma 
-- "felsegjelzese"

select ORSZAG_AZ, ORSZAG_AZ*ORSZAG_AZ as "Azonosito negyzete",
		ORSZAGNEV, 'felsegjelzese', ORSZAG_JELZES
	from dbo.ORSZAG;
go

-- Listazzuk a Szinhaz tabla adatait
select *
	from SZINHAZ;
go

select SZ_H_VAJDASAGI
	from SZINHAZ;
go


-- Listazzuk a SZ_H_VAJDASAGI mezo egyedi ertekeit

select distinct SZ_H_VAJDASAGI
	from SZINHAZ;
go

-- Listazzuk a SZ_H_VAJDASAGI mezo egyedi ertekeit

select SZ_H_AMATOR
	from SZINHAZ;
go

-- Listazzuk a SZ_H_AMATOR mezo egyedi ertekeit

select distinct SZ_H_AMATOR
	from SZINHAZ;
go

/*
SELECT-ek a SzinhPortal adatbazisban 
2. ora - 7. het - 2017.11.08.

Harmadik csoport

*/

use SzinhPortal;
go

select * from dbo.SZINHAZ

-- Listazzuk a szinhazak hovatartozasat es a statuszat
select SZ_H_VAJDASAGI, SZ_H_AMATOR
	from dbo.SZINHAZ;
go

select distinct SZ_H_AMATOR
	from dbo.SZINHAZ;
go

select  distinct SZ_H_AMATOR, SZ_H_VAJDASAGI
	from dbo.SZINHAZ;
go

select * from dbo.HELYISEG

select distinct HELYISEGNEV
	from dbo.HELYISEG;
go

select distinct HELYISEGNEV,H_IRANYITOSZAM
	from dbo.HELYISEG
	order by 1;
go

select UPPER(HELYISEGNEV)
	from dbo.HELYISEG;
go

select LOWER(HELYISEGNEV)
	from dbo.HELYISEG;
go

select left(HELYISEGNEV,3)
	from dbo.HELYISEG;
go

select right(HELYISEGNEV,3)
	from dbo.HELYISEG;
go

select SUBSTRING(HELYISEGNEV,1,3)
	from dbo.HELYISEG;
go

select SUBSTRING(HELYISEGNEV,28,3)
	from dbo.HELYISEG;
go

select HELYISEGNEV, LEN(HELYISEGNEV) as "Karakterszam"
	from dbo.HELYISEG;
go

select HELYISEGNEV,SUBSTRING(HELYISEGNEV,LEN(HELYISEGNEV)-2,3)
		as "Karakterszam"
	from dbo.HELYISEG;
	
-- Csoportositasi fuggvenyek:COUNT, SUM, AVG, MIN, MAX

select * from dbo.ELOADAS_PROJEKT

-- Hany nyilvantartott eloadas van a bazisban

select COUNT(SZINDARAB_AZ)
	from dbo.ELOADAS_PROJEKT;
go

select COUNT(*)
	from dbo.ELOADAS_PROJEKT;
go

select COUNT(EL_FELVONAS_SZAMA)
	from dbo.ELOADAS_PROJEKT;
go

select COUNT(distinct EL_FELVONAS_SZAMA)
	from dbo.ELOADAS_PROJEKT;
go

select SUM(EL_HOSSZA_PERC) as "A musoron levo darabok osszhossza"
	from dbo.ELOADAS_PROJEKT;
go

select SUM(EL_FELVONAS_SZAMA)
	from dbo.ELOADAS_PROJEKT;
go

select SUM(distinct EL_FELVONAS_SZAMA)
	from dbo.ELOADAS_PROJEKT;
go

select AVG(EL_FELVONAS_SZAMA)
	from dbo.ELOADAS_PROJEKT;
go

select AVG(distinct EL_FELVONAS_SZAMA)
	from dbo.ELOADAS_PROJEKT;
go

select MIN(EL_FELVONAS_SZAMA)
	from dbo.ELOADAS_PROJEKT;
go

select MIN(distinct EL_FELVONAS_SZAMA)
	from dbo.ELOADAS_PROJEKT;
go

-- Mennyi a minimalis, maximalis felvonasszam, valamint 
-- a nyilvantartott eloadasok osszes felvonasszama,
-- atlagos felvonasszama, es hany eloadas van a nyilvantartasban

select MIN(EL_FELVONAS_SZAMA)as "Legrovidebb eloadas",
		 MAX(EL_FELVONAS_SZAMA) as "Leghosszabb eloadas",
		 SUM(EL_FELVONAS_SZAMA)as "Ossz felvonasszam",
		 AVG(EL_FELVONAS_SZAMA)as "Atlagos felvonasszam",
		 COUNT(EL_FELVONAS_SZAMA)as "Ossz eloadasszam",
		 SUM(EL_FELVONAS_SZAMA)/COUNT(EL_FELVONAS_SZAMA) as
			"Atlagos felvonasszam gyalog"
	from dbo.ELOADAS_PROJEKT;
go

-- WHERE zaradek

select * from dbo.ELOADAS_A_JATEKRENDBEN
-- listazza az eloadasokat cim szerint, amelyek 2014 ben
-- voltak musoron
select ELOADAS_CIME 
	from dbo.ELOADAS_A_JATEKRENDBEN
	where YEAR(ELOADAS_KEZDETE)= 2014;
go

--Hany szindarabot jatszottak a 2015-os evben?
select count(ELOADAS_CIME) 
	from dbo.ELOADAS_A_JATEKRENDBEN
	where YEAR(ELOADAS_KEZDETE)= 2015;
go

--Hany szindarabot jatszottak a 2015-os evben a
-- 2-es azonositoju szinhazban?
select count(ELOADAS_CIME) 
	from dbo.ELOADAS_A_JATEKRENDBEN
	where YEAR(ELOADAS_KEZDETE)= 2015 and
		SZINHAZ_AZ=3;
go

/*
SELECT-ek a SzinhPortal adatbazisban 
3. ora - 8. het - 2017.11.15.

*/

/*
SELECT-ek a SzinhPortal adatbazisban 
4. ora - 9. het - 2017.11.22.

Harmadik csoport
Kollokvium-peldak megoldasa


 I. kollokvium megoldasa -2017-11-10
                                                          */
/*==============================================================*/

/*
1.	Hozza létre a fenti bal oldali adatbázistáblákat a Hallgatóeredmények
 adatbázisban. Az adatbázis nem létezik. Tiltsa le a vizsgaidőszak 
 törlését, ha van nyilvántartott eredménye, a Vizsgaidőszak 
 kulcsérték-változása pedig propagálódjon a gyerektáblában. 
 Hallgató törlése esetén törlődjenek az osztályzatai, az Indexszám
 változtatások pedig csak akkor legyenek lehetségesek, ha nem kötődnek
 hozzájuk érdemjegyek. A JegyBeírDátum alapértelmezése az aktuális dátum,
 a Jegy értéke nem haladhatja meg a 10-et, a VizsgaIdVége értékének
 nagyobbnak kell lennie a VizsgaIdKezdete értékétől. (5 pont)


*/

use master
drop database Hallgatóeredmények;

create database Hallgatóeredmények;
go

use Hallgatóeredmények;
go

/*==============================================================*/
/* Table: HALLGATO                                              */
/*==============================================================*/
create table HALLGATO (
   INDEXSZAM            char(6)              not null,
   VEZETEKNEV           varchar(15)          not null,
   NEV                  varchar(15)          not null,
   SZULDATUM            date                 not null,
   constraint HallgAzon primary key (INDEXSZAM)
)
go

/*==============================================================*/
/* Table: TANULMANYIPROGRAM                                     */
/*==============================================================*/
create table TANULMANYIPROGRAM (
   TANULMPROGAZON       smallint             not null,
   TANULMPROGNEV        varchar(50)          not null,
   TANULMPROGBEVEZEVE   numeric(4,0)         not null,
   TANULMPROGERVVEGE    decimal(4,0)         not null,
   constraint TPAzon primary key (TANULMPROGAZON)
)
go

/*==============================================================*/
/* Table: VIZSGAIDOSZAK                                         */
/*==============================================================*/

-- Hallgatoi megoldas:
create table VIZSGAIDOSZAK (
   ISKOLAEV             char(5)              not null,
   VIZSGAIDOSZAK        smallint             not null,
   VIZSIDKEZDETE        date                 not null,
   VIZSIDVEGE           date                 not null
	check (vizsidvege > vizsidkezdete),
   constraint VizsgaIdAzon primary key (ISKOLAEV, VIZSGAIDOSZAK)
)
go

-- Eredeti megoldas:
create table VIZSGAIDOSZAK (
   ISKOLAEV             char(5)              not null,
   VIZSGAIDOSZAK        smallint             not null,
   VIZSIDKEZDETE        date                 not null,
   VIZSIDVEGE           date                 not null,
   constraint VizsgaIdAzon primary key (ISKOLAEV, VIZSGAIDOSZAK),
   constraint ck_vizsgahataroklogikaja check (vizsidvege > vizsidkezdete)
)
go


/*==============================================================*/
/* Table: TARGY                                                 */
/*==============================================================*/
create table TARGY (
   TANULMPROGAZON       smallint             not null,
   TARGYAZON            int                  not null,
   TARGYNEV             varchar(30)          not null,
   FELEV                smallint             not null,
   HETIELOADASSZAM      smallint             not null,
   HETILABORSZAM        smallint             null,
   constraint TAzon primary key (TANULMPROGAZON, TARGYAZON),
   constraint Tartozik foreign key (TANULMPROGAZON)
      references TANULMANYIPROGRAM (TANULMPROGAZON)
)
go

/*==============================================================*/
/* Table: EREDMENY                                              */
/*==============================================================*/
create table EREDMENY (
   TANULMPROGAZON       smallint             not null,
   TARGYAZON            int                  not null,
   INDEXSZAM            char(6)              not null,
   ISKOLAEV             char(5)              not null,
   VIZSGAIDOSZAK        smallint             not null,
   JEGY                 numeric(2,0)         not null
		constraint ck_jegyfelsokorlat check (jegy < 11),
   JEGYBEIRDATUM        date                 not null
		constraint df_jegybeirdatum default getdate(),
   constraint EredmAzon 
   primary key (TANULMPROGAZON, TARGYAZON, INDEXSZAM, ISKOLAEV, VIZSGAIDOSZAK),
   constraint VizsgaidőszakOsztályzata foreign key (ISKOLAEV, VIZSGAIDOSZAK)
      references VIZSGAIDOSZAK (ISKOLAEV, VIZSGAIDOSZAK)
      on delete no action on update cascade,
   constraint Vizsgázik foreign key (INDEXSZAM)
      references HALLGATO (INDEXSZAM)
      on delete cascade on update no action
)
go

/*
2./Szúrjon be utólagosan egy 1 jel hosszúságú alfanumerikus 
  Neme (magyarázat:Gender) oszlopot a Hallgató táblába, amelynek 
  alapértelmezett értéke ’F’. Az mező (oszlop) a következő értékeket 
  tartalmazhatja: ’F’, ’f’, ’N’ és ’n’. (1 pont).
*/

-- Elso valtozat:
alter table hallgato
	add Neme char(1) default 'F',
	constraint ck_NemeErtekei check (neme in ('F','f','N','n'));
go

alter table hallgato
	drop constraint DF__HALLGATO__Neme__0CBAE877;
go

alter table hallgato
	drop constraint ck_NemeErtekei;
go
alter table hallgato
	drop column neme;
go

-- Masodik valtozat:
alter table hallgato
	add Neme char(1)  null 
	constraint df_neme default 'F',
	constraint ck_NemeErtekei check (neme in ('F','f','N','n'));
go

alter table hallgato
	drop constraint df_neme;
go

alter table hallgato
	drop constraint ck_NemeErtekei;
go

alter table hallgato
	drop column neme;
go


-- Harmadik valtozat:
alter table hallgato
	add Neme char(1)  null 
	constraint df_neme default 'F'
	constraint ck_NemeErtekei check (neme in ('F','f','N','n'));
go

/*
3./Módosítsa a Hallgató SzülDátum mezőjét dátum-idő típusúra, 
	és ne legyen kötelező kitölteni. (1 pont).
*/

alter table hallgato
	alter /*column*/ SZULDATUM datetime null;
go

/*
4./Készítsen utólagos megszorítást a kitöltendő Jegy mezőre, 
	uc_jegyalsókorlát néven: a Jegy értéke minimálisan 5 lehet. 
	(1 pont).
*/

alter table dbo.EREDMENY
	add constraint uc_jegyalsókorlát
	check (jegy>4);
go

-- hallgatoi megoldas (nem mukodik):
alter table dbo.EREDMENY
	drop constraint uc_jegyalsókorlát
	add constraint uc_jegyalsókorlát
	check (jegy>4);
go





/*
5./Módosítsa a kitöltendő Jegy mező felső értékkorlátját 
	10-ről 9-re. (1 pont).
*/

-- Elso valtozat (ha letezik ck_jegyfelsokorlat):
alter table dbo.EREDMENY
	drop constraint ck_jegyfelsokorlat;
go

alter table dbo.EREDMENY
	add constraint ck_jegyfelsokorlat check (jegy < 10);
go

-- Masodik valtozat (ha nem letezik ck_jegyfelsokorlat):

alter table dbo.EREDMENY
	add constraint ck_jegyfelsokorlat check (jegy < 10);
go


/*
6./ Töltse fel a létrehozott (bal oldali) táblákat egy-egy sorral, 
	a megfelelő sorrendben (a hivatkozási épség - referential 
	integrity - megsértése nélkül, TanulmProgAzon=1 és TárgyAzon=1). 
	Ügyeljen arra, hogy a táblák megváltoztak a létrehozásuk óta! 
	(2 pont)
*/	

insert HALLGATO
	values ('E-495','Mekk','Elek','1994-10-01','F');
go

select * from HALLGATO


insert VIZSGAIDOSZAK
	values ('17/18',2,'2017-10-25','2017-11-03');
go

select * from VIZSGAIDOSZAK	

/* sledece dve tabele se ne moraju popunjavati na kolokvijumu-pocetak*/
insert TANULMANYIPROGRAM
	values (1,'Tanulmanyiprogram-1',2014,2021);
go

insert TARGY
	values (1,1,'Adatbazisok',3,2,2);
go

/* sledece dve tabele se ne moraju popunjavati na kolokvijumu-pocetak*/

insert EREDMENY
	values (1,1,'E-495','17/18',2,9,default);
go

insert EREDMENY (TANULMPROGAZON,TARGYAZON,INDEXSZAM,ISKOLAEV,
			VIZSGAIDOSZAK,JEGY)
	values (1,1,'E-495','17/18',2,9);
go

select * from EREDMENY
delete EREDMENY


/*
7./ Írjon be egy új sort az Eredmény táblába 10-es osztályzattal, 
	a korlátok törlése és módosítása nélkül! Mi az új sor beírásának feltétele
	az eredménytáblába? (1 pont)
*/

/* 
	Az új sor beírása az Eredmény táblába megköveteli 
	a) egy új sor beírását a Hallgató táblába
	VAGY
	b) egy új sor beírását a Vizsgaidőszak táblába
	VAGY
	c) egy új sor beírását a Tárgy táblába
*/

insert HALLGATO
values ('E-494','Jo','Aron','1994-05-25','F');
go
alter table dbo.EREDMENY
	nocheck constraint ck_jegyfelsokorlat;
insert EREDMENY
	values (1,1,'E-494','17/18',2,10,default);
go	
alter table dbo.EREDMENY
	check constraint ck_jegyfelsokorlat;
go

select * from EREDMENY	


/*
8./ Törölje az Eredmény táblából a 10-es osztályzatot 
	tartalmazó sorokat! (1 pont).
*/

delete EREDMENY
	where JEGY=10;
go

/*
9./ Módosítsa az Eredmény tábla 9-es osztályzatot tartalmazó soraiban 
	a dátumot az aktuális dátumra, az osztályzatot pedig 10-re! 
	(1 pont)
*/

alter table dbo.EREDMENY
	nocheck constraint ck_jegyfelsokorlat;
go

update EREDMENY
	set JEGYBEIRDATUM=GETDATE(),
		JEGY=10
	where JEGY=9;
go

alter table dbo.EREDMENY
	check constraint ck_jegyfelsokorlat;
	
/*
10./ Hozzon létre nézettáblát Hallgatónézet néven, amely a 
	következő adatokat tartalmazza (az adott sorrendben): 
	Vezetéknév, Név, Indexszám, SzülDátum. (1 pont).
*/

create view Hallgatonezet (Vezetéknév,Név,Indexszám,Szüldátum) as
	select VEZETEKNEV,NEV,INDEXSZAM,SZULDATUM 
		from  dbo.HALLGATO;
go
select * from Hallgatonezet

drop view Hallgatonezet

create view Hallgatonezet (Hallgató_vezetékneve,Hallgató_Neve,
				Leckekönyv_száma,Születési_dátum) as
	select VEZETEKNEV,NEV,INDEXSZAM,SZULDATUM 
		from  dbo.HALLGATO;
go

create view Hallgatonezet ("Hallgató vezetékneve","Hallgató neve",
				"Leckekönyv száma","Születési dátum") as
	select VEZETEKNEV,NEV,INDEXSZAM,SZULDATUM 
		from  dbo.HALLGATO;
go








/*
5. ora - 10. het - 2017.11.29.


*/

-- Group by záradék

/*
Hány helység van a nyilvántartásunkban?
*/

select COUNT(HELYISEG_AZ)
	from dbo.HELYISEG;
go 

/*
Hány helység van a nyilvántartásunkban orszagazonositonkent?
*/

select ORSZAG_AZ, COUNT(HELYISEG_AZ) as "A helysegek szama"
	from dbo.HELYISEG
	group by ORSZAG_AZ;
go 

select ORSZAGNEV,h.ORSZAG_AZ, COUNT(HELYISEG_AZ) as "A helysegek szama"
	from dbo.HELYISEG h
	join dbo.ORSZAG on orszag.ORSZAG_AZ=h.ORSZAG_AZ
	group by ORSZAGNEV,h.ORSZAG_AZ;
go 


/*
Orszagazonositokent melyik a legnagyobb iranyitoszam?
*/

select ORSZAG_AZ, MAX(H_IRANYITOSZAM)
	from dbo.HELYISEG
	group by ORSZAG_AZ;
go

/*
Orszagazonositokent, azon belul helyisegnevenkent
hany iranyitoszam van?
*/

select ORSZAG_AZ, HELYISEGNEV,count(H_IRANYITOSZAM)
	from dbo.HELYISEG
	group by ORSZAG_AZ, HELYISEGNEV;
go

/*
Orszagazonositokent, azon belul helyisegnevenkent
hany iranyitoszam van es melyek a legnagyobb iranyitoszamok?
*/

select ORSZAG_AZ, HELYISEGNEV,count(H_IRANYITOSZAM), MAX(H_IRANYITOSZAM)
	from dbo.HELYISEG
	group by ORSZAG_AZ, HELYISEGNEV;
go

-- HAVING zaradek


/*
Helyisegazonositokent, hany szemely van a nyilvantartasban?
csak a 2-nel nagyobb szamu csoportokat jelenistuk meg
*/

select HELYISEG_AZ, COUNT(SZEMELY_AZ)
	from dbo.SZEMELY
	group by HELYISEG_AZ
	having COUNT(SZEMELY_AZ)>2;
go


--Nem mukodik, hibas megoldas
select HELYISEG_AZ, COUNT(SZEMELY_AZ)
	from dbo.SZEMELY
	where COUNT(SZEMELY_AZ)>2
	group by HELYISEG_AZ;
go

/*
Hany P (s) betuvel kezdodo vezeteknevu szemelyunk van?
*/

select COUNT(SZEMELY_AZ) as "A P betuvel kezdodo vezeteknevek szama"
	from dbo.SZEMELY
	where LEFT(SZE_VEZETEKNEV,1)='s';
go


/*
Szamlaljuk meg a szemelyek szamat helyiseg-azonositonkent.
csak a haromnal nagyobb helysegazonositokra jelenjenek meg 
a szemelyek szamai
*/

select HELYISEG_AZ, COUNT(SZEMELY_AZ)
	from dbo.SZEMELY
	group by HELYISEG_AZ
	having HELYISEG_AZ>3;
go

select HELYISEG_AZ, COUNT(SZEMELY_AZ)
	from dbo.SZEMELY
	where HELYISEG_AZ>3
	group by HELYISEG_AZ;
go





/*
Szamlaljuk meg a szemelyek szamat helyiseg-azonositonkent.
csak a haromnal nagyobb helysegazonositokra jelenjenek meg 
a szemelyek szamai, amennyiben a szamok nagyobbak 5-nel
*/

select HELYISEG_AZ, COUNT(SZEMELY_AZ)
	from dbo.SZEMELY
	group by HELYISEG_AZ
	having HELYISEG_AZ>3 and COUNT(SZEMELY_AZ)>5;
go

-- ORDER BY zaradek

/*
listazzuk a szemelyek vezetekneveit es neveit.
rendezzuk a listat a vezeteknevek szerint ABC sorrendbe
*/

select distinct SZE_VEZETEKNEV, SZE_NEV
	from dbo.SZEMELY
	order by SZE_VEZETEKNEV;
go

select distinct SZE_VEZETEKNEV, SZE_NEV
	from dbo.SZEMELY
	order by SZE_NEV;
go

select distinct SZE_VEZETEKNEV, SZE_NEV
	from dbo.SZEMELY
	order by SZE_NEV, SZE_VEZETEKNEV desc;
go


/*
6. ora - 11. het - 2017.12.06.


*/

/*
1. Hány színdarab van a nyilvántartásban?
*/
select COUNT(SZINDARAB_AZ)
	from dbo.SZINDARAB;
go
/*
2. Melyik a legkisebb irányítószámmal rendelkező színház a 
	nyilvántartásban?
*/
select SZINHAZNEV, SZ_H_CIM_IRANYITOSZAM
	from dbo.SZINHAZ
	where SZ_H_CIM_IRANYITOSZAM = (select MIN(SZ_H_CIM_IRANYITOSZAM)
									from dbo.SZINHAZ);
go
/*
2a. Melyik a legkisebb irányítószám a színház táblában?
*/
select MIN(SZ_H_CIM_IRANYITOSZAM)
	from dbo.SZINHAZ;
go
/*
3. Hány betűből áll a legrövidebb nevű színház?
*/
select MIN(len(SZINHAZNEV))
	from dbo.SZINHAZ;
go
/*
4. Helységnevenként hány irányítószám van jelen az adatbázisban?
*/
select HELYISEGNEV, COUNT(HELYISEG_AZ)
	from dbo.HELYISEG
	group by HELYISEGNEV;
go
/*
5. Helységnevenként hány irányítószám van jelen az adatbázisban? 
	Csak azok a sorok maradjanak az eredménytáblában, amelyekben ez 
	a szám nagyobb 1-nél!
*/
select HELYISEGNEV, COUNT(HELYISEG_AZ)
	from dbo.HELYISEG
	group by HELYISEGNEV
	having COUNT(HELYISEG_AZ)>1 ;
go
/*
6. Helységnevenként hány irányítószám van jelen az adatbázisban? 
	Csak azok a sorok maradjanak az eredménytáblában, amelyekben a 
	helységnév kezdőbetűje B vagy Z!
*/
select HELYISEGNEV, COUNT(HELYISEG_AZ)
	from dbo.HELYISEG
	group by HELYISEGNEV
	having left(HELYISEGNEV,1)='B' or left(HELYISEGNEV,1)='Z' ;
go

select HELYISEGNEV, COUNT(HELYISEG_AZ)
	from dbo.HELYISEG
	where left(HELYISEGNEV,1)='B' or left(HELYISEGNEV,1)='Z'
	group by HELYISEGNEV;
go
/*
7. Kik azok az aktív személyek, akiknek az életrajza "???" tartalommal
	bír?
*/
select SZE_VEZETEKNEV, SZE_NEV
	from dbo.SZEMELY
	where SZE_AKTIV='true' and 	SZE_ELETRAJZ='???';
go
/*	
8. Kik azok a személyek akik a 2-es vagy 3as helyiségazonosítóval
	rendelkező helyiségben laknak és S a vezetéknevük első betűje?
*/
select SZE_VEZETEKNEV, SZE_NEV, HELYISEG_AZ
	from dbo.SZEMELY
	where (HELYISEG_AZ=2 or HELYISEG_AZ=3) and 	
		left(SZE_VEZETEKNEV,1)='S';
go
/*
9. Kik azok a személyek akik a 2-es vagy 3as helyiségazonosítóval
	rendelkező helyiségben laknak és S a vezetéknevük első betűje, illetve
	(vagy) akik életrajza "???" tartalommal bír és vezetéknevük P vagy
	V?
*/
select SZE_VEZETEKNEV, SZE_NEV, HELYISEG_AZ, SZE_ELETRAJZ
	from dbo.SZEMELY
	where ((HELYISEG_AZ=2 or HELYISEG_AZ=3) and 	
		left(SZE_VEZETEKNEV,1)='S') or 
		(SZE_ELETRAJZ='???' and (LEFT(SZE_VEZETEKNEV,1)='P' or
		LEFT(SZE_VEZETEKNEV,1)='V')) ;
go
/*
10. Listázzuk a vajdasági, aktív személyeket fordított ABC sorrendben
	a vezetéknevük szerint és ABC sorrendben a keresztnevük szerint!
*/
select SZE_VEZETEKNEV,SZE_NEV
	from dbo.SZEMELY
	where SZE_VAJDASAGI='true' and SZE_AKTIV=1
	order by SZE_VEZETEKNEV desc, SZE_NEV;
go
/*
11. Listázzuk azokat az aktív, Vajdaságon kívüli személyeket, akiknek
	a vezetékneve rövidebb 8 betűnél, a nevük pedig nem haladja meg a
	6 betűt vagy akiknek nincs nyilvántartott életrajza!
*/
select SZE_VEZETEKNEV,SZE_NEV, SZE_AKTIV, SZE_VAJDASAGI, SZE_ELETRAJZ
	from dbo.SZEMELY
	where (SZE_VAJDASAGI='false' and SZE_AKTIV=1 and
		LEN(SZE_VEZETEKNEV)<8 and LEN(SZE_NEV)<=6) or
		SZE_ELETRAJZ is null;
go


/*
7. ora - 12. het - 2017.12.13.


*/
use SzinhPortal
/*
1. Mutassuk ki a pillanatnyi datumot ;s idot
*/
select GETDATE()as "TIMESTAMP"

/*
2. Mutassuk ki az aktualis evet, honapot es napot (datepart i 
posebnim funkcijama-year, month i day)
*/
select YEAR(getdate()) as "Aktualis ev",
		month(getdate()) as "Aktualis honap",
		day(getdate()) as "Aktualis nap"
		
select DATEPART(yy,getdate()) as "Aktualis ev",
		DATEPART(mm,getdate()) as "Aktualis honap",
		DATEPART(dd,getdate()) as "Aktualis nap"


/*
2.1. Mely bemutatok voltak az 3 evvel ezelotti evben?
*/
select ELOADAS_CIME, EL_BEMUTATO_DATUM
	from dbo.ELOADAS_PROJEKT
	where YEAR(EL_BEMUTATO_DATUM)=DATEPART(yy,getdate())-3;
go




-- TOBBTABLAS LEKERDEZES

/*
3. Hany szinhazunk van helyisegenkent? - jednotablicni i 
dvotablicni upit (dekartov proizvod i join)
*/
select SZ_H_CIM_HELYSEG, COUNT(SZINHAZ_AZ) as "Szinhazak szama"
	from dbo.SZINHAZ
	group by SZ_H_CIM_HELYSEG;
go


/*
3.1. Hany szinhaz van HELYISEGNEV-ertekek szerint? 
*/
-- Descartes szorzat
select HELYISEGNEV, COUNT(SZINHAZ_AZ)
	from dbo.HELYISEG, dbo.SZINHAZ
	where helyiseg.HELYISEG_AZ=szinhaz.HELYISEG_AZ
	group by HELYISEGNEV;
go

-- JOIN
select HELYISEGNEV, COUNT(SZINHAZ_AZ)
	from dbo.HELYISEG
	join dbo.SZINHAZ on helyiseg.HELYISEG_AZ=szinhaz.HELYISEG_AZ 
	group by HELYISEGNEV;
go

/*
4. Budapesten hany szinhazat tartunk nyilvan? - jednotablicni i 
dvotablicni upit (dekartov proizvod i join)
*/
select SZ_H_CIM_HELYSEG, COUNT(SZINHAZ_AZ) as "Szinhazak szama"
	from dbo.SZINHAZ
	where SZ_H_CIM_HELYSEG='Budapest'
	group by SZ_H_CIM_HELYSEG;
go 

select SZ_H_CIM_HELYSEG, COUNT(SZINHAZ_AZ) as "Szinhazak szama"
	from dbo.SZINHAZ
	group by SZ_H_CIM_HELYSEG
	having SZ_H_CIM_HELYSEG='Budapest';
go 

select HELYISEGNEV, COUNT(SZINHAZ_AZ)
	from dbo.HELYISEG
	join dbo.SZINHAZ on helyiseg.HELYISEG_AZ=szinhaz.HELYISEG_AZ 
	where HELYISEGNEV='Budapest'
	group by HELYISEGNEV;
go

/*
5. Listazza az Ujvidek korzet  (iranyitoszam>=21000 es <22000)
szemelyeit akiknek a keresztneve  'A' betuvel kezdodik, akik aktivak,
es profik
*/
select SZE_VEZETEKNEV,SZE_NEV
	from dbo.SZEMELY
	join dbo.HELYISEG on helyiseg.HELYISEG_AZ=SZEMELY.HELYISEG_AZ
	where H_IRANYITOSZAM between 21000 and 21999 and
		substring(SZE_NEV,1,1)='A' and SZE_AKTIV=1 and
		SZE_AMATOR='false';
go

/*
6. Kik azok a budapesti es Ujvideki szemelyek akik aktivak, 
profik es az eletrajzuk hosszabb 5 betunel
*/
select SZE_VEZETEKNEV,SZE_NEV
	from dbo.SZEMELY 
	join dbo.HELYISEG on helyiseg.HELYISEG_AZ=szemely.HELYISEG_AZ
	where (HELYISEGNEV='Budapest' or HELYISEGNEV='Újvidek') and
		SZE_AKTIV='true' and LEN(SZE_ELETRAJZ)>5;
go

-- Beepitett SELECT mint megoldas-valtozat

/*
7. Listazzuk a szerbiai szemelyeket
*/
select SZE_VEZETEKNEV,SZE_NEV
	from dbo.SZEMELY 
	join dbo.HELYISEG on helyiseg.HELYISEG_AZ=szemely.HELYISEG_AZ
	join dbo.ORSZAG on orszag.ORSZAG_AZ=helyiseg.ORSZAG_AZ
	where ORSZAGNEV='Szerbia';
go

select SZE_VEZETEKNEV,SZE_NEV
	from dbo.SZEMELY
	where HELYISEG_AZ in (select HELYISEG_AZ
							from  dbo.HELYISEG
							where ORSZAG_AZ=(select ORSZAG_AZ
												from dbo.ORSZAG
												where ORSZAGNEV='Szerbia'));
go



/*
8. ora - 13. het - 2017.12.20.


*/
use SzinhPortal;
go

/*
7.1. Listazzuk a szerbiai szemelyeket es varosuk nevet
*/
select SZE_VEZETEKNEV,SZE_NEV,HELYISEGNEV
	from dbo.SZEMELY sz,dbo.HELYISEG h,dbo.ORSZAG o
	where sz.HELYISEG_AZ=h.HELYISEG_AZ and
		h.ORSZAG_AZ=o.ORSZAG_AZ and ORSZAGNEV='Szerbia';
go

select SZE_VEZETEKNEV,SZE_NEV,HELYISEGNEV
	from dbo.SZEMELY sz,dbo.HELYISEG h
	where sz.HELYISEG_AZ=h.HELYISEG_AZ and
		ORSZAG_AZ=(select ORSZAG_AZ
						from dbo.ORSZAG
						where ORSZAGNEV='Szerbia');
go


/*
7.2. Listazzuk a szerbiai szemelyeket, varosuk nevet es
	orszagjelzesuket
*/

select SZE_VEZETEKNEV,SZE_NEV,HELYISEGNEV,ORSZAG_JELZES
	from dbo.SZEMELY
	join dbo.HELYISEG on HELYISEG.HELYISEG_AZ=SZEMELY.HELYISEG_AZ
	join dbo.ORSZAG on ORSZAG.ORSZAG_AZ=HELYISEG.ORSZAG_AZ
	where ORSZAGNEV='Szerbia';
go

/*
9. Melyek a legnagyobb iranyitoszammal rendelkezo helysegek
	(ismetlodes nelkul)? (helyiseg, szinhaz)
*/
select HELYISEGNEV
	from dbo.HELYISEG
	where H_IRANYITOSZAM=(select MAX(H_IRANYITOSZAM)
							from dbo.HELYISEG);
go

select SZ_H_CIM_HELYSEG
	from dbo.SZINHAZ
	where SZ_H_CIM_IRANYITOSZAM=(select MAX(SZ_H_CIM_IRANYITOSZAM)
							from dbo.SZINHAZ);
go


/*
10. Melyek azok a helyisegnevek (ismetlodes nelkul), amelyeknek postai 
	iranyitoszama kisebb, mint az atlagos postai iranyitoszam az 
	adatbazisban?  (helyiseg, szinhaz)
*/	
select distinct HELYISEGNEV 
	from dbo.HELYISEG
	where H_IRANYITOSZAM<(select AVG(H_IRANYITOSZAM)
							from dbo.HELYISEG);
go


/*
10. Melyek azok a helyisegek, amelyeknek postai iranyitoszama nagyobb,
	mint az atlagos postai iranyitoszam az adatbazisban? 
	(helyiseg, szinhaz)
*/

/*
11. Kik azok a szemelyek, akik nem szerzok (alszelect join)?
*/
select SZE_VEZETEKNEV,SZE_NEV
	from dbo.SZEMELY
	where SZEMELY_AZ not in (select SZEMELY_AZ
								from dbo.SZERZO);
go

select SZE_VEZETEKNEV,SZE_NEV
	from dbo.SZEMELY
	left outer join dbo.SZERZO on SZERZO.SZEMELY_AZ=SZEMELY.SZEMELY_AZ
	where SZINDARAB_AZ is null;
	go
	
select SZE_VEZETEKNEV,SZE_NEV
	from dbo.SZERZO
	right join dbo.SZEMELY on SZERZO.SZEMELY_AZ=SZEMELY.SZEMELY_AZ
	where SZINDARAB_AZ is null;
go

/*
12. Kik azok a szemelyek, akik nem forditok (alselect join)?
*/
select SZE_VEZETEKNEV,SZE_NEV
	from dbo.SZEMELY
	where SZEMELY_AZ not in (select SZEMELY_AZ_FORD
								from dbo.SZINDARAB);
go

/*
12. Kik azok a szemelyek, akik forditok (alselect join)?
*/
select SZE_VEZETEKNEV,SZE_NEV
	from dbo.SZEMELY
	where SZEMELY_AZ in (select SZEMELY_AZ_FORD
								from dbo.SZINDARAB);
go


/*
12a. Hany postai iranyitoszam van a nyilvantartasban helyisegnevek
	szerint? Azokat a sorokat tartsuk csak meg az eredmenytablaban,
	amelyekben (1)tobb ((2)kevesebb, (3)egyenlo szamu)szemelyt tartunk nyilvan, 
	mint postai iranyitoszamot.
*/
-- Modositott, pontos megoldas:
-- (1):
select HELYISEGNEV,COUNT(H_IRANYITOSZAM) as "Iranyitoszamok a helysegben"
	from dbo.HELYISEG hk
	group by HELYISEGNEV
	having COUNT(H_IRANYITOSZAM)<(select COUNT(SZEMELY_AZ)
							from dbo.SZEMELY sz
							right join dbo.HELYISEG h on h.HELYISEG_AZ=sz.HELYISEG_AZ
							where hk.HELYISEGNEV=h.HELYISEGNEV
							group by HELYISEGNEV);
go

-- (2):
select HELYISEGNEV,COUNT(H_IRANYITOSZAM) as "Iranyitoszamok a helysegben"
	from dbo.HELYISEG hk
	group by HELYISEGNEV
	having COUNT(H_IRANYITOSZAM)>(select COUNT(SZEMELY_AZ)
							from dbo.SZEMELY sz
							right join dbo.HELYISEG h on h.HELYISEG_AZ=sz.HELYISEG_AZ
							where hk.HELYISEGNEV=h.HELYISEGNEV
							group by HELYISEGNEV);
go

-- (3):
select HELYISEGNEV,COUNT(H_IRANYITOSZAM) as "Iranyitoszamok a helysegben"
	from dbo.HELYISEG hk
	group by HELYISEGNEV
	having COUNT(H_IRANYITOSZAM)=(select COUNT(SZEMELY_AZ)
							from dbo.SZEMELY sz
							right join dbo.HELYISEG h on h.HELYISEG_AZ=sz.HELYISEG_AZ
							where hk.HELYISEGNEV=h.HELYISEGNEV
							group by HELYISEGNEV);
go


select HELYISEGNEV,COUNT(H_IRANYITOSZAM) as "Iranyitoszamok a helysegben"
	from dbo.HELYISEG hk
	group by HELYISEGNEV;
go


select HELYISEGNEV, COUNT(SZEMELY_AZ) as "Szemelyek szama a helysegben"
	from dbo.SZEMELY sz
	right join dbo.HELYISEG h on h.HELYISEG_AZ=sz.HELYISEG_AZ
	group by HELYISEGNEV;
go



/*12b. - Kovács Árpád megoldása - Hany postai iranyitoszam van a nyilvantartasban helyisegnevek szerint?Azokat a
sorokat tartsuk meg az eredmenytablaban amelyekben (1)tobb ((2)kevesebb,(3)egyenlo szamu)szemelyt
tartunk nyilvan,mint postai iranyitoszamot?*/

--		- Kovács Árpád megoldása -
-- (1):
SELECT HELYISEGNEV, COUNT(DISTINCT HELYISEG.H_IRANYITOSZAM) 
		as 'Iranyitoszám', COUNT(SZEMELY.SZE_NEV)  as 'Személyek'  
	FROM HELYISEG 
	left join SZEMELY on HELYISEG.HELYISEG_AZ = SZEMELY.HELYISEG_AZ
	GROUP BY HELYISEG.HELYISEGNEV 
	HAVING COUNT(SZE_NEV)>COUNT(DISTINCT HELYISEG.H_IRANYITOSZAM);
go

-- (2):
SELECT HELYISEGNEV, COUNT(DISTINCT HELYISEG.H_IRANYITOSZAM) 
		as 'Iranyitoszám', COUNT(SZEMELY.SZE_NEV)  as 'Személyek'  
	FROM HELYISEG 
	left join SZEMELY on HELYISEG.HELYISEG_AZ = SZEMELY.HELYISEG_AZ
	GROUP BY HELYISEG.HELYISEGNEV 
	HAVING COUNT(SZE_NEV)<COUNT(DISTINCT HELYISEG.H_IRANYITOSZAM);
go

-- (3):
SELECT HELYISEGNEV, COUNT(DISTINCT HELYISEG.H_IRANYITOSZAM) 
		as 'Iranyitoszám', COUNT(SZEMELY.SZE_NEV)  as 'Személyek'  
	FROM HELYISEG 
	left join SZEMELY on HELYISEG.HELYISEG_AZ = SZEMELY.HELYISEG_AZ
	GROUP BY HELYISEG.HELYISEGNEV 
	HAVING COUNT(SZE_NEV)=COUNT(DISTINCT HELYISEG.H_IRANYITOSZAM);
go



/* 
13. Mennyi a szinhazak atlagszama a varosokban?
 - ugradjenim SELECTOM u FROM
*/
-- 1.megoldas
select AVG(szinhszamapervaros)
	from (select SZ_H_CIM_HELYSEG, convert(numeric(6,2),COUNT(SZINHAZ_AZ)) as szinhszamapervaros
			from dbo.SZINHAZ
			group by SZ_H_CIM_HELYSEG) as A;
go

select SZ_H_CIM_HELYSEG, convert(numeric(6,2),COUNT(SZINHAZ_AZ)) as szinhszamapervaros
			from dbo.SZINHAZ
			group by SZ_H_CIM_HELYSEG;
go

-- 2.megoldas

create view segedview ("helyseg", "helyszinhazainakszama") as
	select SZ_H_CIM_HELYSEG, convert(numeric(6,2),COUNT(SZINHAZ_AZ)) as szinhszamapervaros
			from dbo.SZINHAZ
			group by SZ_H_CIM_HELYSEG;
go

select AVG(helyszinhazainakszama)
	from segedview;
	
drop view segedview;
	
-- 3.megoldas
select SZ_H_CIM_HELYSEG, convert(numeric(6,2),COUNT(SZINHAZ_AZ)) 
			as szinhszamapervaros
	into seged
	from dbo.SZINHAZ
	group by SZ_H_CIM_HELYSEG;
go

select AVG(szinhszamapervaros) 
	from seged;
	
drop table seged;

/*
15. Hany kulonbozo nevu, szinhazzal rendelkezo helysegunk van? 
	(1-iz tabele pozorište, 2-iz tabele mesto i tabele 
	pozorište)
*/ 

select COUNT(distinct SZ_H_CIM_HELYSEG)
	from dbo.SZINHAZ;
go

select distinct HELYISEGNEV, H_IRANYITOSZAM
	from dbo.HELYISEG;
go

/*
21. Listazni a helyisegazonositot, a helyisegnevet es a szinhaznevet a
	szinhaz tablabol azokra a szinhazakra, amelyek nevhossza nagyobb 
	mint 5. Rendezze az eredmenytablat a helyisegazonosito szerint 
	csokkeno sorrendbe, majd a masik ket mezo szerint ABC sorrendbe, az elso 
	mezonel csokkeno a masik mezonel novekvo sorrendbe.
*/

select HELYISEG_AZ, SZ_H_CIM_HELYSEG, SZINHAZNEV
	from dbo.SZINHAZ
	where LEN(SZINHAZNEV)>5
	order by HELYISEG_AZ, SZ_H_CIM_HELYSEG desc, SZINHAZNEV;
go