ac4qf4nd

1USE master
2GO
3
4IF  EXISTS (
5	SELECT name 
6		FROM sys.databases 
7		WHERE name = N'Ekaterina_Abramova'
8)
9DROP DATABASE Ekaterina_Abramova
10GO
11
12CREATE DATABASE Ekaterina_Abramova
13GO
14
15USE Ekaterina_Abramova
16GO
17
18CREATE TABLE Regions (
19    id int PRIMARY KEY NOT NULL,
20    reg_name nvarchar(128) NOT NULL
21)
22GO
23
24CREATE TABLE RegionCodes (
25    code int PRIMARY KEY NOT NULL,
26    region_id int FOREIGN KEY REFERENCES Regions(id)
27)
28GO
29
30CREATE TABLE Persons (
31    id int PRIMARY KEY IDENTITY NOT NULL,
32    family nvarchar(128)
33)
34GO
35
36CREATE TABLE Cars (
37    id int PRIMARY KEY IDENTITY NOT NULL,
38    mark nvarchar(128),
39    color nvarchar(64),
40    number nvarchar(64),
41    region_code int FOREIGN KEY REFERENCES RegionCodes(code),
42    owner int FOREIGN KEY REFERENCES Persons(id),
43
44	--CONSTRAINT Cars_number 
45	--	CHECK (number LIKE '[АВЕКМНОРСТУХABEKMHOPCTYX][0-9][0-9][0-9][АВЕКМНОРСТУХABEKMHOPCTYX][АВЕКМНОРСТУХABEKMHOPCTYX]'),
46	--CONSTRAINT Cars_region_code 
47	--	CHECK (region_code < 100 OR region_code BETWEEN 100 AND 199 OR region_code BETWEEN 200 AND 299 OR region_code BETWEEN 700 AND 799)
48)
49GO
50
51CREATE TRIGGER CarsTrigger ON Cars FOR INSERT AS
52	IF EXISTS (SELECT * FROM inserted 
53					WHERE number NOT LIKE '[АВЕКМНОРСТУХ][0-9][0-9][0-9][АВЕКМНОРСТУХ][АВЕКМНОРСТУХ]')
54	BEGIN
55		RAISERROR ('Неверный номер автомобиля', 16, 1);
56		ROLLBACK TRANSACTION
57		RETURN 
58	END
59GO
60
61CREATE TRIGGER RegionTrigger ON RegionCodes FOR INSERT AS
62	IF EXISTS (SELECT * FROM inserted
63						WHERE NOT (code < 100 OR code BETWEEN 100 AND 199 OR code BETWEEN 200 AND 299 OR code BETWEEN 700 AND 799))
64	BEGIN
65		RAISERROR ('Неверный код региона', 16, 1);
66		ROLLBACK TRANSACTION
67		RETURN 
68	END
69GO
70
71CREATE TABLE Posts (
72    id int PRIMARY KEY IDENTITY NOT NULL,
73    name nvarchar(128)
74)
75GO
76
77CREATE TABLE Records (
78    id int PRIMARY KEY IDENTITY NOT NULL,
79    post_id int FOREIGN KEY REFERENCES Posts(id),
80    car_id int FOREIGN KEY REFERENCES Cars(id),
81    incoming bit,
82    record_time time
83)
84GO
85
86INSERT INTO Regions VALUES
87    (01, N'Республика Адыгея'),
88	(63, N'Самарская область'),
89	(05, N'Республика Дагестан'),
90	(66, N'Свердловская область'),
91	(74, N'Челябинская область'),
92	(78, N'Санкт-Петербург')
93    
94GO
95         
96INSERT INTO RegionCodes VALUES
97    (01, 01),
98	(05, 05),
99	(63, 63),
100	(163, 63),
101	(66, 66),
102	(96, 66),
103	(196, 66),
104	(74, 74),
105	(174, 74),
106	(78, 78),
107	(98, 78),
108	(178, 78)
109 GO
110
111INSERT INTO Persons VALUES
112    (N'Иванов'),
113	(N'Петров'),
114	(N'Сидоров'),
115	(N'Иванова'),
116	(N'Петрова'),
117	(N'Сидорова')
118 GO
119
120INSERT INTO Cars VALUES
121    (N'Honda', N'Красный',  N'В387ТХ', 96, 1),
122    (N'Toyota', N'Черный',  N'А064ВТ', 96, 2),
123    (N'Mazda', N'Черный',  N'О360СК', 98, 3),
124    (N'BMW', N'Красный',  N'С777АК', 174, 4),
125	(N'Daewoo', N'Оранжевый', N'А202КЕ', 05, 5),
126    (N'Mercedes', N'Белый', N'Н404НН', 178, 6)
127 GO
128
129INSERT INTO Posts VALUES
130    (N'Пост 1'),
131    (N'Пост 2'),
132    (N'Пост 3'),
133    (N'Пост 4')
134GO
135
136INSERT INTO Records VALUES
137    (1, 4, 1, N'12:30'),
138    (2, 4, 0, N'14:15'),
139    (1, 2, 0, N'15:30'),
140    (2, 2, 1, N'16:15'),
141    (3, 1, 0, N'15:15'),
142    (4, 1, 1, N'16:15'),
143	(4, 1, 0, N'17:15'),
144    (2, 5, 1, N'17:15'),
145    (3, 5, 0, N'18:15'),
146	(3, 6, 1, N'18:15'),
147	(2, 6, 0, N'19:00'),
148	(2, 3, 1, N'21:00')
149GO
150
151INSERT INTO Cars VALUES
152    ('Honda', 'Красный',  'Z777ZZ', 66, 1)
153GO
154
155INSERT INTO RegionCodes VALUES
156	(563, 66)
157GO
158
159SELECT 
160	CONVERT(nvarchar,r.record_time,108) AS Время,
161	post.name AS Пост,
162 	IIF(r.incoming=1, 'Да', 'Нет') AS 'В город',
163    c.mark AS Марка,
164	c.color AS Цвет,
165	c.number AS Номер, 
166    reg.reg_name AS Регион,
167	pers.family AS Фамилия
168
169FROM Records AS r
170        INNER JOIN Cars AS c ON r.car_id=c.id 
171        INNER JOIN Persons AS pers ON c.owner=pers.id
172        INNER JOIN Posts AS post ON post.id=r.post_id
173        INNER JOIN RegionCodes AS codes ON codes.code=c.region_code
174        INNER JOIN Regions AS reg ON codes.region_id=reg.id
175    ORDER BY r.record_time
176GO
177
178DECLARE @CurrentRegion int = (SELECT id FROM Regions WHERE reg_name='Свердловская область')
179
180DECLARE @transit table(car_id int)
181INSERT INTO @transit SELECT DISTINCT t1.car_id FROM Records AS t1
182	WHERE
183		t1.incoming = 1 
184	AND
185		(SELECT regc.region_id	FROM Cars AS cars 
186			INNER JOIN RegionCodes AS regc ON cars.region_code=regc.code
187				WHERE cars.id=t1.car_id)!=@CurrentRegion AND
188		EXISTS (SELECT * FROM Records AS t2 WHERE
189			t1.car_id = t2.car_id AND
190			t1.post_id != t2.post_id AND
191			t1.record_time < t2.record_time AND
192			t2.incoming = 0
193		)
194
195DECLARE @outer table(car_id int)
196INSERT INTO @outer SELECT DISTINCT t1.car_id FROM Records AS t1
197	WHERE
198		t1.incoming = 1 
199	AND
200		EXISTS (SELECT * FROM Records AS t2 WHERE
201			t1.car_id = t2.car_id AND
202			t1.post_id = t2.post_id AND
203			t1.record_time < t2.record_time AND
204			t2.incoming = 0
205		) AND t1.car_id NOT IN (SELECT * FROM @transit)
206
207DECLARE @local table(car_id int)
208INSERT INTO @local SELECT DISTINCT t1.car_id FROM Records AS t1
209	WHERE
210		t1.incoming = 0 
211	AND
212		(SELECT b.region_id
213			FROM Cars AS a 
214			JOIN RegionCodes AS b ON a.region_code=b.code
215				WHERE a.id=t1.car_id)=@CurrentRegion AND
216		EXISTS (SELECT * FROM Records AS t2 WHERE
217			t1.car_id = t2.car_id AND
218			t1.record_time < t2.record_time AND
219			t2.incoming = 1
220		) AND t1.car_id NOT IN (SELECT * FROM @transit) AND
221		t1.car_id NOT IN (SELECT * FROM @outer)
222
223
224DECLARE @other table(car_id int)
225INSERT INTO @other SELECT DISTINCT t1.car_id FROM Records AS t1
226	WHERE
227		t1.car_id NOT IN (SELECT * FROM @transit) AND
228		t1.car_id NOT IN (SELECT * FROM @outer) AND
229		t1.car_id NOT IN (SELECT * FROM @local)
230
231SELECT 
232	b.id AS 'Транзитные',
233	b.mark AS 'Марка', 
234	b.color as 'Цвет' ,
235	CONCAT(b.number, ' ', b.region_code) AS 'Гос.номер',  
236	reg.reg_name as 'Регион', 
237	pers.family as 'Фамилия'  
238FROM @transit AS a 
239		INNER JOIN Cars AS b ON a.car_id=b.id
240		INNER JOIN Persons AS pers ON b.owner=pers.id
241		INNER JOIN RegionCodes AS codes ON codes.code=b.region_code
242		INNER JOIN Regions AS reg ON codes.region_id=reg.id;
243
244SELECT 
245	b.id AS ' Иногородние', 
246	b.mark AS 'Марка',
247	b.color as 'Цвет' , 
248	CONCAT(b.number, ' ', b.region_code) AS 'Гос.номер', 
249	reg.reg_name as 'Регион', 
250	pers.family as 'Фамилия'
251FROM @outer AS a 
252		INNER JOIN Cars AS b ON a.car_id=b.id
253		INNER JOIN Persons AS pers ON b.owner=pers.id
254		INNER JOIN RegionCodes AS codes ON codes.code=b.region_code
255		INNER JOIN Regions AS reg ON codes.region_id=reg.id;
256
257SELECT
258	b.id AS 'Местные', 
259	b.mark AS 'Марка',
260	b.color as 'Цвет' , 
261	CONCAT(b.number, ' ', b.region_code) AS 'Гос.номер', 
262	reg.reg_name as 'Регион', 
263	pers.family as 'Фамилия' 
264FROM @local AS a
265		INNER JOIN Cars AS b ON a.car_id=b.id
266		INNER JOIN Persons AS pers ON b.owner=pers.id
267		INNER JOIN RegionCodes AS codes ON codes.code=b.region_code
268		INNER JOIN Regions AS reg ON codes.region_id=reg.id;
269
270SELECT
271	b.id AS 'Другие',
272	b.mark AS 'Марка',
273	b.color as 'Цвет' , 
274	CONCAT(b.number, ' ', b.region_code) AS 'Гос.номер', 
275	reg.reg_name as 'Регион', 
276	pers.family as 'Фамилия' 
277FROM @other AS a
278		INNER JOIN Cars AS b ON a.car_id=b.id
279		INNER JOIN Persons AS pers ON b.owner=pers.id
280		INNER JOIN RegionCodes AS codes ON codes.code=b.region_code
281		INNER JOIN Regions AS reg ON codes.region_id=reg.id;
282GO