X3mFmqw6

1USE master
2GO
3
4IF  EXISTS (
5	SELECT name 
6		FROM sys.databases 
7		WHERE name = 'Pavel_Koshara'
8)
9ALTER DATABASE [Pavel_Koshara] set single_user with rollback immediate
10GO
11
12IF  EXISTS (
13	SELECT name 
14		FROM sys.databases 
15		WHERE name = 'Pavel_Koshara'
16)
17DROP DATABASE Pavel_Koshara
18GO
19
20CREATE DATABASE Pavel_Koshara
21GO
22
23USE Pavel_Koshara
24GO
25
26CREATE TABLE regions
27(
28	Id int,
29	rName nvarchar(64)  NOT NULL,
30	CONSTRAINT PK_region_id PRIMARY KEY (Id),
31)
32GO
33
34CREATE TABLE codes
35(
36	Code int NOT NULL,
37	NameId int NOT NULL,
38	CONSTRAINT CH_code CHECK (Code>0 AND Code<=299 OR Code>=700 AND Code<800),
39	CONSTRAINT PK_code PRIMARY KEY (Code),
40	CONSTRAINT FK_rname FOREIGN KEY (NameId) REFERENCES regions(id) ON UPDATE CASCADE
41)
42GO
43
44CREATE TABLE colors(
45	Id int,
46	Name nvarchar(64),
47	CONSTRAINT PK_color_id PRIMARY KEY (Id)
48)
49GO
50
51CREATE TABLE car_marks
52(
53	Id int,
54	Name nvarchar(64),
55	CONSTRAINT PK_mark_id PRIMARY KEY (Id)
56)
57GO
58
59CREATE TABLE cars
60(
61	Id int NOT NULL,
62	MarkId int NOT NULL,
63	ColorId int NOT NULL,
64	DriverSurname nvarchar(20) NOT NULL,
65	RegionCode int NOT NULL,
66	Plate nvarchar(6) NOT NULL UNIQUE,
67	CONSTRAINT FK_region_code FOREIGN KEY (RegionCode) REFERENCES codes(Code) ON UPDATE CASCADE,
68	CONSTRAINT FK_color FOREIGN KEY (ColorId) REFERENCES colors(Id) ON UPDATE CASCADE,
69	CONSTRAINT FK_mark FOREIGN KEY (MarkId) REFERENCES car_marks(Id) ON UPDATE CASCADE,
70	CONSTRAINT CH_plate CHECK (Plate LIKE N'[АВЕКМНОРСТУХ][0-9][0-9][0-9][АВЕКМНОРСТУХ][АВЕКМНОРСТУХ]'),
71	CONSTRAINT PK_car_id PRIMARY KEY (Id),
72)
73GO
74
75CREATE TABLE passes
76(
77	PostId int NOT NULL,
78	Direction bit NOT NULL,
79	CarId int NOT NULL,
80	PassDate date,
81	CONSTRAINT FK_car FOREIGN KEY (CarId) REFERENCES cars(Id) ON UPDATE CASCADE
82)
83GO
84
85INSERT regions(Id, rName) VALUES
86	(1, 'Свердловская область'),
87	(2, 'Республика Дагестан'),
88	(3, 'Московская область'),
89	(4, 'Республика Мордовия'),
90	(5, 'Республика Бурятия')
91GO
92
93INSERT codes(Code, NameId) VALUES
94	(66, 1),
95	(96, 1),
96	(5, 2),
97	(777, 3),
98	(13, 4),
99	(3, 5)
100GO
101
102INSERT car_marks(Id, Name) VALUES
103	(1, 'Черная молния'),
104	(2, 'Богдан'),
105	(3, 'Пегас'),
106	(4, 'Тыква')
107GO
108
109INSERT colors(Id, Name) VALUES
110	(1, 'Нежно черный'),
111	(2, 'Цвет свежего ветра'),
112	(3, 'Черниковый'),
113	(4, 'Баклажановый')
114GO
115
116INSERT cars(Id, MarkId, ColorId, DriverSurname, RegionCode, Plate) VALUES
117	(1, 1, 1, 'Пупков', 66, 'М444НУ'),
118	(2, 2, 2, 'Сталин', 96, 'Т228НУ'),
119	(3, 3, 3, 'Фримен', 5, 'О777ВН'),
120	(4, 4, 4, 'Смиттерс', 777, 'А137ТА')
121GO
122
123INSERT passes(PostId, Direction, CarId, PassDate) VALUES
124	(1, 1, 2, '20160101'),
125	(0, 0, 2, '20160106')
126GO
127
128CREATE FUNCTION is_transit (
129	@id int
130)
131RETURNS TABLE
132	RETURN (
133		SELECT b.CarId FROM passes a, passes b
134			WHERE b.CarId = @id AND
135				  a.CarId = @id AND
136				  b.Direction != a.Direction AND
137				  b.PostId != a.PostId
138	)
139GO
140
141PRINT N'Транзитные'
142SELECT Plate, regions.rName, car_marks.Name, colors.Name, DriverSurname FROM cars
143	INNER JOIN codes ON codes.Code = cars.RegionCode
144	INNER JOIN regions ON regions.Id = codes.NameId
145	INNER JOIN car_marks ON car_marks.Id = cars.MarkId
146	INNER JOIN colors ON colors.Id = cars.ColorId
147	WHERE
148		codes.NameId != 1 AND
149		EXISTS(SELECT * FROM dbo.is_transit(cars.Id))