· 5 years ago · Oct 07, 2020, 08:46 PM
1-- убить все соединения
2USE master;
3GO
4DECLARE @kill varchar(8000) = '';
5
6SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(5), c.session_id) + ';'
7FROM sys.dm_exec_connections AS c
8JOIN sys.dm_exec_sessions AS s
9 ON c.session_id = s.session_id
10WHERE c.session_id <> @@SPID
11ORDER BY c.connect_time ASC
12
13EXEC(@kill)
14GO
15
16
17USE master;
18GO
19--удаление БД
20IF DB_ID (N'Skyscanner') IS NOT NULL
21DROP DATABASE Skyscanner;
22GO
23
24--создание БД
25CREATE DATABASE Skyscanner
26ON ( NAME = Skyscanner_dat,
27FILENAME = '/tmp/data/skyscannerdat.mdf',
28SIZE = 10, MAXSIZE = 150, FILEGROWTH = 5% )
29LOG ON ( NAME = Skyscanner_log,
30FILENAME = '/tmp/data/skyscannerdat.ldf',
31SIZE = 5MB, MAXSIZE = 150MB, FILEGROWTH = 5MB )
32GO
33
34USE Skyscanner;
35GO
36
37--Аэропорт
38IF OBJECT_ID (N'AIRPORTS') IS NOT NULL
39DROP TABLE AIRPORTS;
40GO
41
42CREATE TABLE AIRPORTS (
43 AirportCode NVARCHAR(10) NOT NULL PRIMARY KEY,
44 AirportName NVARCHAR(50) NOT NULL,
45 EnglishName NVARCHAR(50) NULL,
46 Country NVARCHAR(50) NOT NULL,
47 City NVARCHAR (50) NOT NULL
48)
49GO
50
51INSERT INTO AIRPORTS (AirportCode, AirportName, EnglishName, Country, City)
52VALUES ('SVO', N'Шереметьево', 'Sheremetyevo', N'Россия', N'Москва'),
53 --('VKO', N'Внуково', 'Vnukovo', N'Россия', N'Москва'),
54 ('DME', N'Домодедово', 'Domodedovo', N'Россия', N'Москва'),
55 --('BKA', N'Быково', 'Bykovo', N'Россия', N'Москва'),
56 --('ZIA', N'Жуковский', 'Zhukovsky', N'Россия', N'Москва'),
57 --('RVH', N'Ржевка', 'Rzhevka', N'Россия', N'Санкт-Петербург'),
58 ('LED', N'Пулково', 'Pulkovo', N'Россия', N'Санкт-Петербург'),
59 --('UUD', N'Мухино', 'Mukhino', N'Россия', N'Улан-Удэ'),
60 ('SVX', N'Кольцово', 'Koltsovo', N'Россия', N'Екатеринбург'),
61 ('ABA', N'Абакан', 'Abakan', N'Россия', N'Абакан'),
62 --('MRV', N'Минеральные Воды', 'Mineralnye Vodyj', N'Россия', N'Минеральные Воды'),
63 ('CEK', N'Челябинск', 'Chelyabinsk', N'Россия', N'Челябинск');
64 --('IKT', N'Иркутск', 'Irkutsk', N'Россия', N'Иркутск'),
65 --('KUF', N'Курумоч', 'Kurumoch', N'Россия', N'Самара');
66GO
67
68SELECT * FROM AIRPORTS
69GO
70
71IF EXISTS (SELECT * FROM sys.indexes WHERE name = N'AirportsIdx')
72DROP INDEX AirportsIdx ON AIRLINES;
73GO
74
75CREATE INDEX AirportsIdx
76 ON AIRPORTS (AirportCode, AirportName, City DESC)
77GO
78
79SELECT AirportCode, AirportName, City
80FROM AIRPORTS
81WHERE City != N'Москва';
82GO
83
84
85--------------------------------------------------------
86--Авиакомпания
87IF OBJECT_ID (N'AIRLINES') IS NOT NULL
88DROP TABLE AIRLINES;
89GO
90
91CREATE TABLE AIRLINES (
92 AirlineId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
93 AirlineName NVARCHAR(50) NOT NULL,
94 PhoneNumber NVARCHAR(50) NULL
95)
96GO
97
98INSERT INTO AIRLINES (AirlineName, PhoneNumber)
99VALUES (N'Россия', N'8 800-444-55-55; 8 495-139-77-77'),
100 (N'S7', N'+7 495 783-07-07'),
101 (N'Победа', N'8-809-505-47-77'),
102 (N'Аэрофлот', N'8 (800) 444-55-55; +7 (495) 223-55-55'),
103 (N'Ural airlines', N'8 800-7700-262; +7 (499) 920-22-52');
104GO
105
106SELECT * FROM AIRLINES
107GO
108
109IF EXISTS (SELECT * FROM sys.indexes WHERE name = N'AirlinesIdx')
110DROP INDEX AirlinesIdx ON AIRLINES;
111GO
112
113CREATE INDEX AirlinesIdx
114 ON AIRLINES (AirlineId, AirlineName ASC)
115GO
116
117SELECT AirlineId, AirlineName
118FROM AIRLINES
119--WHERE AirlineName <= 150;
120GO
121
122--------------------------------------------------------------
123
124--Маршрут
125IF OBJECT_ID (N'ROUTES') IS NOT NULL
126DROP TABLE ROUTES;
127GO
128
129CREATE TABLE ROUTES (
130 RouteId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
131 DeparturePoint NVARCHAR(50) NOT NULL,
132 DepartureAirport NVARCHAR(50) NOT NULL,
133 ArrivalPoint NVARCHAR(50) NOT NULL,
134 ArrivalAirport NVARCHAR (50) NOT NULL,
135 AverageDuration NVARCHAR(50) NOT NULL,
136
137 --FOREIGN KEY (AirlineName) REFERENCES AIRLINES (AirlineName)
138
139)
140GO
141
142INSERT INTO ROUTES (DeparturePoint, DepartureAirport, ArrivalPoint, ArrivalAirport, AverageDuration)
143VALUES (N'Москва', N'Домодедово', N'Екатеринбург', N'Кольцово', 150),
144 (N'Москва', N'Домодедово', N'Екатеринбург', N'Кольцово', 135),
145 (N'Москва', N'Шереметьево', N'Екатеринбург', N'Кольцово', 145),
146 (N'Санкт-Петербург', N'Пулково', N'Москва', N'Домодедово', 90),
147 (N'Москва', N'Шереметьево', N'Абакан', N'Абакан', 280),
148 (N'Екатеринбург', N'Кольцово', N'Санкт-Петербург', N'Пулково', 170),
149 (N'Москва', N'Шереметьево', N'Челябинск', N'Челябинск', 145);
150GO
151
152SELECT * FROM ROUTES
153GO
154
155IF EXISTS (SELECT * FROM sys.indexes WHERE name = N'RoutesIdx')
156DROP INDEX RoutesIdx ON ROUTES;
157GO
158
159CREATE INDEX RoutesIdx
160 ON ROUTES (DepartureAirport, ArrivalAirport, AverageDuration DESC)
161GO
162
163SELECT DepartureAirport, ArrivalAirport, AverageDuration
164FROM ROUTES
165WHERE AverageDuration <= 150;
166GO
167
168
169--Самолет
170IF OBJECT_ID (N'AIRPLANES') IS NOT NULL
171DROP TABLE AIRPLANES;
172GO
173
174CREATE TABLE AIRPLANES (
175 BoardNumber INT NOT NULL PRIMARY KEY,
176 Model NVARCHAR(50) NOT NULL,
177 SeatsNumber INT NULL DEFAULT 'Unknown'
178)
179GO
180
181INSERT INTO AIRPLANES (BoardNumber, Model, SeatsNumber)
182VALUES (1, N'Airbus A320', 150),
183 (2, N'Airbus A319', 128),
184 (3, N'Boeing 737-800', 158),
185 (4, N'Boeing 777-300', 402);
186GO
187
188SELECT * FROM AIRPLANES
189GO
190
191IF EXISTS (SELECT * FROM sys.indexes WHERE name = N'AirplanesIdx')
192DROP INDEX AirplanesIdx ON AIRPLANES;
193GO
194
195CREATE INDEX AirplanesIdx
196 ON AIRPLANES (BoardNumber, Model, SeatsNumber DESC)
197GO
198
199SELECT BoardNumber, Model, SeatsNumber
200FROM AIRPLANES
201WHERE SeatsNumber BETWEEN 100 AND 200;
202GO
203
204
205--Рейс
206IF OBJECT_ID (N'FLIGHTS') IS NOT NULL
207DROP TABLE FLIGHTS;
208GO
209
210CREATE TABLE FLIGHTS (
211 FlightNumber INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
212 DepartureDate DATETIME NOT NULL,
213 DepartureTime DATETIME NOT NULL,
214 ArrivalDate DATETIME NOT NULL,
215 ArrivalTime DATETIME NOT NULL,
216 RouteId INT NOT NULL,
217 BoardNumber INT NULL,
218 AirlineId INT NOT NULL,
219 FOREIGN KEY (AirlineId) REFERENCES AIRLINES (AirlineId),
220 FOREIGN KEY (RouteId) REFERENCES ROUTES (RouteId)
221)
222GO
223
224INSERT INTO FLIGHTS (DepartureDate, DepartureTime, ArrivalDate, ArrivalTime, RouteId, BoardNumber, AirlineId)
225VALUES ('2020-07-07', '21:30:00', '2020-07-07', '23:45:00', 2, 3, 5),
226 ('2020-07-07', '23:55:00', '2020-07-08', '02:20:00', 3, 4, 4),
227 ('2020-07-05', '22:00:00', '2020-07-06', '00:30:00', 1, 2, 2),
228 ('2020-07-31', '00:05:00', '2020-07-31', '04:45:00', 5, 2, 4),
229 ('2020-07-31', '11:50:00', '2020-07-31', '14:15:00', 7, 2, 4),
230 ('2020-07-11', '18:10:00', '2020-07-11', '21:00:00', 6, 2, 3),
231 ('2020-07-07', '21:30:00', '2020-07-07', '23:00:00', 4, 1, 2);
232GO
233
234SELECT * FROM FLIGHTS
235GO
236
237IF EXISTS (SELECT * FROM sys.indexes WHERE name = N'FlightsIdx')
238DROP INDEX FlightsIdx ON FLIGHTS;
239GO
240
241CREATE INDEX FlightsIdx
242 ON FLIGHTS (DepartureDate, ArrivalDate DESC)
243 INCLUDE (DepartureTime, ArrivalTime)
244GO
245
246SELECT DepartureDate, ArrivalDate
247FROM FLIGHTS
248WHERE DepartureDate = ArrivalDate;
249GO
250
251--Пасажир
252IF OBJECT_ID (N'PASSENGERS') IS NOT NULL
253DROP TABLE PASSENGERS;
254GO
255CREATE TABLE PASSENGERS(
256 PassengerId INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY,
257 PassengerName NVARCHAR(50) NOT NULL,
258 Gender NVARCHAR(10) NULL
259)
260GO
261
262INSERT INTO PASSENGERS (PassengerName, Gender)
263VALUES (N'Петров Петр Петрович', N'м'),
264 (N'Иванов Иван Иванович', N'м'),
265 (N'Алексеева Ангелина Николаевна', N'ж'),
266 (N'Васильев Михаил Александрович', N'м');
267GO
268
269SELECT * FROM PASSENGERS
270GO
271
272IF EXISTS (SELECT * FROM sys.indexes WHERE name = N'PassengersIdx')
273DROP INDEX PassengersIdx ON PASSENGERS;
274GO
275
276CREATE INDEX PassengersIdx
277 ON PASSENGERS (PassengerId)
278 INCLUDE (PassengerName, Gender)
279GO
280
281SELECT PassengerID, PassengerName, Gender
282FROM PASSENGERS
283WHERE Gender = N'м';
284GO
285
286
287--Билет
288IF OBJECT_ID (N'TICKETS') IS NOT NULL
289DROP TABLE TICKETS;
290GO
291
292CREATE TABLE TICKETS (
293 TicketNumber INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
294 PassengerId INT NOT NULL,
295 Plane INT NOT NULL,
296 Class NVARCHAR(50) NOT NULL,
297 Seat INT NOT NULL,
298 Price NVARCHAR(50) NULL DEFAULT 'Unknown',
299 --Discount NVARCHAR(50) NULL DEFAULT 'Unknown',
300 Reservation NVARCHAR (50) NULL,
301 FlightNumber INT NOT NULL,
302 AirlineId INT NOT NULL,
303 FOREIGN KEY (PassengerId) REFERENCES PASSENGERS(PassengerId),
304 FOREIGN KEY (Plane) REFERENCES AIRPLANES(BoardNumber),
305 FOREIGN KEY (FlightNumber) REFERENCES FLIGHTS(FlightNumber),
306 FOREIGN KEY (AirlineId) REFERENCES AIRLINES(AirlineId)
307)
308Go
309
310INSERT INTO TICKETS (PassengerId, Plane, Class, Seat, Price, Reservation, FlightNumber, AirlineId)
311VALUES (2, 1, N'Эконом класс', 1, 3771, N'Зарезервирован', 3, 2),
312 (1, 2, N'Эконом класс', 38, 2999, N'Зарезервирован', 4, 2),
313 (4, 3, N'Бизнес-класс', 20, 54434, N'Не зарезервирован',2, 4),
314 (3, 4, N'Эконом класс', 10, 2450, N'Зарезервирован', 1, 5);
315GO
316
317SELECT * FROM TICKETS
318GO
319
320IF EXISTS (SELECT * FROM sys.indexes WHERE name = N'TicketsIdx')
321DROP INDEX TicketsIdx ON TICKETS;
322GO
323
324CREATE INDEX TicketsIdx
325 ON TICKETS (PassengerID ASC)
326 INCLUDE (Plane , Class)
327GO
328
329SELECT PassengerID, Plane, Class
330FROM TICKETS
331WHERE Class = N'Эконом класс';
332GO
333
334
335IF OBJECT_ID (N'Flight') IS NOT NULL
336DROP VIEW Flight;
337GO
338
339CREATE VIEW Flight AS
340SELECT
341 a.DeparturePoint, a.DepartureAirport, a.ArrivalPoint, a.ArrivalAirport, a.AverageDuration, b.AirlineName, f.DepartureDate
342 FROM FLIGHTS f INNER JOIN AIRLINES b
343 ON b.AirlineId = f.AirlineId
344 INNER JOIN ROUTES a
345 ON f.RouteId = a.RouteId
346 -- WITH CHECK OPTION
347GO
348
349SELECT * FROM Flight;
350GO
351
352
353IF OBJECT_ID (N'Ticket') IS NOT NULL
354DROP VIEW Ticket;
355GO
356
357CREATE VIEW Ticket AS
358SELECT
359 a.TicketNumber, p.PassengerName, b.Model, a.Class, a.Seat, a.Price, a.Reservation, f.DepartureDate, f.ArrivalDate, c.AirlineName
360 FROM TICKETS a INNER JOIN AIRPLANES b
361 ON b.BoardNumber = a.Plane
362 INNER JOIN FLIGHTS f
363 ON f.FlightNumber = a.FlightNumber
364 INNER JOIN PASSENGERS p
365 ON p.PassengerId = a.PassengerId
366 INNER JOIN AIRLINES c
367 ON c.AirlineId = a.AirlineId
368 -- WITH CHECK OPTION
369GO
370
371SELECT * FROM Ticket;
372GO
373
374IF OBJECT_ID(N'Ticket_Qry', N'VBookId') is not NULL
375DROP VIEW Ticket_Qry;
376GO
377
378CREATE VIEW Ticket_Qry
379 WITH SCHEMABINDING
380AS
381 SELECT a.TicketNumber, a.PassengerId, a.Plane, a.Class, a.Price
382 FROM dbo.TICKETS a
383 WHERE a.TicketNumber = a.TicketNumber
384 WITH CHECK OPTION
385GO
386
387IF EXISTS (SELECT * FROM sys.indexes WHERE name = N'Ticket_Qry_Idx')
388DROP INDEX Ticket_Qry_Idx ON Book;
389GO
390
391CREATE UNIQUE CLUSTERED INDEX Ticket_Qry_Idx
392 ON Ticket_Qry (TicketNumber)
393GO
394
395
396--Выводим все индексы в БД
397--SELECT * FROM sysindexes
398--GO
399
400
401--Преобразование коллекций JSON в набор строк
402DECLARE @json NVARCHAR(MAX);
403SET @json = N'[
404 {"id": 1, "info": {"name": "Ivan", "surname": "Ivanov", "route": "Санкт-Петербург - Москва", "seats": ["30"]}, "gender": "м"},
405 {"id": 2, "info": {"name": "Petr", "surname": "Petrov", "route": "Санкт-Петербург - Москва", "seats": ["32", "33", "34"]}, "gender": "м"},
406 {"id": 3, "info": {"name": "Michail", "surname": "Vasiliev", "route": "Москва - Екатеринбург", "seats": ["5", "6"]}, "gender": "м"},
407 {"id": 4, "info": {"name": "Angelina", "surname": "Alekseeva", "route": "Москва - Челябинск", "seats": ["1"]}, "gender": "ж"},
408 {"id": 5, "info": {"name": "Elena", "surname": "Krepostnaya", "route": "Москва - Абакан", "seats": ["40", "41", "42","43"]}, "gender": "ж"} ]';
409
410SELECT *
411FROM OPENJSON(@json) --преобразуем значение JSON в таблицу
412 WITH (
413 id INT 'strict $.id',
414 firstName NVARCHAR(50) '$.info.name',
415 lastName NVARCHAR(50) '$.info.surname',
416 gender NVARCHAR(10),
417 route NVARCHAR(MAX) '$.info.route',
418 dateOfBirth DATETIME2 '$.dob',
419 seates NVARCHAR(MAX) '$.info.seats' AS JSON
420 )
421OUTER APPLY OPENJSON(seates)
422 WITH (seat NVARCHAR(8) '$');
423