Код

use master;

create database AfishaEvents;

use AfishaEvents;

create table Country

(

 CountryID int primary key identity(1,1),

 Country varchar(32)

);

go

create table City

(

 CityID int primary key identity(1,1),

 City varchar(32),

 CountryID int foreign key references Country(CountryId)

);

go

create table Place

(

 PlaceID int primary key identity(1,1),

 Place varchar(32),

 CityID int foreign key references City(CityID)

);

create table EventKategory

(

 KategoryID int primary key identity(1,1),

 KategoryName varchar(32)

);

go

create table [Event]

(

 EventID int primary key identity(1,1),

 EventName varchar(32),

 StartDate datetime2,

 EndDate datetime2,

 PlaceID int foreign key references Place(PlaceID),

 KategoryID int foreign key references EventKategory(KategoryID),

 [Description] varchar(256) not null,

 PG int not null,

 ImageLink nvarchar(128),

 MNoT int,

 PurchasedTicket int default 0

);

go

create table Client

(

 ClientID int primary key identity(1,1),

 FullName varchar(64),

 Email varchar(32),

 DoB datetime2

);

go

create table Ticket

(

 TicketID int primary key identity(1,1),

 ClientID int foreign key references Client(ClientID),

 EventID int foreign key references [Event](EventID),

 Price money

);

go

create table Archive

(

 RiHID int primary key identity(1,1),

 RiH varchar(32),

 TicketID int foreign key references Ticket(TicketID),

 ClientName varchar(64),

 Place varchar(32),

 EventName varchar(32),

 EventDate datetime2,

 Price money

);

go

insert into EventKategory(KategoryName)

values

 ('Спектакль'),

 ('Концерт'),

 ('Выставка'),

 ('Цирк'),

 ('Спорт'),

 ('Семинары и тренинги'),

 ('Кино'),

 ('Юмор'),

 ('Вечеринки'),

 ('Детям'),

 ('Другое')

go

insert into Country(Country)

values

 ('Украина')

go

insert into City(City, CountryID)

values

 ('Kiev', (select CountryID from Country where Country = 'Украина'))

go

insert into Place(Place, CityID)

values

 ('Симферопольская 13/4', (select CityID from City where City = 'Kiev'))

go

insert into Client(FullName, Email, DoB)

values

 ('Сухоребрый Олександр','example1@test.net', '01-01-2000'),

 ('Омельченко Олександр','example2@test.net','01-01-2000'),

 ('Холодный Максим','example3@test.net','01-01-2000'),

 ('Соломко Ярослав','example4@test.net','01-01-2000'),

 ('Ермоленко Валерия','example5@test.net','01-01-2000'),

 ('Чубаков Валерий','example6@test.net', '01-01-2000')

go

insert into [Event](EventName, StartDate, PlaceID, KategoryID, [Description], PG, ImageLink, MNoT)

values

 ('Imagine Dragons - EVOLVE', '08-05-2017', 

 (select PlaceID from Place where Place = 'Симферопольская 13/4'),

 (select KategoryID from EventKategory where KategoryName = 'Концерт'),

 'Это просто лучший концерт', 16,

 'https://www.imaginedragonsmusic.com/sites/g/files/aaj7266/f/favicon_1.png',

 3500)

go

CREATE TRIGGER TicketBuy

ON Ticket

AFTER INSERT

AS

update [Event]

insert into Ticket(EventID, ClientID, Price)

values

 ((select EventID from [Event] where EventName = 'Imagine Dragons - EVOLVE'),

 (select ClientID from Client where FullName = 'Соломко Ярослав'),

 300)

select * from Ticket;

use master;

create database EventPoster;

go

use EventPoster;

go

create table Country

(

 CountryID int primary key identity(1,1),

 Country varchar(32)

);

go

create table City

(

 CityID int primary key identity(1,1),

 City varchar(32),

 CountryID int foreign key references Country(CountryId)

);

go

create table Place

(

 PlaceID int primary key identity(1,1),

 Place varchar(32),

 CityID int foreign key references City(CityID)

);

create table EventKategory

(

 KategoryID int primary key identity(1,1),

 KategoryName varchar(32)

);

go

create table [Event]

(

 EventID int primary key identity(1,1),

 EventName varchar(32),

 StartDate datetime2,

 EndDate datetime2,

 PlaceID int foreign key references Place(PlaceID),

 KategoryID int foreign key references EventKategory(KategoryID),

 [Description] varchar(256) not null,

 PG int not null,

 ImageLink nvarchar(128),

 MNoT int,

 PurchasedTicket int default 0

);

go

create table Client

(

 ClientID int primary key identity(1,1),

 FullName varchar(64),

 Email varchar(32),

 DoB datetime2

);

go

create table Ticket

(

 TicketID int primary key identity(1,1),

 ClientID int foreign key references Client(ClientID),

 EventID int foreign key references [Event](EventID),

 Price money

);

go

create table Archive

(

 RiHID int primary key identity(1,1),

 RiH varchar(32),

 TicketID int foreign key references Ticket(TicketID),

 ClientName varchar(64),

 Place varchar(32),

 EventName varchar(32),

 EventDate datetime2,

 Price money

);

go

insert into EventKategory(KategoryName)

values

 ('Спектакль'),

 ('Концерт'),

 ('Выставка'),

 ('Цирк'),

 ('Спорт'),

 ('Семинары и тренинги'),

 ('Кино'),

 ('Юмор'),

 ('Вечеринки'),

 ('Детям'),

 ('Другое')

go

insert into Country(Country)

values

 ('Украина')

go

insert into City(City, CountryID)

values

 ('Kiev', (select CountryID from Country where Country = 'Украина'))

go

insert into Place(Place, CityID)

values

 ('Симферопольская 13/4', (select CityID from City where City = 'Kiev'))

go

insert into Client(FullName, Email, DoB)

values

 ('Сухоребрый Олександр','example1@test.net', '01-01-2000'),

 ('Омельченко Олександр','example2@test.net','01-01-2000'),

 ('Холодный Максим','example3@test.net','01-01-2000'),

 ('Соломко Ярослав','example4@test.net','01-01-2000'),

 ('Ермоленко Валерия','example5@test.net','01-01-2000'),

 ('Чубаков Валерий','example6@test.net', '01-01-2000')

go

insert into [Event](EventName, StartDate, PlaceID, KategoryID, [Description], PG, ImageLink, MNoT)

values

 ('Imagine Dragons - EVOLVE', '08-05-2017', 

 (select PlaceID from Place where Place = 'Симферопольская 13/4'),

 (select KategoryID from EventKategory where KategoryName = 'Концерт'),

 'Это просто лучший концерт', 16,

 'https://www.imaginedragonsmusic.com/sites/g/files/aaj7266/f/favicon_1.png',

 3500)

go

CREATE TRIGGER TicketBuy1

ON Ticket

AFTER INSERT

AS

alter table [Event](PurchasedTicket)

values

(select count(TicketID)

from Ticket

where EventID = 'Imagine Dragons - EVOLVE'))

insert into Ticket(EventID, ClientID, Price)

values

 ((select EventID from [Event] where EventName = 'Imagine Dragons - EVOLVE'),

 (select ClientID from Client where FullName = 'Соломко Ярослав'),

 300)

 go

create trigger OnInsertClient

on [Client] 

FOR INSERT

as

 if (SELECT COUNT (*) FROM [Client] C inner join inserted I ON C.FullName =I.FullName)>1

 BEGIN

 PRINT'Вы не можете добавить клиента. Такой аккаунт уже существует'

 ROLLBACK TRANSACTION

END

go

create trigger OnInsertEvent

on [Event] 

FOR INSERT

as

 if (SELECT COUNT (*) FROM [Event] C inner join inserted I ON C.EventName =I.EventName)>1

 BEGIN

 PRINT'Вы не можете добавить ивент. Такой ивент уже существует'

 ROLLBACK TRANSACTION

END

go

insert into Client(FullName)

values

('Сухоребрый Олександр')

go

select * from Client;

go

create proc ActualEvent @Actual varchar(32)

as

begin

select EventName, StartDate, EndDate

from [Event] as t, EventKategory as t2

where GETDATE() > StartDate and GETDATE() <t.EndDate and (select KategoryName from EventKategory, [Event] where t2.KategoryID = t.KategoryID) = @Actual 

end;

go

create proc ActualEventData @Actual datetime2

as

begin

select EventName, StartDate, EndDate

from [Event] as t

where @Actual > StartDate and EndDate > @Actual 

end;

go

create proc ActualEventTicket @Actual datetime2

as

begin

select EventName, StartDate, EndDate

from [Event]

where @Actual > StartDate and EndDate > @Actual and MNoT = PurchasedTicket

end;

go

declare @v varchar(32) = 'Концерт'

exec ActualEvent @v

go

declare @v datetime2 = '08-05-2017'

exec ActualEventData @v

go

drop proc ActualEventData;

go

select * from 

[Client];

go

CREATE NONCLUSTERED COLUMNSTORE INDEX MyIndex1 on Client(FullName)

go

drop table Ticket

drop table Place

drop table EventKategory

drop table [Event]

drop table Country

drop table Client

drop table City

drop table Archive