/*
Prvi susret sa MS SQLSERVERom (2008 R2)
2020.10.29.

1. susret -4. nedelja

Četvrtak - 14:00-15:35
*/
--
--
--


create database OtpremaRobe;
go

sp_helpdb

drop database OtpremaRobe

use OtpremaRobe

create table Kupac (
Sif_kupca int constraint Id_kupca primary key,
Naz_kupca varchar(30),
Post_broj numeric (5,0),
Adresa varchar(20));
go

drop table Kupac

create table Kupac (
Sif_kupca int primary key,
Naz_kupca varchar(30),
Post_broj numeric (5,0),
Adresa varchar(20));
go

create table Roba_cenovnik (
Sif_robe int,
Jed_mere char(10),
constraint Id_roba_cenovnik primary key (Sif_robe,Jed_mere),
Naziv_robe varchar(20),
Jed_cena numeric(15,2));
go

create table Otpremnica (
Br_otpremnice int,
Sif_kupca int constraint Dobije foreign key references Kupac(Sif_kupca),
Datum_otpreme date,
Vrednost_otpreme numeric(12,2),
constraint Id_otpremnice primary key (Br_otpremnice));
go

create table Stavka_otpremnice (
Sif_robe int,
Jed_mere char(10),
Br_otpremnice int,
Otpremljena_kol numeric(10,2),
constraint Identifier_1 primary key (Sif_robe,Jed_mere,Br_otpremnice),
constraint Otpr_ima_stavku foreign key (Br_otpremnice) references Otpremnica(Br_otpremnice),
constraint Roba_ulazi_u_stavku foreign key (Sif_robe,Jed_mere) references Roba_cenovnik(Sif_robe,Jed_mere));
go

--
--
--
/*
Drugi susret sa MS SQLSERVERom (2008 R2)
2020.11.05.

2. susret -5. nedelja

Četvrtak - 14:00-15:35
*/
--
--
--



create database OtpremaRobe2;
go

sp_helpdb

drop database OtpremaRobe

use OtpremaRobe2

create table Kupac (
Sif_kupca int constraint Id_kupca primary key,
Naz_kupca varchar(30) not null,
Post_broj numeric (5,0) not null 
	constraint uc_ck_Post_br check(Post_broj>=20000 and Post_broj<=29999) default 24000,
Adresa varchar(20));
go

drop table Kupac

create table Kupac (
Sif_kupca int constraint Id_kupca primary key,
Naz_kupca varchar(30) not null,
Post_broj numeric (5,0) not null 
	constraint uc_ck_Post_br check(Post_broj>=20000 and Post_broj<=29999) 
	constraint uc_df_Post_br default 24000,
Adresa varchar(20))

create table Roba_cenovnik (
Sif_robe int,
Jed_mere char(10),
constraint Id_roba_cenovnik primary key (Sif_robe,Jed_mere),
Naziv_robe varchar(20) not null,
Jed_cena numeric(15,2) not null 
	constraint uc_ck_Jed_cena check(Jed_cena>0));
go

create table Otpremnica (
Br_otpremnice int,
Sif_kupca int constraint Dobije foreign key references Kupac(Sif_kupca)
	on delete cascade on update cascade,
Datum_otpreme date not null
	constraint uc_df_Dat_otpr default (getdate()),
Vrednost_otpreme numeric(12,2),
constraint Id_otpremnice primary key (Br_otpremnice));
go

create table Stavka_otpremnice (
Sif_robe int,
Jed_mere char(10),
Br_otpremnice int,
Otpremljena_kol numeric(10,2) not null
	constraint uc_ck_Otpr_kol check(Otpremljena_kol>0),
constraint Identifier_1 primary key (Sif_robe,Jed_mere,Br_otpremnice),
constraint Otpr_ima_stavku foreign key (Br_otpremnice) references Otpremnica(Br_otpremnice),
constraint Roba_ulazi_u_stavku foreign key (Sif_robe,Jed_mere) references Roba_cenovnik(Sif_robe,Jed_mere)
	on delete no action on update cascade);
go



-- Promena strukture tabele


alter table Kupac
	alter column Naz_kupca char(45) not null
		constraint uc_df_Naz_kupca default ('NAS KUPAC');

--Ne može da se definiše constrant na nivou kolone kod alter column klauzule!!!

alter table Kupac
	alter column Naz_kupca char(45) check(len(Naz_kupca)>0) default ('NAS KUPAC');
	

alter table Kupac
	alter column Naz_kupca char(45) check(len(Naz_kupca)>0);	
	
alter table Kupac
	alter column Naz_kupca int ;	
	
/*
Msg 5074, Level 16, State 1, Line 1
The object 'uc_df_Naz_kupca' is dependent on column 'Naz_kupca'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN Naz_kupca failed because one or more objects access this column.
*/		

--Ne može da se definiše constrant na nivou kolone kod alter column klauzule!!!
		
alter table Kupac
	alter column Naz_kupca char(45) null

--
--
alter table Kupac
	alter column Naz_kupca char(15) null
--
--



alter table Kupac
	add constraint uc_df_Naz_kupca default ('NAS KUPAC') for Naz_kupca

alter table Kupac
	add PIB_Kupca numeric(15,0) null
	constraint uc_ck_Naz_kupca check (PIB_Kupca>0 and PIB_Kupca<=100000000000) 
	constraint uc_df_PIB_Kupca default (9999999999);
	
alter table dbo.Kupac
	drop constraint uc_ck_Naz_kupca
	
alter table Kupac
	add constraint uc_ck_PIB_Kupca check (PIB_Kupca>0 and PIB_Kupca<=100000000000);



-- Unos podataka u tabelu

	
insert Kupac
	values(1,'1.Kupac',21000,'JJ Zmaja 5', 100025684)
	
select * from Kupac

insert Kupac (Sif_kupca)
	values(2)

select * from Kupac


--
--
--
/*
Treći susret sa MS SQLSERVERom (2008 R2)
2020.11.12.

3. susret -6. nedelja

Četvrtak - 14:00-15:35
*/
--
--
--

use OtpremaRobe2

select * from Kupac

insert Kupac (Sif_kupca,PIB_Kupca, Post_broj,Adresa, Naz_kupca)
	values(12, 123456789,11000, 'Nemanjina 6, Beograd','Kupac iz Beograda')

/*
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.
*/


insert Kupac (Sif_kupca,PIB_Kupca, Post_broj,Adresa, Naz_kupca)
	values(12, 123456789,21000, 'Nemanjina 6, Novi Sad','Kupac iz Novog Sada')

/*	
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.
*/

insert Kupac (Sif_kupca,PIB_Kupca, Post_broj,Adresa, Naz_kupca)
	values(12, 123456789,21000, 'Nemanjina 6, Novi Sad','Kup.iz N.Sada')
	
/*Msg 8152, Level 16, State 14, Line 2
String or binary data would be truncated.
The statement has been terminated.
*/

insert Kupac (Sif_kupca,PIB_Kupca, Post_broj,Adresa, Naz_kupca)
	values(12, 123456789,21000, 'Nemanjina 6 N.S.','Kup.iz N.Sada')
	

select * from Kupac

update Kupac
	set
		Post_broj=24415
	where Adresa is null
	
select * from Kupac

update Kupac
	set
		Post_broj=24400,
		Adresa='Petefijeva 1'
	where Adresa is null

select * from Kupac

alter table kupac
	nocheck constraint uc_ck_Post_br
	
update Kupac
	set
		Post_broj=11000,
		Adresa='Jovana Ducica 1'
	where Adresa='Petefijeva 1'
	
select * from Kupac

alter table kupac
	check constraint uc_ck_Post_br

update Kupac
	set
		Post_broj=11500
		
	where Adresa='Jovana Ducica 1'
	
/*
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "uc_ck_Post_br". The conflict occurred in database "OtpremaRobe2", table "dbo.Kupac", column 'Post_broj'.
The statement has been terminated.
*/

select * from Kupac

update Kupac
	set PIB_Kupca=9876451230

select * from Kupac

delete Kupac

select * from Kupac

delete Kupac
	where Adresa is null

select * from Kupac

insert Kupac (Sif_kupca)
	values(2)
	
select * from Kupac

delete Kupac
	where Sif_kupca=10
	
delete Kupac
	where Sif_kupca=12






-- DODATAK!!!

-- Nastaviti sa create view naredbom!!!
use OtpremaRobe2

-- Kreiranje pogleda iz tabele sa istim redosledom i nazivima kolona

create view Pogled_Kupac
	as select * from Kupac
	
select * from Pogled_Kupac

/*
1	1.Kupac        	21000	JJ Zmaja 5	100025684
2	NAS KUPAC      	24000	NULL	9999999999
*/

-- Kreiranje pogleda iz tabele sa razlicitim redosledom i/ili razlicitim nazivima kolona

create view Pogled_Kupac1 (PIB, Ime_kupca)
	as select  PIB_Kupca,Naz_kupca from Kupac
	
select * from Pogled_Kupac1

/*
100025684	1.Kupac        
9999999999	NAS KUPAC      
*/

-- Kreiranje pogleda iz tabele sa razlicitim redosledom i/ili razlicitim nazivima kolona
-- blank u nazivu kolone tabele pogleda

create view Pogled_Kupac2 ("PIB", "Ime kupca", "Poštanski broj", Adresa)
	as select  PIB_Kupca,Naz_kupca,Post_broj,Adresa from Kupac
	
select * from Pogled_Kupac2

/*
100025684	1.Kupac        	21000	JJ Zmaja 5
9999999999	NAS KUPAC      	24000	NULL
*/