6qXT0VGE

1/*
2USE - команда, задающая базу данных, с которой далее будет идти работа.
3GO - управляющая конструкция для перехода к следующему блоку команд.
4*/
5USE master
6GO
7
8/*
9|> Создание и пересоздание базы для хранения состава команд в игровых видах спорта.
10|> База состоит из 2-х файловых групп: первичной и пользовательской. 
11|> В каждой из которых по одному файлу.
12
13IF - управляющая конструкция, позволяющая использовать ветвление.
14EXISTS - предикат, принимающий значение TRUE, если подзапрос содержит любое количество строк, 
15иначе он принимает значение FALSE.
16SELECT - команда, выбирающая данные из таблицы, заданной в параметре FROM, 
17по условию, определённому в параметре WHERE.
18DROP - команда, удаляющая заданный объект. Например, она позволяет удалять базы.
19*/
20-- удаляем базу, если уже существует
21IF EXISTS (SELECT * FROM [sys].[databases] WHERE [name] = 'commands')
22BEGIN
23	ALTER DATABASE [commands] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
24    DROP DATABASE [commands]
25END
26GO
27
28/*
29CREATE - команда, создающая объекты. 
30В данном коде она используется для создания базы данных и таблиц.
31С помощью параметра ON указывается путь для расположения файлов создаваемой базы.
32Описание первичной файловой группы следует за ключевым словом ON и не обязательным PRIMARY.
33Затем следует описание второй файловой группы, которое начинается с ключевого слова FILEGROUP.
34LOG ON указывает путь для расположения лога.
35*/
36-- создаём базу
37-- C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\
38-- /mnt/xfs/mssql/commands/
39CREATE DATABASE [commands]
40ON PRIMARY (
41    NAME = COMMANDS_P,
42    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\commands_primary.mdf',
43    SIZE = 5 MB,
44    FILEGROWTH = 10%
45),
46FILEGROUP SECOND (
47    NAME = COMMANDS_S,
48    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\commands_second.ndf',
49    SIZE = 10 MB,
50    FILEGROWTH = 10%
51)
52LOG ON (
53    NAME = COMMANDS_LOG,
54    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\commands_log.ldf',
55    SIZE = 5 MB,
56    MAXSIZE = 50 MB,
57    FILEGROWTH = 5 MB
58)
59GO
60
61/*
62ALTER - команда, позволяющая изменять свойства уже созданных объектов.
63Сочетание ключевого слова SET с MULTI_USER позволяет объявить БД многопользовательской.
64*/
65ALTER DATABASE [commands] SET MULTI_USER
66GO
67
68/*
69|> Создание таблиц в базе данных, для хранения команд, игроков и связи игрок <---> команда.
70|> Таблица с командами хранится в первичной файловой группе.
71|> Две другие хранятся в пользовательской файловой группе.
72*/
73
74USE [commands]
75/*
76В скобках после команды CREATE описывается структура создавае2мой таблицы.
77С помощью ключевого слова CONSTRAINT задаются именованные ограничения.
78Ограничение PRIMARY KEY задаёт первичный ключ.
79Ключевое слово CLUSTERED указывает на то, что данные на диске должны храниться в порядке,
80определённом данным ключом. 
81В скобках указываются столбцы, по которым строится данный индекс.
82Ключевое слово ON задаёт файловую группу, в которой будет храниться таблица. 
83*/
84-- создание таблицы для команд
85CREATE TABLE [teams] (
86    [code] int NOT NULL,
87    [name] [NVARCHAR](100) NOT NULL,
88    [city] [NVARCHAR](50) NOT NULL,
89    [trainer] [NVARCHAR](200) NOT NULL,
90    [league] [NVARCHAR](100) NOT NULL,
91    [status] BIT DEFAULT 1 NOT NULL,
92    [create_year] SMALLINT NOT NULL,
93    CONSTRAINT PK_TEAMS PRIMARY KEY CLUSTERED ([name], [city])
94) ON [PRIMARY]
95-- создание таблицы для игроков
96CREATE TABLE [players] (
97    [full_name] [NVARCHAR](200) NOT NULL,
98    [birthday] DATETIME NOT NULL,
99    [role] [NVARCHAR](50) NOT NULL,
100    [goals] INT DEFAULT 0 NOT NULL,
101    CONSTRAINT PK_PLAYERS PRIMARY KEY CLUSTERED ([full_name], [birthday])
102) ON [SECOND]
103GO
104/*
105Ограничение FOREIGN KEY задаёт внешний ключ, создаёт связь с другими таблицами.
106Ключевое слово REFERENCES задаёт таблицу и поля, 
107с которыми необходимо связать поля создаваемой таблицы.
108Ключевое слово ON в данном случае задаёт поведение при удалении и при обновлении полей 
109в указанных таблицах.
110*/
111-- создание таблицы для игроков
112CREATE TABLE [team_players] (
113    [team_name] [NVARCHAR](100) NOT NULL,
114    [team_city] [NVARCHAR](50) NOT NULL,
115    [player_name] [NVARCHAR](200) NOT NULL,
116    [player_birthday] DATETIME NOT NULL,
117    [entry_date] SMALLINT NOT NULL,
118    [leave_date] SMALLINT NULL,
119    [number] INT NOT NULL,
120    CONSTRAINT FK_TEAMS FOREIGN KEY ([team_name], [team_city])
121        REFERENCES [teams] ([name], [city])
122        ON DELETE CASCADE 
123        ON UPDATE CASCADE,
124    CONSTRAINT FK_PLAYERS FOREIGN KEY ([player_name], [player_birthday])
125        REFERENCES [players] ([full_name], [birthday])
126        ON DELETE CASCADE 
127        ON UPDATE CASCADE
128) ON [SECOND]
129GO
130
131/*
132|> Добавление в базу тестовых данных, соответствующих ограничениям БД. 
133
134Команда INSERT позволяет добавить значения в таблицу.
135За необязательным ключевым словом INTO следует название таблицы и
136необязательный список столбцов. Если список столбцов не указан, 
137то далее будут ожидаться значения для всех столбцов в таблице.
138Ключевое слово VALUES определяет добавляемые значения.
139DEFAULT указывает на то, что необходимо использовать значение по умолчанию.
140*/
141-- добавление команд
142INSERT INTO [teams] ([code], [name], [city], [trainer], [league], [create_year]) 
143VALUES (1, 'Арсенал', 'Лондон', 'Арсен Венгер', 'Английская Премьер-лига', 1886),
144    (2, 'Челси', 'Лондон', 'Антонио Конте', 'Английская Премьер-лига', 1905),
145    (3, 'Зенит', 'Санкт-Петербург', 'Роберто Манчини ', 'Российская футбольная премьер-лига', 1925),
146    (4, 'Спартак', 'Москва', 'Массимо Каррера', 'Российская футбольная премьер-лига', 1922), 
147    (5, 'Урал', 'Екатеринбург', 'Александр Фёдорович Тарханов', 'Российская футбольная премьер-лига', 1930),
148    (6, 'Манчестер Сити', 'Манчестер', 'Хосеп Гвардиола', 'Английская Премьер-лига', 1880),
149    (7, 'ЦСКА', 'Москва', 'Виктор Гончаренко', 'Российская футбольная премьер-лига', 1911)
150-- добавление игроков
151INSERT INTO [players] ([full_name], [birthday], [role], [goals]) 
152VALUES ('Георгий Тамазович Джикия', '21.11.1993', 'защитник', DEFAULT),
153    ('Александр Юрьевич Ерохин', '13.10.1989', 'полузащитник', DEFAULT),
154    ('Юрий Валентинович Жирков', '20.08.1983', 'полузащитник', 2),
155    ('Александр Александрович Кокорин', '19.03.1991', 'нападающий', 12),
156    ('Дмитрий Дмитриевич Полоз', '12.07.1991', 'нападающий', 2),
157    ('Фёдор Михайлович Смолов', '09.02.1990', 'нападающий', 9),
158    ('Игорь Владимирович Акинфеев', '08.04.1986', 'вратарь', -82)
159-- добавление игроков в клубы
160INSERT INTO [team_players] ([team_name], [team_city], [player_name], [player_birthday], [entry_date], [leave_date], [number]) 
161VALUES ('ЦСКА', 'Москва', 'Игорь Владимирович Акинфеев', '8.04.1986', 2002, NULL, 35),
162    ('Спартак', 'Москва', 'Георгий Тамазович Джикия', '21.11.1993', 2017, NULL, 14),
163    ('Урал', 'Екатеринбург', 'Александр Юрьевич Ерохин', '13.10.1989', 2013, 2016, 21),
164    ('Зенит', 'Санкт-Петербург', 'Александр Юрьевич Ерохин', '13.10.1989', 2017, NULL, 21),
165    ('ЦСКА', 'Москва', 'Юрий Валентинович Жирков', '20.08.1983', '2004', 2009, 18),
166    ('Челси', 'Лондон', 'Юрий Валентинович Жирков', '20.08.1983', '2009', 2011, 18),
167    ('Зенит', 'Санкт-Петербург', 'Юрий Валентинович Жирков', '20.08.1983', 2016, NULL, 18),
168    ('Зенит', 'Санкт-Петербург', 'Александр Александрович Кокорин', '19.03.1991', 2016, NULL, 9),
169    ('Урал', 'Екатеринбург', 'Фёдор Михайлович Смолов', '09.02.1990', 2014, 2015, 90),
170    ('Зенит', 'Санкт-Петербург', 'Дмитрий Дмитриевич Полоз', '12.07.1991', 2017, NULL, 7)
171GO
172
173/*
174|> Вывод данных из таблиц БД
175
176Ключевое слово AS позволяет переназвать столбец в выводимых данных.
177*/
178-- вывод данных о командах
179SELECT [code] AS [Код команды], [name] AS [Имя команды], [city] AS [Город], [trainer] AS [Тренер], [league] AS [Лига], [status] AS [Статус], [create_year] AS [Дата создания] 
180FROM [teams]
181-- вывод данных о игроках
182SELECT [full_name] AS [ФИО], [birthday] AS [Дата рождения], [role] AS [Роль], [goals] AS [Количество голов] 
183FROM [players]
184-- вывод данных о игроках в командах
185SELECT [player_name] AS [ФИО], [player_birthday] AS [Дата рождения], [team_name] AS [Название команды], [team_city] AS [Город], [number] AS [Номер игрока в команде], [entry_date] AS [Начал играть], [leave_date] AS [Закончил играть] 
186FROM [team_players]
187GO
188
189/*
190|> Вывод данных из таблиц БД, с учётом связей
191
192Ключевое слово AS также позволяет дать таблице псевдоним для укорачивания условия WHERE
193и списка выборки.
194Оператор LEFT JOIN работает следующим образом:
1951. Происходит формирование таблицы внутренним соединением (JOIN) левой и правой таблиц.
1962. В результат добавляются записи левой таблицы не вошедшие 
197в результат формирования таблицы внутренним соединением.
198Для них, соответствующие записи из правой таблицы заполняются значениями NULL.
199Оператор FULL JOIN помимо данных шагов выполняет ещё один: добавляет в результат записи, 
200не вошедшие в него в результате формирования таблицы внешним левым соединением, и заполняет их
201значениями NULL.
202*/
203SELECT [full_name] AS [ФИО], [birthday] AS [Дата рождения], [role] AS [Роль], [goals] AS [Количество голов], [number] AS [Номер игрока в команде], [entry_date] AS [Начал играть], [leave_date] AS [Закончил играть], [name] AS [Название команды], [city] AS [Город], [trainer] AS [Тренер], [league] AS [Лига], [status] AS [Статус], [create_year] AS [Дата создания], [code] AS [Код команды] 
204FROM [teams] AS t 
205    LEFT JOIN [team_players] AS tp ON t.name = tp.team_name AND t.city = tp.team_city
206    FULL JOIN [players] AS p ON tp.player_name = p.full_name AND tp.player_birthday = p.birthday
207GO
208
209/*
210|> Индивидуальное задание:
211|> По любой части названия команды и города вывести все сведения о ее игроках. 
212|> Переменная «город» может быть NULL, тогда она игнорируется.
213
214Оператор DECLARE позволяет объявить переменную на уровне скрипта и
215задать ей тип и значение. 
216Переменные могут содержать скалярные значения и таблицы.
217Ключевое слово LIKE позволяет произвести поиск по строке. 
218% указывает на любое кол-во символов.
219*/
220
221---- определение переменных city и name, необходимых для указания названия команды и города.
222DECLARE @city NVARCHAR(100) = 'Ека'
223DECLARE @name NVARCHAR(100) = 'Ура'
224
225-- определение временной таблицы
226DECLARE @team table(
227    [name] [NVARCHAR](100) NOT NULL,
228    [city] [NVARCHAR](50) NOT NULL
229)
230-- поиск команды, подходящей под заданные условия
231IF (@city IS NOT NULL)
232    INSERT INTO @team SELECT [name], [city] FROM [teams] WHERE [name] LIKE '%' + @name + '%' AND [city] LIKE '%' + @city + '%'
233ELSE
234    INSERT INTO @team SELECT [name], [city] FROM [teams] WHERE [name] LIKE '%' + @name + '%'
235
236-- вывод данных о игроках команды
237SELECT [full_name] AS [ФИО], [birthday] AS [Дата рождения], [role] AS [Роль], [goals] AS [Количество голов], [number] AS [Номер игрока в команде], [entry_date] AS [Начал играть], [leave_date] AS [Закончил играть]
238FROM @team AS t 
239    JOIN [team_players] AS tp ON t.name = tp.team_name AND t.city = tp.team_city
240    JOIN [players] AS p ON tp.player_name = p.full_name AND tp.player_birthday = p.birthday
241GO
242
243
244
245--2
246
247
248DECLARE @Average int = (select avg(tp.leave_date - tp.entry_date) from team_players as tp
249	where tp.leave_date is not null);
250
251select p.*, @Average as 'average' from players as p
252	join team_players as tp on p.full_name = tp.player_name
253where tp.leave_date - tp.entry_date > @Average;
254
255
256
257--3
258
259IF EXISTS ( SELECT *									
260            FROM   sysobjects 
261            WHERE  name = N'UpdateCode'
262                   AND type = N'P')
263	DROP PROCEDURE dbo.UpdateCode
264
265USE commands;  
266GO  
267CREATE PROCEDURE dbo.UpdateCode
268	@NewCode int,
269	@OldCode int
270AS
271BEGIN
272	IF NOT EXISTS (SELECT * FROM teams WHERE code = @NewCode)
273	BEGIN
274		IF EXISTS (SELECT * FROM teams WHERE code = @OldCode)
275		BEGIN
276			UPDATE teams SET code = @NewCode WHERE code = @OldCode
277		END
278		ELSE
279		BEGIN
280			SELECT 'Указанного ключа в таблице нет, проверьте вводные данные' AS 'ПРЕДУПРЕЖДЕНИЕ'
281		END
282	END
283	ELSE
284	BEGIN
285		SELECT 'Невозможно вставить уже существующий ключ' AS 'ОШИБКА'
286	END
287END
288GO
289
290select * from teams
291DECLARE @NewCode int = 10
292EXECUTE UpdateCode @NewCode, @OldCode = 3
293select * from teams
294
295
296DECLARE @NewCode int = 10
297
298--4
299declare @TeamName nvarchar(100) = (SELECT [name] from teams where code = @NewCode)
300select * from teams
301DELETE FROM team_players where team_name = @TeamName
302DELETE FROM teams WHERE code = @NewCode
303select * from teams
304
305--5
306
307IF EXISTS ( SELECT *
308            FROM   sysobjects 
309            WHERE  name = N'Num5'
310                   AND type = N'P')
311	DROP PROCEDURE dbo.Num5
312
313
314USE commands
315GO  
316CREATE PROCEDURE dbo.Num5
317	@TeamName nvarchar(100),
318	@Code int
319AS
320BEGIN
321	if exists (select * from teams where code = @Code and [name] = @TeamName)
322	begin
323		select TOP(1) p.*, tp.entry_date from teams as t
324			join team_players as tp on tp.team_name = t.[name]
325			join players as p on tp.player_name = p.full_name
326		where tp.leave_date is null and (t.[name] = @TeamName or t.code = @Code)
327		ORDER BY tp.entry_date desc, p.birthday
328	end
329	else
330	begin
331		select null
332	end
333
334	
335	select top (3) tp.team_name from team_players as tp
336		where tp.leave_date is null
337	GROUP BY tp.team_name 
338	order by count(tp.player_name) desc
339END
340GO
341
342EXECUTE Num5 @TeamName = 'спартак', @Code = 4