6t0uUJ0i

1USE master
2GO
3
4IF  EXISTS (
5	SELECT name 
6		FROM sys.databases 
7		WHERE name = N'GusarovVadim'
8)
9ALTER DATABASE [GusarovVadim] set single_user with rollback immediate
10GO
11
12IF  EXISTS (
13	SELECT name 
14		FROM sys.databases 
15		WHERE name = N'GusarovVadim'
16)
17DROP DATABASE [GusarovVadim]
18GO
19
20CREATE DATABASE [GusarovVadim]
21GO
22
23USE [GusarovVadim]
24GO
25 
26IF EXISTS(
27  SELECT *
28    FROM sys.schemas
29   WHERE name = 'Football'
30) DROP SCHEMA Football
31GO
32 
33CREATE SCHEMA Football
34GO
35
36CREATE TABLE Football.Clubs (
37    id int PRIMARY KEY IDENTITY NOT NULL,
38    name nvarchar(128),
39	check(name != '')
40)
41
42CREATE TABLE Football.Goalkeepers (
43    id int PRIMARY KEY IDENTITY NOT NULL,
44	club_id int FOREIGN KEY REFERENCES Football.Clubs(id),
45	name nvarchar(128),
46	family nvarchar(128),
47	check(name != '' AND family != '')
48)
49
50CREATE TABLE Football.Players (
51    id int PRIMARY KEY IDENTITY NOT NULL,
52	club_id int FOREIGN KEY REFERENCES Football.Clubs(id),
53	name nvarchar(128),
54	family nvarchar(128),
55	check(name != '' AND family != '')
56)
57
58CREATE TABLE Football.Matches (
59    id int PRIMARY KEY IDENTITY NOT NULL,
60    match_date date,
61	club_home int FOREIGN KEY REFERENCES Football.Clubs(id),
62	club_guest int FOREIGN KEY REFERENCES Football.Clubs(id),
63	goalkeeper_home int FOREIGN KEY REFERENCES Football.Goalkeepers(id),
64	goalkeeper_guest int FOREIGN KEY REFERENCES Football.Goalkeepers(id),
65	first_score int,
66	second_score int
67)
68
69CREATE TABLE Football.Goals (
70    id int PRIMARY KEY IDENTITY NOT NULL,
71    match_id int FOREIGN KEY REFERENCES Football.Matches(id),
72	player int FOREIGN KEY REFERENCES Football.Players(id)
73)
74go
75
76IF OBJECT_ID ( 'goals_scored', 'F' ) IS NOT NULL
77    DROP FUNCTION goals_scored
78GO
79
80CREATE FUNCTION goals_scored(@pl_id INT) RETURNS INT
81	AS BEGIN
82		DECLARE @count INT = (SELECT count(*) FROM Football.Goals AS b WHERE b.player = @pl_id)
83		RETURN @count
84	END
85GO
86
87IF OBJECT_ID ( 'get_points', 'F' ) IS NOT NULL
88    DROP FUNCTION get_points
89GO
90
91create function get_points(@team_id int, @when date) returns int
92	as begin
93		declare @points int = (select sum(
94			case
95				when game.club_home = @team_id and game.first_score > game.second_score then 3
96				when game.club_guest = @team_id and game.second_score > game.first_score then 3
97				when (game.club_guest = @team_id or game.club_home = @team_id) and game.second_score = game.first_score then 1
98				else 0 end) from Football.Matches as game where game.match_date <= @when)
99		return @points;
100	end
101	go
102
103IF OBJECT_ID ( 'scored', 'F' ) IS NOT NULL
104    DROP FUNCTION scored
105GO
106
107create function scored(@team_id int, @where nvarchar(20), @when date) returns int
108	as begin
109		declare @scored_home int = (select sum(game.first_score ) from Football.Matches as game where game.club_home = @team_id and game.match_date <= @when)
110		declare @scored_away int = (select sum(game.second_score ) from Football.Matches as game where game.club_guest = @team_id and game.match_date <= @when)
111		if (@scored_home is null)
112			set @scored_home = 0
113		if (@scored_away is null)
114			set @scored_away = 0
115		if (@where = 'home')
116			return @scored_home
117		if(@where = 'guest')
118			return @scored_away
119		return @scored_home + @scored_away
120	end
121go 
122
123-- подсчет пропущенных голов всего
124IF OBJECT_ID ( 'missed', 'F' ) IS NOT NULL
125    DROP FUNCTION missed
126GO
127
128create function missed(@team_id int, @where nvarchar(20), @when date) returns int
129	as begin
130		declare @scored_home int = (select sum(game.second_score ) from Football.Matches as game where game.club_home = @team_id and game.match_date <= @when)
131		declare @scored_away int = (select sum(game.first_score ) from Football.Matches as game where game.club_guest = @team_id and game.match_date <= @when)
132		if (@scored_home is null)
133			set @scored_home = 0
134		if (@scored_away is null)
135			set @scored_away = 0
136		if (@where = 'home')
137			return @scored_home
138		if (@where = 'guest')
139			return @scored_away
140		return @scored_home + @scored_away
141	end
142go
143
144IF OBJECT_ID ( 'GusarovVadim.CorrectScore', 'F' ) IS NOT NULL
145    DROP FUNCTION GusarovVadim.CorrectScore
146GO
147
148CREATE FUNCTION CorrectScore (@first_score int, @second_score int)
149RETURNS tinyint
150AS
151BEGIN
152    IF (@first_score >= 0 AND @second_score >= 0) 
153        RETURN 1
154    RETURN 0
155END
156GO
157
158CREATE TRIGGER valid_score ON Football.Matches  FOR INSERT AS
159BEGIN
160    IF EXISTS (
161    SELECT first_score, second_score
162    FROM inserted WHERE dbo.CorrectScore(first_score, second_score) = 0
163    )
164    BEGIN
165        PRINT 'Некорректный счёт'
166        ROLLBACK TRANSACTION
167    END
168END
169GO
170
171insert into Football.Clubs Values
172('Урал'),
173('Зенит'),
174('Ростов'),
175('Рубин')
176go
177
178insert into Football.Players Values
179(1, 'Игор0ь', 'Портнягин'),
180(1, 'Владимир', 'Ильин'),
181(1, 'Эдгар', 'Манучарян'),
182(1, 'Петрус', 'Бумаль'),
183(1, 'Алексей', 'Евсеев'),
184(1, 'Юрий', 'Бавин'),
185(1, 'Николай', 'Димитров'),
186(1, 'Никита', 'Глушков'),
187(1, 'Александр', 'Павленко'),
188(1, 'Александр', 'Щербаков' ),
189(2, 'Артём', 'Дзюба'),
190(2, 'Александр', 'Кокорин'),
191(2, 'Себастьян', 'Дриусси'),
192(2, 'Дмитрий', 'Полоз'),
193(2, 'Юрий', 'Жирков'),
194(2, 'Виктор', 'Файзулин'),
195(2, 'Кристиан', 'Нобоа'),
196(2, 'Матиас', 'Краневиттер'),
197(2, 'Далер', 'Кузяев'),
198(3, 'Александр', 'Бухаров'),
199(3, 'Элдор', 'Шомуродов'),
200(3, 'Владимир', 'Дядюн'),
201(3, 'Жосимар', 'Кинтеро'),
202(3, 'Александр', 'Гацкан'),
203(3, 'Жан', 'Майер'),
204(3, 'Павел', 'Могилевец'),
205(3, 'Александр', 'Зуев'),
206(3, 'Игорь', 'Киреев'),
207(4, 'Сердар', 'Азмун'),
208(4, 'Рубен', 'Рочина'),
209(4, 'Максим', 'Канунников'),
210(4, 'Александр', 'Сонг'),
211(4, 'Тарас', 'Бурлак'),
212(4, 'Олег', 'Кузьмин'),
213(4, 'Егор', 'Сорокин'),
214(4, 'Владимир', 'Гранат'),
215(4, 'Федор', 'Кудряшов')
216go
217
218insert into Football.Goalkeepers values
219(1, 'Дмитрий', 'Арапов'),
220(2, 'Михаил', 'Кержаков'),
221(3, 'Сергей', 'Песьяков'),
222(4, 'Александр', 'Фильцов')
223go
224
225insert into Football.Matches values
226('20160710', 1, 2, 1, 2, 3, 2),
227('20160717', 1, 3, 1, 3, 2, 2),
228('20160724', 1, 4, 1, 4, 3, 2),
229('20160731', 2, 3, 2, 3, 0, 2),
230('20160807', 2, 4, 2, 4, 1, 3),
231('20160814', 3, 4, 3, 4, 0, 0)
232
233insert into Football.Goals values
234(1, 10),
235(1, 15),
236(1, 7),
237(1, 1),
238(1, 2),
239(2, 3),
240(2, 6),
241(2, 20),
242(2, 21),
243(3, 4),
244(3, 6),
245(3, 8),
246(3, 29),
247(3, 34),
248(4, 21),
249(4, 20),
250(5, 11),
251(5, 31),
252(5, 33),
253(5, 29)
254
255IF OBJECT_ID('strikers', 'U') IS NOT NULL
256  DROP VIEW  strikers
257GO
258
259CREATE VIEW strikers AS
260	SELECT DISTINCT s.name + ' ' + s.family AS 'Бомбардир', dbo.goals_scored(b.player) AS 'Забил' FROM Football.Goals AS b 
261		JOIN Football.Players AS s ON b.player = s.id
262GO
263SELECT * FROM strikers ORDER BY 'Забил' DESC
264
265IF OBJECT_ID('goalkeepers', 'U') IS NOT NULL
266  DROP VIEW  goalkeepers
267GO
268
269CREATE VIEW goalkeepers AS
270	SELECT c.name AS 'Клуб', s.name + ' ' + s.family AS 'Вратарь' FROM Football.Goalkeepers AS s
271		JOIN Football.Clubs AS c ON c.id = s.club_id 
272	GO
273
274SELECT * FROM goalkeepers
275
276IF OBJECT_ID('game_details', 'U') IS NOT NULL
277  DROP VIEW  game_details
278GO
279
280CREATE VIEW game_details AS 
281			 SELECT g.match_date AS 'Дата Матча', 
282					lf.name AS 'Хозяева', 
283					gkh.Вратарь AS 'Вратарь Хозяев',
284					rg.name AS 'Гости', 
285					gkg.Вратарь AS 'Вратарь Гостей',
286					g.first_score AS 'Счет Хозяева',
287					g.second_score AS 'Счет Гости' FROM Football.Matches AS g
288	join Football.Clubs AS lf ON g.club_home = lf.id
289	join Football.Clubs AS rg ON g.club_guest = rg.id
290	join goalkeepers AS gkh ON gkh.Клуб = lf.name
291	join goalkeepers AS gkg ON gkg.Клуб = rg.name
292GO
293
294select * from game_details order by 'Дата Матча'
295
296IF OBJECT_ID('table_visualization', 'U') IS NOT NULL
297  DROP PROCEDURE  table_visualization
298GO
299
300create procedure table_visualization
301    @date date
302    as
303    declare @conv nvarchar(20) = convert(date, @date)
304   
305    declare @clubNames nvarchar(MAX) = ''
306    select @clubNames += QUOTENAME(c.name) + ',' 
307	from (SELECT DISTINCT Football.Clubs.name FROM Football.Clubs join Football.Matches on 
308	Football.Clubs.id = Football.Matches.club_home or Football.Clubs.id = Football.Matches.club_guest
309	where Football.Matches.match_date <= @date) as c
310    set @clubNames = LEFT(@clubNames, len(@clubNames) -1)
311 
312    declare @clubNulls nvarchar(MAX) = ''
313    select @clubNulls += CONCAT('isnull(', QUOTENAME(c.name), ','' '') as' , QUOTENAME(c.name)) + ', ' 
314	from (SELECT DISTINCT name FROM Football.Clubs join Football.Matches on 
315	Football.Clubs.id = club_home or Football.Clubs.id = club_guest
316	where match_date <= @date) as c
317 
318    declare @q nvarchar(MAX) = '
319        declare @tbl table([Команда] nvarchar(20),away nvarchar(20), score nvarchar(10), points int)
320       
321        insert into @tbl
322        select
323              c.name as [Хозяева], a.name as [Гости], concat(g.first_score, ''-'', g.second_score) as [''счет''],  
324              dbo.get_points(c.id,' + 'convert(date, ' + '''' + @conv + '''' + ')' +')
325                from Football.Matches as g  join Football.Clubs as c on g.club_home = c.id
326                                join Football.Clubs as a on g.club_guest = a.id
327                                where g.match_date <= ' + 'convert(date, ' + '''' + @conv + '''' + ')' + '
328        insert into @tbl
329        select  a.name as [Хозяева], c.name as [Гости], concat(g.second_score, ''-'', g.first_score) as [''счет''],
330             dbo.get_points(a.id,' + 'convert(date, ' + '''' + @conv + '''' + ')' + ')
331                from Football.Matches as g  join Football.Clubs as c on g.club_home = c.id
332                                join Football.Clubs as a on g.club_guest = a.id
333                                where g.match_date <=' + 'convert(date, ' + '''' + @conv + '''' + ')' + '
334 
335 
336        select [Команда],' + @clubNulls + '
337        points from @tbl
338        pivot (
339        max(score)
340        FOR away IN (' + @clubNames + ')
341        ) as pv order by points desc'
342    execute sp_executesql @q
343GO
344
345exec table_visualization '23.07.2016'
346exec table_visualization '09.08.2016'
347
348IF OBJECT_ID('league_table', 'U') IS NOT NULL
349  DROP FUNCTION league_table
350GO
351
352create function league_table(@date date) 
353	returns @league_tab table (
354			club_id int,
355			club_name nvarchar(40),
356			points int,
357			scored int,
358			missed int
359	) AS BEGIN
360			INSERT INTO @league_tab 
361			SELECT
362			c.id, 
363			c.name AS ' ', 
364			dbo.get_points(c.id, @date), 
365			dbo.scored(c.id, 'total', @date),
366			dbo.missed(c.id, 'total', @date)
367			FROM Football.Clubs AS c
368			RETURN  
369		  END
370GO
371
372IF OBJECT_ID('show_table', 'U') IS NOT NULL
373  DROP PROCEDURE  show_table
374GO
375
376CREATE PROCEDURE show_table
377	@date date 
378	 AS
379		SELECT club_name AS ' ', points AS 'очки', scored AS 'забито', missed AS 'пропущено' 
380			FROM league_table(@date) 
381				ORDER BY points DESC, 
382						dbo.scored(league_table.club_id, 'guest', @date) DESC, 
383						scored - missed DESC
384GO
385
386exec show_table '09.08.2016'