JSzaSX7A

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'красный'
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    c1.Color AS N'Цвет',
259   dbo.NameRegion.Name_Region AS N'Название региона', 
260   c1.Id_Post AS N'Пост въезда',
261   c2.Id_Post  AS N'Пост выезда'
262
263FROM dbo.Journal as c1 inner join 
264	dbo.Journal as c2 on c1.Number_Car = c2.Number_Car and c1.Number_Region = c2.Number_Region inner join
265	dbo.NumbersRegion on dbo.NumbersRegion.Number = c1.Number_Region inner join
266	dbo.NameRegion on dbo.NameRegion.Number_Region = dbo.NumbersRegion.Main_Number inner join
267	dbo.Posts on dbo.Posts.IdPost = c1.Id_Post
268
269where UPPER(c1.Direction) = N'in'and UPPER(c2.Direction) = N'out' and 
270	c1.Id_Post = c2.Id_Post and c1.Time_transit < c2.Time_transit
271
272
273
274--транзитные
275SELECT CONCAT(c1.Number_Car, N' ' ,c1.Number_Region) AS N'Номер машины',
276    c1.Owner_Car AS N'Фамилия',
277    c1.Mark AS N'Марка',
278    c1.Color AS N'Цвет',
279   dbo.NameRegion.Name_Region AS N'Название региона', 
280   c1.Id_Post AS N'Пост въезда',
281   c2.Id_Post  AS N'Пост выезда'
282
283FROM dbo.Journal as c1 inner join 
284	dbo.Journal as c2 on c1.Number_Car = c2.Number_Car and c1.Number_Region = c2.Number_Region inner join
285	dbo.NumbersRegion on dbo.NumbersRegion.Number = c1.Number_Region inner join
286	dbo.NameRegion on dbo.NameRegion.Number_Region = dbo.NumbersRegion.Main_Number inner join
287	dbo.Posts on dbo.Posts.IdPost = c1.Id_Post
288
289where UPPER(c1.Direction) = N'in'and UPPER(c2.Direction) = N'out' and c1.Id_Post != c2.Id_Post
290	and c1.Time_transit < c2.Time_transit and dbo.NumbersRegion.Main_Number != N'66' 
291	
292
293-- местные
294SELECT CONCAT(c1.Number_Car, N' ' ,c1.Number_Region) AS N'Номер машины',
295		c1.Owner_Car AS N'Фамилия',
296		c1.Mark AS N'Марка',
297		c1.Color AS N'Цвет',
298		dbo.NameRegion.Name_Region AS N'Название региона',
299		c1.Id_Post AS N'Пост'
300FROM dbo.Journal as c1 inner join 
301	dbo.Journal as c2 on c1.Number_Car = c2.Number_Car and c1.Number_Region = c2.Number_Region inner join
302	dbo.NumbersRegion on dbo.NumbersRegion.Number = c1.Number_Region inner join
303	dbo.NameRegion on dbo.NameRegion.Number_Region = dbo.NumbersRegion.Main_Number inner join
304	dbo.Posts on dbo.Posts.IdPost = c1.Id_Post
305WHERE dbo.NumbersRegion.Main_Number = N'66' and UPPER(c1.Direction) = N'out'and UPPER(c2.Direction )= N'in' 
306		and c1.Id_Post = c2.Id_Post and c1.Time_transit < c2.Time_transit 
307
308--прочие
309SELECT CONCAT(c1.Number_Car, N' ' ,c1.Number_Region) AS N'Номер машины',
310    c1.Owner_Car AS N'Фамилия',
311    c1.Mark AS N'Марка',
312    c1.Color AS N'Цвет',
313   dbo.NameRegion.Name_Region AS N'Название региона', 
314   c1.Id_Post AS N'Пост въезда',
315   c2.Id_Post  AS N'Пост выезда'
316
317FROM dbo.Journal as c1 inner join 
318	dbo.Journal as c2 on c1.Number_Car = c2.Number_Car and c1.Number_Region = c2.Number_Region inner join
319	dbo.NumbersRegion on dbo.NumbersRegion.Number = c1.Number_Region inner join
320	dbo.NameRegion on dbo.NameRegion.Number_Region = dbo.NumbersRegion.Main_Number inner join
321	dbo.Posts on dbo.Posts.IdPost = c1.Id_Post
322
323where (UPPER(c1.Direction) = N'in'and UPPER(c2.Direction) = N'out' and c1.Id_Post != c2.Id_Post and
324	c1.Time_transit < c2.Time_transit and dbo.NumbersRegion.Main_Number = N'66') or
325	(dbo.NumbersRegion.Main_Number != N'66' and UPPER(c1.Direction) = N'out'and UPPER(c2.Direction) = N'in' 
326		and c1.Id_Post = c2.Id_Post and c1.Time_transit < c2.Time_transit )