fEVnD3fn

1USE master
2
3DROP DATABASE DamirShaikhislamov
4CREATE DATABASE DamirShaikhislamov
5
6USE DamirShaikhislamov
7 
8CREATE TABLE Regions (id int PRIMARY KEY NOT NULL, name nvarchar(128) NOT NULL)
9CREATE TABLE Codes (code int PRIMARY KEY NOT NULL, region_id int FOREIGN KEY REFERENCES Regions(id))
10CREATE TABLE Drivers (id int PRIMARY KEY IDENTITY NOT NULL,name nvarchar(128), family nvarchar(128))
11CREATE TABLE Posts (id int PRIMARY KEY IDENTITY NOT NULL,name nvarchar(128))
12
13CREATE TABLE Cars (
14    id int PRIMARY KEY IDENTITY NOT NULL,
15    mark nvarchar(128),
16    color nvarchar(64),
17    number nvarchar(64),
18    regcode int FOREIGN KEY REFERENCES Codes(code),
19    owner int FOREIGN KEY REFERENCES Drivers(id),
20	CHECK (regcode < 100 
21		OR regcode BETWEEN 100 AND 199 
22		OR regcode BETWEEN 200 AND 299 
23		OR regcode BETWEEN 700 AND 799)
24)
25
26
27CREATE TABLE Register (
28    id int PRIMARY KEY IDENTITY NOT NULL,
29    post_id int FOREIGN KEY REFERENCES Posts(id),
30    car_id int FOREIGN KEY REFERENCES Cars(id),
31    incoming bit,
32    record_time datetime
33)
34GO
35
36
37CREATE TRIGGER CarsTrigger ON Cars FOR INSERT AS
38	IF EXISTS (SELECT * FROM inserted WHERE number NOT LIKE 
39		'[АВЕКМНОРСТУХABEKMHOPCTYX][0-9][0-9][0-9][АВЕКМНОРСТУХABEKMHOPCTYX][АВЕКМНОРСТУХABEKMHOPCTYX]') 
40	BEGIN
41		RAISERROR ('Неверный номер автомобиля', 16, 1);
42		ROLLBACK TRANSACTION;
43		RETURN 
44	END
45GO
46
47CREATE FUNCTION action (@carId int, @regId int, @time datetime)
48RETURNS int
49AS
50BEGIN
51    DECLARE @result bit = (
52		SELECT TOP(1) incoming FROM Register 
53			WHERE car_id = @carId AND id != @regId AND record_time <= @time 
54			ORDER BY record_time 
55			DESC
56		)
57
58	IF @result IS NULL RETURN -1
59	RETURN CAST(@result AS int)
60END
61GO
62
63CREATE TRIGGER RecordsTrigger ON Register FOR INSERT AS
64	IF EXISTS (SELECT * FROM inserted WHERE incoming=dbo.action(car_id, id, record_time))
65	BEGIN
66		RAISERROR ('Неверное действие для машины', 16, 1);
67		ROLLBACK TRANSACTION;
68		RETURN 
69	END
70GO
71
72 
73INSERT INTO Regions VALUES
74    (13, 'Калининградская область'),
75	(23, 'Липецкая область'),       
76    (50, 'Пермский край'),
77	(66, 'Тамбовская область'),    
78    (74, 'Курганская область'),
79	(77, 'Ненецкий автономный округ')   
80GO
81   
82INSERT INTO Codes VALUES
83    (66, 66), (96, 66), (196, 66),
84	(77, 77), (97, 77), (99, 77), 
85	(177, 77),(197, 77),(199, 77),
86	(777, 77),(23, 23), (93, 23),
87	(123, 23),(74, 74), (174, 74),
88	(13, 13), (113, 13),(50, 50),
89	(90, 50), (150, 50),(190, 50)
90GO
91 
92INSERT INTO Drivers VALUES
93    ('Фэндю', 'Жаданов'),
94    ('Беловор', 'Костарев'),
95    ('Сибит', 'Гусаров'),
96    ('Анкорг', 'Вульберг'),
97    ('Битрек', 'Чепух')
98 GO
99
100INSERT INTO Cars VALUES
101    ('Запорожец', 'Красный', 'B387ТХ', 96, 1),
102    ('КИА', 'Черный', 'А064ВТ', 96, 1),
103    ('БЭХА', 'Черный',  'О360СК', 66, 2),
104    ('Мазда', 'Красный',   'С777АК', 174, 3),
105    ('Вольскваген', 'Белый','Н404НН', 777, 4),
106    ('Китайкар', 'Оранжевый',  'А202КЕ', 123, 5),
107    ('Фераре', 'Желтый',  'А501СВ', 23, 4)
108 GO
109
110INSERT INTO Codes VALUES
111(000,50)
112
113INSERT INTO Cars VALUES
114    ('Запорожец', 'Красный',  'B387ТA', 000, 1)
115GO
116 
117INSERT INTO Cars VALUES
118    ('Запорожец', 'Красный',  'Z777ZZ', 23, 1)
119GO
120
121
122INSERT INTO Posts VALUES
123    ('Северный пост'),
124    ('Южный пост'),
125    ('Западный пост'),
126    ('Восточный пост'),
127    ('Северо-Западный пост')
128GO
129
130INSERT INTO Register VALUES
131    (1, 4, 1, '12:30'),
132    (2, 4, 0, '14:15'),
133    (1, 4, 1, '15:30'),
134    (2, 4, 0, '16:15'),
135    (3, 1, 0, '15:15'),
136    (4, 1, 1, '16:15'),
137    (5, 5, 1, '17:15'),
138    (5, 5, 0, '18:15'),
139	(3, 7, 0, '18:15')
140GO
141	
142INSERT INTO Register VALUES
143	(3, 7, 0, '18:15')
144GO
145
146SELECT Convert(nvarchar,r.record_time,108) AS Время, post.name AS Пост, IIF(r.incoming=1, 'Да', 'Нет') AS 'В город',
147    c.mark AS Марка, c.color AS Цвет, c.number AS Номер, 
148    reg.name AS Регион, person.family AS Фамилия, person.name AS Имя
149    FROM Register AS r
150        INNER JOIN Cars AS c ON r.car_id=c.id 
151        INNER JOIN Drivers AS person ON c.owner=person.id
152        INNER JOIN Posts AS post ON post.id=r.post_id
153        INNER JOIN Codes AS codes ON codes.code=c.regcode
154        INNER JOIN Regions AS reg ON codes.region_id=reg.id
155    ORDER BY r.record_time
156GO
157
158DECLARE @CurrentRegion int = (SELECT id FROM Regions WHERE name='Тамбовская область')
159
160DECLARE @transit table(car_id int)
161INSERT INTO @transit SELECT DISTINCT t1.car_id FROM Register AS t1
162	WHERE
163		t1.incoming = 1 AND (
164		SELECT codes.region_id FROM Cars AS cars 
165			JOIN Codes AS codes ON cars.regcode=codes.code
166				WHERE cars.id=t1.car_id)!=@CurrentRegion AND EXISTS (
167					SELECT * FROM Register AS t2 WHERE
168						t1.car_id = t2.car_id AND
169						t1.post_id != t2.post_id AND
170						t1.record_time < t2.record_time AND
171						t2.incoming = 0
172		)
173
174DECLARE @outer table(car_id int)
175INSERT INTO @outer SELECT DISTINCT t1.car_id FROM Register AS t1
176	WHERE
177		t1.incoming = 1 AND
178		EXISTS (SELECT * FROM Register AS t2 WHERE
179			t1.car_id = t2.car_id AND
180			t1.post_id = t2.post_id AND
181			t1.record_time < t2.record_time AND
182			t2.incoming = 0
183		) AND t1.car_id NOT IN (SELECT * FROM @transit)
184
185DECLARE @local table(car_id int)
186INSERT INTO @local SELECT DISTINCT t1.car_id FROM Register AS t1
187	WHERE
188		t1.incoming = 0 AND
189		(SELECT b.region_id
190			FROM Cars AS a 
191			JOIN Codes AS b ON a.regcode=b.code
192				WHERE a.id=t1.car_id)=@CurrentRegion AND
193		EXISTS (SELECT * FROM Register AS t2 WHERE
194			t1.car_id = t2.car_id AND
195			t1.record_time < t2.record_time AND
196			t2.incoming = 1
197		) AND t1.car_id NOT IN (SELECT * FROM @transit) AND
198		t1.car_id NOT IN (SELECT * FROM @outer)
199
200DECLARE @other table(car_id int)
201INSERT INTO @other SELECT DISTINCT t1.car_id FROM Register AS t1
202	WHERE
203		t1.car_id NOT IN (SELECT * FROM @transit) AND
204		t1.car_id NOT IN (SELECT * FROM @outer) AND
205		t1.car_id NOT IN (SELECT * FROM @local)
206
207SELECT b.id AS 'Транзитные', b.mark AS 'Марка', b.color as 'Цвет' , b.number AS 'Номер',
208	b.regcode AS 'Код региона', reg.name as 'Регион', person.family as 'Фамилия', person.name as 'Имя' FROM @transit AS a 
209		JOIN Cars AS b ON a.car_id=b.id
210		JOIN Drivers AS person ON b.owner=person.id
211		JOIN Codes AS codes ON codes.code=b.regcode
212		JOIN Regions AS reg ON codes.region_id=reg.id;
213
214SELECT b.id AS ' Иногородние', b.mark AS 'Марка',b.color as 'Цвет' , b.number AS 'Номер',
215	b.regcode AS 'Код региона', reg.name as 'Регион', person.family as 'Фамилия', person.name as 'Имя' FROM @outer AS a 
216		JOIN Cars AS b ON a.car_id=b.id
217		JOIN Drivers AS person ON b.owner=person.id
218		JOIN Codes AS codes ON codes.code=b.regcode
219		JOIN Regions AS reg ON codes.region_id=reg.id;
220
221SELECT b.id AS 'Местные', b.mark AS 'Марка',b.color as 'Цвет' , b.number AS 'Номер',
222	b.regcode AS 'Код региона', reg.name as 'Регион', person.family as 'Фамилия', person.name as 'Имя' FROM @local AS a
223		JOIN Cars AS b ON a.car_id=b.id
224		JOIN Drivers AS person ON b.owner=person.id
225		JOIN Codes AS codes ON codes.code=b.regcode
226		JOIN Regions AS reg ON codes.region_id=reg.id;
227
228SELECT b.id AS 'Другие', b.mark AS 'Марка',b.color as 'Цвет' , b.number AS 'Номер',
229	b.regcode AS 'Код региона', reg.name as 'Регион', person.family as 'Фамилия', person.name as 'Имя' FROM @other AS a
230		JOIN Cars AS b ON a.car_id=b.id
231		JOIN Drivers AS person ON b.owner=person.id
232		JOIN Codes AS codes ON codes.code=b.regcode
233		JOIN Regions AS reg ON codes.region_id=reg.id;
234GO
235
236SELECT Count(*) AS 'Количество машин' FROM Cars;