· 8 years ago · Nov 30, 2017, 09:28 PM
1USE master
2GO
3
4IF EXISTS (
5 SELECT name
6 FROM sys.databases
7 WHERE name = N'Nagaev'
8)
9ALTER DATABASE [Nagaev] set single_user with rollback immediate
10GO
11
12IF EXISTS (
13 SELECT name
14 FROM sys.databases
15 WHERE name = N'Nagaev'
16)
17DROP DATABASE Nagaev
18GO
19
20CREATE DATABASE Nagaev
21GO
22
23USE Nagaev
24GO
25
26CREATE TABLE club (
27 id int PRIMARY KEY IDENTITY NOT NULL,
28 name nvarchar(128)
29)
30
31CREATE TABLE position (
32 id tinyint PRIMARY KEY NOT NULL,
33 name varchar(3) NOT NULL
34)
35go
36
37CREATE TABLE player (
38 id int PRIMARY KEY NOT NULL,
39 club_id int,
40 first_name nvarchar(30),
41 second_name nvarchar(30),
42 position_id tinyint NOT NULL,
43
44 CONSTRAINT FK_club FOREIGN KEY (club_id) REFERENCES club(id),
45 CONSTRAINT FK_position FOREIGN KEY (position_id) REFERENCES position(id)
46
47)
48
49CREATE TABLE match (
50 id int PRIMARY KEY NOT NULL,
51 home_club_id int NOT NULL,
52 guest_club_id int NOT NULL,
53 home_score tinyint NOT NULL,
54 guest_score tinyint NOT NULL,
55 match_date date NOT NULL,
56
57 CONSTRAINT FK_home_club FOREIGN KEY (home_club_id) REFERENCES club(id),
58 CONSTRAINT FK_guest_club FOREIGN KEY (guest_club_id) REFERENCES club(id),
59)
60
61CREATE TABLE goal (
62 id int PRIMARY KEY IDENTITY NOT NULL,
63 match_id int NOT NULL,
64 player_id int NOT NULL,
65
66 CONSTRAINT FK_match FOREIGN KEY (match_id) REFERENCES match(id),
67 CONSTRAINT FK_player FOREIGN KEY (player_id) REFERENCES player(id)
68)
69go
70
71insert into position values
72(1, 'GK'),
73(2, 'LWB'),
74(3, 'RWB'),
75(4, 'LB'),
76(5, 'RB'),
77(6, 'CB'),
78(7, 'CDM'),
79(8, 'CM'),
80(9, 'LWM'),
81(10, 'RWM'),
82(11, 'CAM'),
83(12, 'ST')
84go
85
86insert into club Values
87('Chelsea'),
88('Manchester United'),
89('Manchester City')
90go
91
92insert into player values
93(1, 1, 'Thibaut', 'Cortois', 1),
94(3, 1, 'Marcos', 'Alonso', 2),
95(4, 1, 'Victor', 'Moses', 3),
96(5, 1, 'David', 'Luiz', 4),
97(6, 1, 'Gary', 'Cahill', 6),
98(7, 1, 'Cesar', 'Azplilicueta', 5),
99(8, 1, 'Ngolo', 'Kante', 7),
100(9, 1, 'Tiemoue', 'Bakayoko', 7),
101(10, 1, 'Cesc', 'Fabregas', 8),
102(11, 1, 'Eden', 'Hazard', 9),
103(12, 1, 'Pedro', 'Rodriguez', 10),
104(13, 1, 'Alvaro', 'Morata', 12),
105(14, 2, 'David', 'de Gea', 1),
106(16, 2, 'Antonio', 'Valencia', 3),
107(17, 2, 'Daley', 'Blind', 2),
108(18, 2, 'Phil', 'Jones', 6),
109(19, 2, 'Chris', 'Smalling', 6),
110(20, 2, 'Eric', 'Bailly', 6),
111(21, 2, 'Marouane', 'Fellaini', 7),
112(22, 2, 'Ander', 'Herrera', 7),
113(23, 2, 'Paul', 'Pogba', 11),
114(24, 2, 'Marcus', 'Rashford', 9),
115(25, 2, 'Genrikh', 'Mkhitaryan', 10),
116(26, 2, 'Romelu', 'Lukaku', 12),
117(27, 3, 'Ederson', 'Santa di Moraes', 1),
118(29, 3, 'Benjamin', 'Mendy', 5),
119(30, 3, 'Kyle', 'Walker', 4),
120(31, 3, 'Nicolas', 'Ottamendi', 6),
121(32, 3, 'John', 'Stones', 6),
122(33, 3, 'Fabian', 'Delph', 7),
123(34, 3, 'Kevin', 'de Bruyne', 11),
124(35, 3, 'David', 'Silva', 11),
125(36, 3, 'Gabriel', 'Jesus', 9),
126(37, 3, 'Leroy', 'Sane', 10),
127(38, 3, 'Sergion', 'Aguero', 12)
128GO
129
130insert into match(id, match_date, home_club_id, guest_club_id, home_score, guest_score) values
131(1, '10.09.2017', 1, 3, 2, 2),
132(2, '17.09.2017', 3, 2, 2, 1),
133(3, '24.09.2017', 2, 1, 0, 3)
134GO
135
136insert into goal values
137(1, 11),
138(1, 38),
139(1, 13),
140(1, 36),
141(2, 36),
142(2, 38),
143(2, 26),
144(3, 11),
145(3, 11),
146(3, 3)
147GO
148
149
150
151
152
153
154
155drop function goals_scored
156go
157create function goals_scored(@pl_id int)
158returns int
159as
160begin
161 declare @count int = (select count(*) from goal as g where g.player_id = @pl_id)
162 return @count
163end
164GO
165
166DROP VIEW forwards
167GO
168create view forwards as
169 select distinct p.first_name + ' ' + p.second_name as 'Бомбардир',
170 c.name as 'Клуб', dbo.goals_scored(g.player_id) as 'Забито'
171 from goal as g
172 join player as p on g.player_id = p.id
173 join club as c on p.club_id = c.id
174go
175
176select * from forwards order by 'Забито' desc
177
178DROP VIEW goalkeepers
179GO
180
181create view goalkeepers as
182 select c.name as 'Клуб', p.first_name + ' ' + p.second_name as 'Вратарь' from player as p
183 join club as c on c.id = p.club_id where p.position_id = 1
184GO
185
186DROP VIEW game_summary
187GO
188create view game_summary as
189 select g.match_date as 'Дата Матча',
190 gkh.Вратарь as 'Вратарь ХозÑев',
191 home.name as 'ХозÑева',
192 concat(g.home_score, ':', g.guest_score) as 'Счет',
193 guest.name as 'ГоÑти',
194 gkg.Вратарь as 'Вратарь ГоÑтей'
195 from match as g
196 join club as home on g.home_club_id = home.id
197 join club as guest on g.guest_club_id = guest.id
198 join goalkeepers as gkh on gkh.Клуб = home.name
199 join goalkeepers as gkg on gkg.Клуб = guest.name
200GO
201
202select * from game_summary order by 'Дата Матча'
203
204
205
206
207
208DROP FUNCTION league_table
209GO
210create FUNCTION league_table(@date date)
211returns @league_tab table (
212 club_id int,
213 club_name nvarchar(40),
214 points int,
215 scored_goals int,
216 missed_goals int)
217as
218begin
219 insert into @league_tab
220 select
221 c.id,
222 c.name as ' ',
223 dbo.get_points(c.id, @date),
224 dbo.scored_goals(c.id, 'total', @date),
225 dbo.missed_goals(c.id, 'total', @date)
226 from club as c
227 return
228end
229go
230
231DROP procedure show_table
232GO
233create procedure show_table (@date date) as
234 select club_name as 'Команда', points as 'Очки', scored_goals as 'Забито', missed_goals as 'Пропущено'
235 from league_table(@date)
236 order by points desc,
237 dbo.scored_goals(league_table.club_id, 'guest', @date) desc,
238 scored_goals - missed_goals desc
239go
240
241DROP FUNCTION get_points
242GO
243create function get_points(@team_id int, @when date) returns int
244 as begin
245 declare @points int = (select sum(
246 case
247 when match.home_club_id = @team_id and match.home_score > match.guest_score then 3
248 when match.guest_club_id = @team_id and match.guest_score > match.home_score then 3
249 when (match.guest_club_id = @team_id or match.home_club_id = @team_id) and match.guest_score = match.home_score then 1
250 else 0 end) from match where match.match_date <= @when)
251 return @points;
252 end
253 go
254
255
256DROP FUNCTION scored_goals
257GO
258create function scored_goals(@team_id int, @where nvarchar(20), @when date) returns int
259 as begin
260 declare @scored_home int = (select sum(match.home_score) from match where match.home_club_id = @team_id and match.match_date <= @when)
261 declare @scored_away int = (select sum(match.guest_score) from match where match.guest_club_id = @team_id and match.match_date <= @when)
262 if (@scored_home is null)
263 set @scored_home = 0
264 if (@scored_away is null)
265 set @scored_away = 0
266 if (@where = 'home')
267 return @scored_home
268 if(@where = 'guest')
269 return @scored_away
270 return @scored_home + @scored_away
271 end
272 go
273
274drop function missed_goals
275GO
276create function missed_goals(@team_id int, @where nvarchar(20), @when date) returns int
277 as begin
278 declare @scored_home int = (select sum(match.guest_score) from match where match.home_club_id = @team_id and match.match_date <= @when)
279 declare @scored_away int = (select sum(match.home_score) from match where match.guest_club_id = @team_id and match.match_date <= @when)
280 if (@scored_home is null)
281 set @scored_home = 0
282 if (@scored_away is null)
283 set @scored_away = 0
284 if (@where = 'home')
285 return @scored_home
286 if (@where = 'guest')
287 return @scored_away
288 return @scored_home + @scored_away
289 end
290 go
291
292exec show_table '10.09.2017'
293exec show_table '17.09.2017'
294exec show_table '24.09.2017'
295
296
297
298
299
300
301
302
303
304drop procedure table_visualization
305go
306create procedure table_visualization
307 @date date
308 as
309 declare @tbl table(home nvarchar(20), away nvarchar(20), score nvarchar(5), points int)
310 insert into @tbl
311 select
312 c.name, a.name, concat(g.home_score, ':', g.guest_score),
313 dbo.get_points(c.id, @date)
314 from match as g join club as c on g.home_club_id = c.id
315 join club as a on g.guest_club_id = a.id
316 where g.match_date <= @date
317 insert into @tbl
318 select a.name as [ХозÑева], c.name as [ГоÑти], concat(g.guest_score, ':', g.home_score) as ['Ñчет'],
319 dbo.get_points(a.id, @date)
320 from match as g join club as c on g.home_club_id = c.id
321 join club as a on g.guest_club_id = a.id
322 where g.match_date <= @date
323
324
325 select * from @tbl
326
327 select
328 home as ' ',
329 isnull([Chelsea], ' ') as [Chelsea],
330 isnull([Manchester United], ' ') as [Manchester United],
331 isnull([Manchester City], ' ') as [Manchester City],
332 points as 'очки'
333 from @tbl pivot (
334 max(score)
335 FOR away IN ([Chelsea],[Manchester United],[Manchester City])
336 ) as pvt order by points desc
337 go
338
339exec table_visualization '24.09.2017'