/* III - grupa - sreda - 17-30 - 19-05

--  -- 1. nedelja - MEP model entiteta i poveznika projektor
--  -- 2. nedelja - MEP pomocu SyBase PowerDesigner-a
--  -- 3. nedelja - na prvom pravom laboratorijskom casu
--	--	--		-- MS SQL server nije radio - bila je pokazna nastava

--  -- 4. nedelja - 21-10-2015
--  Prva laboratorija
-- -- create database, use, create table naredba, not null, unique,
-- -- default, check,
-- -- kljuc(primarni) i spoljni kljucevi, prosti/slozeni, alter table

*/
create database NarudzbaRobe1
go
use NarudzbaRobe1
go

create table Kupac
(sif_kupca tinyint primary key,
naz_kupca varchar(30),
post_broj numeric(5),
adresa varchar(20))
go

create table Narudzba
(br_narudzbe tinyint,
sif_kupca tinyint references kupac(sif_kupca),
datum_narudzbe date,
constraint kljuc_narudzbe primary key(br_narudzbe))
go

sp_help narudzba
go
create table Roba_cenovnik
(sif_robe tinyint,
jed_mere char(10),
naziv_robe varchar(20),
jed_cena numeric(15,2),
constraint klj_roba_cenov primary key(sif_robe,jed_mere))
go

create table Stavka_narudzbe
(sif_robe tinyint,
jed_mere char(10),
br_narudzbe tinyint,
narucena_kol numeric(12,2),
constraint klj_stav_nar primary key(sif_robe,jed_mere,br_narudzbe),
constraint sp_klj_nar foreign key(br_narudzbe) 
	references narudzba(br_narudzbe),
constraint sp_klj_roba_cen foreign key(sif_robe,jed_mere)
	references roba_cenovnik(sif_robe,jed_mere))
go

alter table kupac
	alter column naz_kupca varchar(30) not null
	go
	
sp_help kupac
go

alter table kupac
	add br_sprata_br_stana varchar(20)
	go

alter table kupac
	drop column br_sprata_br_stana
	go
	
alter table kupac
	add constraint uc_ptt_vojv 
	check(post_broj between 20000 and 30000)
	go
	
alter table kupac
	drop constraint uc_ptt_vojv
go

/*

--  -- 5. nedelja -- 28-10-2015
--  -- Druga laboratorija 
-- -- use, alter table, sp_help, insert, delete, update
-- -- select

*/

-- Drugi cas u laboratoriji - 28-10-2015

use NarudzbaRobe1;
go

sp_help narudzba;
go

alter table narudzba
	drop constraint FK__Narudzba__sif_ku__03317E3D;
go

alter table narudzba
	add constraint sp_klj_kupac foreign key(sif_kupca)
	references kupac(sif_kupca)
	on delete no action
	on update cascade;
go

sp_help stavka_narudzbe
go

alter table narudzba
	alter constraint sp_klj_kupac foreign key (sif_kupca)
	references kupac(sif_kupca)
	on delete cascade
	on update cascade;
	go

sp_help kupac;
go

insert Kupac
	values(1,'Kupac1',24000,'Vase Stajica 12');
	go

select * from Kupac;
go

alter table kupac
	drop column post_broj;
	go

alter table kupac
	add post_broj numeric(5) not null default(24000)
	check(post_broj between 20000 and 30000);
	go
	
insert Kupac
	values(2,'Kupac2','Vase Stajica 12',27000);
	go

insert Kupac
	values(3,'Kupac3','Vase Stajica 10',25000);
	go

insert Kupac(adresa,sif_kupca,naz_kupca)
	values('Vase Stajica 2',4,'Kupac4');
	go

update Kupac
	set adresa='Vase Sajica 5'
	where sif_kupca=2;
	go

insert Narudzba
	values(1,1,'2015.10.28');
	go

select * from Narudzba;
go

insert Narudzba
	values(2,1,'10/27/2015');
	go

insert Narudzba
	values(4,3,'28-10-2015');
	go

insert Narudzba
	values(4,3,'2015-10-24');
	go

delete Kupac
	where sif_kupca=1;
go

select * from Kupac;
go

update Kupac
	set sif_kupca=111
	where sif_kupca=1;
	go
	
select * from Narudzba;
go

sp_help stavka_narudzbe;
go

alter table stavka_narudzbe
	drop constraint sp_klj_nar;
go

alter table stavka_narudzbe
	drop constraint sp_klj_roba_cen;
go

/*
constraint sp_klj_nar foreign key(br_narudzbe) 
	references narudzba(br_narudzbe),
constraint sp_klj_roba_cen foreign key(sif_robe,jed_mere)
	references roba_cenovnik(sif_robe,jed_mere))
	*/

alter table stavka_narudzbe
add constraint sp_klj_nar foreign key(br_narudzbe) 
	references narudzba(br_narudzbe)
	on update cascade;
go

alter table stavka_narudzbe
add constraint sp_klj_roba_cen foreign key(sif_robe,jed_mere)
	references roba_cenovnik(sif_robe,jed_mere)
	on update cascade;
go

sp_help stavka_narudzbe;
go

/*
--  -- 6. nedelja -- 04-11-2015
--  -- Treca laboratorija  
-- -- use, alter table-unique, sp_help, insert, delete, update
-- -- select, create view

alter table kupac
add unique(telefon);
go

ili navodjenjem imana ogranicenja:

alter table kupac
add constraint uc_jedinstven_telfon unique(telefon);
go

alter table kupac
nocheck constraint uc_jedinstven_telfon;
go

alter table kupac
check constraint uc_jedinstven_telfon;
go

alter table kupac
drop constraint uc_jedinstven_telfon;
go

alter table narudzba
 add constraint uc_datum_narudzbe_ogranicenja
 check(datum_narudzbe<=getdate());
 go

alter table narudzba
add constraint uc_datum_narudzbe_default
default getdate() for datum_narudzbe;
go

insert into narudzba
	values(7,3, getdate(), 15000000);
	go

update narudzba 
set datum_narudzbe = GETDATE()
where br_narudzbe=3;
go

truncate table narudzba;
go

create view Pogled_kupca as
	(select naz_kupca,sif_kupca,adresa,post_broj,telefon
		from kpac);
go


*/

use NarudzbaRobe1
go

alter table kupac
add unique(post_broj);
go

alter table kupac
drop constraint UQ__Kupac__8D592AE8108B795B;
go

alter table kupac
add constraint uc_jedinstven_ptt_broj unique(post_broj);
go

alter table narudzba
 add constraint uc_datum_narudzbe_ogranicenja
 check(datum_narudzbe<=getdate());
 go
 
 select * from Narudzba;
 go
 
 insert Narudzba
	values(5,3,'11/27/2015');
	go
	
insert into narudzba
	values(7,3, getdate());
	go
	
alter table narudzba
nocheck constraint uc_datum_narudzbe_ogranicenja;
go

alter table narudzba
check constraint uc_datum_narudzbe_ogranicenja;
go

truncate table narudzba;
go


create view Pogled_kupca as
	(select naz_kupca,sif_kupca,adresa,post_broj
		from kupac);
go

select * from Pogled_kupca;
go

select * from Kupac;
go

insert Kupac
	values(1,'Kupac1-1','Vase Stajica 12-1',24001);
	go

-- 
--

/* Kreiranje baze*/

create database PP;
go

use PP;
go

create table mesto
(Sif_mesta int primary key,
Drzava varchar(30) not null,
Naziv_mesta varchar (30) not null,
Postanski_broj decimal(5,0) not null,
Reg_oznaka char(8));
go

insert into mesto
	values (1, 'Srbija','Subotica',24000, 'SU');
	go
	
insert into mesto
	values (2, 'Srbija','Novi Sad',21000, 'NS');
	go
	
insert into mesto
	values (3, 'Srbija','Bajmok',24210, 'SU');
	go
	
insert into mesto
	values (4, 'Srbija','Palic',24413, 'SU');
	go

insert into mesto
	values (5, 'Srbija','Kanjiza',24000, 'KA');
	go
	
insert into mesto
	values (6, 'Srbija','Senta',24620, 'SA');
	go

select * from mesto;
go

/* listati Nazive mesta i registarske oznake iz tabele mesto */

select Naziv_mesta, Reg_oznaka
	from mesto;
go

/* listati Nazive mesta i registarske oznake iz tabele mesto 
	sa sledecim nazivima kolona: 'Mesto' i 
	'Registarska oznaka pocinje slovima'*/
	
select Naziv_mesta as 'Mesto', 
		Reg_oznaka as 'Registarska oznaka pocinje slovima'
	from mesto;
go

/* listati Nazive mesta i registarske oznake iz tabele mesto 
	sa sledecim nazivima kolona: 'Mesto' i 
	'Registarska oznaka pocinje slovima'
	
	Izmedju vrednosti mesta i registarske oznake da se pojavi sadrzaj
	', sa reg. oznakom '*/


select rtrim(Naziv_mesta) as 'Mesto',
		', sa reg. oznakom ',
		Reg_oznaka as 'Registarska oznaka pocinje slovima'
	from mesto
	where Naziv_mesta='Palic';
go

select rtrim(Reg_oznaka) as 'RO',
		', je reg. oznaka za mesto ',
		Naziv_mesta as 'Mesto'
	from mesto
	where Naziv_mesta='Palic';
go

select rtrim(Reg_oznaka) as 'RO',
		', je reg. oznaka za mesto ',
		Naziv_mesta as 'Mesto'
	from mesto;
go

select Reg_oznaka as 'RO',
		', je reg. oznaka za mesto ',
		Naziv_mesta as 'Mesto'
	from mesto;
go

update mesto
	set Reg_oznaka='SU - PAL'
	where Naziv_mesta='Palic';
	go