•	Креирање на нова база
create database DbTest


•	Команда за префрлање на соодветна база
use DbTest


•	Креирање на табела
create table Users
(
	Id bigint primary key identity(1,1)
	,Name nvarchar(200)
	,UserName Varchar(30)
	,[Password] Varchar(30)
	,CashAccountId Bigint
	,ClientId Bigint
)

create table Clients
(
	Id bigint primary key identity(1,1)
	,Name Nvarchar(200)
	,[Type] Bit
	,IdentificationNumber Varchar(36)
	,[Address] Varchar(100)
	,Tel Varchar(30)
	,Mob Varchar(30)
	,Email Varchar(30)
	,Web Varchar(100)
)

•	Измена на ClientId колоната од Users табелата да е надворешен клуч кон Clients табелата
alter table Users add constraint clientId_foreignKey foreign key (ClientId) references Clients(Id)


•	Додавање на записи во Users табелата преку select команди
insert into Users(Name,UserName,[Password],ClientId)
select 'user3','username3','3df2432',null

•	Додавање на записи во Users табелата преку values синтакса
insert into Users(Name,UserName,[Password],ClientId)
values('user1','username1','32432',null)



•	Команди за работа со множество на податоци (унија, пресек, разлика)
select 'user3','username3','3df2432',null
union
select 'user4','username4','3df2432',null

select 'user3','username3','3df2432',null
intersect
select 'user4','username4','3df2432',null

select 'user3','username3','3df2432',null
except
select 'user4','username4','3df2432',null





•	Приказ на сите податоци од Users табелата
select * from Users


create table Currencies
(
	Currency Varchar(3)
	,Name Varchar(30)
	,Symbol Nvarchar(5)
	,[Image] Varbinary(8000)
)


•	Прогласување на табела на колона за примарен клуч.
	- прво се додава constraint да не може колоната да прима NULL вредност
alter table Currencies alter column Currency Varchar(3) NOT NULL
alter table Currencies add  Primary key (Currency)


•	Бришење на колона од табела
alter table Currencies drop column Symbol


•	Додавање на колона во табела
alter table Currencies add Symbol Nvarchar(5)

alter table Users add constraint clientId_foreignKey foreign key (ClientId) references Clients(Id)


•	Приказ на дефиниција на Currencies табела (колони, податочен тип на колона, identity колони, constraints, индекси...)
sp_help 'dbo.Currencies'


select * from Currencies

•	Unique, Check constarint и calculated field
create table AccountClasses
(
	Id Bigint primary key identity(1,1)
	,Name Varchar(30)
	,[Description] Nvarchar(100)
	,[Type] Int unique check([Type] in (1,2,3,4))
	,IsCashAccount as case when [Type] = 1 then 1 else 0 end
	,IsCurrentAccount as case when [Type] = 2 then 1 else 0 end
	,IsDepositAccount as case when [Type] = 3 then 1 else 0 end
	,IsCreditAccount as case when [Type] = 4 then 1 else 0 end
)


sp_help 'dbo.AccountClasses'

insert into AccountClasses(Name,[Description],[Type])
values ('AccClass1','AccClass1Desc',2), ('AccClass2','AccClass2Desc',3)

select * from AccountClasses


•	Бришење на конкретен запис од табела
delete from AccountClasses
where Id  = 2


•	Бришење на податоците од цела табела
truncate table AccountClasses


•	Бришење на табела
drop table AccountClasses


create table AccountTypes
(
	Id Bigint primary key identity(1,1)
	,ClassId bigint foreign key references AccountClasses(Id)
	,Name Varchar(30)
	,[Description] Nvarchar(100)
)


create table Accounts
(
	Id Bigint primary key identity(1,1)
	,TypeId bigint foreign key references AccountTypes(Id)
	,AccountNumber Varchar(30) unique
	,Currency varchar(3) foreign key references Currencies(Currency)
	,ClientId bigint foreign key references Clients(Id)
	,[Status] Varchar(1) check ([Status] in ('A','O','C'))
	,Blocked bit
	,Balance Decimal(19,2)
	,OverdraftLimit Decimal(19,2)
	,AmountOnHold Decimal(19,2)
	,AvailableBalance as Balance+OverdraftLimit-AmountOnHold
)


alter table Users add constraint cashAccountId_foreignKey foreign key (CashAccountId) references Accounts(Id)

•	Користење на subquery
select * from AccountTypes
where ClassId in (select Id from AccountClasses where Name='')



•	Примена на IN оператор (филтер по множество на податоци)
select * from AccountTypes
where ClassId in (1,5,4)


•	Примена на order by (подредување на податоци)
select * from AccountClasses
order by [Type] asc


•	Промена на запис во табела
update AccountClasses
set [Type] = 4
where Id = 4

update Accounts
set OverdraftLimit = OverdraftLimit+5


•	JOIN на табели (join, left join, right join, full join)
select * from AccountClasses ac
join AccountTypes at on ac.Id = at.ClassId


•	Копирање од една во друга табела
select * into AccountClasses2 from AccountClasses
where 1=0


•	Враќа првиот податок кој не е NULL (може да прима повеќе параметри)
SELECT coalesce(Name,Username)
from Users


•	Ако првиот параметар не е NULL, го враќа параметарот, во спротивно го враќа вториот параметар
SELECT isnull(Name,'default')
from Users


•	Враќа NULL ако првиот параметар е ист со вториот
select nullif(Symbol,'s'),* from Currencies


•	Спојување на текстуални податоци
select  'asdsad'+'fdas'


•	Групирање и филтрирање на податоци
select Name, min([TYPE]), [Description] from AccountClasses
group by Name,[Description]
having min([TYPE]) >1




•	Користење на LIKE операторот
select  * from AccountClasses
where Name like '%2%'

% _ []


•	Работа со datetime податочни типови
declare @p1 datetime

set @p1 = GETDATE()	--datetime
select @p1
select CAST(GETDATE() as date)

declare @p1 datetime
set @p1 = GETDATE()
select DATEPART(yyyy,@p1)


insert into dbo.AccountTypes(ClassId, Name, [Description])
values (1, 'type1', 'desc1'),(4, 'type2', 'desc2'),(1, 'type3' ,'desc3')

insert into dbo.Accounts(TypeId, AccountNumber, Currency, ClientId, [Status], Blocked,Balance, OverdraftLimit, AmountOnHold)
values (null, '123abcww2r1', 'EUR', null, 'A',0,null, 150, 200)

update Currencies
set Symbol = 'd'
where Currency = 'CHF'

insert into Currencies
values ('USD','Dolari',null,null)

select coalesce(Currency,'EUR'),* from Accounts
select * from Currencies

select * from Accounts a
join Currencies c on a.Currency = c.Currency

update Accounts
set Currency = 'EUR'
where Id = 3

create table TransactionTypes
(
	Id Bigint primary key identity(1,1)
	,Name Varchar(30)
	,[Description] Nvarchar(100)
	,AccountClassDebit bigint foreign key references AccountClasses(Id)
	,AccountClassCredit bigint foreign key references AccountClasses(Id)
	,ReflectsAmountOnHold bit default 0
	,HasFee bit
)




create table Transactions
(
	Id Bigint primary key identity(1,1)
	,TransactionTypeId bigint foreign key references TransactionTypes(Id)
	,UserId bigint foreign key references Users(Id)
	,EntryDate Date default getdate()
	,[Date] Date
	,Currency varchar(3) foreign key references Currencies(Currency)
	,AccountIdDebit Bigint foreign key references Accounts(Id)
	,AccountIdCredit Bigint foreign key references Accounts(Id)
	,Fee Decimal(19,2)
	,Amount Decimal(19,2)
	,[Description] Nvarchar(100)
)

go

create table PostingEntries
(
	Id Bigint primary key identity(1,1)
	,TransactionId bigint foreign key references Transactions(Id)
	,AccountId bigint foreign key references Accounts(Id)
	,DebitCredit SmallInt check (DebitCredit in (-1,0,1))
	,ContraAccountId bigint foreign key references Accounts(Id)
	,Currency varchar(3) foreign key references Currencies(Currency)
	,Amount Decimal(19,2)
	,StatementNo int
)