b9iQw5Ey

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