/*
Prvi cas sa MS SQL serverom 
Grupa 1 
Od 9:45 do 11:20

-- 26.10.2016. - 1. susret - 4. nedelja

-- Kreiranje baze podataka

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

create database OtpremaRobe_1_gr;
go

use OtpremaRobe_1_gr;
go

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

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

create table Cenovnik (
	Sif_robe int,
	Jed_mere char(10),
	Naz_robe varchar(20),
	Cena numeric(15,2),
	constraint Id_roba_cenovnik primary key (sif_robe,jed_mere));
	go

/*
Drugi cas sa MS SQL serverom 
Grupa 1 
Od 9:45 do 11:20

-- 02.11.2016. - 2. susret - 5. nedelja

-- Kreiranje baze podataka

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

use OtpremaRobe_1_gr;
go

drop table dbo.Otpremnica
go
	
	
drop table dbo.Kupac
go

create table Kupac (
	Sif_kupca int primary key,
	Naz_kupca varchar(30) not null,
	Post_broj numeric(5) not null 
		constraint kontrola_post_broja
			check (post_broj between 20000 and 29999) 
		constraint post_broj_default default 24000,
	Adresa varchar(20));
go

create table Otpremnica (
	Broj_otpremnice int primary key,
	Sif_kupca int foreign key 
		references Kupac(Sif_kupca)
		ON DELETE  cascade ON UPDATE cascade,
	Datum_otpreme date not null constraint datum_otpreme_default 
		default getdate(),
	Vrednost_otpreme numeric(12,2));
go

create table Roba_Cenovnik (
	Sif_robe int,
	Jed_mere char(10) not null,
	Naz_robe varchar(20) not null,
	Jed_cena numeric(15,2) constraint jed_cena_check
		check (jed_cena>0),
	constraint Id_roba_cenovnik primary key (sif_robe,jed_mere));
	go
	
create table Stavka_otpremnice (
	Sif_robe int,
	Jed_mere char(10),
	Br_otpremnice int,
	Otpremljena_kol numeric(10,2) not null 
		constraint Otpr_kol_check check (otpremljena_kol>0),
	constraint Id_stvka_otpr 
		primary key (sif_robe,jed_mere,br_otpremnice),
	constraint Sp_klj_prema_otpremnici foreign key (Br_otpremnice)
		 references otpremnica(Broj_otpremnice),
	constraint Sp_klj_prema_robi_cenovnik foreign key (Sif_robe,Jed_mere)
		references dbo.Roba_Cenovnik(Sif_robe,Jed_mere));
	go
	
/*
Treci cas sa MS SQL serverom 
Grupa 1 
Od 9:45 do 11:20

-- 09.11.2016. - 3. susret - 6. nedelja

-- Modifikacija strukture tabela
	ALTER TABLE - dodavanje kolone, modifikacija kolone ,
		dodavanje ogranicenja na nivou tabele(default), 
		modifikacija ogranicenja-nemoguca, brisanje polja,
		brisanje ogranicenja


Grupa 1
*/

use OtpremaRobe_1_gr;
go

-- Dodati polje telefon u tabelu kupac sa opisom char(10)

alter table kupac
	add telefon char(10);
go

-- Modifikovati polje telefon u tabelu kupac sa opisom char(15)
--	sa default vrednoscu '+381641527216', naziv ogranicenja je
--	uc_default_telefon

/*
alter table kupac
	alter column telefon char(15)
	constraint uc_default_telefon default '+381641527216';
go
 
 promenom karakteristika polja constraint se ne moze definisati

*/


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

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

-- Brisati polje telefon

alter table kupac
	drop column telefon;
go
--nemoguce je brisati polje ako ima ogranicenje na nivou tabele

-- Brisati ogranicenje za telefon, pa onda polje telefon
alter table kupac
	drop constraint uc_default_telefon;
go

alter table kupac
	drop column telefon;
go

-- dodavanje novog polja telefon sa default-om

alter table kupac
	add telefon char(15) 
	constraint uc_def_telefon default '+381641527216';
go

-- brisanje polja telefon

alter table kupac
	drop column telefon;
go

alter table kupac
	drop constraint uc_def_telefon;
go

alter table kupac
	drop column telefon;
go

-- Dodati novo polje u tabeli kupca sa nazivom mobilni_telefon
-- opis polja je karakter duzine 15, obavezno za popunjavanje

alter table kupac
	add mobilni_telefon char(15) not null;
go

-- Izmeniti polje mobilni_telefon: polje ne mora da se popuni, a treba da ima 
-- default vrednost +38162474935
alter table kupac
	alter column mobilni_telefon char(15) default '+38162474935';
go
--ovo prethodno ne ide, no u dva koraka se moze odraditi

alter table kupac
	alter column mobilni_telefon char(15);
go

alter table kupac
	add constraint uc_default_mobtel default '+38162474935'
	for mobilni_telefon;
go

-- Izmeniti polje mobilni_telefon polje treba da je numericko, duzine 9,
-- a treba ograniciti unos vrednosti u granicama izmedju 62474001 i 62474999
alter table kupac
	drop constraint uc_default_mobtel;
go

alter table kupac
	alter column mobilni_telefon numeric(9);
go

alter table kupac
	add constraint uc_num_mobtel_check
		check(mobilni_telefon between 62474001 and 62474999);
go

-- Unositi jednog kupca sa brojem mobilnog 999999999, ostali podaci
-- po slobodnom izboru

insert Kupac
	values(1, 'Prvi Kupac', 21000, 'Adresa Prvog Kupca', 999999999);
go
-- ne moze se uneti beroj mobtela zbog ogranicenja

alter table kupac
	nocheck constraint uc_num_mobtel_check;
go

insert Kupac
	values(1, 'Prvi Kupac', 21000, 'Adresa Prvog Kupca', 999999999);
go

select * from Kupac;
go

alter table kupac
	check constraint uc_num_mobtel_check;
go

insert Kupac
	values(2, 'Dvi Kupac', 21000, 'Adresa Prvog Kupca', 999999999);
go

/*
Cetvrti cas sa MS SQL serverom 
Grupa 1 
Od 9:45 do 11:20

-- 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 1
*/

use OtpremaRobe_1_gr;
go

select *
	from Kupac;
go

/*
Uneti tri kupca u tabelu Kupac, vrednosti:
i, 'NazKupcai', 2400i, 'AdrKupcai', 60000i

*/
insert Kupac
	values(3, 'NazKupca3',24003,'AdrKupca3',62474003);
go
select *
	from Kupac;
go

/*
Uneti cetvrti kupac sa sledecim redosledom vrednosti:
'NazKupca4', 4, 24004,600004, 'AdrKupca4'
*/
insert Kupac (Naz_kupca,Sif_kupca,Post_broj,mobilni_telefon,Adresa)
	values('NazKupca4',4,24004,62474004,'AdrKupca4');
go


/*
 Uneti dva nova kupca, ali popunjavati samo obavezna polja
 
 */
 
select * from Kupac;
go 

delete Kupac;
go

 insert Kupac
	values (5, 'Nazkupca5', 24005, null, null);
go 

 insert Kupac (Sif_kupca, mobilni_telefon, Post_broj)
	values (7, 62474007, 24007);
go 
 insert Kupac (Sif_kupca, Naz_kupca, Post_broj)
	values (2, 'Nazkupca2', 24002);
go

/*
Modifikovati vrednost primarnog kljuca na 55 za kupca
sa vrednoscu prim. kljuca 5
*/

update Kupac
	set sif_kupca=55
	where sif_kupca=5;
	go
select *
	from Kupac;
go

/*
Modifikovati vrednost primarnog kljuca na 5 za kupca
sa vrednoscu prim. kljuca 1
*/

update Kupac
	set sif_kupca=5
	where sif_kupca=1;
	go

/*
Modifikovati vrednost primarnog kljuca na 5 za kupca
sa vrednoscu prim. kljuca 55
*/

update Kupac
	set sif_kupca=5
	where sif_kupca=55;
	go

update Kupac
	set Adresa='default1', mobilni_telefon=62474066
	where sif_kupca=11;
	go
	
update Kupac
	set mobilni_telefon=1;
	go

sp_help otpremnica
go
sp_help kupac
go

/*
Puniti tabelu otpremnica sa 3 reda, podaci:
i,i,getdate(),60000i
*/
insert Otpremnica
	values(1,null,GETDATE(),600001);
go

select *
	from Kupac;
go
select *
	from Otpremnica;
go

update Otpremnica
	set Sif_kupca=2
	where Broj_otpremnice=1;
go

/*
Modifikovati strukturu tabele Otpremnica.
Izmeniti polje sif_kupca u smislu da se mora popunjavati.
*/
alter table dbo.Otpremnica
	add constraint uc_sifkupca_notnull
		check(Sif_kupca is not null);
go

insert Otpremnica
	values(3,5,GETDATE(),600003);
go

/*
Izbrisati kupac sa sifrom 2
*/

delete Kupac
	where Sif_kupca=2;
go
update Kupac
	set Sif_kupca=555
		where Sif_kupca=5;
go

/*
Kreirati tabelu pogleda...
Kreirati pogled o kupcu sa nazivom, postanskim brojem, adresom i
mobilnim telefonom
*/

create view view_kupac 
	as (select naziv_kupca, post_broj, adresa, mobilni_telefon
			from Kupac);
go

select *
	from view_kupac;
go

create view view_kupac1 (Kupac, Postanski_broj, Adresa, Mobilni_telefon) 
	as (select naziv_kupca, post_broj, adresa, mobilni_telefon
			from Kupac);
go

select *
	from view_kupac1;
go