· 3 years ago · Nov 24, 2021, 09:50 AM
1use KN302_PELEVINA;
2go;
3
4-- Создание Схемы практики
5
6create schema gibdd_practice;
7go;
8
9-- Создание таблицы с регионами
10create table gibdd_practice.regions (
11 id int not null primary key,
12 name varchar(40) not null
13);
14
15insert into gibdd_practice.regions (id, name)
16values (77, 'Москва'),
17 (24, 'Красноярский край'),
18 (78, 'Санкт-Петербург'),
19 (66, 'Свердловская область');
20go;
21
22-- Создание таблицы со справочником кодов регионов
23create table gibdd_practice.region_codes (
24 code int not null primary key,
25 region_id int not null foreign key references gibdd_practice.regions (id),
26);
27go
28
29insert into gibdd_practice.region_codes (code, region_id)
30values (77, 77),
31 (99, 77),
32 (97, 77),
33 (177, 77),
34 (199, 77),
35 (197, 77),
36 (777, 77),
37 (799, 77),
38 (797, 77),
39 (24, 24),
40 (84, 24),
41 (88, 24),
42 (124, 24),
43 (78, 78),
44 (98, 78),
45 (178, 78),
46 (198, 78),
47 (66, 66),
48 (96, 66),
49 (196, 66);
50go
51-- На этом моменте мне надоело. Будем считать, что других регионов не существует
52
53-- Создание таблицы с постами ГИБДД
54create table gibdd_practice.posts (
55 id int not null identity(1,1) primary key,
56 region_id int not null foreign key references gibdd_practice.regions (id),
57);
58go;
59
60insert into gibdd_practice.posts (region_id)
61values (66),
62 (66),
63 (66),
64 (66),
65 (66);
66go;
67
68-- Создание таблицы с типами автомобилей
69create table gibdd_practice.car_types (
70 id int not null identity(1,1) primary key,
71 name varchar(15) not null
72);
73go;
74
75insert into gibdd_practice.car_types (name)
76values ('Транзитный'),
77 ('Иногородний'),
78 ('Местный'),
79 ('Прочий');
80go;
81
82-- Создание таблицы с соответствием номер автомобиля – тип
83create table gibdd_practice.number_car_type (
84 number varchar(10) not null primary key,
85 car_type_id int not null foreign key references gibdd_practice.car_types (id)
86);
87go;
88
89-- Создание функции для проверки корректности автомобильного номера
90create function gibdd_practice.check_car_number (@car_number varchar(10))
91returns bit
92as
93begin
94 declare @first_letter varchar(1) = substring(@car_number, 1, 1);
95 declare @number varchar(3) = substring(@car_number, 2, 3);
96 declare @tail_letters varchar(2) = substring(@car_number, 5, 2);
97 declare @region_code varchar(3) = substring(@car_number, 7, 3);
98
99 return (
100 select IIF(@first_letter like '[АЕУТНЕОРХВСМТК]' and
101 @number like '[0-9][0-9][0-9]' and @number != '000' and
102 @tail_letters like '[АЕУТНЕОРХВСМТК][АЕУТНЕОРХВСМТК]' and
103 (@region_code like '[127][0-9][0-9]' or @region_code like '[0-9][0-9]'), true, false)
104 );
105end;
106go
107
108-- Создание таблицы с проездами через посты
109create table gibdd_practice.passes (
110 id int not null identity(1,1) primary key,
111 post_id int not null foreign key references gibdd_practice.posts (id),
112 direction bit not null, -- 1 – въезд, 0 – выезд
113 number varchar(10) not null,
114 pass_time datetime default current_timestamp,
115 constraint check_number check (gibdd_practice.check_car_number(number) = 1)
116);
117go;
118
119CREATE trigger gibdd_practice.passes_insert
120 on gibdd_practice.passes
121 instead of insert
122 as
123 declare @post_id int,
124 @direction bit,
125 @number varchar(10),
126 @pass_time datetime;
127 select @post_id = i.post_id,
128 @direction = i.direction,
129 @number = i.number,
130 @pass_time = i.pass_time
131 from inserted i
132 -- Проверяем, что автомобиль не едет 2 раза подряд в том же направлении
133 if exists (
134 select *
135 from (
136 select top 1 p.number, p.direction
137 from gibdd_practice.passes p
138 where p.number = @number
139 order by p.pass_time desc
140 ) p
141 where @direction = p.direction
142 )
143 begin
144 raiserror ('Автомобиль не может проехать пост в том же направлении', 16, 1);
145 rollback transaction;
146 return;
147 end
148
149 -- Проверяем, что между прохождениями поста прошло больше 5 минут
150 if exists (
151 select *
152 from (
153 select top 1 p.number, p.pass_time
154 from gibdd_practice.passes p
155 where p.number = @number
156 order by p.pass_time desc
157 ) p
158 where datediff(minute, p.pass_time, @pass_time) < 5
159 )
160 begin
161 raiserror ('Прошло меньше 5 минут после последнего прохождения поста ГИБДД', 16, 1);
162 rollback transaction;
163 return;
164 end;
165
166 declare @region_code varchar(3) = substring(@number, 7, 3);
167 declare @region_id varchar(2) = (select region_id from region_codes where code = @region_code);
168 declare @post_region_id varchar(2) = (select region_id from gibdd_practice.posts where id = @post_id);
169 -- Выставляем тип автомобиля
170 if exists (
171 select *
172 from (
173 select top 1 p.number, p.direction, p.post_id
174 from gibdd_practice.passes p
175 where p.number = @number
176 order by p.pass_time desc
177 ) p
178 where p.direction = 1 and @direction = 0 and p.post_id != @post_id and @post_region_id != @region_id
179 )
180 begin
181 if exists (
182 select *
183 from gibdd_practice.number_car_type nc
184 where nc.number = @number
185 )
186 begin
187 update gibdd_practice.number_car_type
188 set car_type_id = 1
189 where number = @number;
190 end
191 else
192 begin
193 insert into gibdd_practice.number_car_type (number, car_type_id)
194 values (@number, 1);
195 end
196 end
197 else if exists (
198 select *
199 from (
200 select top 1 p.number, p.direction, p.post_id
201 from gibdd_practice.passes p
202 where p.number = @number
203 order by p.pass_time desc
204 ) p
205 where p.direction = 1 and @direction = 0 and p.post_id = @post_id and @post_region_id != @region_id
206 )
207 begin
208 if exists (
209 select *
210 from gibdd_practice.number_car_type nc
211 where nc.number = @number
212 )
213 begin
214 update gibdd_practice.number_car_type
215 set car_type_id = 2
216 where number = @number;
217 end
218 else
219 begin
220 insert into gibdd_practice.number_car_type (number, car_type_id)
221 values (@number, 2);
222 end
223 end
224 else if exists (
225 select *
226 from (
227 select top 1 p.number, p.direction, p.post_id
228 from gibdd_practice.passes p
229 where p.number = @number
230 order by p.pass_time desc
231 ) p
232 where p.direction = 0 and @direction = 1 and p.post_id = @post_id and @post_region_id = @region_id
233 )
234 begin
235 if exists (
236 select *
237 from gibdd_practice.number_car_type nc
238 where nc.number = @number
239 )
240 begin
241 update gibdd_practice.number_car_type
242 set car_type_id = 3
243 where number = @number;
244 end
245 else
246 begin
247 insert into gibdd_practice.number_car_type (number, car_type_id)
248 values (@number, 3);
249 end
250 end
251 else
252 begin
253 if exists (
254 select *
255 from gibdd_practice.number_car_type nc
256 where nc.number = @number
257 )
258 begin
259 update gibdd_practice.number_car_type
260 set car_type_id = 4
261 where number = @number;
262 end
263 else
264 begin
265 insert into gibdd_practice.number_car_type (number, car_type_id)
266 values (@number, 4);
267 end
268 end
269
270 -- Добавляем запись в таблицу
271
272 insert into gibdd_practice.passes (post_id, direction, number, pass_time)
273 values (@post_id, @direction, @number, @pass_time);
274go;
275
276-- Тесты- Корректные номера
277insert into gibdd_practice.passes (post_id, direction, number)
278values (1, 1, 'а001ам01');
279
280insert into gibdd_practice.passes (post_id, direction, number)
281values (1, 1, 'а001аа196');
282
283insert into gibdd_practice.passes (post_id, direction, number)
284values (1, 1, 'а001ат196');
285
286-- Некорректные номера
287insert into gibdd_practice.passes (post_id, direction, number)
288values (1, 1, 'лфывлофт');
289
290insert into gibdd_practice.passes (post_id, direction, number)
291values (1, 1, 'а000аа00');
292
293insert into gibdd_practice.passes (post_id, direction, number)
294values (1, 1, 'ф123аа00');
295
296insert into gibdd_practice.passes (post_id, direction, number)
297values (1, 1, 'а001аа001');
298
299insert into gibdd_practice.passes (post_id, direction, number)
300values (1, 1, 'а001аа401');
301
302-- Много времени между въездами
303insert into gibdd_practice.passes (post_id, direction, number, pass_time)
304values (1, 0, 'а001ро196', '2007-04-30T13:10:00');
305
306insert into gibdd_practice.passes (post_id, direction, number, pass_time)
307values (1, 1, 'а001ро196', '2007-04-30T13:20:00');
308
309-- Мало времени между въездами
310insert into gibdd_practice.passes (post_id, direction, number, pass_time)
311values (1, 1, 'а001ах196', '2007-04-30T13:20:00');
312
313insert into gibdd_practice.passes (post_id, direction, number, pass_time)
314values (1, 0, 'а001ах196', '2007-04-30T13:22:00');
315
316-- Повторный въезд
317insert into gibdd_practice.passes (post_id, direction, number)
318values (1, 1, 'а001ас196');
319insert into gibdd_practice.passes (post_id, direction, number)
320values (1, 1, 'а001ас196');
321
322-- Повторный выезд
323insert into gibdd_practice.passes (post_id, direction, number)
324values (1, 0, 'а001ар196');
325insert into gibdd_practice.passes (post_id, direction, number)
326values (1, 0, 'а001ар196');
327
328-- Местный автомобиль
329insert into gibdd_practice.passes (post_id, direction, number, pass_time)
330values (1, 0, 'с001ро196', '2007-04-30T13:10:00');
331
332insert into gibdd_practice.passes (post_id, direction, number, pass_time)
333values (1, 1, 'с001ро196', '2007-04-30T13:20:00');
334
335-- Транзитный автомобиль
336insert into gibdd_practice.passes (post_id, direction, number, pass_time)
337values (1, 1, 'а001ро24', '2007-04-30T13:10:00');
338
339insert into gibdd_practice.passes (post_id, direction, number, pass_time)
340values (2, 0, 'а001ро24', '2007-04-30T13:20:00');
341
342-- Иногородний автомобиль
343insert into gibdd_practice.passes (post_id, direction, number, pass_time)
344values (1, 1, 'а001ро124', '2007-04-30T13:10:00');
345
346insert into gibdd_practice.passes (post_id, direction, number, pass_time)
347values (1, 0, 'а001ро124', '2007-04-30T13:20:00');
348
349
350
351-- Запросы
352
353-- Получение всех номеров определенного типа
354CREATE VIEW gibdd_practice.numbers_of_other_type
355AS
356select nc.number, 'Прочий' as ['Тип']
357from gibdd_practice.number_car_type nc
358left join gibdd_practice.car_types ct
359on nc.car_type_id = ct.id
360where ct.name = 'Прочий';
361GO
362-- Query the view
363SELECT number as ['Номер'], ['Тип']
364FROM gibdd_practice.numbers_of_other_type
365ORDER BY number;
366go;
367
368
369CREATE VIEW gibdd_practice.numbers_of_tranzit_type
370AS
371select nc.number, 'Транзитный' as ['Тип']
372from gibdd_practice.number_car_type nc
373left join gibdd_practice.car_types ct
374on nc.car_type_id = ct.id
375where ct.name = 'Транзитный';
376GO
377-- Query the view
378SELECT number as ['Номер'], ['Тип']
379FROM gibdd_practice.numbers_of_tranzit_type
380ORDER BY number;
381go;
382
383
384
385