6G8HMzKv

1USE master
2GO
3 
4IF EXISTS (
5    SELECT  name
6    FROM    sys.databases
7    WHERE   name = N'Soldatenlo' )
8ALTER DATABASE [Soldatenko] set single_user with rollback immediate
9GO
10 
11IF EXISTS (
12    SELECT  name
13    FROM    sys.databases
14    WHERE   name = N'Soldatenko' )
15DROP DATABASE [Soldatenko]
16GO
17 
18CREATE DATABASE [Soldatenko]
19GO
20 
21USE [Soldatenko]
22GO
23
24IF OBJECT_ID('game', 'U') IS NOT NULL
25  DROP TABLE  game
26GO
27
28-- можно добавить разных check-ов,  но зачем
29create table game(
30    game_id int primary key, -- id игры, primary key
31    game_date date, -- дата матча
32    home_id int, -- id домашней команды foreign key
33    guest_id int, -- id гостевой команды foreign key
34    home_score int, -- голы домашней команды
35    guest_score int, -- голы гостевой команды
36)
37
38IF OBJECT_ID('club', 'U') IS NOT NULL
39  DROP TABLE  club
40GO
41 
42create table club(
43    club_id int primary key, -- id клуба
44    club_name nvarchar(40), -- имя клуба
45)
46
47IF OBJECT_ID('positions', 'U') IS NOT NULL
48  DROP TABLE  positions
49GO
50 
51create table positions(
52    pos_id int primary key, -- позиция id
53    pos_name nvarchar(40), -- имя позиции: вратарь, защитник, полузащитник, форвард
54)
55
56
57IF OBJECT_ID('squad', 'U') IS NOT NULL
58  DROP TABLE  squad
59GO
60 
61create table squad(
62    player_id int identity(1,1) primary key, -- id игрока
63    pl_name nvarchar(20), -- имя игрока
64    club_id int, -- id клуба, foreign key
65    position int, -- позиция в составе, foreign key
66    foreign key(club_id) references club(club_id),
67    foreign key(position) references positions(pos_id)
68)
69
70-- инициализация клубов:
71
72insert into club(club_id, club_name)
73    values
74        (1, 'Спартак'),
75        (2, 'Челси'),
76        (3, 'Цска')
77
78-- позиции
79insert into positions(pos_id, pos_name)
80    values
81        (1, 'вратарь'),
82        (2, 'защитник'),
83        (3, 'полузащитник'),
84        (4, 'форвард')
85
86-- Cпартак
87insert into squad(pl_name, club_id, position)
88    values
89        ('Cелихов', 1, 1), -- 1
90        ('Таски', 1, 2),
91        ('Джикия', 1, 2),
92        ('Боккетти', 1, 2),
93        ('Комбаров', 1, 2),
94        ('Кутепов', 1, 2),
95        ('Ещенко', 1, 2),
96        ('Ананидзе', 1, 3),
97        ('Глушаков', 1, 3),
98        ('Промес', 1, 3),
99        ('Cамедов', 1, 3),
100        ('Попов', 1, 4),
101		('Зе Луиш', 1, 4) -- 13
102
103-- Челси
104insert into squad(pl_name, club_id, position)
105    values
106        ('Куртуа', 2, 1), -- 14
107        ('Аспиликуета', 2, 2),
108        ('Дзаппакоста', 2, 2),
109        ('Луиз', 2, 2),
110        ('Кэйхилл', 2, 2),
111        ('Кристенсен', 2, 2),
112        ('Луиз', 2, 2),
113        ('Виллиан', 2, 3),
114        ('Фабрегас', 2, 3),
115        ('Канте', 2, 3),
116        ('Бакайоко', 2, 3),
117        ('Азар', 2, 4),
118		('Мората', 2, 4) -- 26
119
120-- Цска
121insert into squad(pl_name, club_id, position)
122    values
123        ('Акинфеев', 3, 1), -- 27
124        ('Фернандес', 3, 2),
125        ('Васин', 3, 2),
126        ('Березуцкий', 3, 2),
127        ('Васин', 3, 2),
128        ('Игнашевич', 3, 2),
129        ('Щенников', 3, 2),
130        ('Вернблум', 3, 3),
131        ('Дзагоев', 3, 3),
132        ('Головин', 3, 3),
133        ('Натхо', 3, 3),
134        ('Витиньо', 3, 4),
135
136IF OBJECT_ID('bombardiers', 'U') IS NOT NULL
137  DROP TABLE  bombardiers
138GO
139
140create table bombardiers (
141	game_id int, -- id игры, в которой производились голы, foreign key
142	player_id int, -- id игрока, забившего гол, foreign key
143	foreign key(game_id) references game(game_id),
144	foreign key(player_id) references squad(player_id)
145)
146
147
148insert into game(game_id, game_date, home_id, guest_id, home_score, guest_score)
149    values
150		(1, '14.08.2017', 1, 2, 0, 4),
151		(2, '15.08.2017', 2, 3, 2, 0),
152		(3, '16.08.2017', 3, 1, 2, 2)
153
154select * from game
155insert into bombardiers(game_id, player_id)
156	values
157		(1, 25),
158		(1, 25),
159		(1, 26),
160		(1, 21),
161		(2, 12),
162		(2, 11),
163		(2, 39),
164		(2, 39),
165		(3, 25),
166		(3, 20)
167
168IF OBJECT_ID ('dbo.goals_by_player', 'F' ) IS NOT NULL   
169    DROP FUNCTION dbo.goals_by_player
170GO  
171
172create function goals_by_player(@pl_id int) returns int
173	as begin
174		declare @count int = (select count(*) from bombardiers as b where b.player_id = @pl_id)
175		return @count
176	end
177	go
178
179
180IF OBJECT_ID('strikers', 'U') IS NOT NULL
181  DROP VIEW strikers
182GO
183
184create view strikers as
185	select distinct s.pl_name as 'Бомбардир', dbo.goals_by_player(b.player_id) as 'Забил' from bombardiers as b 
186		join squad as s on b.player_id = s.player_id
187go
188
189select * from strikers order by 'Забил' desc
190
191IF OBJECT_ID('dbo.goalkeepers', 'U') IS NOT NULL
192  DROP VIEW dbo.goalkeepers
193GO
194
195create view goalkeepers as
196	select c.club_name as 'Клуб', s.pl_name as 'Вратарь' from squad as s
197		join club as c on c.club_id = s.club_id where s.position = 1
198	go
199
200select * from goalkeepers
201
202IF OBJECT_ID('dbo.game_details', 'U') IS NOT NULL
203  DROP VIEW dbo.game_details
204GO
205
206create view game_details as 
207			 select g.game_date as 'Дата Матча', 
208					lf.club_name as 'Хозяева', 
209					gkh.Вратарь as 'Вратарь Хозяев',
210					rg.club_name as 'Гости', 
211					gkg.Вратарь as 'Вратарь Гостей',
212					g.home_score as 'Счет Хозяева',
213					g.guest_score as 'Счет Гости' from game as g
214	join club as lf on g.home_id = lf.club_id
215	join club as rg on g.guest_id = rg.club_id
216	join goalkeepers as gkh on gkh.Клуб = lf.club_name
217	join goalkeepers as gkg on gkg.Клуб = rg.club_name
218
219select * from game_details order by 'Дата Матча'
220
221
222------------------------------------------------------------------------------
223IF OBJECT_ID('dbo.get_points', 'U') IS NOT NULL
224  DROP PROCEDURE dbo.get_points
225GO
226
227create function get_points(@team_id int, @when date) returns int
228	as begin
229		declare @points int = (select sum(
230			case
231				when game.home_id = @team_id and game.home_score > game.guest_score then 3
232				when game.guest_id = @team_id and game.guest_score > game.home_score then 3
233				when (game.guest_id = @team_id or game.home_id = @team_id) and game.guest_score = game.home_score then 1
234				else 0 end) from game where game.game_date <= @when)
235		return @points;
236	end
237	go
238
239IF OBJECT_ID('dbo.table_visualization', 'U') IS NOT NULL
240  DROP PROCEDURE dbo.table_visualization
241GO
242
243create procedure table_visualization
244	@date date
245	as
246		declare @tbl table([Команда] nvarchar(20),away nvarchar(20), score nvarchar(10), points int)
247		
248		insert into @tbl
249		select
250			  c.club_name as [Хозяева], a.club_name as [Гости], 
251			  concat(g.home_score, '-', g.guest_score) as ['счет'],  
252			  dbo.get_points(c.club_id, @date) 
253				from game as g  join club as c on g.home_id = c.club_id
254								join club as a on g.guest_id = a.club_id
255								where g.game_date <= @date 
256		insert into @tbl
257		select  a.club_name as [Хозяева], c.club_name as [Гости], concat(g.guest_score, '-', g.home_score) as ['счет'],
258			 dbo.get_points(a.club_id, @date)
259				from game as g  join club as c on g.home_id = c.club_id
260								join club as a on g.guest_id = a.club_id
261								where g.game_date <= @date 
262
263
264		select [Команда], isnull([Челси], ' ') as [Челси], 
265		isnull([Спартак], ' ') as [Спартак], 
266		isnull([Цска], ' ') as [Цска], 
267		points from @tbl
268		pivot (
269		max(score)
270		FOR away IN ([Челси],[Спартак],[Цска])
271		) as pv order by points desc
272	go
273
274exec table_visualization '18.08.2017'
275
276---------------------------------------------------------------------
277
278-- подсчет забитых голов по критерию
279IF OBJECT_ID('dbo.scored', 'U') IS NOT NULL
280  DROP PROCEDURE dbo.scored
281GO
282
283create function scored(@team_id int, @where nvarchar(20), @when date) returns int
284	as begin
285		declare @scored_home int = (select sum(game.home_score ) from game where game.home_id = @team_id and game.game_date <= @when)
286		declare @scored_away int = (select sum(game.guest_score ) from game where game.guest_id = @team_id and game.game_date <= @when)
287		if (@scored_home is null)
288			set @scored_home = 0
289		if (@scored_away is null)
290			set @scored_away = 0
291		if (@where = 'home')
292			return @scored_home
293		if(@where = 'guest')
294			return @scored_away
295		return @scored_home + @scored_away
296	end
297	go 
298
299
300-- подсчет пропущенных голов всего
301IF OBJECT_ID('dbo.missed', 'U') IS NOT NULL
302  DROP PROCEDURE dbo.missed
303GO
304
305create function missed(@team_id int, @where nvarchar(20), @when date) returns int
306	as begin
307		declare @scored_home int = (select sum(game.guest_score ) from game where game.home_id = @team_id and game.game_date <= @when)
308		declare @scored_away int = (select sum(game.home_score ) from game where game.guest_id = @team_id and game.game_date <= @when)
309		if (@scored_home is null)
310			set @scored_home = 0
311		if (@scored_away is null)
312			set @scored_away = 0
313		if (@where = 'home')
314			return @scored_home
315		if (@where = 'guest')
316			return @scored_away
317		return @scored_home + @scored_away
318	end
319	go
320
321IF OBJECT_ID('dbo.league_table', 'U') IS NOT NULL
322  DROP PROCEDURE dbo.league_table
323GO
324
325create function league_table(@date date) 
326	returns @league_tab table (
327			club_id int,
328			club_name nvarchar(40),
329			points int,
330			scored int,
331			missed int
332	) as begin
333			insert into @league_tab 
334			select
335			c.club_id, 
336			c.club_name as ' ', 
337			dbo.get_points(c.club_id, @date), 
338			dbo.scored(c.club_id, 'total', @date),
339			dbo.missed(c.club_id, 'total', @date)
340			from club as c
341			return  
342		  end
343		  go -- внутреннее представление
344
345
346IF OBJECT_ID('dbo.show_table', 'U') IS NOT NULL
347  DROP PROCEDURE dbo.show_table
348GO
349
350create procedure show_table
351	@date date 
352	 as 
353		select club_name as ' ', points as 'очки', scored as 'забито', missed as 'пропущено' 
354			from league_table(@date) 
355				order by points desc, 
356						dbo.scored(league_table.club_id, 'guest', @date) desc, 
357						scored - missed desc
358	go -- основная процедура
359
360exec show_table '18.08.2017'
361exec show_table '15.08.2017'
362exec show_table '14.08.2017'