/*
Prvi cas sa MS SQL serverom 
Grupa 1 
Utorak od 11:30 do 13:05

-- 10.10.2017. - 1. susret - 3. nedelja

-- Kreiranje baze podataka, kreiranje tabela sa primarnim i spoljnim ključevima

1.	Kreirati bazu podataka
Naziv baze podataka: OtpremaRobe
LDM: u datoteci Zadatak-Nedelja3--Feladat-3het.docx/pdf
Grupa 1
*/

-- Bisati stare baze podataka
/*
Naredba za brisanje je 
drop database nazivbazepodataka;
go

Instalaciona verzija MSSQL Server-a je na desktopu,
folder Aplikacije i unutar u folderu Microsoft SQL Deramspark

*/
create database OtpremaRobe;
go

use OtpremaRobe;
go
create table Kupac (
sif_kupca int not null 
	constraint Id_kupca primary key,
Naz_kupca varchar(30),
Post_broj numeric(5),
Adresa varchar(20));
go

create table Otpremnica (
Br_otpremnice int,
Sif_kupca int,
Datum_otpreme date,
Vrednost_otpremnice decimal(12,2),
constraint Id_otpremnice primary key (Br_otpremnice),
constraint Otpr_spklj foreign key (Sif_kupca) 
	references kupac(sif_kupca));
go

drop table Otpremnica
go


	


/*
Drugi cas sa MS SQL serverom 
Grupa 1 
Utorak od 11:30 do 13:05

-- 17.10.2017. - 2. susret - 4. nedelja

-- Kreiranje tabela sa raznim ograničenjima

1.  Završiti zadatak sa prethodnih vežbi
 
2.	Kreirati bazu podataka sa opisanim ogranicenjima
Naziv baze podataka: OtpremaRobe
LDM: u datoteci Zadatak-Nedelja3--Feladat-3het.docx/pdf
Grupa 1
*/

-- Ogranicenja na nivou kolone za table Otpremnica

drop table Otpremnica
go

create table Otpremnica (
Br_otpremnice tinyint not null 
	default 1 primary key 
	check (Br_otpremnice between 1 and 255),
Sif_kupca int not null default 2
	foreign key references Kupac(Sif_kupca) 
	check (Sif_kupca between 1 and 10000),
Datum_otpreme date not null default (getdate()) 
	check (Datum_otpreme>getdate()),
Vrednost_otpreme numeric(12,2) not null default 0
	check (Vrednost_otpreme>10));
go 

use OtpremaRobe;
go

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

create table Stavka_otpremnice (
Sif_robe tinyint not null,
Jed_mere char(10) not null,
Br_otpremnice int not null foreign key references
	Otpremnica(Br_otpremnice),
Otpremljena_kol numeric(10,2),
constraint sp_klj_prema_roba_cenovniku
	foreign key (Sif_robe,Jed_mere)
	references Roba_cenovnik(Sif_robe,Jed_mere));
go

drop table Stavka_otpremnice;
go


drop table Otpremnica;
go

create table Otpremnica (
Br_otpremnice int,
Sif_kupca int,
Datum_otpreme date,
Vrednost_otpremnice decimal(12,2),
constraint Id_otpremnice primary key (Br_otpremnice),
constraint Otpr_spklj foreign key (Sif_kupca) 
	references kupac(sif_kupca) on delete cascade on update cascade);
go

create table Stavka_otpremnice (
Sif_robe tinyint not null,
Jed_mere char(10) not null,
Br_otpremnice int not null foreign key references
	Otpremnica(Br_otpremnice),
Otpremljena_kol numeric(10,2),
constraint sp_klj_prema_roba_cenovniku
	foreign key (Sif_robe,Jed_mere)
	references Roba_cenovnik(Sif_robe,Jed_mere)
	on delete no action on update cascade);
go

-- Kreiranje 4 tabele sa ogranicenjima ostaju za domaci zadatak
-- Sintaksa naredbi za alter table, insert, update i delete je objasnjeno 



/*
Treci cas sa MS SQL serverom 
Grupa 1 
Utorak od 11:30 do 13:05

-- 24.10.2017. - 3. susret - 5. nedelja

-- Modifikacija strukture tabele (polja i ogranicenja) 
-- i unos, modifikacija i brisne podataka u tabelama

*/

use OtpremaRobe;
go

alter table kupac
alter column Adresa char(15) not null;
go

-- 2018
alter table dbo.Kupac
	alter column Naz_kupca varchar(50) not null;
go

alter table dbo.Kupac
	add constraint uc_Postbr_ogr check (Post_broj between 20000 and 29999);
go 



alter table dbo.Kupac
	alter column Post_broj int not null;
go

alter table dbo.Kupac
	nocheck constraint uc_Postbr_ogr;
	go
	
alter table dbo.Kupac
	drop column Post_broj;
go

alter table dbo.Otpremnica
	add constraint uc_default_dat_otpr default getdate() for Datum_otpreme;
go

alter table dbo.Otpremnica
	drop uc_default_dat_otpr;
go

alter table dbo.Otpremnica
	alter column Datum_otpreme date not null default getdate();


alter table dbo.Otpremnica
drop column Sif_kupca;
go

alter table dbo.Otpremnica
	drop Otpr_spklj;
go

alter table dbo.Otpremnica
drop column Sif_kupca;
go

alter table dbo.Otpremnica
	add Sif_kupca int not null 
	constraint Otpr_spklj foreign key references dbo.Kupac(sif_kupca);
go







-- 2018


alter table kupac
add Adr_kbr varchar(5) not null;
go

alter table kupac
drop column Adr_kbr;
go

alter table kupac
drop constraint Id_kupca;
go
-- Ne moze se izbrisati zbog ref. integriteta

alter table kupac
add Adr_kbr varchar(5) not null
constraint Kbr_default default 10;
go

alter table kupac
drop constraint Kbr_default;
go

alter table kupac
drop column Adr_kbr;
go

alter table kupac
add Adr_kbr varchar(5) not null
default 10;
go

alter table kupac
drop constraint DF__Kupac__Adr_kbr__21B6055D;
go

-- 2018

select *
	from Kupac;
go

alter table Kupac
	nocheck constraint Id_kupca;
go

-- 2018-pocetak-02-11-2018
insert into Kupac
values (1,'Kupac',24000,'Marka Oreskovic', 
		'16');
go

select * from Kupac;
go

insert Kupac
values (5,'Kupac',24000,'Marka Oreskovic', 
		'16');
go

insert Kupac (sif_kupca,Naz_kupca,Post_broj,Adresa)
values (4,'Kupac',24000,'Marka Oreskovic');
go

alter table dbo.Kupac
	add constraint uc_default default '30' for Adr_kbr;
go

insert Kupac (sif_kupca,Post_broj,Adresa)
values (6,24000,'Marka Oreskovic');
go

select * from Otpremnica;

insert Otpremnica (Br_otpremnice,Sif_kupca)
	values (11,4);
go

update Kupac
	set 
		sif_kupca=11,
		Post_broj=35000
	where sif_kupca=4;
go


delete Kupac
	where sif_kupca=11;
go	

-- create view
create view PogledNaKupca(Kupac,Grad,Poštanski_broj)
	as
		(select Naz_kupca,Adresa,Post_broj
			from Kupac);
go 

select * from PogledNaKupca



-- 2018-kraj-02-11-2018

insert into Kupac
values (1,'Kupac',24000,'Marka Oreskovic', 
		'16');
go

insert into Kupac
values (1,'Kupac',24000,'M. Oreskovica', 
		16);
go
select * from Kupac;
go

insert into Kupac
values (2,'Kupac1',24000,'M. Oreskovica', 
		16);

go

insert into Kupac
values (3,null,24000,'M. Oreskovica', 
		16);
go

insert into Kupac (sif_kupca,Adresa)
values (4,'Segedinski put');
go

-- Not null kolone se moraju puniti podacima
-- ili moraju imati default vrednost

update Kupac
set
Naz_kupca='Kkupac';
go

select * from Kupac;
go

update Kupac
set
Naz_kupca='Kupac1'
where sif_kupca=1;
go




update Kupac
set
Naz_kupca='Kupac2',
Adresa='M.Oreskov2'
where sif_kupca=2;
go

delete Kupac
where Naz_kupca is null;
go

delete Kupac
where sif_kupca=1;
go

create view Pogled_kupca (
	Ime_kupca, Adresa_kupca, Postanski_broj)
as
(select Naz_kupca,Adresa,Post_broj
	from Kupac);
go

select * from Pogled_kupca;
go