· 8 years ago · Jan 21, 2018, 03:36 PM
1use master
2go
3
4
5if exists (
6 select name
7 from sys.databases
8 where name = N'yuliyayakovenko' )
9alter database [yuliyayakovenko] set single_user with rollback immediate
10go
11
12
13if exists (
14 select name
15 from sys.databases
16 where name = N'yuliyayakovenko' )
17drop database [yuliyayakovenko]
18go
19
20
21create database [yuliyayakovenko]
22go
23
24
25use [yuliyayakovenko]
26go
27
28
29
30if object_id('yuliyayakovenko.Companies', 'U') is not null
31 drop table yuliyayakovenko.Companies
32go
33
34
35create table Companies (
36 id int,
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'КомпаниÑ1'),
45 (2, N'КомпаниÑ2'),
46 (3, N'КомпаниÑ3'),
47 (4, N'КомпаниÑ4')
48go
49
50
51
52if object_id('yuliyayakovenko.RegularFlights', 'U') is not null
53 drop table yuliyayakovenko.RegularFlights
54go
55
56
57
58create table RegularFlights (
59 id_company int,
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
74 /*
75 su: Город1 ---> Город2
76 ty: Город3 ---> Город4
77 mig: Город5 ---> Город3
78 */
79
80
81 (1, N'su_>_1', N'su', 'Город1', 'Город2', '12:20', '17:00'),
82 (1, N'su_<_1', N'su', 'Город2', 'Город1', '09:20', '14:00'),
83 (1, N'ty_>_1', N'ty', 'Город3', 'Город4', '15:15', '03:00'),
84 (1, N'ty_<_1', N'ty', 'Город4', 'Город3', '15:15', '03:00'),
85 (1, N'mig_>_1', N'mig', 'Город5', 'Город3', '06:25', '08:30'),
86 (1, N'mig_<_1', N'mig', 'Город3', 'Город5', '14:25', '16:30'),
87
88 (2, N'su_>_2', N'su', 'Город1', 'Город2', '12:20', '17:00'),
89 (2, N'su_<_2', N'su', 'Город2', 'Город1', '09:20', '14:00'),
90
91 (3, N'su_>_3', N'su', 'Город1', 'Город2', '12:20', '17:00'),
92 (3, N'su_<_3', N'su', 'Город2', 'Город1', '09:20', '14:00'),
93 (3, N'ty_>_3', N'ty', 'Город3', 'Город4', '15:15', '03:00'),
94 (3, N'ty_<_3', N'ty', 'Город4', 'Город3', '15:15', '03:00'),
95
96 (4, N'ty_>_4', N'ty', 'Город3', 'Город4', '15:15', '03:00'),
97 (4, N'ty_<_4', N'ty', 'Город4', 'Город3', '15:15', '03:00')
98go
99
100
101
102if object_id('yuliyayakovenko.Flights', 'U') is not null
103 drop table yuliyayakovenko.Flights
104go
105
106
107
108create table Flights (
109 number_flights nvarchar(20),
110 departure_date date,
111 passenger nvarchar(40),
112 seat nvarchar(3),
113 constraint CHK_seat check (seat like '[a-d][0-9][0-9]' or seat like '[a-d][0-9]'),
114)
115go
116
117
118if object_id('yuliyayakovenko.Duplicate_trigger', 'TR') is not null
119 drop trigger yuliyayakovenko.Duplicate_trigger
120go
121
122create trigger Duplicate_trigger
123on Flights
124after insert
125as
126if exists (
127 select this.seat
128 from Flights this, Flights that
129 where this.seat = that.seat and this.number_flights = that.number_flights and this.departure_date = that.departure_date
130 group by this.seat, this.number_flights, this.departure_date
131 having count(*) > 1
132)
133begin
134 raiserror ('ÐеÑколько человек ÑидÑÑ‚ на одном меÑте', 10, 1)
135 rollback transaction
136 return
137end
138go
139
140
141insert into Flights (number_flights, departure_date, passenger, seat) values
142 (N'su_>_2','20170205', 'ФамилиÑ1', 'a1'),
143 --(N'su_>_2','20170205', 'Ðа том же меÑте', 'a1'),
144 -- однофамилец на другом меÑте
145 (N'su_>_2','20170207', 'ФамилиÑ1', 'a2'),
146 (N'su_>_3','20170205', 'ФамилиÑ2', 'b2'),
147 (N'su_>_3','20170205', 'ФамилиÑ3', 'c3'),
148 (N'su_>_3','20170205', 'ФамилиÑ4', 'd4'),
149 --(N'su->_1','20170205', 'Формат меÑта', 'qwe'),
150
151 (N'su_<_2','20170214', 'ФамилиÑ1', 'a1'),
152 (N'su_<_2','20170216', 'ФамилиÑ1', 'a2'),
153 (N'su_<_3','20170214', 'ФамилиÑ2', 'b2'),
154 (N'su_<_3','20170214', 'ФамилиÑ3', 'c3'),
155 (N'su_<_3','20170214', 'ФамилиÑ4', 'd4'),
156
157 (N'ty_>_3','20170214', 'people 1', 'd4'),
158 (N'ty_>_3','20170214', 'people 2', 'd5'),
159 (N'ty_<_3','20170218', 'people 3', 'd4'),
160 (N'ty_<_3','20170218', 'people 4', 'd3'),
161
162 (N'mig_>_1', '20170312', 'ФамилиÑ1', 'c2'),
163 (N'mig_<_1', '20170314', 'ФамилиÑ1', 'c4'),
164
165 (N'mig_>_1', '20170312', 'no name 1', 'd7'),
166 (N'mig_>_1', '20170320', 'no name 2', 'a1'),
167 (N'mig_>_1', '20170320', 'no name 3', 'a2'),
168 (N'mig_>_1', '20170321', 'no name 4', 'a2'),
169
170
171 (N'ty_>_4', '20170316', 'не ФамилиÑ1 1', 'a1'),
172 (N'ty_>_4', '20170316', 'не ФамилиÑ1 2', 'b2'),
173 (N'ty_>_4', '20170316', 'не ФамилиÑ1 3', 'c3'),
174
175 (N'ty_<_4', '20170320', 'не ФамилиÑ1 1', 'a3')
176go
177
178select Companies.name_company as "Ðазвание компании",
179 count(RegularFlights.number_flights) as "КоличеÑтво рейÑов"
180from Companies
181inner join
182 RegularFlights on RegularFlights.id_company = Companies.id
183group by Companies.name_company, Companies.id
184order by Companies.id
185go
186
187select RegularFlights.number_flights as "РейÑ",
188 RegularFlights.type_aircraft as "Самолёт",
189 RegularFlights.departure_city as "Город отправлениÑ",
190 RegularFlights.arrival_city as "Город прибытиÑ",
191 RegularFlights.departure_time as "Ð’Ñ€ÐµÐ¼Ñ Ð¾Ñ‚Ð¿Ñ€Ð°Ð²Ð»ÐµÐ½Ð¸Ñ",
192 RegularFlights.arrival_time as "Ð’Ñ€ÐµÐ¼Ñ Ð¿Ñ€Ð¸Ð±Ñ‹Ñ‚Ð¸Ñ",
193 count(Flights.number_flights) as "КоличеÑтво перевезённых паÑÑажиров"
194from RegularFlights
195inner join
196 Flights on Flights.number_flights = RegularFlights.number_flights
197group by RegularFlights.number_flights, RegularFlights.type_aircraft, RegularFlights.departure_city, RegularFlights.arrival_city,
198 RegularFlights.departure_time, RegularFlights.arrival_time
199having count(Flights.number_flights) = (
200 select TOP 1 count(Flights.number_flights)
201 from Flights
202 group by Flights.number_flights
203 order by count(Flights.number_flights) desc
204 )
205go
206
207declare @given_sity nvarchar(20) = 'Город5';
208
209select day(Flights.departure_date) as "Дни вылета",
210 @given_sity as "Указанный город"
211from Flights
212inner join
213 RegularFlights on Flights.number_flights = RegularFlights.number_flights
214where RegularFlights.departure_city = @given_sity
215group by Flights.departure_date
216having count(Flights.number_flights) = (
217 select TOP 1 count(Flights.number_flights)
218 from Flights
219 inner join
220 RegularFlights on Flights.number_flights = RegularFlights.number_flights
221 where RegularFlights.departure_city = @given_sity
222 group by Flights.departure_date
223 order by count(Flights.number_flights) desc
224 )
225go
226
227if object_id('yuliyayakovenko.Departures', 'U') is not null
228 drop table yuliyayakovenko.Departures
229go
230
231select distinct Flights.number_flights, Flights.departure_date into Departures from Flights
232
233select Companies.name_company as "КомпаниÑ",
234 sum((datediff(minute, RegularFlights.departure_time, RegularFlights.arrival_time) + 60 * 24) % (60 * 24))
235 as "Суммарное времÑ, которое провели её Ñамолеты в небе"
236from Companies
237inner join
238 RegularFlights on Companies.id = RegularFlights.id_company
239inner join
240 Departures on Departures.number_flights = RegularFlights.number_flights
241group by Companies.name_company, Companies.id
242go
243
244declare @given_date date = '20170214';
245select Companies.name_company as "КомпаниÑ",
246 count(Flights.number_flights) as "КоличеÑтво перевезённых паÑÑажиров"
247from Companies
248inner join
249 RegularFlights on Companies.id = RegularFlights.id_company
250inner join
251 Flights on Flights.number_flights = RegularFlights.number_flights
252where Flights.departure_date = @given_date
253group by Companies.name_company
254order by count(Flights.number_flights) desc
255go
256
257
258
259
260-- ПРОВЕРКРТРИГГЕРÐ
261--insert into Flights (number_flights, departure_date, passenger, seat) values
262-- (N'su_>_2','20170205', 'ФамилиÑ1', 'a1')
263--go