KcQa8U2m

1USE master
2GO
3
4
5IF EXISTS (
6    SELECT  name
7    FROM    sys.databases
8    WHERE   name = N'Mamonov' )
9ALTER DATABASE [Mamonov] set single_user with rollback immediate
10GO
11
12
13IF EXISTS (
14    SELECT  name
15    FROM    sys.databases
16    WHERE   name = N'Mamonov' )
17DROP DATABASE [Mamonov]
18GO
19
20
21CREATE DATABASE [Mamonov]
22GO
23
24
25USE [Mamonov]
26GO
27
28
29
30IF OBJECT_ID('Mamonov.Companies', 'U') IS NOT NULL
31    DROP TABLE  Mamonov.Companies
32GO
33
34
35CREATE TABLE Companies (
36    id  tinyint,
37    name_company nvarchar(30),
38    CONSTRAINT PK_Company PRIMARY KEY (id)
39)
40GO
41
42
43INSERT INTO Companies (id, name_company) VALUES
44	(1, N'Российско-государственная'),
45	(2, N'Частная российская'),
46	(3, N'Иностранно-государственная'),
47	(4, N'Частная иностранная')
48GO
49
50
51
52IF OBJECT_ID('Mamonov.RegularFlights', 'U') IS NOT NULL
53    DROP TABLE  Mamonov.RegularFlights
54GO
55
56
57
58CREATE TABLE RegularFlights (
59    id_company  TINYINT,
60	number_flights nvarchar(20),
61    type_aircraft nvarchar(20),
62	departure_city nvarchar(20),
63	arrival_city nvarchar(20),
64	departure_time time(0),
65	arrival_time time(0),
66    CONSTRAINT FK_company FOREIGN KEY (id_company) REFERENCES Companies(id) ON UPDATE CASCADE
67)
68GO
69
70
71INSERT INTO RegularFlights (id_company, number_flights, type_aircraft, departure_city, arrival_city,
72							departure_time, arrival_time) VALUES
73	(1, N'su_>_1', N'su', 'Екатеринбург', 'Иерусалим', '12:00', '17:00'),
74	(1, N'su_<_1', N'su', 'Иерусалим', 'Екатеринбург', '09:00', '14:00'),
75	(1, N'ty_>_1', N'ty', 'Москва', 'Париж', '15:00', '03:00'),
76	(1, N'ty_<_1', N'ty', 'Париж', 'Москва', '15:00', '03:00'),
77	(1, N'mig_>_1', N'mig', 'Верхняя Пышма', 'Москва', '06:00', '08:30'),
78	(1, N'mig_<_1', N'mig', 'Москва', 'Верхняя Пышма', '14:00', '16:30'),
79
80	(2, N'su_>_2', N'su', 'Екатеринбург', 'Иерусалим', '12:00', '17:00'),
81	(2, N'su_<_2', N'su', 'Иерусалим', 'Екатеринбург', '09:00', '14:00'),
82
83	(3, N'su_>_3', N'su', 'Екатеринбург', 'Иерусалим', '12:00', '17:00'),
84	(3, N'su_<_3', N'su', 'Иерусалим', 'Екатеринбург', '09:00', '14:00'),
85	(3, N'ty_>_3', N'ty', 'Москва', 'Париж', '15:00', '03:00'),
86	(3, N'ty_<_3', N'ty', 'Париж', 'Москва', '15:00', '03:00'),
87
88	(4, N'ty_>_4', N'ty', 'Москва', 'Париж', '15:00', '03:00'),
89	(4, N'ty_<_4', N'ty', 'Париж', 'Москва', '15:00', '03:00')
90GO
91
92
93
94IF OBJECT_ID('Mamonov.Flights', 'U') IS NOT NULL
95    DROP TABLE  Mamonov.Flights
96GO
97
98
99
100CREATE TABLE Flights (
101    number_flights nvarchar(20),
102	departure_date date,
103	passenger nvarchar(40),
104	seat nvarchar(3),
105	CONSTRAINT CHK_seat CHECK (seat LIKE '[a-d][0-9][0-9]' or seat LIKE '[a-d][0-9]'),
106)
107GO
108
109INSERT INTO Flights (number_flights, departure_date, passenger, seat) VALUES
110	-- 1
111	(N'su_>_1','20170205', 'Мамонов', 'a1'),
112	-- одно место занято 2-мя людьми
113	-- (N'su->_1','20170205', 'Неправильнов_1', 'b2'),
114	-- однофамилей на другом месте
115	(N'su_>_1','20170205', 'Мамонов', 'a2'),
116	(N'su_>_1','20170205', 'Иванов', 'b2'),
117	(N'su_>_1','20170205', 'Петров', 'c3'),
118	(N'su_>_1','20170205', 'Сидоров', 'd4'),
119	-- неправильный формат места
120	--(N'su->_1','20170205', 'Неправильнов_2', 'qwe')
121
122	--2
123	(N'su_<_1','20170205', 'Мамонов', 'a1'),
124	(N'su_<_1','20170205', 'Мамонов', 'a2'),
125	(N'su_<_1','20170205', 'Иванов', 'b2'),
126	(N'su_<_1','20170205', 'Петров', 'c3'),
127	(N'su_<_1','20170205', 'Сидоров', 'd4')
128GO
129
130
131
132
133
134DECLARE @timeFrom time(0) = '12:34';
135print @timeFrom
136
137-- 1) Вывести список компаний с указанием количества рейсов, осуществляющими ими.
138SELECT Companies.name_company as "Название компании",
139	   COUNT(RegularFlights.number_flights) as "Количество рейсов"
140FROM Companies
141INNER JOIN
142	RegularFlights ON RegularFlights.id_company = Companies.id
143GROUP BY Companies.name_company, Companies.id
144ORDER BY Companies.id
145GO
146
147
148
149
150
151
152SELECT COUNT(Flights.number_flights)
153FROM Flights
154GROUP BY Flights.number_flights
155GO
156
157-- 2) Вывести информацию о рейсе, перевезшем максимальное количество пассажиров.
158SELECT RegularFlights.number_flights as "Рейс",
159	   RegularFlights.type_aircraft as "Самолёт",
160	   RegularFlights.departure_city as "Город отправления",
161	   RegularFlights.arrival_city as "Город прибытия",
162	   RegularFlights.departure_time as "Время отправления",
163	   RegularFlights.arrival_time as "Время прибытия",
164	   COUNT(Flights.number_flights) as "Количество перевезённых пассажиров"
165FROM RegularFlights
166INNER JOIN
167	Flights ON Flights.number_flights = RegularFlights.number_flights
168GROUP BY RegularFlights.number_flights, RegularFlights.type_aircraft, RegularFlights.departure_city, RegularFlights.arrival_city,
169		 RegularFlights.departure_time, RegularFlights.arrival_time
170HAVING COUNT(Flights.number_flights) = (
171										SELECT MAX (COUNT(Flights.number_flights))
172										FROM Flights
173										)
174GO