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

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

-- Kreiranje baze podataka

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

create database NarudzbaRobe_2016;
go

use NarudzbaRobe_2016;
go

create table Kupac (
	sif_kupca int constraint Id_kljuca primary key,
	naziv_kupca varchar(30),
	post_broj numeric(5),
	adresa char(20));
go

drop table Kupac;
go

create table Roba_cenovnik (
	sif_robe int,
	jed_mere char(10),
	naziv_robe varchar(20),
	jed_cena numeric(15,2),
	constraint Id_roba_cenovnik primary key (sif_robe,jed_mere));
go

create table narudzba (
	br_narudzbe int constraint Id_narudzbe primary key,
	sif_kupca int foreign key references dbo.Kupac(sif_kupca),
	datum_narudzbe date);
go 


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

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

-- Kreiranje baze podataka

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


use NarudzbaRobe_2016;
go

drop table dbo.narudzba;
go

drop table dbo.Kupac;
go

drop table dbo.Roba_cenovnik;
go

create table Kupac (
	sif_kupca int constraint Id_kljuca primary key,
	naziv_kupca varchar(30) not null,
	post_broj numeric(5) not null
	constraint post_broj_check 
		check (post_broj between 20000 and 29999)
	constraint post_broj_def default 24000,
	adresa char(20));
go

create table Roba_cenovnik (
	sif_robe int,
	jed_mere char(10),
	naziv_robe varchar(20) not null,
	jed_cena numeric(15,2) not null
		constraint jed_cena_check 
			check(jed_cena>0),
	constraint Id_roba_cenovnik primary key (sif_robe,jed_mere));
go

create table narudzba (
	br_narudzbe int constraint Id_narudzbe primary key,
	sif_kupca int,
	datum_narudzbe date not null 
		constraint dat_nar_def default getdate(),
	constraint Sp_kljuc_prema_kupcu foreign key (sif_kupca) 
		references dbo.Kupac(sif_kupca)
		ON DELETE cascade ON UPDATE cascade);
go

create table Stavka_narudzbe (
	sif_robe int,
	jed_mere char(10),
	br_narudzbe int,
	narucena_kol numeric(12,2) not null
		constraint nar_kol_check check(narucena_kol>0),
	constraint Id_stavka_narudzbe
		primary key (sif_robe,jed_mere,br_narudzbe),
	constraint Sp_klj_prema_narudzbi foreign key
		(br_narudzbe) references dbo.narudzba(br_narudzbe),
	constraint Sp_klj_prema_robicenovniku foreign key
		(sif_robe,jed_mere) references dbo.Roba_cenovnik(sif_robe,jed_mere)
		ON DELETE no action ON UPDATE cascade);
go 

drop table Stavka_narudzbe;
go


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

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

-- Modifikacija strukture tabela


Grupa 2
*/


use NarudzbaRobe_2016;
go

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

alter table kupac
	add telefon char(10);
go

-- Modifikovati polje telefon u tabeli 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
-- Modifikovati polje telefon na char(20)

alter table kupac
	alter column telefon char(20);
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(20) default '+381641527216';
go

-- brisanje polja telefon
alter table kupac
	drop column telefon;
go
alter table kupac
	drop constraint DF__Kupac__telefon__20C1E124;
go
alter table kupac
	drop column telefon;
go

alter table kupac
	add telefon char(20) 
	constraint uc_def_telefon default '+381641527216';
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 '+381641527216';
go

alter table kupac 
	alter column mobilni_telefon char(15)
	constraint uc_fe_mobtel default '+381641527216';
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_def_mobtel default '+381641527216'
	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 
	alter column mobilni_telefon numeric(9)
	check(mobilni_telefon between 62474001 and 62474999);
go

alter table kupac
	drop constraint uc_def_mobtel;
go

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

alter table kupac
	add constraint uc_mobtel_check
	check(mobilni_telefon between 62474001 and 62474999);
go
-- Izmeniti ogranicenje check na polju mbilni_telefon: nove granice su 62400000 i
--	62500000
alter table kupac
	alter constraint uc_mobtel_check
	check(mobilni_telefon between 6240000 and 62500000);
go

-- Unositi jednog kupca sa brojem mobilnog 999999999, ostali podaci
-- po slobodnom izboru
insert Kupac
	values(1, 'Prvi Kupac', 21000, 'Adresa Prvog Kupca', '+38124628000',
			999999999);
go

-- ne moze se uneti beroj mobtela zbog ogranicenja

-- kontrola ogranicenja se ne izvrsava posle izvrsenja
-- sledece naredbe
alter table kupac
	nocheck constraint uc_mobtel_check;
go
-- unos je time omogucen
insert Kupac
	values(1, 'Prvi Kupac', 21000, 'Adresa Prvog Kupca', '+38124628000',
			999999999);
go

select *
	from Kupac;
go
-- ponovno se aktivira provera ogranicenja
alter table kupac
	check constraint uc_mobtel_check;
go
-- kupac se ne moze uneti
insert Kupac
	values(2, 'Drugi Kupac', 21000, 'Adresa Prvog Kupca', '+38124628000',
			999999999);
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
*/

use NarudzbaRobe_2016;
go



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

*/
select *
	from Kupac;
go

insert Kupac
	values(3,'Nazkupca_3', 24003,'AdrKupac3','600003',62474003);
go

select *
	from Kupac;
go

/*
Uneti cetvrti kupac sa sledecim redosledom vrednosti:
'NazKupca4', 4, 24004,'600004','AdrKupca4', 62474004
*/

insert Kupac (naziv_kupca,sif_kupca,post_broj,telefon,adresa,mobilni_telefon)
	values('NazKupca4', 4, 24004,'600004','AdrKupca4', 62474004);
go


/*
 Uneti dva nova kupca, ali popunjavati samo obavezna polja
 
 */

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

select *
	from Kupac;
go

insert Kupac (sif_kupca,naziv_kupca,post_broj) 
	values(6,'Nazkupca_6', 24006);
go

 
select * from Kupac;
go 

/*
Modifikovati vrednost primarnog kljuca na 11 (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 555, a naziva kupca na NazKupca555
 za kupca sa vrednoscu prim. kljuca 55
*/

update Kupac
	set sif_kupca=555, 
		naziv_kupca='NazKupca555'
	where sif_kupca=55;
	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 narudzba;
go
sp_help kupac
go
select *
	from Kupac;
go

/*
Puniti tabelu narudzba sa 3 reda, podaci:
i,i,getdate()
*/

insert narudzba
	values(1,1,GETDATE());
go

insert narudzba
	values(2,null,GETDATE());
go

insert narudzba
	values(3,11,GETDATE());
go
insert narudzba
	values(4,11,GETDATE());
go

select *
	from Kupac;
go
select *
	from narudzba;
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.
Ne moze se sprovesti ako u tabeli ima neodgovarajucih redova-ovom uslovu
*/
alter table dbo.narudzba
	add constraint uc_spk_notnull
		check(sif_kupca is not null);
go

delete narudzba
	where sif_kupca is null;
go

/*
Izbrisati kupac sa sifrom 1
*/

delete Kupac
	where Sif_kupca=1;
go
update Kupac
	set Sif_kupca=111
		where Sif_kupca=11;
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