Знакомства с MS SQL Server

Южная Америка

Афганистан

Кабул

647500

29822848

Азия

Бангладеш

Дакка

144000

160221000

Азия

Бахрейн

Манама

701

1397000

Азия

Белиз

Бельмопан

22966

377968

Северная Америка

Белоруссия

Минск

207595

9498400

Европа

Бельгия

Брюссель

30528

11250585

Европа

Бенин

Порто-Ново

112620

11167000

Африка

Болгария

София

110910

7153784

Европа

Боливия

Сукре

1098580

10985059

Южная Америка

Ботсвана

Габороне

600370

2209208

Африка

Бразилия

Бразилиа

8511965

206081432

Южная Америка

Буркина-Фасо

Уагадугу

274200

19034397

Африка

Бутан

Тхимпху

47000

784000

Азия

Великобритания

Лондон

244820

65341183

Европа

Венгрия

Будапешт

93030

9830485

Европа

Венесуэла

Каракас

912050

31028637

Южная Америка

Восточный Тимор

Дили

14874

1167242

Азия

Вьетнам

Ханой

329560

91713300

Азия

Пример 1: Напишите функцию для вывода столицы данной страны, и вызовите ее:

CREATE FUNCTION Пример1 (

@Страна AS VARCHAR(50)

)

RETURNS VARCHAR(50) AS

BEGIN

DECLARE @S AS VARCHAR(50) SELECT

@S = Столица

FROM

Страны

END

WHERE

Название = @Страна RETURN @S

SELECT dbo.Пример1('Австрия')

Пример 2: Напишите функцию для перевода площади в тыс. кв. км., и вызовите ее:

CREATE FUNCTION Пример2 (

@Площадь AS FLOAT

)

RETURNS FLOAT AS

BEGIN

DECLARE @P AS FLOAT

SET @P = ROUND(@Площадь / 1000, 2) RETURN @P

END

SELECT

Название, Столица, Континент, Население,

dbo.Пример2(Площадь) AS [Площадь тыс.кв.км]

FROM

Страны

Пример 3: Напишите функцию для вычисления плотности населения, и вызовите ее:

CREATE FUNCTION Пример3 (

@Население AS INT, @Площадь AS FLOAT

)

RETURNS FLOAT AS

BEGIN

DECLARE @P AS FLOAT

SET @P = ROUND(CAST(@Население AS FLOAT) / @Площадь, 2) RETURN @P

END

SELECT

Название, Столица, Континент, Население, Площадь,

dbo.Пример3(Население, Площадь) AS Плотность

FROM

Страны

ORDER BY

Плотность DESC

Пример 4: Напишите функцию для поиска страны второй по площади, и вызовите ее: CREATE FUNCTION Пример4()

RETURNS VARCHAR(50) AS

BEGIN

DECLARE @P AS VARCHAR(50) DECLARE @M1 AS FLOAT DECLARE @M2 AS FLOAT

SELECT

@M1 = MAX(Площадь)

FROM

Страны

SELECT

@M2 = MAX(Площадь)

FROM

Страны

WHERE

Площадь < @M1

SELECT

@P = Название

FROM

Страны

WHERE

Площадь = @M2

RETURN @P

END

SELECT

dbo.Пример4() AS [Второй по площади страна]

Пример 5: Напишите функцию для поиска страны с минимальной площадью в задан- ной части света, и вызовите ее. Если часть света не указана, выбрать Европу:

CREATE FUNCTION Пример5 (

@Конт AS VARCHAR(50) = 'Европа'

)

RETURNS VARCHAR(50) AS

BEGIN

DECLARE @P AS VARCHAR(50) DECLARE @M AS FLOAT

SELECT

@M = MIN(Площадь)

FROM

Страны

WHERE

Континент = @Конт

SELECT

@P = Название

FROM

Страны

WHERE

Континент = @Конт AND

Площадь = @M

RETURN @P

END

SELECT

dbo.Пример5('Азия') AS [Наименьшая по площади страна в Азии]

SELECT

dbo.Пример5(DEFAULT) AS [Наименьшая по площади страна в Европе]

Пример 6: Напишите функцию для замены букв в заданном слове от второй до пред- последней на точку, и примените ее для названия страны:

CREATE FUNCTION Пример6 (

@A AS VARCHAR(50)

)

RETURNS VARCHAR(50) AS

BEGIN

RETURN LEFT(@A, 1) + REPLICATE('.', LEN(@A) - 2) + RIGHT(@A, 1)

END

SELECT

dbo.Пример6(Название) AS [Скрытое название]

,Столица

,Континент

,Площадь

,Население

FROM

Страны

Пример 7: Напишите функцию, которая возвращает количество стран, содержащих в названии заданную букву:

CREATE FUNCTION Пример7 (

@C AS CHAR(1)

)

RETURNS INT AS

BEGIN

DECLARE @K AS INT

SELECT

@K = COUNT(*)

FROM

Страны

WHERE

CHARINDEX(@C, Название) > 0

RETURN @K

END

Пример 8: Напишите функцию для вывода списка стран с населением больше задан- ного числа, и вызовите ее:

CREATE FUNCTION Пример8 (

@N AS INT

)

RETURNS TABLE AS

RETURN (

SELECT

Название

,Столица

,Площадь

,Население

,Континент

FROM

Страны

SELECT

*

WHERE

Население > @N

)

FROM

dbo.Пример8(100000000)

Пример 9: Напишите функцию для вывода списка стран с площадью в интервале за- данных значений, и вызовите ее:

CREATE FUNCTION Пример9

(

@A AS FLOAT, @B AS FLOAT

)

RETURNS TABLE AS

RETURN (

SELECT

Название

,Столица

,Площадь

,Население

,Континент

FROM

Страны

SELECT

*

WHERE

Площадь BETWEEN @A AND @B

)

FROM

dbo.Пример9(1000, 10000)

Пример 10: Напишите функцию для возврата таблицы с названием страны и плотно- стью населения, и вызовите ее:

CREATE FUNCTION Пример10()

RETURNS @Ст_Плот TABLE

(

Название VARCHAR(50), Плотность FLOAT

)

AS BEGIN

INSERT

@Ст_Плот SELECT

Название

, CAST(Население AS FLOAT) / Площадь AS Плотность

FROM

Страны

RETURN

END

SELECT

Название

,Плотность

FROM

dbo.Пример10()

Пример 11: Удалите функцию из примера 10:

DROP FUNCTION Пример10

Задание

Напишите функцию для вывода названия страны с заданной столицей, и вызовите ее.

Напишите функцию для перевода населения в млн. чел. и вызовите ее.

Напишите функцию для вычисления плотности населения заданной части света и вызовите ее.

Напишите функцию для поиска страны, третьей по населению и вызовите ее.

Напишите функцию для поиска страны с максимальным населением в заданной ча- сти света и вызовите ее. Если часть света не указана, выбрать Азию.

Напишите функцию для замены букв в заданном слове от третьей до предпослед- ней на “тест” и примените ее для столицы страны.

Напишите функцию, которая возвращает количество стран, не содержащих в назва- нии заданную букву.

Напишите функцию для возврата списка стран с площадью меньше заданного числа и вызовите ее.

Напишите функцию для возврата списка стран с населением в интервале заданных значений и вызовите ее.

Напишите функцию для возврата таблицы с названием континента и суммарным населением и вызовите ее.

Напишите функцию IsPalindrom(P) целого типа, возвращающую 1, если целый па- раметр P (P > 0) является палиндромом, и 0 в противном случае.

Напишите функцию Quarter(x, y) целого типа, определяющую номер координатной четверти, содержащей точку с ненулевыми вещественными координатами (x, y).

Напишите функцию IsPrime(N) целого типа, возвращающую 1, если целый пара- метр N (N > 1) является простым числом, и 0 в противном случае.

Напишите код для удаления созданных вами функций.



Лабораторная работа 10

Хранимые процедуры

Цель работы

Изучение создания хранимых процедур.

Изучение передачи входных параметров.

Изучение передачи выходных параметров.

Изучение вызовов хранимых процедур.

Изучение удаления хранимых процедур.

Теоретическая часть

При программировании в SQL Server введенный код сначала компилируется, потом за- пускается. Процесс компиляции может занимать определенное время. На языке Transact-SQL также есть возможность написанный блок кода сохранить и заранее скомпилировать. Осо- бенно, если код многократно используется в операции базы данных, отличным решением бу- дет произвести его инкапсуляцию в процедуры. Для этой цели используются хранимые про- цедуры, которые представляют собой набор инструкций, выполняющихся как единое целое. Процедуры аналогичны конструкциям в других языках программирования и выполняют сле- дующие задачи:

обрабатывают входные параметры и возвращают значения в виде выходных парамет- ров;

содержат инструкции, которые выполняют операции в базе данных, в отличии от пользовательских функций;

возвращают сведения об успешном или неуспешном завершении.

В клиент-серверной и распределенных системах хранимые процедуры позволяют су- щественно сократить сетевой трафик, поскольку по сети отправляется только вызов на выпол- нение процедуры.

С точки зрения безопасности, хранимые процедуры выполняют очень большую роль, так как устраняют необходимость предоставлять разрешения на уровне объектов и упрощают формирование уровней безопасности. С помощью хранимых процедур можно предотвратить атаки типа «инъекция SQL».

Хранимая процедура создается с помощью команды CREATE PROCEDURE или CREATE PROC, которая имеет следующий упрощенный вид:

CREATE {PROC | PROCEDURE} <название>

[<@параметр> <тип> [= <значение по умолчанию>] [OUT | OUTPUT]] AS

[BEGIN]

<команды> [END]

При создании процедуры после команды CREATE указывается тип создаваемого объ- екта с помощью ключевого слова PROCEDURE или его сокращенного варианта PROC.

Названия процедур должны соответствовать требованиям, предъявляемым к идентифи- каторам, и должны быть уникальными в базе данных. При этом не следует пользоваться пре- фиксом «sp_». Этим префиксом в SQL Server обозначаются системные процедуры.

В хранимую процедуру можно передать до 2100 параметров. При выполнении проце- дуры значение каждого из объявленных параметров должно быть указано пользователем, если для параметра не определено значение по умолчанию.

Ключевое слово OUT (можно использовать и OUTPUT) показывает, что параметр про- цедуры является выходным.

Для выполнения хранимой процедуры используется ключевое слово EXECUTE (или EXEC). Процедуру также можно вызывать и выполнять без ключевого слова, если она явля- ется первой инструкцией. Синтаксис команды EXECUTE имеет следующий вид:

EXECUTE [<@статус возврата>=] <название процедуры> [<@параметр>=] <значе- ние>| <@переменная> [OUTPUT] | [DEFAULT]

В отличии от вызова функций, при вызове хранимых процедур с указанием названия параметра ([<@параметр>=] <значение>), последовательность параметров можно не соблю- дать.

Для выходных параметров при вызове указывается ключевое слово OUTPUT.

Если для параметра указано значение по умолчанию, можно его использовать с помо- щью ключевого слова DEFAULT.

Для удаления хранимых процедур используется команда DROP PROCEDURE. Упро- щенный синтаксис имеет следующий вид:

DROP PROC | PROCEDURE [IF EXISTS] <название хранимой процедуры>

Ключевые слова IF EXISTS удаляют хранимую процедуру только в том случае, если она уже существует.

Практическая часть

Дана таблица Страны:

Название	Столица	Площадь	Население	Континент
Австрия	Вена	83858	8741753	Европа
Азербайджан	Баку	86600	9705600	Азия
Албания	Тирана	28748	2866026	Европа
Алжир	Алжир	2381740	39813722	Африка
Название	Столица	Площадь	Население	Континент
Ангола	Луанда	1246700	25831000	Африка
Аргентина	Буэнос-Айрес	2766890	43847000	Южная Америка
Афганистан	Кабул	647500	29822848	Азия
Бангладеш	Дакка	144000	160221000	Азия
Бахрейн	Манама	701	1397000	Азия
Белиз	Бельмопан	22966	377968	Северная Америка
Белоруссия	Минск	207595	9498400	Европа
Бельгия	Брюссель	30528	11250585	Европа
Бенин	Порто-Ново	112620	11167000	Африка
Болгария	София	110910	7153784	Европа
Боливия	Сукре	1098580	10985059	Южная Америка
Ботсвана	Габороне	600370	2209208	Африка
Бразилия	Бразилиа	8511965	206081432	Южная Америка
Буркина-Фасо	Уагадугу	274200	19034397	Африка
Бутан	Тхимпху	47000	784000	Азия
Великобритания	Лондон	244820	65341183	Европа
Венгрия	Будапешт	93030	9830485	Европа
Венесуэла	Каракас	912050	31028637	Южная Америка
Восточный Тимор	Дили	14874	1167242	Азия
Вьетнам	Ханой	329560	91713300	Азия

Пример 1: Напишите хранимую процедуру для вывода информации о сервере, о базе данных и о текущем пользователе, и вызовите ее:

CREATE PROC Пример1 AS

BEGIN

SELECT

@@Servername AS Сервер

,@@Version AS [Версия СУБД]

,Db_Name() AS [База данных]

,User AS [Пользователь базы данных]

,System_User AS [Системный пользователь]

END

EXECUTE Пример1

стран:

Пример 2: Напишите хранимую процедуру, которая выводит названия и столицы всех

CREATE PROC Пример2 AS

BEGIN

SELECT

Название

, Столица

END

FROM

Страны

Пример 3: Напишите хранимую процедуру, которая выводит список стран заданной части света, и вызовите ее:

CREATE PROC Пример3

@Конт AS VARCHAR(50)

AS BEGIN

SELECT

Название

,Столица

,Площадь

,Население

FROM

Страны

WHERE

Континент = @Конт

END

EXECUTE Пример3 'Азия'

Пример 4: Напишите хранимую процедуру, которая выводит список стран, площадь которых находится в заданном интервале, и вызовите ее:

CREATE PROC Пример4 @A AS FLOAT, @B AS FLOAT

AS BEGIN

SELECT

Название

,Столица

,Площадь

,Население

,Континент

FROM

Страны

WHERE

Площадь BETWEEN @A AND @B

END

EXECUTE Пример4 1000, 10000

Пример 5: Напишите хранимую процедуру, которая возвращает количество стран, со- держащих в названии заданную букву, и вызовите ее:

CREATE PROC Пример5

@Буква AS CHAR(1), @Количество AS INT OUTPUT

AS BEGIN

SELECT

@Количество = COUNT(*)

FROM

Страны

WHERE

CHARINDEX(@Буква, Название) > 0

END

DECLARE @К AS INT DECLARE @Б AS CHAR(1) SET @Б = 'у'

EXECUTE Пример5 @Б, @К OUTPUT SELECT

@К AS [Количество стран]

Пример 6: Напишите хранимую процедуру для вывода трех стран с наименьшей пло- щадью в заданной части света, и вызовите ее. Если часть света не указана, выбрать Европу:

CREATE PROC Пример6

@Конт AS VARCHAR(50) = 'Европа'

AS BEGIN

SELECT TOP 3

Название

,Столица

,Площадь

,Население

,Континент

FROM

Страны WHERE

Континент = @Конт ORDER BY

Площадь

END

EXECUTE Пример6 DEFAULT

Пример 7: Напишите хранимую процедуру, которая создает таблицу «Страны_У», и заполняет ее странами, названия которых начинаются на букву «У»:

CREATE PROC Пример7 AS

BEGIN

SELECT

Название

,Столица

,Площадь

,Население

,Континент

INTO FROM

Страны_У Страны

WHERE

LEFT(Название, 1) = 'У'

END

EXECUTE Пример7

Пример 8: Напишите хранимую процедуру, которая удаляет таблицу «Страны_У» и возвращает количество строк:

CREATE PROC Пример8 AS

BEGIN

DECLARE @K AS INT

SELECT

@K = COUNT(*)

FROM

Страны_У

DROP TABLE Страны_У RETURN @K

END

DECLARE @C AS INT

EXECUTE @C = Пример8

SELECT @C AS [Количество строк в удаленной таблице]

Пример 9: Напишите код, который удаляет хранимую процедуру «Пример8»: DROP PROC Пример8

Задание

Напишите хранимую процедуру для вывода информации о сервере, о базе данных, о текущем пользователе, о текущем времени, и вызовите ее.

Напишите хранимую процедуру, которая выводит данные всех стран.

Напишите хранимую процедуру, которая выводит список стран, кроме заданной части света, и вызовите ее.

Напишите хранимую процедуру, которая выводит список стран, население кото- рых находится в заданном интервале, и вызовите ее.

Напишите хранимую процедуру, которая возвращает количество стран, у которых в названии отсутствует заданная буква, и вызовите ее.

Напишите хранимую процедуру для вывода пяти стран с наибольшим населением в заданной части света, и вызовите ее. Если часть света не указана, выбрать Африку.

Напишите хранимую процедуру, которая создает таблицу «Страны_<первая буква вашей фамилии>», и заполняет ее странами, названия которых начинаются с первой буквой вашей фамилии.

Напишите хранимую процедуру, которая удаляет таблицу, которую вы создали в предыдущем задании и возвращает количество удаленных строк.

Напишите хранимую процедуру, принимающую число и возвращающую количе- ство цифр в нем через параметр OUTPUT.

Напишите хранимую процедуру AddRightDigit, добавляющую к целому положи- тельному числу K справа цифру D (D - входной параметр целого типа, лежащий в диапазоне [0..9], K - параметр целого типа, являющийся одновременно входным и выходным).

Напишите хранимую процедуру InvDigit, меняющую порядок следования цифр це- лого положительного числа K на обратный (K - параметр целого типа, являющийся одновре- менно входным и выходным).

Напишите хранимую процедуру Swap, меняющую содержимое переменных X и Y (X и Y - вещественные параметры, являющиеся одновременно входными и выходными).

Напишите хранимую процедуру SortInc, меняющую содержимое переменных A, B, C, таким образом, чтобы их значения оказались упорядоченными по возрастанию (A, B, C

- вещественные параметры, являющиеся одновременно входными и выходными).

Напишите хранимую процедуру DigitCountSum, находящую количество C цифр целого положительного числа K, а также их сумму S (K - входной, C, S - выходные параметры целого типа).

Напишите код, который удаляет все хранимые процедуры, вами созданные.

Лабораторная работа 11

Триггеры

Цель работы

Изучить создание триггеров.

Изучить триггеры после событий.

Изучить триггеры вместо событий.

Изучить виртуальные таблицы в триггерах.

Изучить приостановление триггеров.

Изучить удаление триггеров.

Теоретическая часть

Триггер - это вид хранимой процедуры, который вызывается автоматически при опре- деленных событиях. Часто триггеры применяются для автоматической поддержки целостно- сти и защиты БД.

В MS SQL Server существует три вида триггеров, которые отличаются по функциям и по синтаксису создания и изменения:

Триггеры DML вызываются при выполнении команд INSERT, UPDATE или DELETE. Можно создать триггер, реагирующий на две или на все три команды.

Триггеры DDL реагируют на события изменения структуры БД: создание, изменение или удаление отдельных объектов БД.

Триггеры входа в систему запускаются при соединении пользователя с экземпляром сервера. Их можно применять для дополнительной проверки полномочий пользователей.

Триггеры DML можно вызвать после событий (FOR | AFTER), или вместо него (INSTEAD OF).

Триггер AFTER выполняется после успешного завершения вызвавшего его события. Можно определить несколько АFТЕR-триггеров для каждой операции. Триггер INSTEAD OF вызывается вместо выполнения команд. Для каждой операции INSERT, UPDATE, DELETE можно определить только один INSTEAD ОF-триггер.

Упрощенный синтаксис создания триггера имеет следующий вид:

CREATE TRIGGER <название триггера> ON <название таблицы>

<FOR | AFTER | INSTEAD OF> <INSERT | UPDATE | DELETE> AS

[BEGIN] <команды> [END]

Ключевое слово FOR или AFTER указывает, что триггер DML срабатывает только по- сле успешного запуска всех операций в инструкции SQL, по которой срабатывает триггер.

Ключевое слово INSTEAD OF указывает, что триггер DML выполняется вместо ин- струкции SQL, по которой он срабатывает, то есть переопределяет действия запускающих ин- струкций.

В определении триггера ключевые слова INSERT | UPDATE | DELETE определяют ин- струкции изменения данных, при применении которых к таблице или представлению сраба- тывает триггер DML. Указание хотя бы одного варианта обязательно. В определении триггера разрешены любые сочетания вариантов в любом порядке.

Триггеры не вызываются рекурсивно.

Хотя инструкция TRUNCATE TABLE по сути аналогичная инструкции DELETE, она не активирует триггер.

Если триггер выполняется для события добавления данных (команды INSERT), в теле триггера доступна виртуальная таблица INSERTED, которая содержит список добавленных данных.

Если триггер выполняется для события удаления данных (команды DELETE), в теле триггера доступна виртуальная таблица DELETED, которая содержит список удаленных дан- ных.

Если триггер выполняется для события изменения данных (команды UPDATE), в теле триггера доступны две виртуальные таблицы INSERTED и DELETED, которые содержат спи- сок новых и старых данных, соответственно.

Если при определенных обстоятельствах выполнение триггера нежелательно, то можно его отключить. Для этого используется команда DISABLE TRIGGER, его синтаксис:

DISABLE TRIGGER <название триггера> ON <название таблицы>

А когда триггер снова понадобится, его можно включить с помощью команды ENABLE TRIGGER, его синтаксис:

ENABLE TRIGGER <название триггера> ON <название таблицы>

Для удаления триггера используется команда DROP TRIGGER, его синтаксис: DROP TRIGGER <название триггера>

Практическая часть

Дана таблица Ученики:



ID	Фамилия	Предмет	Школа	Баллы
1	Иванова	Математика	Лицей	98,5
2	Петров	Физика	Лицей	99
3	Сидоров	Математика	Лицей	88
4	Полухина	Физика	Гимназия	78
ID	Фамилия	Предмет	Школа	Баллы
5	Матвеева	Химия	Лицей	92
6	Касимов	Химия	Гимназия	68
7	Нурулин	Математика	Гимназия	81
8	Авдеев	Физика	Лицей	87
9	Никитина	Химия	Лицей	94

Пример 1: Напишите триггер на добавление записи в таблицу «Ученики». Данный триггер, в случае успешного добавления данных, выводит «Запись добавлена»:

CREATE TRIGGER Пример1 ON Ученики FOR INSERT

AS BEGIN

PRINT 'Запись добавлена'

END

Пример 2: Напишите триггер на удаление записи из таблицы «Ученики». Данный триг- гер, в случае успешного удаления данных, выводит «Запись удалена»:

CREATE TRIGGER Пример2 ON Ученики AFTER DELETE

AS BEGIN

PRINT 'Запись удалена'

END

Пример 3: Напишите триггер на добавление, изменение и удаление данных для таб- лицы «Ученики». Данный триггер выводит «Таблица изменена»:

CREATE TRIGGER Пример3 ON Ученики FOR INSERT, UPDATE, DELETE

AS BEGIN

PRINT 'Таблица изменена'

END

Пример 4: Напишите триггер на удаление записи из таблицы «Ученики». Данный триг- гер, при попытке удаления данных, выводит «Нельзя удалить данные»:

CREATE TRIGGER Пример4 ON Ученики INSTEAD OF DELETE

AS BEGIN

PRINT 'Нельзя удалить данные'

END

Пример 5: Создать таблицу «Ученики_Архив», которая будет содержать все данные об удаленных учениках и даты их удаления. Написать триггер, который будет фиксировать в таб- лице «Ученики_Архив» данные ученика, удаленного из таблицы «Ученики»:

CREATE TABLE Ученики_Архив (

ID INT NOT NULL,

Фамилия VARCHAR(50) NULL, Предмет VARCHAR(50) NULL, Школа VARCHAR(50) NULL, Баллы FLOAT NULL,

Удалено DATETIME NOT NULL

)

CREATE TRIGGER Пример5 ON Ученики FOR DELETE

AS BEGIN

INSERT

Ученики_Архив SELECT

ID,

Фамилия, Предмет, Школа, Баллы,

GETDATE() AS Удалено

END

FROM

DELETED

Пример 6: Напишите команды для приостановления и запуска триггера из примера 5: DISABLE TRIGGER Пример5 ON Ученики

ENABLE TRIGGER Пример5 ON Ученики

Пример 7: Напишите команды для удаления триггера из примера 5: DROP TRIGGER Пример5

Задание

Напишите триггер на изменение записи в таблице «Ученики». Данный триггер, в случае изменения данных, должен вывести «Запись изменена».

Напишите триггер на добавление и удаление записи из таблицы «Ученики». Данный триггер, в случае успешного добавления или удаления данных, должен вывести «Количество строк изменено».

Напишите триггер на добавление, изменение и удаление данных в таблице «Уче- ники». Данный триггер должен вывести «{Текущий пользователь} изменил таблицу. Время:

{текущее время}».

Напишите триггер на изменение записи в таблице «Ученики». Данный триггер, при попытке изменения данных, должен вывести «Нельзя редактировать данные».

Создать таблицу «Ученики_{Ваша_фамилия}», которая будет содержать фамилии удаленных учеников и даты их удаления. Написать триггер, который будет фиксировать в таб- лице «Ученики_{Ваша_фамилия}» данные учеников при удалении из таблицы «Ученики», в том случае, если у них остались однофамильцы в таблице «Ученики».

Напишите команды для приостановления и запуска триггера из предыдущей задачи.

Напишите команды для удаления всех созданных вами триггеров.

Лабораторная работа № 15

Представления и табличные объекты

Цель работы

Изучить создание и удаление представлений.

Изучить табличные переменные.

Изучить временные таблицы.

Изучить производные таблицы.

Теоретическая часть

Представление - это виртуальная таблица, содержимое которой определяется запро- сом. Как и таблица, представление состоит из ряда именованных столбцов и строк данных.

Представления, как таблицы, могут иметь до 1024 столбцов.

Запрос для создания представления может обращаться не более чем к 256 таблицам.

Можно создавать представления на основе других представлений, при этом уровень вложенности не может быть больше 32-х.

Представление можно использовать как обычную таблицу. Упрощенный синтаксис создания преставления имеет следующий вид:

CREATE VIEW <название> <список столбцов> AS <запрос SELECT>

Запрос SELECT, используемый в определении представления, не может включать пред- ложение ORDER BY, если только в списке выбора инструкции SELECT нет также предложе- ния TOP.

Для удаления представления используется команда DROP VIEW, его синтаксис: DROP VIEW <название>

В Transact-SQL есть специальный тип данных для хранения результирующего набора для обработки в будущем. Его используют в основном для временного хранения набора строк, возвращаемых как результирующий набор функций с табличным значением. Функции и пере- менные могут быть объявлены как табличные переменные. Табличные переменные могут ис- пользоваться в функциях, хранимых процедурах и пакетах. Для объявления табличных пере- менных используется следующий синтаксис:

DECLARE <@название переменной> TABLE (<объявление столбцов>)

Табличная переменная ведет себя как локальная переменная, она имеет точно опреде- ленную область применения.

Табличная переменная может быть применена в любом месте, где используется таблица или табличное выражение в инструкциях SELECT, INSERT, UPDATE и DELETE. Но таблич- ную переменную нельзя использовать в инструкции SELECT … INTO …

Табличные переменные автоматически очищаются в конце функции, хранимой проце- дуры или пакета, в котором они были определены.

Операция присвоения между табличными переменными не поддерживается.

В MS SQL Server для хранения промежуточных данных можно использовать времен- ные таблицы. Они по поведению не отличаются от базовых таблиц. Создание, удаление и об- ращение к ним аналогично к базовым.

Первый символ в названии временной таблицы должен быть знак решетки #. Для ло- кальных временных таблиц используется один знак #. Локальные временные таблицы до- ступны в течение текущей сессии и удаляются, когда пользователь отсоединяется от сервера. Для глобальных временных таблиц используются два знака ##. Глобальные временные таблицы доступны всем открытым сессиям базы данных и удаляются, когда все пользователи, ссылающиеся на таблицы, отсоединяются от сервера.

Временные таблицы хранятся в системной базе данных TEMPDB.

Для принудительного удаления временных таблиц используется команда DROP TABLE.

В MS SQL Server можно создать временно именованный результирующий набор, назы- ваемый обобщенным табличным выражением. Он формируется при выполнении простого за- проса.

За обобщенным табличным выражением должны следовать одиночные инструкции SELECT, INSERT, UPDATE или DELETE, ссылающиеся на некоторые или на все столбцы.

Обобщенные табличные выражения хранятся в оперативной памяти и существуют только во время первого выполнения запроса, который представляет эту таблицу.

Практическая часть

Пример 1: Создайте представление, содержащее список стран, население которых меньше 1 млн. чел., а площадь больше 100 тыс. кв. км, и используйте его:

CREATE VIEW Пример1 AS

SELECT

Название

,Столица

,Площадь

,Население

,Континент

FROM

Страны

WHERE

Население < 1000000 AND

Площадь > 100000

SELECT

Название

,Столица

,Площадь

,Население

,Континент

FROM

Пример1

Пример 2: Создайте представление, содержащее список континентов, суммарную пло- щадь и суммарное население стран, которые находятся на каждом континенте и используйте его:

CREATE VIEW Пример2 (

Континент

,Площадь

,Население

) AS

SELECT

Континент

,SUM(Площадь)

,SUM(Население)

FROM

Страны

GROUP BY

Континент

SELECT

Континент

,Площадь

,Население

FROM

Пример2

Пример 3: Создайте представление, содержащее фамилии преподавателей, должность, каждого преподавателя, звание, степень, место работы, зарплату и используйте его:

CREATE VIEW Пример3 (

Фамилия

,Должность

,Звание

,Степень

,Кафедра

,Зарплата

) AS

SELECT

Фамилия

,Должность

,Звание

,Степень

,Название

,Зарплата

FROM

Сотрудник С

INNER JOIN Преподаватель П ON С.Таб_номер = П.Таб_номер INNER JOIN Кафедра К ON С.Шифр = К.Шифр

SELECT

Фамилия

,Должность

,Звание

,Степень

,Кафедра

,Зарплата

FROM

Пример3

Пример 4: Создайте табличную переменную, содержащую три столбца («Номер не- дели», «Дата начала», «Дата конца»). Заполните ее для текущего года и используйте:

DECLARE @Пример4 TABLE (

[Номер недели] INT, [Дата начала] DATE, [Дата конца] DATE

)

DECLARE @T AS DATE, @N INT = 1

SET @T = CAST(YEAR(GETDATE()) AS CHAR(4)) + '0101' WHILE DATEPART(WEEKDAY, @T) > 1

SET @T = DATEADD(DAY, -1, @T) PRINT DATEPART(WEEK, @T)

WHILE YEAR(@T) < YEAR(DATEADD(YEAR, 1, GETDATE())) BEGIN

INSERT

@Пример4 VALUES

(@N, @T, DATEADD(DAY, 6, @T))

END

SET @T = DATEADD(DAY, 7, @T) SET @N = @N + 1

SELECT

[Номер недели]

,[Дата начала]

,[Дата конца]

FROM

@Пример4

Пример 5: Создайте табличную переменную, содержащую список стран, площадь ко- торых в 1000 раз меньше, чем средняя площадь стран в мире и используйте:

DECLARE @Пример5 TABLE (

Название VARCHAR(50), Столица VARCHAR(50), Площадь FLOAT, Население BIGINT, Континент VARCHAR(50)

)

INSERT INTO

@Пример5 SELECT

Название

,Столица

,Площадь

,Население

,Континент

FROM

Страны

WHERE

Площадь * 1000 < (

SELECT

AVG(Площадь)

SELECT

Название

,Столица

,Площадь

,Население

,Континент

FROM

)

Страны

FROM

@Пример5

Пример 6: Создайте локальную временную таблицу, имеющую три столбца («Название месяца», «Количество экзаменов», «Количество студентов»), заполните и используйте ее:

SELECT

DATENAME(MONTH, Дата) AS [Название месяца]

, COUNT(DISTINCT Код) AS [Количество экзаменов]

, COUNT(DISTINCT Рег_номер) AS [Количество студентов]

INTO FROM

#Пример6 Экзамен

GROUP BY

DATENAME(MONTH, Дата)

SELECT * FROM #Пример6

Пример 7: Создайте глобальную временную таблицу, содержащую название стран и плотность их населения, заполните и используйте ее:

CREATE TABLE ##Пример7 (

Название VARCHAR(50), Плотность FLOAT

)

INSERT INTO

##Пример7

(Название, Плотность)

SELECT

Название, ROUND(Население / Площадь, 0) AS Плотность

FROM

Страны

SELECT * FROM ##Пример7 DROP TABLE #Пример6

Пример 8: С помощью обобщенных табличных выражений, напишите запрос для вы- вода списка сотрудников, чьи зарплаты меньше, чем средняя зарплата по кафедре, их зарплаты и название кафедры:

WITH СЗК AS (

SELECT

К.Название AS Кафедра

,К.Шифр

,AVG(Зарплата) AS [Средняя зарплата по кафедре]

FROM

Сотрудник С

INNER JOIN Кафедра К ON С.Шифр = К.Шифр

GROUP BY

К.Название, К.Шифр

) SELECT

С.Фамилия

, С.Зарплата

, З.Кафедра

, З.[Средняя зарплата по кафедре]

FROM

Сотрудник С

INNER JOIN СЗК З ON С.Шифр = З.Шифр

WHERE

С.Зарплата < З.[Средняя зарплата по кафедре]

Задание

Создайте представление, содержащее список африканских стран, население которых больше 10 млн. чел., а площадь больше 500 тыс. кв. км, и используйте его.

Создайте представление, содержащее список континентов, среднюю площадь стран, которые находятся на нем, среднюю плотность населения, и используйте его.

Создайте представление, содержащее фамилии преподавателей, их должность, зва- ние, степень, место работы, количество их экзаменов, и используйте его.

Создайте табличную переменную, содержащую три столбца («Номер месяца»,

«Название месяца», «Количество дней»), заполните ее для текущего года, и используйте ее.

Создайте табличную переменную, содержащую список стран, площадь которых в 100 раз меньше, чем средняя площадь стран на континенте, где они находятся, и используйте ее.

Создайте локальную временную таблицу, имеющую три столбца («Номер недели»,

«Количество экзаменов», «Количество студентов»), заполните и используйте ее.

Создайте глобальную временную таблицу, содержащую название континентов, наибольшую и наименьшую площадь стран на них, заполните и используйте ее.

С помощью обобщенных табличных выражений напишите запрос для вывода списка сотрудников, чьи зарплаты меньше, чем средняя зарплата по факультету, их зарплаты и назва- ние факультета.

Напишите команды для удаления всех созданных вами представлений.



Лабораторная работа 12

Курсоры

Цель работы

Изучить использование курсоров.

Изучить прокрутку курсоров.

Изучить использование циклов в курсорах.

Изучить использование переменных в курсорах.

Теоретическая часть

Инструкции Transact-SQL выполняют операции над множествами, но интерактивным приложениям иногда требуется обрабатывать результаты построчно. Для выполнения команд над отдельной строкой предусмотрены курсоры. Открытие курсора в результирующем наборе делает возможной его построчную обработку. Можно присвоить курсор переменной или па- раметру с типом данных CURSOR.

Курсоры позволяют усовершенствовать обработку результатов: позиционируясь на отдельные строки результирующего набора;

получая одну или несколько строк от текущей позиции в результирующем наборе; поддерживая изменение данных в строках в текущей позиции результирующего набора;

поддерживая разные уровни видимости изменений, сделанных другими пользова-

телями для данных, представленных в результирующем наборе;

предоставляя инструкциям Transact-SQL в скриптах, хранимых процедурах и триг- герах доступ к данным результирующего набора.

Обычно курсоры используются для выбора из базы данных некоторого подмножества хранимой в ней информации. В каждый момент времени прикладной программой может быть проверена одна строка курсора. Курсоры часто применяются в операторах SQL, встроенных в написанные на языках процедурного типа прикладные программы. Некоторые из них неявно создаются сервером базы данных, в то время как другие определяются программистами.

В соответствии со стандартом SQL при работе с курсорами можно выделить следую- щие основные действия:

создание или объявление курсора;

открытие курсора, т.е. наполнение его данными, которые сохраняются в много- уровневой памяти;

выборка из курсора и изменение с его помощью строк данных;

программ;

закрытие курсора, после чего он становится недоступным для пользовательских

освобождение курсора, т.е. удаление курсора как объекта, поскольку его закрытие

необязательно освобождает ассоциированную с ним память.

После освобождения курсора ассоциированная с ним память также освобождается. При этом становится возможным повторное использование его имени.

В некоторых случаях применение курсора неизбежно. Однако по возможности этого следует избегать и работать со стандартными командами обработки данных: SELECT, UPDATE, INSERT, DELETE. Помимо того, что курсоры не позволяют проводить операции изменения над всем объемом данных, скорость выполнения операций обработки данных по- средством курсора заметно ниже, чем у стандартных средств Transact-SQL.

SQL Server поддерживает четыре типа курсоров:

Однонаправленный курсор - указывается как FORWARD_ONLY и не поддерживает прокрутку. Он также называется курсором FIREHOSE и поддерживает только получение строк последовательно, от начала до конца курсора. Строки нельзя получить из базы данных, пока они не будут выбраны. Результаты всех инструкций INSERT, UPDATE и DELETE, вли- яющих на строки результирующего набора (выполненных текущим пользователем или зафик- сированных другими пользователями), отображаются как строки, выбранные из курсора.

Статический курсор - полный результирующий набор статического курсора создается в базе данных TEMPDB при открытии курсора. Статический курсор всегда отображает резуль- тирующий набор точно в том виде, в котором он был при открытии курсора. Статическими курсорами обнаруживаются лишь некоторые изменения или не обнаруживаются вовсе, но при этом в процессе прокрутки такие курсоры потребляют сравнительно мало ресурсов. Статиче- ские курсоры всегда доступны только для чтения.

KEYSET курсор - членство и порядок строк в курсоре, управляемом набором ключей, являются фиксированными при открытии курсора. Такие курсоры управляются с помощью набора уникальных идентификаторов - ключей. Ключи создаются из набора столбцов, кото- рый уникально идентифицирует строки результирующего набора.

Динамический курсор - это противоположность статических курсоров. Динамические курсоры отражают все изменения строк в результирующем наборе при прокрутке курсора. Значения типа данных, порядок и членство строк в результирующем наборе могут меняться для каждой выборки. Все инструкции UPDATE, INSERT и DELETE, выполняемые пользова- телями, видимы посредством курсора.

Упрощенный шаблон использования курсора имеет следующий синтаксис:

DECLARE <имя_курсора> CURSOR [FORWARD_ONLY | SCROLL]

FOR <SELECT_оператор> OPEN <имя_курсора>

FETCH <NEXT|PRIOR|FIRST|LAST|ABSOLUTE <число>| RELATIVE <число>>

FROM <имя_курсора> INTO <@переменная> CLOSE <имя_курсора> DEALLOCATE <имя_курсора>

Инструкция DEALLOCATE удаляет связь между курсором и переменной, и освобож- дает структуры данных, составляющие курсор.

После создания курсора, чтобы его использовать, надо открыть курсор с помощью ко- манды OPEN. А команда CLOSE закрывает открытый курсор, высвобождая текущий резуль- тирующий набор и снимая блокировки курсоров для строк.

Для получения определенной строки используется команда FETCH.

Ключевое слово NEXT возвращает следующую строку и перемещает указатель теку- щей строки на возвращенную строку. Если инструкция FETCH NEXT выполняет первую вы- борку в отношении курсора, она возвращает первую строку в результирующем наборе. NEXT является параметром по умолчанию выборки из курсора.

Ключевое слово PRIOR возвращает предыдущую строку и перемещает указатель теку- щей строки на возвращенную строку. Если инструкция FETCH PRIOR выполняет первую вы- борку из курсора, не возвращается никакая строка и положение курсора остается перед первой строкой.

Ключевое слово FIRST возвращает первую строку в курсоре и делает ее текущей. Ключевое слово LAST возвращает последнюю строку в курсоре, делая ее текущей. Ключевое слово ABSOLUTE с аргументом - положительным целым числом, возвращает строку с указанным номером от начала курсора, и делает ее текущей строкой. Если число отрицательное, возвращает строку с указанным номером от конца курсора, делая ее текущей строкой. Ключевое слово RELATIVE с аргументом - положительным целым числом возвращает строку с указанным номером после текущей строки и делает ее текущей строкой. Если число отрицательное, возвращает строку с указанным номером до текущей строки и делает ее теку- щей строкой. Если число равно 0, возвращает текущую строку.

Ключевое слово INTO позволяет поместить данные из столбцов выборки в локальные переменные. Каждая переменная из списка, слева направо, связывается с соответствующим столбцом в результирующем наборе курсора. Типы данных переменных должны соответство- вать типам данных соответствующего столбца результирующего набора. Количество перемен- ных и столбцов тоже должны совпадать. Для обработки результирующего набора построчно можно использовать инструкцию FETCH NEXT в цикле WHILE. Как условие в цикле используется функция @@FETCH_STATUS, которая возвращает состояние последней инструкции FETCH, вызван- ной в любом курсоре, открытом в рамках этого подключения.

Функция @@FETCH_STATUS возвращает одно из четырех значений:

0 Инструкция FETCH была выполнена успешно.

-1 Выполнение инструкции FETCH завершилось неудачно или строка оказалась вне пределов результирующего набора.

-2 Выбранная строка отсутствует.

-9 Курсор не выполняет операцию выборки.

Упрощенный синтаксис использования функции @@FETCH_STATUS имеет следую- щий вид:

FETCH NEXT FROM <имя_курсора> WHILE @@FETCH_STATUS = 0 BEGIN

FETCH NEXT FROM <имя_курсора> END

Практическая часть

Таблица Ученики:

ID	Фамилия	Предмет	Школа	Баллы
1	Иванова	Математика	Лицей	98,5
2	Петров	Физика	Лицей	99
3	Сидоров	Математика	Лицей	88
4	Полухина	Физика	Гимназия	78
5	Матвеева	Химия	Лицей	92
6	Касимов	Химия	Гимназия	68
7	Нурулин	Математика	Гимназия	81
8	Авдеев	Физика	Лицей	87
9	Никитина	Химия	Лицей	94
10	Барышева	Химия	Лицей	88

Пример 1: Создайте курсор, содержащий отсортированные по алфавиту фамилии уче- ников и названия их предметов, откройте его, выведите первую строку, закройте и освободите курсор:

DECLARE MyCursor CURSOR FOR

SELECT

Фамилия

,Предмет

FROM

Ученики

ORDER BY

Фамилия OPEN MyCursor FETCH MyCursor

CLOSE MyCursor DEALLOCATE MyCursor

Пример 2: Создайте курсор с прокруткой, содержащий список учеников, откройте его, выведите пятую, предыдущую, с конца четвертую, шесть позиций назад находящуюся, четыре позиций вперед находящуюся, следующую, первую, последнюю строку, закройте и освобо- дите курсор:

DECLARE MyCursor CURSOR SCROLL FOR

SELECT

ID

,Фамилия

,Предмет

,Школа

,Баллы

FROM

Ученики

OPEN MyCursor

FETCH ABSOLUTE 5 FROM MyCursor FETCH PRIOR FROM MyCursor FETCH ABSOLUTE -4 FROM MyCursor FETCH RELATIVE -6 FROM MyCursor FETCH RELATIVE 4 FROM MyCursor FETCH NEXT FROM MyCursor

FETCH FIRST FROM MyCursor FETCH LAST FROM MyCursor

CLOSE MyCursor DEALLOCATE MyCursor

Пример 3: С помощью курсора, вычислите среднее арифметическое значение балла у учеников с наибольшим и наименьшим баллом:

DECLARE MyCursor CURSOR SCROLL FOR

SELECT

Баллы

FROM

Ученики

ORDER BY

Баллы

DECLARE @S FLOAT = 0, @B FLOAT

OPEN MyCursor

FETCH FIRST FROM MyCursor INTO @B SET @S = @S + @B

FETCH LAST FROM MyCursor INTO @B SET @S = @S + @B

SET @S = @S / 2 PRINT @S

CLOSE MyCursor DEALLOCATE MyCursor

Пример 4: С помощью курсора, сгенерируйте строку вида «Ученики <список фамилий и названий школ, разделенных запятыми> участвовали в олимпиаде»:

DECLARE MyCursor CURSOR SCROLL FOR

SELECT

Фамилия

,Школа

FROM

Ученики

DECLARE @S VARCHAR(2000), @F VARCHAR(50), @W VARCHAR(50)

OPEN MyCursor

SET @S = 'Ученики'

FETCH NEXT FROM MyCursor INTO @F, @W WHILE @@FETCH_STATUS = 0

BEGIN

SET @S = @S + ', ' + @F + ' из школы "' + @W + '"' FETCH NEXT FROM MyCursor INTO @F, @W

END

SET @S = @S + ' участвовали на олимпиаде.' PRINT @S

CLOSE MyCursor DEALLOCATE MyCursor

Пример 5: Создайте курсор, содержащий список учеников, с его помощью выведите учеников с четной позицией:

DECLARE MyCursor CURSOR SCROLL FOR

SELECT

ID

,Фамилия

,Предмет

,Школа

,Баллы

FROM

Ученики

OPEN MyCursor

FETCH ABSOLUTE 2 FROM MyCursor WHILE @@FETCH_STATUS = 0 BEGIN FETCH RELATIVE 2 FROM MyCursor

END

CLOSE MyCursor DEALLOCATE MyCursor

Пример 6: Создайте курсор, содержащий отсортированный по убыванию баллов спи- сок учеников, откройте его, для каждого ученика выведите фамилию, предмет, школу, баллы и разницу баллов с предыдущим учеником:

DECLARE MyCursor CURSOR SCROLL FOR

SELECT

Фамилия

,Предмет

,Школа

,Баллы

FROM

Ученики

DECLARE @F VARCHAR(50) DECLARE @P VARCHAR(50) DECLARE @S VARCHAR(50) DECLARE @B FLOAT DECLARE @OB FLOAT = 0

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @F, @P, @S, @B WHILE @@FETCH_STATUS = 0

BEGIN

SELECT

@F AS Фамилия

,@P AS Предмет

,@S AS Школа

,@B AS Баллы

,ABS(@B - @OB) AS Разница SET @OB = @B

FETCH NEXT FROM MyCursor INTO @F, @P, @S, @B

END

CLOSE MyCursor DEALLOCATE MyCursor

Задание

Создайте курсор, содержащий отсортированные по баллам фамилии и баллы уче- ников, откройте его, выведите первую строку, закройте и освободите курсор.

Создайте курсор с прокруткой, содержащий список учеников, откройте его, выве- дите пятую, предыдущую, с конца четвертую, следующую, первую строку, закройте и освобо- дите курсор.

Создайте курсор с прокруткой, содержащий список учеников, откройте его, выве- дите последнюю, шесть позиций назад находящуюся, четыре позиций вперед находящуюся строку, закройте и освободите курсор.

С помощью курсора, вычислите сумму баллов у учеников с наибольшим и наименьшим баллом.

С помощью курсора, сгенерируйте строку вида «Ученики <список фамилий и названий предметов, разделенных запятыми> участвовали в олимпиаде».

Создайте курсор, содержащий список учеников, с его помощью выведите учеников с нечетной позицией.

Создайте курсор, содержащий отсортированный по убыванию баллов список уче- ников, откройте его, для каждого ученика выведите фамилию, предмет, школу, баллы и про- центное соотношение баллов с предыдущим учеником.

Лабораторная работа 13

Оконные функции

Цель работы

Изучить оконные функции.

Изучить аналитические функции.

Изучить ранжирующие функции.

Изучить функции смещения.

Теоретическая часть

Оконные функции используются для вычисления в заданной секции. Оконные функции не приводят к группированию строк в одну строку вывода, строки сохраняют свои отдельные идентификаторы, а агрегированное значение добавляется к каждой строке.

Для определения секций используется инструкция OVER. Упрощенный синтаксис имеет следующий вид:

<функция> <столбец для вычислений> OVER ([PARTITION BY <столбец для группи- ровки>] [ORDER BY <столбец для сортировки>])

Предложение PARTITION BY разделяет результирующий набор запроса на секции. Оконная функция применяется к каждой секции отдельно, и вычисление начинается заново для каждой секции.

Предложение ORDER BY определяет сортировку строк в каждой секции результирую- щего набора.

Оконные функции разделяются на агрегирующие, ранжирующие и функции смещения. Агрегирующие функции MAX(), MIN(), SUM, AVG(), COUNT() можно применить к секциям результирующего набора запроса. Синтаксис функций имеет следующий вид:

MAX|MIN|SUM|AVG|COUNT(<столбец>) OVER([PARTITION BY <столбцы>] [ORDER BY <столбцы>])

В агрегирующих функциях предложение PARTITION BY можно не указывать, тогда функция обрабатывает все строки результирующего набора запроса как одну группу. Предло- жение ORDER BY определяет последовательность, в которой строкам назначаются уникаль- ные номера. Его также можно не указать.

Ранжирующие функции возвращают ранжирующее значение для каждой строки в сек- ции и являются недетерминированными.

В ранжирующих функциях предложение PARTITION BY можно не указывать, тогда функция обрабатывает все строки результирующего набора запроса как одну группу. Предло- жение ORDER BY определяет последовательность, в которой строкам назначаются уникаль- ные номера. Оно должно указываться обязательно.

Transact-SQL содержит следующие ранжирующие функции:

ROW_NUMBER - нумерует выходные данные результирующего набора, то есть, воз- вращает последовательный номер строки, начиная с 1, в секции результирующего набора. Синтаксис функции имеет следующий вид:

ROW_NUMBER( ) OVER([PARTITION BY <столбцы>] ORDER BY <столбцы>)

RANK - возвращает ранг каждой строки в секции результирующего набора. Ранг строки вычисляется как единица плюс количество рангов, находящихся до этой строки. В от- личии от функции ROW_NUMBER, RANK назначает одинаковое значение строкам, претен- дующим на один ранг. Синтаксис функции имеет следующий вид:

RANK( ) OVER([PARTITION BY <столбцы>] ORDER BY <столбцы>)

DENSE_RANK - возвращает ранг каждой строки в секции результирующего набора без промежутков в значениях ранжирования. Ранг определенной строки равен количеству различ- ных значений рангов, предшествующих строке, увеличенному на единицу. Синтаксис функ- ции имеет следующий вид:

DENSE_RANK( ) OVER([PARTITION BY <столбцы>] ORDER BY <столбцы>)

NTILE - распределяет строки упорядоченной секции в заданное количество групп. Группы нумеруются, начиная с единицы. Для каждой строки функция NTILE возвращает но- мер группы, которой принадлежит строка. Синтаксис функции имеет следующий вид:

NTILE(<количество групп>) OVER([PARTITION BY <столбцы>] ORDER BY

<столбцы>)

Функции смещения возвращают значение из другой строки секции результирующего набора запроса. Предложение ORDER BY должно указываться обязательно.

Transact-SQL содержит следующие функции смещения:

Функции LAG() и LEAD() - обращаются к данным из предыдущей или последующей строки того же результирующего набора. Синтаксис функций имеет следующий вид:

LAG|LEAD(<столбец> [,<смещение>] [,<значение по умолчанию>]) OVER([PARTI- TION BY <столбцы>] ORDER BY <столбцы>)

Параметр «смещение» указывает количество строк до строки перед или после текущей строки, из которой необходимо получить значение. Если значение аргумента не указано, то по умолчанию принимается 1.

«Значение по умолчанию» должно иметь такой же тип данных, как первый параметр, используется, когда «смещение» находится за пределами секции. Если не задано, то возвра- щается NULL.

Функции FIRST_VALUE() и LAST_VALUE() - возвращают первое или последнее зна- чение из упорядоченного набора значений. Синтаксис функций имеет следующий вид:

FIRST_VALUE|LAST_VALUE(<столбец>) OVER([PARTITION BY <столбцы>] OR-

DER BY <столбцы>)

Практическая часть

Таблица Ученики:

ID	Фамилия	Предмет	Школа	Баллы
1	Иванова	Математика	Лицей	98,5
2	Петров	Физика	Лицей	99
3	Сидоров	Математика	Лицей	88
4	Полухина	Физика	Гимназия	78
5	Матвеева	Химия	Лицей	92
6	Касимов	Химия	Гимназия	68
7	Нурулин	Математика	Гимназия	81
8	Авдеев	Физика	Лицей	87
9	Никитина	Химия	Лицей	94
10	Барышева	Химия	Лицей	88

Пример 1: Вывести список учеников и максимальный балл в каждой строке: SELECT

FROM

Фамилия

,Предмет

,Школа

,Баллы

, MAX(Баллы) OVER() AS Макс_Балл Ученики

Пример 2: Вывести список учеников и разницу между баллами ученика и минималь- ным баллом в каждой строке:

SELECT

FROM

Фамилия

,Предмет

,Школа

,Баллы

, Баллы - MIN(Баллы) OVER() AS Разница

Ученики

Пример 3: Вывести список учеников и процентное соотношение к суммарному баллу в каждой строке:

SELECT

FROM

Фамилия

,Предмет

,Школа

,Баллы

,Баллы * 100 / SUM(Баллы) OVER() AS Процент Ученики

Пример 4: Вывести список учеников и средний балл в школе в каждой строке: SELECT

FROM

Фамилия

,Предмет

,Школа

,Баллы

,AVG(Баллы) OVER(PARTITION BY Школа) AS Сред_Шк Ученики

Пример 5: Вывести список учеников и количество учеников в школе в каждой строке, отсортировать по школам в оконной функции:

SELECT

Кол_Шк

FROM

Фамилия

,Предмет

,Школа

,Баллы

,COUNT(*) OVER(PARTITION BY Школа ORDER BY Школа) AS

Ученики

Пример 6: Вывести список учеников и номер строки при сортировке по баллам по убы- ванию:

SELECT

ROW_NUMBER() OVER(ORDER BY Баллы DESC) AS Номер_строки

,Фамилия

,Предмет

FROM

,Школа

,Баллы Ученики

Пример 7: Вывести список учеников и номер строки внутри школы при сортировке по баллам по убыванию:

SELECT

ROW_NUMBER() OVER(PARTITION BY Школа ORDER BY Баллы DESC) AS

Номер_строки

,Фамилия

,Предмет

,Школа

,Баллы

FROM

Ученики

Пример 8: Вывести список учеников и ранг по баллам в каждой школе: SELECT

RANK() OVER(PARTITION BY Школа ORDER BY Баллы DESC) AS Ранг_Шк

,Фамилия

,Предмет

,Школа

,Баллы

FROM

Ученики

Пример 9: Вывести список учеников и сжатый ранг по баллам в каждой школе. Резуль- тат отсортировать по фамилии в алфавитном порядке:

SELECT

DENSE_RANK() OVER(PARTITION BY Школа ORDER BY Баллы DESC) AS

Сж_Ранг_Шк

FROM

,Фамилия

,Предмет

,Школа

,Баллы

Ученики

ORDER BY

Фамилия

Пример 10: Вывести список учеников, распределенных по трем группам по фамилии: SELECT

NTILE(3) OVER(ORDER BY Фамилия) AS Гр_Фам

,Фамилия

,Предмет

,Школа

,Баллы

FROM

Ученики

Пример 11: Вывести список учеников, распределенных по двум группам по баллам внутри школы:

SELECT

NTILE(2) OVER(PARTITION BY Школа ORDER BY Баллы DESC) AS Гр_Балл

,Фамилия

,Предмет

,Школа

,Баллы

FROM

Ученики

Пример 12: Вывести список учеников и разницу с баллами предыдущего ученика, при сортировке по возрастанию баллов:

SELECT

Фамилия

,Предмет

,Школа

,Баллы

,Баллы - LAG(Баллы) OVER(ORDER BY Баллы) AS Разница

FROM

Ученики

Пример 13: Вывести список учеников и разницу с баллами ученика через две позиции при сортировке по убыванию баллов, значение по умолчанию использовать 0:

SELECT

Фамилия

,Предмет

,Школа

,Баллы

FROM

,Баллы - LEAD(Баллы, 2, 0) OVER(ORDER BY Баллы DESC) AS Разница Ученики

Пример 14: Вывести список учеников и разницу с баллами первого ученика при сорти- ровке по убыванию баллов:

SELECT

Фамилия

,Предмет

,Школа

,Баллы

,FIRST_VALUE(Баллы) OVER(ORDER BY Баллы DESC) - Баллы AS Разница

FROM

Ученики

Пример 15: Вывести список учеников и разницу с баллами последнего ученика в школе при сортировке по убыванию баллов:

SELECT

Фамилия

,Предмет

,Школа

,Баллы

,LAST_VALUE(Баллы) OVER(ORDER BY Баллы RANGE BETWEEN UN- BOUNDED PRECEDING AND UNBOUNDED FOLLOWING) - Баллы AS Разница

FROM

Ученики

Задание

Вывести список учеников и разницу между баллами ученика и максимальным бал- лом в каждой строке.

Вывести список учеников и процентное соотношение к среднему баллу в каждой строке.

Вывести список учеников и минимальный балл в школе в каждой строке.

Вывести список учеников и суммарный балл в школе в каждой строке, отсортиро- вать по школам в оконной функции.

Вывести список учеников и номер строки при сортировке по фамилиям в обратном алфавитном порядке.

Вывести список учеников, номер строки внутри школы и количество учеников в школе при сортировке по баллам по убыванию.

Вывести список учеников и ранг по баллам.

Вывести список учеников и сжатый ранг по баллам. Результат отсортировать по фамилии в алфавитном порядке.

Вывести список учеников, распределенных по пяти группам по фамилии.

Вывести список учеников, распределенных по трем группам по баллам внутри школы.

Вывести список учеников и разницу с баллами ученика, находящегося выше на три позиции при сортировке по возрастанию баллов.

Вывести список учеников и разницу с баллами следующего ученика при сорти- ровке по убыванию баллов, значение по умолчанию использовать 0.