/*
Első találkozás az MS SQLSERVER 2008 R2-vel
2020.10.22.

1. találkozás -3. hét

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

-- Adatbazis, tabla elsodleges es kulso kulcs letrehozasa

1.	Létrehozni a következő adatbázist
Az adatbázis neve: Arurendeles
LDM: a Zadatak-Nedelja3--Feladat-3het.docx/pdf adattarban

-- A meglevo regi adatbazisok torlesa az MSSQL Server nyilvantartasabol

Utasitas az adatbazisok torlesere: 
drop database adatbazisneve;
go

*/




create database Árurendelés;
go

use Árurendelés;
go

create table Vevo (
Vevo_kod int constraint Vevo_azon primary key,
Vevo_nev varchar(30),
Ir_szam numeric(5),
Helyseg varchar(20),
Utca_hszs varchar(20));
go

drop table Vevo

create table Vevo (
Vevo_kod int,
Vevo_nev varchar(30),
Ir_szam numeric(5),
Helyseg varchar(20),
Utca_hszs varchar(20),
constraint Vevo_azon primary key (Vevo_kod));
go



/*
Második találkozás az MS SQLSERVER 2008 R2-vel
2020.10.29.

2. találkozás -4. hét

Csütörtök - 15:45-17:20

-- Tabla-letrehozas kulonbozo megszoritasokkal

*/

use Árurendelés;
go

create table Aru_arlista (
Aru_kod int,
Mertekegyseg char(10),
constraint Aru_azon primary key (Aru_kod,Mertekegyseg),
Aru_megnevezes varchar(20),
Egysegar numeric(15,2));
go

create table Rendeles (
Rendeles_sz int,
Vevo_kod int constraint Rendel foreign key references Vevo(Vevo_kod),
Kelt date,
constraint Rendeles_azon primary key (Rendeles_sz));
go

create table Rendeles_tetel (
Aru_kod int,
Mertekegyseg char(10),
Rendeles_sz int,
Rendelt_menny numeric(12,2),
constraint Identifier_1 primary key (Aru_kod,Mertekegyseg,Rendeles_sz),
constraint Rendeleshez_tartozik foreign key (Rendeles_sz) 
	references Rendeles(Rendeles_sz),
constraint Aru_szerepel_a_tetelben foreign key (Aru_kod,Mertekegyseg) 
	references Aru_arlista (Aru_kod,Mertekegyseg));
go


--
--
--
/*
A második feladat megoldása: az adatbázis neve Arurendeles2
minden más (táblanév, oszlopnév, korlátok) ugyanaz(ok) marad(nak),
kivéve természetesen az új korlátokat
*/
--
--
--


create database Arurendeles2;
go

use Arurendeles2

create table Vevo (
Vevo_kod int constraint Vevo_azon primary key,
Vevo_nev varchar(30) not null,
Ir_szam numeric(5) not null check (Ir_szam >= 20000 and Ir_szam <= 20000) default 24000,
Helyseg varchar(20),
Utca_hszs varchar(20));
go

drop table Vevo

create table Vevo (
Vevo_kod int constraint Vevo_azon primary key,
Vevo_nev varchar(30) not null,
Ir_szam numeric(5) not null 
	constraint ck_Ir_szam check (Ir_szam >= 20000 and Ir_szam <= 20000) 
	constraint df_Ir_szam default 24000,
Helyseg varchar(20),
Utca_hszs varchar(20));
go

create table Aru_arlista (
Aru_kod int,
Mertekegyseg char(10),
constraint Aru_azon primary key (Aru_kod,Mertekegyseg),
Aru_megnevezes varchar(20) not null,
Egysegar numeric(15,2) not null constraint ck_Egysegar check(Egysegar>0));
go

create table Rendeles (
Rendeles_sz int,
Vevo_kod int constraint Rendel foreign key references Vevo(Vevo_kod)
	on delete cascade on update cascade,
Kelt date not null constraint df_Kelt_akt_datum default (getdate()),
constraint Rendeles_azon primary key (Rendeles_sz));
go

create table Rendeles_tetel (
Aru_kod int,
Mertekegyseg char(10),
Rendeles_sz int,
Rendelt_menny numeric(12,2) not null constraint ck_Rendelt_menny check(Rendelt_menny>0),
constraint Identifier_1 primary key (Aru_kod,Mertekegyseg,Rendeles_sz),
constraint Rendeleshez_tartozik foreign key (Rendeles_sz) 
	references Rendeles(Rendeles_sz),
constraint Aru_szerepel_a_tetelben foreign key (Aru_kod,Mertekegyseg) 
	references Aru_arlista (Aru_kod,Mertekegyseg)
	on delete no action on update cascade);
go

--
--
--
/*
Harmadik találkozás az MS SQLSERVER 2008 R2-vel
2020.11.05.

3. találkozás -5. hét

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

-- Adatbazis tabla strukturajanak modositasa 
		(mezok es korlatok/megszoritasok)
-- Adatbevitel, adatmodositas es adattorles a tablakban

*/
--
--
--

use Arurendeles2
go

--tabla-szerkezet modositasok

alter table dbo.Vevo
	drop constraint ck_Ir_szam;
	
alter table dbo.Vevo
	add constraint ck_Ir_szam check(Ir_szam>=20000 and Ir_szam<=29999)
	
alter table dbo.Vevo
	alter column Vevo_nev char(25)
	
alter table dbo.Vevo
	add constraint Egyedivevonev unique (Vevo_nev)

alter table dbo.Vevo
	alter column Vevo_nev varchar(50)
	
alter table dbo.Vevo
	drop constraint Egyedivevonev
	
alter table dbo.Vevo
	alter column Vevo_nev varchar(50)

alter table dbo.Vevo
	add constraint df_Vevo_nev
	default 'Sajat Vevo' for Vevo_nev

alter table dbo.Vevo
	add Vevo_PIB int

	
-- adatbevitel / adatbeszuras

insert Vevo
	values (1,'Elso Vevo',24000,'Szabadka','Pipiter utca 2',100835489)

select * from dbo.Vevo

insert Vevo (Vevo_kod,Helyseg,Utca_hszs)
	values (1,'Zabadka','Harap utca 2')
/*
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'Vevo_azon'. Cannot insert duplicate key in object 'dbo.Vevo'. The duplicate key value is (1).
The statement has been terminated.
*/

insert Vevo (Vevo_kod,Helyseg,Utca_hszs)
	values (2,'Zabadka','Harap utca 2')

select * from dbo.Vevo

insert Vevo (Vevo_kod,Vevo_PIB,Vevo_nev)
	values (3,135835489,'Uj vevo')
	
select * from dbo.Vevo

insert Vevo (Vevo_kod,Vevo_PIB,Vevo_nev,Ir_szam)
	values ('4',132385489,'Uj vevo',110000)

/*
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting int to data type numeric.
The statement has been terminated.
*/

insert Vevo (Vevo_kod,Vevo_PIB,Vevo_nev,Ir_szam)
	values (4,132385489,'Uj vevo',110000)

/*
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting int to data type numeric.
The statement has been terminated.
*/

insert Vevo (Vevo_kod,Vevo_PIB,Vevo_nev,Ir_szam)
	values (4,100000489,'Uj vevo',110000)

/*Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting int to data type numeric.
The statement has been terminated.
*/

insert Vevo (Vevo_kod,Vevo_PIB,Vevo_nev,Ir_szam)
	values (4,100000489,'Uj vevo',11000)
	
/*
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "ck_Ir_szam". The conflict occurred in database "Arurendeles2", table "dbo.Vevo", column 'Ir_szam'.
The statement has been terminated.
*/

insert Vevo (Vevo_kod,Vevo_PIB,Vevo_nev,Ir_szam)
	values (4,100000489,'Uj vevo',21000)
	

--
--
--
/*
Negyedik találkozás az MS SQLSERVER 2008 R2-vel
2020.11.12.

4. találkozás -6. hét

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

-- Adatmodositas es adattorles a tablakban
-- Nézettáblák

*/
--
--
--

use Árurendelés;

select * from Vevo

use Arurendeles2

select * from Vevo

update dbo.Vevo
	set 
	Vevo_nev='Javitott Uj vevo'
	where vevo_PIB is null;
	
select * from Vevo

update dbo.Vevo
	set 
	Vevo_nev='Javitott Uj vevo1',
	Helyseg='Subotica'
	where Vevo_kod=3 --is null;
	
select * from Vevo

update dbo.Vevo
	set 
	Vevo_nev='Javitott Uj vevo2',
	Helyseg='Subotica-Szabadka'
	where Vevo_kod between 2 and 10

select * from Vevo


delete Vevo
	where Vevo_kod=10
	
select * from Vevo


delete Vevo
	where vevo_PIB is null

select * from Vevo


create view Vevonezet
	as select * from Vevo

select * from Vevonezet

delete Vevonezet

select * from Vevonezet

drop view dbo.Vevonezet

create view Vevonezet (Vevőnév, PIB, Helység)
	as (select Vevo_nev, Vevo_PIB, Helyseg
		 from Vevo)

select * from Vevonezet
select * from Vevo

create view Vevonezet (Vevőnév, PIB, Helység)
	as (select Vevo_nev, Vevo_PIB, Helyseg
		 from Vevo)
		 
select * from Vevo
select * from Vevonezet

create view Vevonezet1 ("Vevőnév", "Ir_szam", "Helység", "Utca_hszszam")
	as (select Vevo_nev,Ir_szam,Helyseg,Utca_hszs
		 from Vevo)
		 
select * from Vevonezet1




-- Adalék-1 - Appendix-1


use Arurendeles2

-- Ha az oszlopnév üres/blank jelet is tartalmaz

create view Vevonezet2 ("Vevő neve","Vevő kódja", "Irányítószám", "Helység", "Utca_hszszam")
	as select Vevo_nev,Vevo_kod,Ir_szam,Helyseg,Utca_hszs
		 from Vevo
		 
select * from Vevonezet2

/*
Elso Vevo	1	24000	Szabadka	Pipiter utca 2
Sajat Vevo	2	24000	Zabadka	Harap utca 2
Uj vevo	3	24000	NULL	NULL
Uj vevo	4	21000	NULL	NULL
*/




-- Adalék-2 - Appendix-2

/*
A Vevo tablaban ervenzes ertek-korlatozas van az Ir_szam oszlopra: ck_Ir_szam 
([Ir_szam]>=(20000) AND [Ir_szam]<=(29999)).
A korlat torlesa nelkul is beirhato nem megengedett ertek (11000) a
kovetkezo korlat-ellenorzes felfuggesztesevel (utana persze vissza kell allitani
az ellenorzes folztatasat):
*/

alter table dbo.Vevo
	nocheck constraint ck_Ir_szam

update dbo.Vevo
	set 
	Ir_szam=11000
	where Helyseg='Zabadka'
	
select * from dbo.Vevo

/*
1	Elso Vevo	24000	Szabadka	Pipiter utca 2	100835489
2	Sajat Vevo	11000	Zabadka	Harap utca 2	NULL
3	Uj vevo	24000	NULL	NULL	135835489
4	Uj vevo	21000	NULL	NULL	100000489
*/

select * from Vevonezet2

/*
Elso Vevo	1	24000	Szabadka	Pipiter utca 2
Sajat Vevo	2	11000	Zabadka	Harap utca 2
Uj vevo	3	24000	NULL	NULL
Uj vevo	4	21000	NULL	NULL
*/


alter table dbo.Vevo
	check constraint ck_Ir_szam
	
	
update dbo.Vevo
	set 
	Ir_szam=11000
	where Helyseg is null

/*
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "ck_Ir_szam". The conflict occurred in database "Arurendeles2", table "dbo.Vevo", column 'Ir_szam'.
The statement has been terminated.
*/

-- A nocheck - check zaradek az alter table utasitasnal nemcsak modositasnal (update),
-- de uj sor beszurasanal/beirasanal (insert) is alkalmazhato