/*
Elso talalkozas az MS SQL szerverrel 
Harmadik csoport 
Od 14:00 do 15:35

-- 26.10.2016. - 1. talalkozas - 4. het

-- Adatbazis letrehozasa

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

create database Arurendeles_2016;
go

use Arurendeles_2016;
go

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

drop table Vevo;
go

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


create table Rendeles (
	rendeles_sz int constraint Rendeles_azon primary key,
	vevo_kod int not null 
		constraint Rendeles_kk_vevo foreign key references vevo(vevo_kod),
	kelt date);
go

drop table rendeles;
go


/*
Masodik talalkozas az MS SQL szerverrel 
Harmadik csoport 
Od 14:00 do 15:35

-- 02.11.2016. - 2. talalkozas - 5. het

-- Adatbazis letrehozasa

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

use Arurendeles_2016;
go

drop table rendeles;
go

drop table vevo;
go

drop table Aru_arlista;
go

create table Vevo (
	Vevo_kod int,
	Vevo_nev varchar(30) not null,
	Ir_szam numeric(5) not null 
		constraint Ir_szam_check
			check(ir_szam between 20000 and 29999)
		constraint Ir_szam_def default 24000,
	Helyseg varchar(20),
	Utca_hsz varchar(20),
	constraint Vevo_azon primary key (vevo_kod));
go

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

create table Rendeles (
	rendeles_sz int constraint Rendeles_azon primary key,
	vevo_kod int not null 
		constraint Rendeles_kk_vevo foreign key references vevo(vevo_kod)
			ON DELETE cascade ON UPDATE cascade,
	kelt date not null
		constraint kelt_def default getdate());
go


create table Rendeles_tetel (
	Aru_kod int,
	Mertekegyseg char(10),
	Rendeles_szam int,
	Rendelt_menny numeric(12,2) not null
		constraint rend_menny_check check(Rendelt_menny>0),
	constraint Rendelestetel_Kulcs 
		primary key (aru_kod,mertekegyseg,Rendeles_szam),
	constraint KK_a_rendeles_fele
		foreign key (rendeles_szam) references
			dbo.Rendeles(rendeles_sz),
	constraint KK_az_aru_arurendeles_fele
		foreign key (Aru_kod,Mertekegyseg) references
			dbo.Aru_arlista(Aru_kod,Mertekegyseg)
			ON DELETE no action ON UPDATE cascade);
go 


/*
Harmadik talalkozas az MS SQL szerverrel 
Harmadik csoport 
Od 14:00 do 15:35

-- 09.11.2016. - 3. talalkozas - 6. het

-- A tablaszerkezet modositasa



Harmadik csoport
*/

use Arurendeles_2016;
go


-- Adja hozza a vevo tablahoz a telefon oszlopot char(10) leirassal
alter table vevo
	add telefon char(10);
go


-- Modositsa a telefon oszlopot char(15)-re es rendeljen hozza
--	default erteket: '+381641527216', a default korlat neve legyen
--	uc_default_telefon

alter table vevo
	alter column telefon char(15) default '+381641527216';
go

alter table vevo
	alter column telefon char(15) 
	constraint uc_default_telefon default '+381641527216';
go

/*
 mezovaltoztatasnal a korlatok nem valtoztathatok meg
*/

alter table vevo
	alter column telefon char(15);
go

alter table vevo
	add constraint uc_default_telefon default '+381641527216'
		for telefon;
go

-- Toroljuk a telefon mezot

alter table vevo
	drop column telefon;
go

-- a telefon nem torolheto a raepitett korlat miatt

alter table vevo
	drop constraint uc_default_telefon;
go

alter table vevo
	drop column telefon;
go

-- Vigyuk be ujra a telefon mezot char(20) leirassal es 

-- uc_default_telefon default '+381641527216' korlattal

alter table vevo
	add telefon char(20)
	constraint uc_default_telefon default '+381641527216';
go


alter table vevo
	drop constraint uc_default_telefon
	drop column telefon;
go
-- nem mukodik
-- tobb zaradek felsorolasa nem megengedett


-- Adja hozza a vevo tablahoz a mobil_telefon mezot
-- 15 hosszusagu, karakter tipusu, kotelezo kitoltesu
alter table vevo
	add mobil_telefon char(15) not null;
go


-- Valtoztassunk a mobil_telefon mezon: ne legyen kitoltendo, de legyen 
-- default erteke +38162474935
--ket lepesben oldhato meg

--1
alter table vevo
	alter column mobil_telefon char(15);
go
alter table vevo
	add constraint uc_df_mobitel default '+381641527216'
	for mobil_telefon;
go

--1-2
alter table vevo
	drop constraint uc_df_mobitel;
go
-- 2
alter table vevo
	drop column mobil_telefon;
go

alter table vevo
	add mobil_telefon char(15)
	constraint uc_mobitel_df default '+381641527216';
go

-- Modositsa a mobil_telefon mezot, legyen numerikus, 9 hosszusagu
-- es korlatozott ertekbefogadasu: 62474001 es 62474999 ertek kozott
alter table vevo
	drop constraint uc_mobitel_df;
go

--1
alter table vevo
	alter column mobil_telefon numeric(9);
go

alter table vevo
	add constraint uc_ck_mobitel
	check(mobil_telefon between 62474001 and 62474999);
go

-- 1-2
alter table vevo
	drop constraint uc_ck_mobitel;
go

-- 2

alter table vevo
	drop column mobil_telefon;
go

alter table vevo
	add mobil_telefon numeric(9)
	constraint uc_mob_DICK_CK
	CHECK (mobil_telefon between 62474001 and 62474999);
go


-- Valtoztassa meg az ertekkorlat hatarait: 62400000 es
--	62500000

alter table vevo
	alter constraint uc_mob_DICK_CK;
go
-- nincs rA LEHETOSEG, HOGY a korlathatarokat megvaltoztassuk
-- torolni kell, majd az uj hatarokkal ujraalakitani

-- Vigyen be egy vevot 999999999-es mobilszammal, a tobbi adat tetszoleges


insert Vevo
	values(1, 'Elso Vevo', 21000, 'Ujvidek', 'az', 'telefon',
			999999999);
go 
-- a vevo nem viheto be a mobiltelefonra megfogalmazott korlat miatt

-- a korlat-ellenorzest fel lehet fuggeszteni:

alter table vevo
	nocheck constraint uc_mob_DICK_CK;
go


-- ekkor mar beviheto a sor a korlat feletti ertekkel
insert Vevo
	values(1, 'Elso Vevo', 21000, 'Ujvidek', 'az', 'telefon',
			999999999);
go

select *
	from Vevo;
go

-- a korlat-ellenorzes visszaallitasa
alter table vevo
	check constraint uc_mob_DICK_CK;
go

-- a kovetkezo bevitel mar nem lehetseges 
insert Vevo
	values(2, 'Elso Vevo', 21000, 'Ujvidek', 'az', 'telefon',
			999999999);
go




use Arurendeles_2016;
go

/*
Cetvrti cas sa MS SQL serverom 
Grupa 2 
Od 11:30 do 13:05

-- 16.11.2016. - 4. susret - 7. nedelja

-- Unos, Modifikacija i brisanje podataka u tabeli
	-insert tablename(columnname1, columname2, ...)
		values (value1, value2, ...)
	-unos vrednosti spoljnog kljuca koja ne postoji
		u tabeli roditelj kao vr.primarnog kljuca
	-update tablename
		set columname=value1, columname2=value2, ...
		where ...
	-promena vrednosti spoljnog kljuca na vrednost koja ne postoji
		u tabeli roditelj kao vr.primarnog kljuca
	-delete tablename
		where ...
	- brisanje podatka (reda) iz tabele roditelj koja ima dete
	-truncate
	-create view viewname(c1,c2,...) as
		(select cc1,cc2,...
			from tablename)

Grupa 2
*/

/*
Irjon be harom vevot a kovetkezo ertekekkel:
i, 'VevoNevi', 2400i, 'VevoVarosi','VevoUtcaHszi',
 '60000i', 225588i 
*/
insert Vevo
	values(4,'VevoNev3',24003,'VevoVaros3','VevoUtcaHsz3',
			'600003', 62474003);
go





select *
	from Vevo;
go



/*
Szurja be a negyedik vevot a kovetkezo adatokkal:
'Vevo_nev4', 4, 24004,'600004','VevoVarosi','VevoUtcaHszi', 62474004
*/

insert Vevo (Vevo_nev,Vevo_kod,Ir_szam,telefon,Helyseg,
				Utca_hsz,mobil_telefon)
	values('Vevo_nev4', 5, 24004,'600004','VevoVarosi',
				'VevoUtcaHszi', 62474004);
go


/*
 Szurjon be ket uj vevot, amelyeknel csak a k0telezo
	kitoltesu mezoket kell ertekkel ellatni
 
 */
insert Vevo
	values(6,'VevoNev3',24003,null,null,null,null);
go


select *
	from Vevo;
go

insert Vevo (Vevo_nev,Vevo_kod,Ir_szam)
	values('Vevo_nev7', 7, 24007);
go
 

/*
Modositsa a vevokodot 7 ertekre abban a sorban, ahol a
vevokod erteke 1
*/

update Vevo
	set Vevo_kod=1
	where Vevo_kod=7;
	go
/*
Modositsa a vevokodot 11 ertekre abban a sorban, ahol a
vevokod erteke 1
*/
update Vevo
	set Vevo_kod=11
	where Vevo_kod=1;
	go

/*
Torolje a sort, amelyben a telefon mezo tartalma 600003
*/

delete Vevo
	where telefon='600003';
go

select *
	from Vevo;
go
select *
	from Rendeles;
go


sp_help narudzba;
go
sp_help kupac
go
select *
	from Kupac;
go

/*
Szurjon be a rendeles tablaba 3 sort, a kovetkezo adatokkal:
i,i,getdate()
*/

insert Rendeles
	values(3,11,GETDATE());
go





/*
Torolje a vevo tablabol a 2-es vevokodu vevot
*/

delete vevo
	where Vevo_kod=2;
go
update Kupac
	set Sif_kupca=111
		where Sif_kupca=11;
go


/*
Hozzon letre nezettablat...
Hozzon letre nezettablat view_vevo neven, amely tartalmazza a
vevonevet, helyseget es a mobiltelefonszamot
*/
create view view_vevo 
	as (select Vevo_nev, Helyseg, mobil_telefon
			from Vevo);
go

select *
	from view_vevo;
go

/*
Hozzon letre nezettablat...
Hozzon letre nezettablat view_vevo neven, amely tartalmazza a
vevonevet, helyseget es a mobiltelefonszamot, de a nezettablaban
a mezonevek nem egyezhetnek meg a vevo fizikai tabla neveivel, 
hanem a kovetkezo neveket kell alkalmazni:Vevo, Varos, Mobilszam

*/
create view view_vevo1 (Vevo, Varos, Mobilszam) 
	as (select Vevo_nev, Helyseg, mobil_telefon
		from Vevo);
go

select *
	from view_vevo1;
go