b1vx0XXZ

1use master 
2GO 
3
4IF EXISTS ( 
5SELECT name 
6FROM sys.databases 
7WHERE name = N'[ AMitrofanova]' 
8) 
9ALTER DATABASE AMitrofanova set single_user with rollback immediate 
10GO 
11
12IF EXISTS ( 
13SELECT name 
14FROM sys.databases 
15WHERE name = N'AMitrofanova' 
16) 
17DROP DATABASE AMitrofanova 
18GO 
19
20IF OBJECT_ID('AMitrofanova') IS NOT NULL 
21DROP DATABASE AMitrofanova 
22GO 
23
24CREATE DATABASE AMitrofanova 
25GO 
26
27USE AMitrofanova 
28GO 
29
30
31CREATE TABLE Posts 
32( 
33IdPost int NOT NULL, 
34Name_Post nvarchar(80) NULL, 
35CONSTRAINT Id_Post PRIMARY KEY (IdPost) 
36) 
37GO 
38
39
40CREATE TABLE NameRegion 
41( 
42Number_Region nvarchar(3) NOT NULL, 
43Name_Region nvarchar(80) NOT NULL, 
44CONSTRAINT Number_Region PRIMARY KEY (Number_Region) 
45) 
46GO 
47
48CREATE TABLE Owners 
49( 
50Surname nvarchar(80) NOT NULL, 
51Penalty int, 
52CONSTRAINT S_name PRIMARY KEY (Surname), 
53) 
54GO 
55
56CREATE TABLE NumbersRegion 
57( 
58Main_Number nvarchar(3) NOT NULL, 
59Number nvarchar(3) NOT NULL, 
60CONSTRAINT NumberReg PRIMARY KEY (Number), 
61CONSTRAINT Region FOREIGN KEY (Main_Number) REFERENCES NameRegion(Number_Region), 
62) 
63GO 
64/* 
65CREATE TABLE Cars 
66( 
67Numb_Car nvarchar(6) NOT NULL, 
68Numb_Region nvarchar(3) NOT NULL, 
69Mark nvarchar(20) NOT NULL, 
70Color nvarchar(40) NOT NULL, 
71CONSTRAINT CarNumber PRIMARY KEY (Numb_Car, Numb_Region), 
72CONSTRAINT Number_Reg FOREIGN KEY (Numb_Region) REFERENCES NumbersRegion(Number) 
73) 
74GO */ 
75
76
77CREATE TABLE Journal 
78( 
79Id_Post int NOT NULL, 
80Number_Car nvarchar(6) NOT NULL CHECK (Number_Car LIKE 
81N'[АВЕКМНОРСТУХавекмнорстух][0-9][0-9][0-9][АВЕКМНОРСТУХавекмнорстух][АВЕКМНОРСТУХавекмнорстух]' and 
82Number_Car != N'[АВЕКМНОРСТУХавекмнорстух]000[АВЕКМНОРСТУХавекмнорстух][АВЕКМНОРСТУХавекмнорстух]'), 
83Number_Region nvarchar(3) NOT NULL CHECK (Number_Region LIKE N'[0-9][0-9]' or Number_Region LIKE N'[127][0-9][0-9]'), 
84Owner_Car nvarchar(80) NOT NULL, 
85Mark nvarchar(20) NOT NULL, 
86Color nvarchar(40) NOT NULL, 
87Time_transit datetime NOT NULL, 
88Direction nvarchar(40) NOT NULL, 
89CONSTRAINT Post FOREIGN KEY (Id_Post) REFERENCES Posts, 
90CONSTRAINT Num_Reg FOREIGN KEY (Number_Region) REFERENCES NumbersRegion, 
91--CONSTRAINT Car_Number FOREIGN KEY (Number_Car, Number_Region) REFERENCES Cars(Numb_Car, Numb_Region), 
92CONSTRAINT OwnerС FOREIGN KEY (Owner_Car) REFERENCES Owners (Surname), 
93) 
94GO 
95
96
97CREATE TRIGGER checkNumber 
98ON Journal 
99FOR INSERT 
100AS IF EXISTS (SELECT * FROM inserted 
101WHERE (((inserted.Number_Car not LIKE N'[АВЕКМНОРСТУХавекмнорстух][0-9][0-9][0-9][АВЕКМНОРСТУХавекмнорстух][АВЕКМНОРСТУХавекмнорстух]')) 
102or (inserted.Number_Car like N'[АВЕКМНОРСТУХавекмнорстух]000[АВЕКМНОРСТУХавекмнорстух][АВЕКМНОРСТУХавекмнорстух]'))) 
103BEGIN 
104PRINT 'Ошибка в номере' 
105ROLLBACK TRANSACTION 
106END 
107GO 
108
109CREATE TRIGGER checkReg 
110on NumbersRegion 
111for INSERT 
112AS 
113IF EXISTS (SELECT * FROM inserted 
114WHERE (inserted.Number not LIKE N'[0-9][0-9]' and inserted.Number not LIKE N'[127][0-9][0-9]')) 
115begin 
116PRINT 'Ошибка в номере' 
117ROLLBACK TRANSACTION 
118return 
119end 
120else print'ok' 
121go 
122
123
124ALTER TABLE Journal ENABLE TRIGGER checkNumber 
125GO 
126
127ALTER TABLE NumbersRegion ENABLE TRIGGER checkReg 
128GO 
129
130
131
132INSERT INTO Posts 
133VALUES 
134(1, N'Северный') 
135,(2, N'Южный') 
136,(3, N'Западный') 
137,(4, N'Восточный') 
138GO 
139
140INSERT INTO NameRegion 
141VALUES 
142(N'11', N'Республика Коми') 
143,(N'12', N'Республика Марий Эл') 
144,(N'13', N'Республика Мордовия') 
145,(N'14', N'Республика Саха') 
146,(N'15', N'Республика Северная Осетия — Алания') 
147,(N'16', N'Республика Татарстан') 
148,(N'17', N'Республика Тыва') 
149,(N'18', N'Удмуртская Республика') 
150,(N'19', N'Республика Хакасия') 
151,(N'21', N'Чувашская Республика') 
152,(N'66', N'Свердловская область')
153,(N'23', N'Краснодарский край') 
154,(N'24', N'Красноярский край') 
155,(N'25', N'Приморский край') 
156--,(N'616', N'ab') 
157GO 
158
159INSERT INTO NumbersRegion 
160VALUES 
161(N'13', N'13') 
162,(N'13', N'113') 
163,(N'16', N'16') 
164,(N'16', N'116') 
165,(N'21', N'21') 
166,(N'21', N'121') 
167,(N'23', N'23') 
168,(N'66', N'66')
169,(N'66', N'196')
170,(N'66', N'96')
171,(N'23', N'93') 
172,(N'23',N'123') 
173,(N'24', N'24') 
174,(N'24', N'84') 
175,(N'24', N'88') 
176,(N'24', N'124') 
177,(N'25', N'25') 
178--,(N'616', N'616') 
179,(N'25', N'125') 
180GO 
181
182INSERT INTO Owners 
183values 
184(N'Иванов', 100) 
185,(N'Петров', 0) 
186,(N'Сидоров', 0)
187go 
188/* 
189insert into Cars 
190values 
191(N'А152АС', N'16', N'Lada', N'черный') 
192,(N'А652АС', N'25', N'Lada', N'белый') 
193,(N'С652АС', N'25', N'Lada', N'розовый') 
194,(N'Н652АС', N'16', N'Lada', N'серый') 
195,(N'А658АС', N'25', N'Lada', N'розовый') 
196,(N'С852АС', N'125', N'Lada', N'желтый') 
197,(N'Т652АС', N'116', N'Lada', N'красный') 
198go 
199*/ 
200
201INSERT INTO Journal 
202VALUES 
203(2, N'А152АС', N'16', N'Иванов',N'Lada', N'черный', N'20161020 10:10:10.000', N'in') --иногородняя 
204,(2, N'А152АС', N'16', N'Иванов',N'Lada', N'черный', N'20161020 10:10:12.000', N'out') 
205,(1, N'С152АС', N'16', N'Иванов',N'Lada', N'черный', N'20161020 10:10:10.000', N'in') --иногородняя 
206,(1, N'С152АС', N'16', N'Иванов',N'Lada', N'черный', N'20161020 10:10:12.000', N'out') 
207,(3, N'Н652АС', N'116', N'Иванов', N'Lada', N'серый', N'20161020 10:10:10.000', N'in') --транзитная
208,(2, N'Н652АС', N'116', N'Иванов', N'Lada', N'серый', N'20161020 10:10:13.000', N'out') 
209,(2, N'А652АС', N'25', N'Иванов', N'Lada', N'розовый', N'20161020 10:10:10.000', N'out') --прочая 
210,(2, N'А652АС', N'25', N'Иванов', N'Lada', N'розовый', N'20161020 10:10:17.000', N'in') 
211,(2, N'С852АС', N'125', N'Петров', N'Lada', N'красный', N'20161020 10:10:18.000', N'out') --прочая
212,(2, N'С852АС', N'125', N'Петров', N'Lada', N'красный', N'20161020 10:10:18.500', N'in') 
213,(3, N'У089СО', N'96', N'Сидоров', N'Opel', N'красный', N'20161020 10:11:18.000', N'out') --местная 
214,(4, N'У089СС', N'196', N'Сидоров', N'Opel', N'красный', N'20161020 10:12:19.000', N'in') --прочая
215,(3, N'У089СО', N'96', N'Сидоров', N'Opel', N'красный', N'20161020 10:12:19.000', N'in')
216,(1, N'У089СС', N'196', N'Сидоров', N'Opel', N'красный', N'20161020 10:14:19.000', N'out')  
217GO 
218
219
220-- все машины региона Х
221SELECT CONCAT(c1.Number_Car, N' ' ,c1.Number_Region) AS N'Номер машины',
222    c1.Owner_Car AS N'Фамилия',
223    c1.Mark AS N'Марка',
224    c1.Color AS N'Цвет',
225   dbo.NameRegion.Name_Region AS N'Название региона'
226FROM dbo.Journal as c1 inner join 
227	dbo.Journal as c2 on c1.Number_Car = c2.Number_Car and c1.Number_Region = c2.Number_Region inner join
228	dbo.NumbersRegion on dbo.NumbersRegion.Number = c1.Number_Region inner join
229	dbo.NameRegion on dbo.NameRegion.Number_Region = dbo.NumbersRegion.Main_Number 
230where dbo.NumbersRegion.Main_Number = N'16'
231group by CONCAT(c1.Number_Car, N' ' ,c1.Number_Region), c1.Owner_Car, c1.Mark, c1.Color, dbo.NameRegion.Name_Region 
232
233-- количество машин региона Х
234SELECT COUNT(dbo.Journal.Number_Car)/2 AS N'Количество машин', dbo.NameRegion.Name_Region  as N'Регион'
235FROM dbo.Journal INNER JOIN 
236dbo.NumbersRegion on dbo.Journal.Number_Region = dbo.NumbersRegion.Number inner join 
237dbo.NameRegion on dbo.NumbersRegion.Main_Number = dbo.NameRegion.Number_Region 
238where dbo.NumbersRegion.Main_Number = N'16' 
239GROUP BY dbo.NameRegion.Name_Region
240
241--машина марки Х и цвета У
242SELECT CONCAT(c1.Number_Car, N' ' ,c1.Number_Region) AS N'Номер машины',
243    c1.Owner_Car AS N'Фамилия',
244    c1.Mark AS N'Марка',
245   dbo.NameRegion.Name_Region AS N'Название региона'
246FROM dbo.Journal as c1 inner join 
247	dbo.Journal as c2 on c1.Number_Car = c2.Number_Car and c1.Number_Region = c2.Number_Region inner join
248	dbo.NumbersRegion on dbo.NumbersRegion.Number = c1.Number_Region inner join
249	dbo.NameRegion on dbo.NameRegion.Number_Region = dbo.NumbersRegion.Main_Number 
250where UPPER(c1.Color) = N'черный' and UPPER(c1.Mark) = N'Lada'
251group by CONCAT(c1.Number_Car, N' ' ,c1.Number_Region), c1.Owner_Car, c1.Mark, dbo.NameRegion.Name_Region 
252
253
254-- все машины цвета Х
255SELECT CONCAT(c1.Number_Car, N' ' ,c1.Number_Region) AS N'Номер машины',
256    c1.Owner_Car AS N'Фамилия',
257    c1.Mark AS N'Марка',
258   dbo.NameRegion.Name_Region AS N'Название региона'
259FROM dbo.Journal as c1 inner join 
260	dbo.Journal as c2 on c1.Number_Car = c2.Number_Car and c1.Number_Region = c2.Number_Region inner join
261	dbo.NumbersRegion on dbo.NumbersRegion.Number = c1.Number_Region inner join
262	dbo.NameRegion on dbo.NameRegion.Number_Region = dbo.NumbersRegion.Main_Number 
263where UPPER(c1.Color) = N'красный'
264group by CONCAT(c1.Number_Car, N' ' ,c1.Number_Region), c1.Owner_Car, c1.Mark, dbo.NameRegion.Name_Region 
265
266-- Иногородние
267
268SELECT CONCAT(c1.Number_Car, N' ' ,c1.Number_Region) AS N'Номер машины',
269    c1.Owner_Car AS N'Фамилия',
270    c1.Mark AS N'Марка',
271    c1.Color AS N'Цвет',
272   dbo.NameRegion.Name_Region AS N'Название региона', 
273   c1.Id_Post AS N'Пост въезда',
274   c2.Id_Post  AS N'Пост выезда'
275
276FROM dbo.Journal as c1 inner join 
277	dbo.Journal as c2 on c1.Number_Car = c2.Number_Car and c1.Number_Region = c2.Number_Region inner join
278	dbo.NumbersRegion on dbo.NumbersRegion.Number = c1.Number_Region inner join
279	dbo.NameRegion on dbo.NameRegion.Number_Region = dbo.NumbersRegion.Main_Number inner join
280	dbo.Posts on dbo.Posts.IdPost = c1.Id_Post
281
282where UPPER(c1.Direction) = N'in'and UPPER(c2.Direction) = N'out' and 
283	c1.Id_Post = c2.Id_Post and c1.Time_transit < c2.Time_transit
284
285
286
287--транзитные
288SELECT CONCAT(c1.Number_Car, N' ' ,c1.Number_Region) AS N'Номер машины',
289    c1.Owner_Car AS N'Фамилия',
290    c1.Mark AS N'Марка',
291    c1.Color AS N'Цвет',
292   dbo.NameRegion.Name_Region AS N'Название региона', 
293   c1.Id_Post AS N'Пост въезда',
294   c2.Id_Post  AS N'Пост выезда'
295
296FROM dbo.Journal as c1 inner join 
297	dbo.Journal as c2 on c1.Number_Car = c2.Number_Car and c1.Number_Region = c2.Number_Region inner join
298	dbo.NumbersRegion on dbo.NumbersRegion.Number = c1.Number_Region inner join
299	dbo.NameRegion on dbo.NameRegion.Number_Region = dbo.NumbersRegion.Main_Number inner join
300	dbo.Posts on dbo.Posts.IdPost = c1.Id_Post
301
302where UPPER(c1.Direction) = N'in'and UPPER(c2.Direction) = N'out' and c1.Id_Post != c2.Id_Post
303	and c1.Time_transit < c2.Time_transit and dbo.NumbersRegion.Main_Number != N'66' 
304	
305
306-- местные
307SELECT CONCAT(c1.Number_Car, N' ' ,c1.Number_Region) AS N'Номер машины',
308		c1.Owner_Car AS N'Фамилия',
309		c1.Mark AS N'Марка',
310		c1.Color AS N'Цвет',
311		dbo.NameRegion.Name_Region AS N'Название региона',
312		c1.Id_Post AS N'Пост'
313FROM dbo.Journal as c1 inner join 
314	dbo.Journal as c2 on c1.Number_Car = c2.Number_Car and c1.Number_Region = c2.Number_Region inner join
315	dbo.NumbersRegion on dbo.NumbersRegion.Number = c1.Number_Region inner join
316	dbo.NameRegion on dbo.NameRegion.Number_Region = dbo.NumbersRegion.Main_Number inner join
317	dbo.Posts on dbo.Posts.IdPost = c1.Id_Post
318WHERE dbo.NumbersRegion.Main_Number = N'66' and UPPER(c1.Direction) = N'out'and UPPER(c2.Direction )= N'in' 
319		and c1.Id_Post = c2.Id_Post and c1.Time_transit < c2.Time_transit 
320
321--прочие
322SELECT CONCAT(c1.Number_Car, N' ' ,c1.Number_Region) AS N'Номер машины',
323    c1.Owner_Car AS N'Фамилия',
324    c1.Mark AS N'Марка',
325    c1.Color AS N'Цвет',
326   dbo.NameRegion.Name_Region AS N'Название региона', 
327   c1.Id_Post AS N'Пост въезда',
328   c2.Id_Post  AS N'Пост выезда'
329
330FROM dbo.Journal as c1 inner join 
331	dbo.Journal as c2 on c1.Number_Car = c2.Number_Car and c1.Number_Region = c2.Number_Region inner join
332	dbo.NumbersRegion on dbo.NumbersRegion.Number = c1.Number_Region inner join
333	dbo.NameRegion on dbo.NameRegion.Number_Region = dbo.NumbersRegion.Main_Number inner join
334	dbo.Posts on dbo.Posts.IdPost = c1.Id_Post
335
336where (UPPER(c1.Direction) = N'in'and UPPER(c2.Direction) = N'out' and c1.Id_Post != c2.Id_Post and
337	c1.Time_transit < c2.Time_transit and dbo.NumbersRegion.Main_Number = N'66') or
338	(dbo.NumbersRegion.Main_Number != N'66' and UPPER(c1.Direction) = N'out'and UPPER(c2.Direction) = N'in' 
339		and c1.Id_Post = c2.Id_Post and c1.Time_transit < c2.Time_transit )