-- CAS 01 -- DDL

--CREATE
--DROP
--ALTER


--CREATE TABLE
--explicitno
--implicitno(automatski)  --select into


--CREATE TABLE schema_name.naziv_tabele
--(naziv_obelezja data_type(size) oganicenje,
-- naziv_obelezja1,
-- naziv_obelezja2,
-- ogranicenje
--);

--database schema
CREATE SCHEMA Production AUTHORIZATION dbo
go

DROP SCHEMA Production;

CREATE TABLE Production.Categories
(
categoryid INT NOT NULL,
categoryname NVARCHAR(15) NOT NULL,
description NVARCHAR(200) NOT NULL
)
GO

--DROP TABLE
DROP TABLE Production.Categories
GO

--naming tables and columns

--regular
--delimited

--SET QUOTED_IDENTIFIER

SET QUOTED_IDENTIFIER ON --default

CREATE TABLE "Tomorrow's Schedule"
( id int)

DROP TABLE [Tomorrow's Schedule]

SET QUOTED_IDENTIFIER OFF

CREATE TABLE "Tomorrow's Schedule"
( id int)

SET QUOTED_IDENTIFIER ON;--vratiti na default

--NULL

CREATE TABLE Production.Categories(
	categoryid INT NOT NULL,
	categoryname NVARCHAR(15) NOT NULL,
	description NVARCHAR(200) NOT NULL);
GO

--select into
SELECT mbr, prz, ime
INTO radnik2
FROM hr2016.dbo.radnik
GO

select *
from radnik2


drop table radnik2
GO

--zadatak
--1. Napraviti tabelu Projekat2 u kojoj ce
--biti svi radnici(njihov maticni broj, ime i prezime)
--koji rade na projektu sa sifrom 2.
select radnik.*
into Projekat2
from hr2016.dbo.radnik
inner join hr2016.dbo.radproj on radnik.mbr=radproj.mbr
where spr=2

select *
from Projekat2


--pregled svih sema

SELECT name FROM sys.schemas

/*Kreirati shemu Test i u okviru nje tabelu Test1
koja ima sledeca obelezja test_id tipa INT,
test_ime tipa NVARCHAR velicine 15, i opis tipa
NVARCHAR velicine 200. Ni jedno od obelezja ne sme
da dozvoli NULL.*/
CREATE SCHEMA Test
GO
CREATE TABLE Test.Test1(
test_id int NOT NULL,
test_ime nvarchar(15) NOT NULL,
opis nvarchar(200) NOT NULL,
)


/*Kreirati novu tabelu Test2 u okviru sheme Test koja
ima samo obelezje test_id tipa int, ne dozvoljava
NULL vrednosti.*/
CREATE TABLE Test.Test2(
test_id int NOT NULL,
)

--ALTER TABLE
--dodavanje kolona
--brisanje kolona
--izmena definicije kolone
	--tipa podatka
	--velicinu tipa kolone

--ALTER TABLE schema_name.table_name
--	ADD column_name type(size) NOT NULL;

/*Dodati u tabelu Test2 obelezje test_ime
tipa NVARCHAR(15)
koje ima ogranicenje NOT NULL */

ALTER TABLE Test.Test2
	ADD test_ime NVARCHAR(15) NOT NULL;
GO

/*Dodati i obelezje opis koje ima iste
karakteristike kao i u Test1.*/
ALTER TABLE Test.Test2
	ADD opis NVARCHAR(15) NOT NULL;
GO

/*Uneti jednu torku u tabelu Test1.*/
INSERT INTO Test.Test1 values(1,'Novo', 'Opis imena');

/*Uneti te iste podatke u tabelu Test2,
tako sto prvo treba seletovati podatke
iz tabele Test1 i te iste upisati u Test2.*/

INSERT INTO Test.Test2 (test_id, test_ime, opis)
	SELECT test_id, test_ime, opis
	FROM Test.Test1
GO

--work with NULL columns

INSERT INTO Test.Test2 values (2, 'Novo ime', 'Opis novog imena');

select *
from Test.Test2

ALTER TABLE Test.Test2
	ALTER COLUMN opis NVARCHAR(500);
GO

SELECT opis
FROM Test.Test2;

UPDATE Test.Test2
SET opis = NULL
WHERE test_id=1;


ALTER TABLE Test.Test2
	ALTER COLUMN opis NVARCHAR(500) NULL;


UPDATE Test.Test2
SET opis = NULL
WHERE test_id=1;

ALTER TABLE Test.Test2
	ALTER COLUMN opis NVARCHAR(500) NOT NULL;

UPDATE Test.Test2
SET opis = 'Opis'
WHERE test_id=1;

ALTER TABLE Test.Test2
	ALTER COLUMN opis NVARCHAR(500) NOT NULL;

SELECT opis
FROM Test.Test2;

/*2.Modifikovati tabelu Projekat2 ubacivanjem
još jedne kolone za obeležje datum,
koje predstavlja datum angažovanja.
Tip obeležja je DATE.*/
select *
from Projekat2;

ALTER TABLE projekat2
	ADD  datum DATE;

select *
from Projekat2;

--Brisanje postojecih kolona
ALTER TABLE projekat2
	DROP COLUMN  datum;

select *
from Projekat2;

/*Uraditi sledeće modifikacije na tabeli Projekat2:
a) dodati kolonu adresa sa tipom podataka
	VARCHAR dužine 12,
b) promeniti tip kolone adresa u tip
	podataka VARCHAR dužine 30,
c) obrisati kolonu adresa iz tabele.
*/

ALTER TABLE Projekat2
	ADD adresa VARCHAR(12)

ALTER TABLE Projekat2
	ALTER COLUMN adresa VARCHAR(30)

ALTER TABLE Projekat2
	DROP COLUMN adresa

--Promena naziva objekta
sp_rename 'Projekat2','Projekat2Nova';

select *
from Projekat2Nova;

sp_rename 'Projekat2Nova','Projekat2';

--Promena naziva kolone
sp_rename 'Projekat2.datum','datumNovi';

select *
from Projekat2;


--TRUNCATE TABLE
select * from Projekat2;

TRUNCATE TABLE Projekat2;

select * from Projekat2;

--instrukcije za pisanje cistog koda
SELECT OBJECT_ID('Sales.Customer', 'U');
GO

SELECT OBJECT_ID('Test.Test1', 'U');
GO

SELECT OBJECT_ID('Radnik', 'U');
GO

IF OBJECT_ID('Test.Test2', 'U') IS NOT NULL
	DROP TABLE Test.Test2;
GO

-- RECNIK PODATAKA

SELECT *
FROM sys.all_objects
WHERE type='U'
GO

SELECT *
FROM sys.tables
GO

--DESCRIBE TABLE
exec sp_columns radnik

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
Where TABLE_NAME = 'radnik'

----------------------------------------------------------------------------------------------------
-- CAS 02 -- DDL: Integritet podataka


/******* Ogranicnje primarnog kljuca ******/
--koristite bazu [it<br_indeksa>g<godina>] ne [Test]
use [Test]

create schema Test

CREATE TABLE Test.Kategorije(
	id_kategorije int PRIMARY KEY, --kreiranje pri definiciji kolone
	naziv_kategorije varchar(30),
	opis_kategorije varchar(500)
);

DROP TABLE Test.Kategorije;

CREATE TABLE Test.Kategorije(
	id_kategorije int,
	naziv_kategorije varchar(30),
	opis_kategorije varchar(500),
	CONSTRAINT PK_Kategorije_id_kategorije PRIMARY KEY(id_kategorije)--kreiranje sa CONSTRAINT
);

--unos podataka kako bi testirali ogranicenje PRIMARY KEY
insert into Test.Kategorije values (1,'kategorija1', 'opis kategorije 1');
insert into Test.Kategorije values (2,'kategorija2', 'opis kategorije 1');
insert into Test.Kategorije values (3,'kategorija3', 'opis kategorije 3');
insert into Test.Kategorije values (4,'kategorija4', 'opis kategorije 4');
insert into Test.Kategorije values (4,'kategorija44', 'opis kategorije 44');
insert into Test.Kategorije values (NULL,'kategorijaNULL', 'opis kategorije NULL');


select * from Test.Kategorije --proveravamo sta je upisano

drop table Test.Kategorije --pre svakog novog kreiranja brisemo tabelu

--kreiranje tabele bez ogranicenja PK
CREATE TABLE Test.Kategorije(
	id_kategorije int not null, --moramo da dodamo not null ako zelimo kasnije da dodamo PK kroz ALTER TABLE
	naziv_kategorije varchar(30),
	opis_kategorije varchar(500)
	);

insert into Test.Kategorije values (1,'kategorija1', 'opis kategorije 1');
insert into Test.Kategorije values (2,'kategorija1', 'opis kategorije 1');
insert into Test.Kategorije values (2,'kategorija1', 'opis kategorije 1');

-- naknadno dodavanje PK ogranicenje kroz ALTER TABLE
--obratiti paznju na not null(pukasati bez not null--videti gresku)
ALTER TABLE Test.Kategorije
	ADD CONSTRAINT PK_Kategorije_id_kategorije PRIMARY KEY(id_kategorije);
GO

--brisemo podatke iz tabele
truncate table Test.Kategorije

--proveravamo podatke u tabeli
select * from Test.Kategorije

--brisemo citavu tabelu
drop table Test.Kategorije

--kreiranje slozenog kljuca
CREATE TABLE Test.Kategorije(
	id_kategorije int,
	id_kategorije_novi int,
	naziv_kategorije varchar(30),
	opis_kategorije varchar(500),
	CONSTRAINT PK_Kategorije_id PRIMARY KEY (id_kategorije, id_kategorije_novi)
	);

--unos vrednosti u tabelu kako bi proverili ogranicenje slozenog kljuca
insert into Test.Kategorije values (1,1,'kategorija1', 'opis kategorije 1');
select * from Test.Kategorije

insert into Test.Kategorije values (2,2,'kategorija2', 'opis kategorije 2');
insert into Test.Kategorije values (2,2,'kategorija1', 'opis kategorije 1');
insert into Test.Kategorije values (1,2,'kategorija12', 'opis kategorije 12');

-- Iscitavanja PK ogranicenje iz recnika podataka

SELECT *
FROM sys.key_constraints
where type='PK';

/******* Unique ogranicenjev ********/
drop table Test.Kategorije

CREATE TABLE Test.Kategorije(
	id_kategorije int,
	naziv_kategorije varchar(30),
	opis_kategorije varchar(500)
	);

insert into Test.Kategorije values (1,'kategorija1', 'opis kategorije 1');

insert into Test.Kategorije values (1,'kategorija2', 'opis kategorije 1');

insert into Test.Kategorije values (2,'kategorija1', 'opis kategorije 1');

insert into Test.Kategorije values (3,null, 'opis kategorije 3');

ALTER TABLE Test.Kategorije
	ADD CONSTRAINT UQ_ Kategorije_naziv_kategorije UNIQUE(naziv_kategorije);
GO

SELECT * FROM Test.Kategorije

-- DEFAULT "ogranicenje"

drop table Test.Kategorije

CREATE TABLE Test.Kategorije(
	id_kategorije int,
	naziv_kategorije varchar(30),
	opis_kategorije varchar(500),
	velicina_kategorije int DEFAULT (0)
	);

insert into Test.Kategorije values (1,'kategorija1', 'opis kategorije 1', 5);
insert into Test.Kategorije(id_kategorije,naziv_kategorije,opis_kategorije)
					 values (1,'kategorija1', 'opis kategorije 1');
insert into Test.Kategorije(id_kategorije,naziv_kategorije,opis_kategorije,velicina_kategorije)
			values (1,'kategorija1', 'opis kategorije 1',10);

select * from Test.Kategorije

select * from Test.Proizvod

delete from Test.Kategorija
where id_kategorije=3;


SELECT *
FROM sys.default_constraints

-- Check ogranicenje
drop table Test.Kategorije

CREATE TABLE Test.Kategorije(
	id_kategorije int,
	naziv_kategorije varchar(30),
	opis_kategorije varchar(500),
	oznaka_kategorije varchar(1),
	velicina_kategorije int DEFAULT (0),
	CONSTRAINT PK_Kategorije_id_kategorije PRIMARY KEY (id_kategorije),
	CONSTRAINT UQ_Kategorije_naziv_kategorije UNIQUE (naziv_kategorije)
	);

ALTER TABLE Test.Kategorije
	ADD CONSTRAINT CHK_Kategorije_oznaka_kategorije CHECK (oznaka_kategorije>0);
GO

insert into Test.Kategorije values (1,'kategorija1', 'opis kategorije 1', 5);

insert into Test.Kategorije(id_kategorije,naziv_kategorije,opis_kategorije, oznaka_kategorije)
					 values (2,'kategorija2', 'opis kategorije 2','1');

insert into Test.Kategorije(id_kategorije,naziv_kategorije,opis_kategorije, oznaka_kategorije)
					 values (3,'kategorija3', 'opis kategorije 3','1');

SELECT * FROM Test.Kategorije

--PRIMER CHECK OGRANICENJA
--Dodati ogranicenje za pol, da moze da ima
--samo vrednosti M i Z
create table student
(
	id int,
	ime char(20),
	pol char(30),
	CONSTRAINT CHK_Student_Pol check (pol in ('M','Z'))
);

insert into student
values (1, 'Dajana', 'Z');

insert into student
values (2, 'Stefan', 'M');

insert into student
values (3, 'Vanja', null);

insert into student
values (4, 'Ana', 'm');

select * from student

--ispraviti prethodni primer i dozvoliti da mogu
--da se unesu vrednosti koje su velika slova 'M' 'Z'

--PRIMER2 CHECK OGRANICENJA
create table person
(
	person_id int primary key,
	mobile_phone numeric(13),
	phone numeric(13),
	CONSTRAINT CK_Phone_Number CHECK (mobile_phone is not null or phone is not null)
);

insert into person values (1,null,022732233);
insert into person values (2,066254965,null);
insert into person values (3,066258965,022732233);
insert into person values (4,null,null);

select *
from person;

/****** Ogranicenje spoljnog/stranog kljuca  ******/

CREATE TABLE Test.Proizvod(
	id_proizvoda int,
	naziv_proizvoda varchar(30),
	opis_proizvoda varchar(500),
	cena int,
	id_kategorije int,
	CONSTRAINT PK_id_proizvoda PRIMARY KEY (id_proizvoda),
	CONSTRAINT FK_Proizvod_Kategorija FOREIGN KEY (id_kategorije)
			REFERENCES Test.Kategorije(id_kategorije)--on delete set null
													 --on delete cascade
);

select * from Test.Kategorije

insert into Test.Proizvod values (1, 'cokolada', 'mlecna cokolada', 50, 1)
insert into Test.Proizvod values (2, 'cips', 'cips sa ukusom soli', 20, 2)
insert into Test.Proizvod values (3, 'sok', 'sok od limuna', 10, 4)
insert into Test.Proizvod values (3, 'sok', 'sok od limuna', 10, null)

select * from Test.Proizvod

SELECT *
	FROM sys.foreign_keys
WHERE name = 'FK_Products_Categories';


--TABELE KOJE SE MEDJUSOBNO REFERENCIRAJU
--Napisati script koji ce omoguciti kreiranje
--tabele koka koja ima obelezja kokaID i jajeID,
--kao i tabele jaje koja ima obelezja jajeID i kokaID
--sa ogranicenjima referencijalnih integriteta gde
--jaje referencira koku, a koka jaje.
CREATE TABLE koka
(
	kokaID INT PRIMARY KEY,
	jajeID INT,
	CONSTRAINT FC_KOKA_JAJE FOREIGN KEY (jajeID)
		REFERENCES jaje (jajeID)
);
CREATE TABLE jaje
(
	jajeID INT PRIMARY KEY,
	kokaID INT,
	CONSTRAINT FC_JAJE_KOKA FOREIGN KEY (kokaID)
		REFERENCES koka (kokaID)
);
CREATE TABLE koka
(
	kokaID INT PRIMARY KEY,
	jajeID INT
);
CREATE TABLE jaje
(
	jajeID INT PRIMARY KEY,
	kokaID INT
);
ALTER TABLE koka ADD CONSTRAINT FC_KOKA_JAJE
	FOREIGN KEY (jajeID) REFERENCES jaje (jajeID);

ALTER TABLE jaje ADD CONSTRAINT FC_JAJE_KOKA
	FOREIGN KEY (kokaID) REFERENCES koka (kokaID);



INSERT INTO koka VALUES(3, 4);
INSERT INTO jaje VALUES(4, 3);
go


ALTER TABLE koka
	NOCHECK CONSTRAINT FC_KOKA_JAJE;
GO
ALTER TABLE jaje
	NOCHECK CONSTRAINT FC_JAJE_KOKA;
GO

INSERT INTO koka VALUES(1, 2);
INSERT INTO jaje VALUES(2, 1);

ALTER TABLE koka
	CHECK CONSTRAINT FC_KOKA_JAJE;
GO
ALTER TABLE jaje
	CHECK CONSTRAINT FC_JAJE_KOKA;
GO

ALTER TABLE koka
	DROP CONSTRAINT FC_KOKA_JAJE;

ALTER TABLE jaje
	DROP CONSTRAINT FC_JAJE_KOKA;

drop table koka;
drop table jaje;
----------------------------------------------------------------------------------------------------
/*
TIPOVI PROMENLJIVIH i KONVERZIJA TIPOVA
*/
/*
TIPOVI PODATAKA
*/
--EXACT NUMBER               int, numeric
--CHARACTER STRING           char, varchar
--UNICODE CHARACTER STRING   nchar, nvarchar
--APROXIMATE NUMERIC         float, real
							--float truble
							declare @f as float = '29545428.022495';
							select cast(@f as numeric(28,14)) as value;
							--rezultat nije isti kao ulazna vrednost
--BINARY STRING              binary, varbinary
--DATE AND TIME              date, time, datetime2, smalldatetime, datetime, datetimeoffset
--others...

--prezime --razlika izmedju char i varchar
char(30);
varchar(30);
/*
KONVERZIJA TIPOVA
CAST, CONVERTE, PARSE -- TRY_CAST, TRY_CONVERTE, TRY_PARSE
*/
--cast ima izraz i ciljani tip
--primer
SELECT CAST(123 AS int);
SELECT CAST('abc' AS int);
SELECT TRY_CAST('abc' AS int);

--razlika između cast i try_cast
SELECT CAST('abc' AS int);
SELECT TRY_CAST('abc' AS int);--vraca null ukoliko ne može da izvrši konvertovanje

SELECT CAST('02-21-2019' AS date);
SELECT TRY_CAST('21-02-2019' AS date);

--convert ima i treci argument koji predstavlja stil
--primer
SELECT CONVERT(date, '21-02-2019', 105); -- 105: dd-mm-yyyy

--razlika CONVERT and TRY_CONVERT
SELECT CONVERT(date, '21-02-2019', 101); -- 101: mm/dd/yyyy
SELECT TRY_CONVERT(date, '21-02-2019', 101); -- 101: mm/dd/yyyy

SELECT CONVERT(date, '1/2/2019', 101);
SELECT CONVERT(date, '21/2/2019', 101);

--parse definiše i kulturu
--primer
SELECT PARSE('1/2/2019' AS DATE USING 'en-US');

--literal of a type
SELECT CAST(N'123' AS int);--from Unicode charecter strings
SELECT CAST('123' AS int);--regular character strings

--KONVERZIJA /
--isti tipovi
--implicitna konverzija
declare @string char;
set @string='1';
declare @string1 char;
set @string1='1';
select @string+@string1;

--KONVERZIJA +
--razliciti tipovi
--implicitna konverzija
declare @int int;
set @int='1';
declare @string1 char;
set @string1='1';
select @int + @string1;

declare @int3 int;
set @int3='5';
declare @numeric numeric;
set @numeric='2';
select @int3 / @numeric;

--RAD SA TIPOVIMA PODATAKA
/*
DATE AND TIME FUNCTION
*/
--CURENT DATA and TIME
-- Iscitavanje trenutnog vremena i datuma
select GETDATE() as Datum; --datetime type
go
select CURRENT_TIMESTAMP as Trenutak; --standardna i preporucuje se za koriscenje
go
select SYSDATETIME(); --SYSDATETIME vraca DATETIME2, veca preciznost
go

-- Ni jedna od ovih funkcija ne vraca samo datum ili vreme

-- Iscitavanjae samo datuma
select CAST(GETDATE() as DATE);
go
-- Iscitavanje samo vremena
select CAST(GETDATE() as TIME);
go

-- DATE i TIME delovi
-- DATEPART funkcija, vraca integer DATEPART(deo, date), drugi parametar mora biti tip podatka date
-- oznake delova su: year, month, day
select DATEPART(year,'11-10-2019'); --select DATEPART(year,'11/10/2017'); --select DATEPART(year,'11102017');
go
select DATEPART(month,'11-10-2019');
go
select DATEPART(day,'11-10-2019');
go

-- Funkcija DATENAME() vraca ime dela kao string
select DATENAME(month,'11-10-2019');
go

-- dw = day of week
select DATENAME(dw,'11-10-2019');
go

--zavisi od jezika u sesiji
--SET LANGUAGE { [ N ] 'language' | @language_var }
SET LANGUAGE Italian;
go

--vracamo na engleski
SET LANGUAGE us_english;
go

--kreiranje datuma od brojeva
--DATEFROMPARTS(2012,02,12) vraca date
SELECT DATEFROMPARTS(2019,02,13);
go

-- Koliko dana ima mesec? EOMONTH()
--funkcija vraca poslednji dan u mesecu
select EOMONTH('02-02-2019');
go

--ADD I DIFF
-- Aritmetika sa datumima. DATEADD()
--funkcija dodaje celi broj delova datuma na ulazni datum
select DATEADD(year,2,'02-02-2019');
go
select DATEADD(month,14,'02-02-2019');
go

-- DATEDIFF() funkijca vraca razliku izmedju dva datuma
-- broj dana
select DATEDIFF(day,'02-02-2019','01-01-2018');
go
select DATEDIFF(day,'02-02-2018','01-01-2019');
go
-- broj meseci
select DATEDIFF(month,'02-02-2018','01-01-2019');
go
select DATEDIFF(month,'20180202','20190101'); --kada prosledjujemo drugaciji format datuma
go
-- broj godina
select DATEDIFF(year,'02-02-2018','01-01-2019');
go

--kod broja godina treba biti pazljiv jer prebrajamo samo godine
select DATEDIFF(year,'20111231','20120101');
go
select DATEDIFF(year,'12-31-2011','02-01-2012');
go

-- Funkcije za rad sa karakterima
-- KONKATENACIJA stringova moguca je
--koriscenjem + operatora ili CONCAT() funkcije

select mbr, nar, nar + N' ' + ime + N' ' + nap
                + N'. ' + prz as "pun naziv"
from radnik
left join projekat on radnik.mbr=projekat.ruk;
go

-- + operator vraca NULL marker ako je jedan od stringova NULL
--standardno ponasanje koje je moguce promeniti
PRINT 'Setting CONCAT_NULL_YIELDS_NULL ON';
GO

-- SET CONCAT_NULL_YIELDS_NULL ON and testing.
SET CONCAT_NULL_YIELDS_NULL ON;
GO

SELECT 'abc' + NULL ;
GO

-- SET CONCAT_NULL_YIELDS_NULL OFF and testing.
SET CONCAT_NULL_YIELDS_NULL OFF;
GO
SELECT 'abc' + NULL;
GO
--vratiti na pocetno stanje--default
SET CONCAT_NULL_YIELDS_NULL ON;
GO


-- koristiti COALESCE() funkciju koja menja NULL marker sa onim sto prosledimo kao drugi parametar
select mbr, nar,
COALESCE(nar,'') + N' ' + ime + N' ' + COALESCE(nap,'nap') + N'. ' + prz as "pun naziv"
from radnik
left join projekat on radnik.mbr=projekat.ruk;
go

-- Upotreba CONCAT() funkcije
select mbr, nar,
CONCAT(nar, ' ', ime, ' ', nap, '. ', prz) as "pun naziv"
from radnik
left join projekat on radnik.mbr=projekat.ruk;
go
-- CONCAT() funkcija zamenjuje NULL marker sa praznim stringom, za raliku od + operatora

-- Podstringovi
-- SUBSTRING()funkcija izdvaja podstring od pocetne pozicije sa zadatom duzinom
select SUBSTRING('abcde',2,4);
go
select SUBSTRING('abcde',2,6);
go

-- alternativno mogu se koristiti LEFT() i RIGHT() funkcije
select LEFT('abcde',2);
go
select RIGHT('abcde',2);
go
select LEFT('  abcde',2);
go

-- uklanjanje praznina
-- LTRIM(), RTRIM()
select LTRIM('  abcde ');
go
select RTRIM('  abcde ');
go

-- ocistimo praznine pa izdvojimo prva dva karaktera
select LEFT(LTRIM('  abcde'),2);
go

-- Pronalazenje podstringa u stringu
-- CHARINDEX()
select CHARINDEX(' ','Dr. Nikolic Miladin');
go


--koristeci prethodno spomenute funkcije izdvojiti
--"titulu" u prosledjenom stringu
-- cesto se CHARINDEX() koristi u kombinaciji
--sa trim ili podstring funkcijama
select LEFT('Dr. Nikolic Miladin',
CHARINDEX('.','Dr. Nikolic Miladin'));
go

--izdvojiti samo ime iz punog imena i prezimena
--koristeci samo funkciju left i carindex
declare @fullname varchar(20);--ako ne napisem duzinu podrazumeva se da je 1
set @fullname= 'Mirko Mirkovic';
select @fullname;
select LEFT(@fullname, CHARINDEX(' ', @fullname)-1);

-- odredjivanje duzine stringa
-- LEN() i DATALENGTH()
-- LEN() vraca duzinu stringa kao broj karaktera a ne kao broj bajtova
select LEN('abcd');
go
select LEN(N'abcd');
go
select LEN('abcd    ');--razmak ne broji kao karakter
go
select LEN(N'abcd  ');--razmak ne broji kao karakter cak ni kada je unicode
go

select DATALENGTH('abcd');
go
select DATALENGTH(N'abcd');
go
select DATALENGTH('abcd   ');
go
select DATALENGTH(N'abcd   ');
go

-- Promena stringa
-- REPLACE(), REPLICATE(), STUFF()
-- zamena bilo kog podstringa sa novim
select REPLACE('.1.2.3.', '.', '/');
go
select REPLACE('.1.2.3.', ';', '/');
go

-- generisanje stringova kopiranjem originala
select REPLICATE('OK ',10);
go

-- brisanje delova stringova i zamena sa drugim stringom
select STUFF('x,y,z', 3, 1, 'O');
go
select STUFF('x,y,z', 1, 2, '');
go

--UPPER, LOWER, LTRIM, RTRIM, FORMAT
select UPPER('ok');
select LOWER('OK');
select LTRIM('   ok   ');
select RTRIM('   ok   ');

--ne postoji funkcija koja odseca prazne stringove
--i sa leve i sa desne strane odjednom
select LTRIM(RTRIM('     ok      '));

-- CASE iskaz
-- CASE iskaz se koristi kada zelimo da
--primenimo uslovnu logiku prilikom iscitavanja vrednosti

-- jednostavan oblik CASE iskaza:
select prz, ime,
	case sodel
		when 0 then 'ne radi nigde'
		when 1 then 'Radi u jedinici'
		when 2 then 'Radi u dvojci'
		when 3 then 'Radi u trojci'
		else 'Uknown'
	end as odeljenje
from radnik;
go

-- searched oblik CASE iskaza
select ime, plt,
	case
		when plt < 1000 then 'Low'
		when plt >= 1000 and plt < 50000 then 'Medium'
		when plt > 5000 then 'High'
		else 'Unknown'
	end as Plata
from radnik;
go

-- COALESCE i ISNULL
declare
@x as VARCHAR(3) = NULL,
@y as VARCHAR(10) = '1234567890';

select COALESCE(@x, @y) as [COALESCE],
ISNULL(@x, @y) as [ISNULL];

-- OBRATITI PAZNJU KOJI TIP JE VRACEN!

-- Zadatak 1. Iscitati trenutni Datum u sledecim
--formatima: dd.mm.yyyy, yyyy/mm/dd, dd-mm-yyyy
DECLARE @D DATE = SYSDATETIME();
SELECT FORMAT (@D, 'dd.MM.yyyy', 'en-US') as 'date'
DECLARE @D2 DATE = SYSDATETIME();
SELECT FORMAT (@D2, 'dd-MM-yyyy', 'en-US') as 'date'
DECLARE @D3 DATE = SYSDATETIME();
SELECT FORMAT (@D3, 'yyyy/MM/dd', 'en-US') as 'date'
GO

DECLARE @now DATE = CURRENT_TIMESTAMP;
SELECT CONVERT(varchar, @now, 104),
CONVERT(varchar, @now, 111),
CONVERT(varchar, @now, 105)
GO

-- Zadatak 2. Prikazati Ime i Prezime i datume rodjenja
--svih radnika koji su rodjeni izmedju
--1-og i 10-og u mesecu.

-- Zadatak 3. Prebrojati koliko je radnika rodjeno
--na svaki dan u nedelji. U kom danu
--ima najvise rodjenih radnika?

-- Zadtak 4. Izlistati imena i prezimena
--i datume rodjenja radnika koji su rodjeni
--na poslednji dan u mesecu.

-- Zadatak 5. Prikazati broj radnika po godinama rođenja.

-- Zadatak 6. Za sve zaposlene iz tabele radnik izlistati ime,
--prezime, user name i emali, koji treba da budu u formatu,
-- username (prvo slovo imena + prezime + godina rodjenja
--(sve malim slovima)) Email adresa je username@mail.com
----------------------------------------------------------------------------------------------------
use [Test]
--kreirati schemu
create schema Test;

--pre svakog ponovnog kreiranja tabele obavezno obrisati postojecu
drop table Test.Student

create table Test.Student(
	id int identity, --default za identity je (1,1)
					--prvi broj oznacava da pocinjem od 1,
					--a drugi da se uvecava za 1
	broj_indeksa varchar(30),
	ime varchar(30),
	prezime varchar(30),
	godine numeric(2)
)

--pre i posle inserta proveriti sta se nalazi u tabeli
select  from Test.Student

--uneti nekoliko torki(obatiti paznju da ne unosimo vrednosti za id)
insert into Test.Student values ('gg34g2012', 'Marko', 'Markovic', 19)
insert into Test.Student values ('gg22g2012', 'Milan', 'Milanovic', 20)
insert into Test.Student values ('gg33g2012', 'Ivana', 'Vasic', 20)

--select koji vraca trenutne vrednosti za identity
select
SCOPE_IDENTITY() as [SCOPE_IDENTITY],
@@IDENTITY as [@@IDENTITY],
ident_current('Test.Student') as [ident_current];

--pokusati sledeci insert(torka nece biti upisana u tabelu jer ne zadovoljava sva ogranicenja)
insert into Test.Student values ('gg44g2012', 'Dragana', 'Vukosic', 'broj godina')

--pokrenuti sledeci insert
insert into Test.Student values ('gg44g2012', 'Dragana', 'Vukosic', 20)

--proveriti da li je torka uneta i koja je vrednost za id(zasto je tako)
select  from Test.Student


--pokusati sve kao i sa prethodnim primerom
drop table Test.Student

create table Test.Student(
	id int identity(5,4),--identity koji pocinje od 5 i uvecava se za 4
	broj_indeksa varchar(30),
	ime varchar(30),
	prezime varchar(30),
	godine numeric(2)
)

select  from Test.Student

insert into Test.Student values ('gg34g2012', 'Marko', 'Markovic', 19)
insert into Test.Student values ('gg22g2012', 'Milan', 'Milanovic', 20)
insert into Test.Student values ('gg33g2012', 'Ivana', 'Vasic', 20)

select
SCOPE_IDENTITY() as [SCOPE_IDENTITY],
@@IDENTITY as [@@IDENTITY],
ident_current('Test.Student') as [ident_current];

insert into Test.Student values ('gg44g2012', 'Dragana', 'Vukosic', 'broj godina')

insert into Test.Student values ('gg44g2012', 'Dragana', 'Vukosic', 20)
select  from Test.Student

--unos explicitnih vrednosti za kolonu id
set identity_insert Test.Student on--setovanje identity_insert
insert into Test.Student(id, broj_indeksa,ime,prezime,godine)
values (34,'gg34g2012', 'Marko', 'Markovic', 19)
insert into Test.Student(id, broj_indeksa,ime,prezime,godine)
values (35,'gg22g2012', 'Milan', 'Milanovic', 20)
insert into Test.Student(id, broj_indeksa,ime,prezime,godine)
values (36,'gg33g2012', 'Ivana', 'Vasic', 20)

select  from Test.Student

set identity_insert Test.Student off --vratiti na defaultno stanje

--obrisati sve podatke iz tabele sa truncate
truncate table Test.Student

--proveriti vrednosti za identity
select
SCOPE_IDENTITY() as [SCOPE_IDENTITY],
@@IDENTITY as [@@IDENTITY],
ident_current('Test.Student') as [ident_current];

--ponovo uraditi inserte u tabelu
insert into Test.Student values ('gg34g2012', 'Marko', 'Markovic', 19)
insert into Test.Student values ('gg22g2012', 'Milan', 'Milanovic', 20)
insert into Test.Student values ('gg33g2012', 'Ivana', 'Vasic', 20)


--obrisati sve podatke iz tabele sa delete
delete from Test.Student

--proveriti vrednosti za identity
select
SCOPE_IDENTITY() as [SCOPE_IDENTITY],
@@IDENTITY as [@@IDENTITY],
ident_current('Test.Student') as [ident_current];


--resetovanje vrednosti za identity
dbcc checkident('Test.Student', reseed, 4)
dbcc checkident('Test.Student', reseed, 10)



SEKVENCE

--minimalna sintaksa za kreiranje sekvence
--create sequence schema.sequenca
create sequence Test.ID

--INCREMENT BY Increment value. The default is 1.
--MINVALUE The minimum value to support. The default is the minimum value in the type. For example, for an INT type, it will be -2147483648.
--MAXVALUE The maximum value to support. The default is the maximum value in the type.
--CYCLE  NO CYCLE Defines whether to allow the sequence to cycle or not. The de-fault is NO CYCLE.
--START WITH The default is MINVALUE for an ascending sequence (positive increment) and MAXVALUE for a descending one.

--brisanje sekvence
drop sequence Test.ID

--kreiranje sekvence
create sequence Test.ID as int --tipa int
start with 1 --pocinje od
minvalue 1 --minimalna vrednost sekvence
increment by 2 --uvecava se za 2
cycle --ciklicna je

--citanje vrednosti sekvence
select next value for Test.ID

--izmena definicije sekvence, kroz alter
alter sequence Test.ID

--restartovanje vrednosti sekvence na neki broj
alter sequence Test.ID
	restart with 2147483647


--napraviti tabelu koja nema nikakvih dodatnih ogranicenja
drop table Test.Student
create table Test.Student(
	id int,
	broj_indeksa varchar(30),
	ime varchar(30),
	prezime varchar(30),
	godine numeric(2)
)

--upisati nekoliko torki koristeci prethodno kreiranu sekvencu
insert into Test.Student values
(next value for Test.ID, 'hh45g2015', 'Miroslav', 'Mirkovic', 25)

select  from Test.Student

insert into Test.Student values
(next value for Test.ID, 'hh45g2015', 'Miroslav', 'Mirkovic', 25)

insert into Test.Student values
(next value for Test.ID, 'hh45g2015', 'Miroslav', 'Mirkovic', 25)

insert into Test.Student values
(next value for Test.ID, 'hh45g2015', 'Miroslav', 'Mirkovic', 25)

--dodavanje sekvence u definiciju tabele kroz default ogranicenje
ALTER TABLE Test.Student
	ADD CONSTRAINT DFT_Student_id DEFAULT (next value for Test.ID) for id;

--pokusati nove inserte u tabelu
insert into Test.Student(broj_indeksa,ime,prezime,godine) values ('jj58g2016', 'Ana', 'Nikolic', 22)

--proveriti da li se koristi sekvenca
select  from Test.Student

--ukoliko se ne ogranici dodatno moguce je uneti eksplicitne vrednosti za kolonu id
insert into Test.Student(id, broj_indeksa,ime,prezime,godine) values (55,'jj58g2016', 'Ana', 'Nikolic', 22)


--citanje podataka o kreiranim sekvencama
select
from sys.sequences