====== Лабораторная работа № 4 ======

====== Освоение программирования с помощью встроенного языка Transact SQL ======

Цель работы – знакомство с основными приципами программирования в MS SQL Server средствами встроенного языка Transact SQL.

Содержание работы:

1. Знакомство с правилами обозначения синтаксиса команд в справочной системе MS SQL Server (утилита Books Online).

2. Изучение правил написания программ на Transact SQL.

3. Изучение правил построения идентификаторов, правил объявления переменных и их типов.

4. Изучение работы с циклами и ветвлениями.

5. Изучение работы с переменными типа Table и Cursor.

6. Проработка всех примеров, анализ результатов их выполнения.

7. Выполнение индивидуальных заданий по вариантам.

Пояснения к выполнению работы

Для освоения программирования используем пример базы данных c названием DB_Books, которая была создана в лабораторной работе №1.

При выполнении примеров и заданий обращайте внимание на соответствие названий БД, таблиц и других объектов проекта.

====== 1. Специальные знаки и простейшие операторы в Transact SQL ======

^ Знак  ^ Назначение       ^ Знак  ^Назначение ^
| *     | Знак умножения  |" "    | В них заключают строковые значения, если SET QUOTED_IDENTIFIER OFF|
| -     |Знак вычитания   |‘ ’    | В них заключают строковые значения*     | 
|%      |Остаток от деления двух чисел |<> |Не равно|
|+ |Знак сложения или конкатенации (объединение двух строк в одну)| [ ] |Аналог кавычек, в них можнозаключать названия идентификаторов, если в их названиях встречаются пробелы|
|=| Знак равенства или сравнения| !< |Не менее чем|
|<=| Меньше или равно| !> |Не более чем|
|>=| Больше или равно| >| Больше|
|!=| Не равно| <| Меньше|
|@| Ставится перед именем переменной|. |Разделяет родительские и подчиненные объекты|
|@@| Указывает на системные функции|/ |Знак деления|
|--| Однострочный комментарий или комментарий с текущей позиции и до конца строки| /* */ | Многострочный комментарий|

====== 2. Идентификаторы ======
** Идентификаторы **  –  это  имена  объектов,  на  которые  можно  ссылаться  в программе,  написанной  на  языке Transact SQL.  Первый  символ  может  состоять  из  букв  английского  алфавита  или "_",   "@",   "#". В качестве остальных символов идентификатора могут быть дополнительно использованы цифры и символ "$". 
Имя  идентификатора  не  должно  совпадать  с  зарезервированным  словом (среда SQL Server Management Studio подсвечивает зарезервированные слова синим цветом).

Иногда в качестве идентификатора удобно использовать словосочетание. Можно воспользоваться заменой пробела на нижнее подчеркивание, а можно использовать ограничители идентификаторов - квадратные скобки или одинарные кавычки. Здесь надо обратить внимание на параметр QUOTED_IDENTIFIER.

Для ограничителей идентификаторов при установленном параметре
<code sql> SET QUOTED_IDENTIFIER ON </code> 
можно  использовать  как  квадратные  скобки, так и одинарные кавычки, а строковые значения могут быть заключены только в одинарные кавычки (режим по умолчанию).
 
Если использовать установленный параметр в режиме
<code sql> SET QUOTED_IDENTIFIER OFF </code> 
то в качестве ограничителей идентификаторов можно использовать только квадратные скобки, а строковые значения указываются в одинарных или двойных кавычках.

** Переменные ** используются  для  сохранения  промежуточных  данных  в  хранимых  процедурах  и  функциях.  Все переменные  считаются  локальными. Имя переменной должно начинаться с @.

====== 3. Объявление переменных ======
Синтаксис в обозначениях MS SQL Server:
<code sql>
DECLARE @имя_переменной1 тип_переменной, …, @имя_переменнойN тип_переменной
</code>

Если  тип  переменной  предполагает  указание  размера, то  используется следующий синтаксис для объявления переменных: 
<code sql>
DECLARE @имя_переменной1 тип_переменной(размер),
         …, 
        @имя_переменнойN тип_переменной(размер)
</code> 
** Пример: **
<code sql> 
DECLARE @a INT, @b numeric(10,2) 
DECLARE @str CHAR(20)
</code>

====== 4. Присвоение значений переменным и вывод значений на экран ======
Присвоение с помощью SET – обычное присвоение, синтаксис: 
<code sql> SET @имя_переменной = значение </code> 
** Пример: **
<code sql>
DECLARE @a INT, @b numeric(10,2) 
SET @a = 20 
SET @b = (@a+@a)/15 
SELECT @b        -- вывод на экран результата
</code>

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

** Пример: **
<code sql>
DECLARE @a INT 
SELECT @a = COUNT(*) FROM Authors
</code> 
** Пример: **
<code sql> 
DECLARE @str CHAR(30) 
SELECT @str = name_author FROM Authors
</code>  
В данном примере в переменную поместится последнее значение из результата запроса. 

Сочетание ключевых слов SET и SELECT

** Пример: ** 
<code sql>
DECLARE @a INT 
SET @a = (SELECT COUNT(*) FROM Authors)
</code> 
====== 5. Работа с датой и временем ======
Оператор SET DATEFORMAT dmy | ymd | mdy  задает  порядок  следования компонентов даты. 

** Пример: **
<code sql>
SET DATEFORMAT dmy                 -- задает порядок следования компонентов даты.
DECLARE @d DateTime                -- объявляем переменную типа DateTime  
SET @d = '30.10.1821 0:00:00'      -- устанавливаем значение переменной d 
SET @d = @d+1                      -- прибавляем один день
SELECT @d                          -- выводим значение переменной d в таблице  
</code>


====== 6. Создание временной таблицы через переменную типа TABLE ======

Объявляется через DECLARE с указанием в скобках столбцов таблицы, их типов, размеров, значений по умолчанию, а также индексов типа PRIMARY KEY или UNIQUE.

** Пример: **
<code sql>
/*Объявляем таблицу*/
DECLARE @mytable TABLE (                       -- Объявление переменной типа TABLE
    id INT,                                    -- Тип целых значений
    myname CHAR(20) DEFAULT 'Введите имя'      -- Тип значений и в ячейке выводится сообщение  
    )                                            
INSERT INTO @mytable(id) VALUES (1)             -- Добавляем строку
SELECT * FROM @mytable                          -- Выводим значение
</code>

** Пример: **
<code sql>
/*Объявляем таблицу*/
DECLARE  @mytable  TABLE(
    id  INT,
    myname CHAR(20)  DEFAULT  'Введите имя'
    ) 
INSERT @mytable
    SELECT 
        Code_publish,
        City
    FROM Publishing_house 
SELECT * FROM @mytable
</code>

====== 7.  Преобразование типов переменных ======

Функция CAST возвращает значение, преобразованное к указанному типу:
<code sql>
CAST(@переменная или значение AS требуемый_тип_данных)
</code>

** Пример: **
<code sql>
DECLARE @d DateTime, @str char(20)  -- объявляем переменные типа DateTime и char
SET @d = '31.01.2005 13:23:15'      -- устанавливаем значение переменной d 
SET @str = CAST(@d AS Char(20))     -- устанавливаем значение str, приводим к формату char
SELECT @str                         -- выводит значение переменной  
</code>

Функция CONVERT возвращает значение, преобразованное к указанному типу по заданному формату. Изучить дополнительно, по желанию.
====== 8. Операторские скобки ======
<code sql>
BEGIN 
/* В них  нельзя  помещать  команды,  изменяющие  структуры  объектов БД.
   Операторские  скобки  должны  содержать  хотя  бы  один  оператор.
   Требуются  для  конструкций  поливариантных  ветвлений,  условных  и  циклических конструкций
*/ 
END
</code> 

====== 9.  Условная конструкция IF ======
** Синтаксис: **
<code sql>
IF условие
    Набор операторов1
ELSE
    Набор операторов2
</code>
** Пример: **
<code sql>
DECLARE @a INT                                       -- объявляет переменную типа INT
DECLARE @str CHAR(40)                                -- объявляет переменную типа CHAR  
SET @a = (SELECT COUNT(name_author) FROM Authors)    -- устанавливает значение a, равное количеству
                                                     -- записей в столбце name_author  
IF @a > 10
BEGIN
    SET @str = 'Количество авторов больше 10'         -- если а>10, то выводится сообщение "Количество авторов больше 10" 
    SELECT @str                                       -- вывод значения переменной str 
END
ELSE
BEGIN
    SET @str = ' Количество авторов = ' + str(@a)     -- иначе количество авторов равно значению переменной а
    SELECT @str                                       -- вывод значения переменной str 
END
</code>


====== 10.  Цикл WHILE ======
** Синтаксис: **
<code sql>
WHILE Условие
    Набор операторов1
BREAK
    Набор опреторов2
CONTINUE
</code>

Конструкции BREAK и CONTINUE являются необязательными. Цикл можно принудительно остановить, если в его теле выполнить команду BREAK. Если же нужно начать цикл заново, не дожидаясь выполнения всех команд в теле, необходимо выполнить команду CONTINUE.

** Пример: **
<code sql>
DECLARE @a INT               -- объявляет переменную типа INT     
SET @a = 1                   -- устанавливает значение а =1 
WHILE @a < 100               -- цикл работает пока значение переменной а<100
BEGIN
    PRINT @a                 -- вывод на экран значения переменной
    IF (@a>40) AND (@a<50)   -- если значение переменной а>40  и a <50
       BREAK                 -- выход и выполнение 1-й команды за циклом
    ELSE                     -- иначе
       SET @a = @a+rand()*10 -- устанавливаем значение а равное переменной а + случайное число из диапазона (0, 1),
                             -- умноженное на 10
    CONTINUE                 -- запускаем цикл заново, не дожидаясь выполнения всех команд в теле
END
PRINT @a                     -- вывод на экран значения переменной
</code>


====== 11. Объявление курсора ======
CURSOR – это набор строк, являющийся результатом выполнения запроса. В один момент времени доступна лишь одна строка (текущая), по курсору можно передвигаться и получать доступ к элементарным данным. При объявлении курсора создается временная копия данных, которая сохраняется в БД tempdb.

Динамический курсор – данные в курсоре могут быть изменены.

Статический курсор – данные в курсоре не меняются.

Стандартный способ объявления курсора, синтаксис в обозначениях MS SQL Server:

<code sql>
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
</code>

** Примеры объявления курсоров **

1. Объявляем курсор с названием MyCursor1, который содержит всю информацию об авторах, двигаться по нему можно только от первой записи вниз до последней. Курсор является динамическим.
<code sql>
DECLARE MyCursor1 CURSOR FOR (select * from Authors)
</code>

2. Объявляем курсор с названием MyCursor1, который содержит всю информацию об авторах, двигаться по нему можно только от первой записи вниз до последней. Курсор является статическим.
<code sql>
DECLARE MyCursor1 INSENSITIVE CURSOR FOR (select * from Authors)
</code>

3. Объявляем курсор с названием MyCursor1, который содержит всю информацию об авторах, двигаться по нему можно в любом направлении. Курсор является динамическим.
<code sql>
DECLARE MyCursor1 SCROLL CURSOR FOR (select * from Authors)
</code>

4. Объявляем курсор с названием MyCursor1, который содержит всю информацию об авторах, двигаться по нему можно в любом направлении. Курсор является статическим.
<code sql>
DECLARE MyCursor1 INSENSITIVE SCROLL CURSOR FOR (select * from Authors)
</code>

5. Объявляем курсор с названием MyCursor1, который содержит всю информацию об авторах, двигаться по нему можно только от первой записи вниз до последней. Курсор является динамическим. Данные доступны только для чтения.
<code sql>
DECLARE MyCursor1 CURSOR FOR (select * from Authors) FOR READ ONLY
</code>

6. Объявляем курсор с названием MyCursor1, который содержит всю информацию об авторах, двигаться по нему можно только от первой записи вниз до последней. Курсор является динамическим. Данные курсора можно менять.
<code sql> 
DECLARE MyCursor1 CURSOR FOR (select * from Authors) FOR UPDATE
</code>

====== 12.  Операторы для работы с курсором ======
Прежде чем обратиться к данным курсора, его нужно после объявления открыть.

** Синтаксис оператора OPEN ** в обозначениях MS SQL Server: 
<code sql>
OPEN { { [ GLOBAL ] cursor_name} | cursor_variable_name } 
</code>

** Пример: ** 
<code sql>
DECLARE MyCursor1 CURSOR FOR (select * from Authors) 
OPEN MyCursor1 
</code>

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

** Синтаксис оператора CLOSE ** в обозначенияхMS SQL Server:
<code sql>
CLOSE { { [ GLOBAL ] cursor_name} | cursor_variable_name}
</code>

** Пример: **
<code sql>
DECLARE MyCursor1 CURSOR FOR (select * from Authors) 
OPEN MyCursor1 
--здесь операторы работы с курсором
CLOSE MyCursor1 
</code> 

Если  курсором  больше  не  будут  пользоваться, то его необходимо уничтожить и освободить переменную.
 
** Синтаксис оператора DEALLOCATE ** в обозначениях MS SQL Server: 
<code sql>
DEALLOCATE { { [ GLOBAL ] cursor_name} | @cursor_variable_name }
</code> 
 
** Пример: **
<code sql>
DECLARE MyCursor1 CURSOR FOR (select * from Authors) 
OPEN MyCursor1 
--здесь операторы работы с курсором
CLOSE MyCursor1 
DEALLOCATE MyCursor1
</code>
 
FETCH – оператор движения  по  записям  курсора  и  извлечения данных текущей записи в указанные переменные. ** Синтаксис оператора FETCH ** в обозначениях MS SQL Server:
<code sql> 
FETCH 
[ [ NEXT | PRIOR | FIRST | LAST 
| ABSOLUTE { n | @nvar } 
| RELATIVE { n | @nvar } 
] 
FROM 
] 
{ { [ GLOBAL ] cursor_name} | @cursor_variable_name } 
[ INTO @variable_name [1 ,..., n ] ] 
</code>

** Пример: **

<code sql> 
DECLARE MyCursor1 SCROLL CURSOR FOR (select * from Authors) 
DECLARE @i bigint, @s char(20), @d smalldatetime 
OPEN MyCursor1 
FETCH FIRST FROM MyCursor1 INTO @i, @s, @d 
PRINT @i 
PRINT @s 
PRINT @d 
CLOSE MyCursor1 
DEALLOCATE MyCursor1
</code>
 
@@FETCH_STATUS  – данная функция определяет признак конца или начала текущего курсора. Функция  принимает одно из следующих  значений: 
  * 0 – находимся  в  пределах курсора, не в конце; 
  * 1 – попытка выйти за пределы первой записи вверх (в никуда);
  * 2 – попытка выйти за пределы последней записи вниз(в никуда). 

** Пример: **
<code sql>
DECLARE MyCursor1 SCROLL CURSOR FOR (select * from Authors) 
DECLARE @i bigint, @s char(20), @d smalldatetime       -- объявляем переменные
OPEN MyCursor1                                         -- открываем MyCursor1
FETCH FIRST FROM MyCursor1 INTO @i, @s, @d             -- устанавливаем курсор на первую строку таблицы Authors 
                                                  
WHILE @@FETCH_STATUS = 0                               -- пока находимся в пределах таблицы
BEGIN 
    FETCH NEXT FROM MyCursor1 INTO @i, @s, @d          -- записываем данные из текущей строки в переменные
    PRINT @i                                           -- выводим code_author в переменную i
    PRINT @s                                           -- выводим name_author в переменную s
    PRINT @d                                           -- выводим birthday в переменную d
END 
CLOSE MyCursor1                                        -- закрываем курсор 
DEALLOCATE MyCursor1                                   -- освобождаем память
</code>

====== 13. Пример создания и запуска процедуры с курсором ======

<code sql>
-- пример создания процедуры
USE DB_book;
GO
IF OBJECT_ID ('Book','P') IS NOT NULL             -- если уже существует объект (процедура) с именем 'Book',
    DROP PROCEDURE Book;                          -- удаляем этот объект
GO           
CREATE PROCEDURE Book                             -- создаем процедуру Book, возвращающую значение переменной cur1         
    @cur1 CURSOR VARYING OUTPUT                   -- параметры курсора в хранимой процедуре обязательно должны быть
                                                  -- CURSOR VARYING OUTPUT
AS                                                -- далее - тело процедуры
    SET @cur1 = CURSOR FOR                        -- объявляем переменную типа курсор
    SELECT name_author FROM Authors;              -- выводим в курсор значения записи name_author
    OPEN @cur1;                                   -- открываем переменную        
GO

--пример запуска процедуры с курсором
USE DB_book;
GO 
DECLARE @MyCursor CURSOR;                         -- объявляем курсор
DECLARE @a CHAR(30);                              -- объявляем переменную @a
EXEC book @cur1 =@MyCursor OUTPUT;                -- запускаем процедуру и выводим возвращаемые значения
                                                  -- в объявленный курсор
              
FETCH NEXT FROM @MyCursor INTO @a;                -- устанавливаем курсор на первую запись таблицы и выводим
                                                  -- значение в переменную @a
                                                  
WHILE (@@FETCH_STATUS =0)                         -- пробегаем по всем строкам таблицы 
BEGIN
    PRINT @a;                                     -- выводим на экран значение переменной @a
    FETCH NEXT FROM @MyCursor INTO @a;            -- устанавливаем курсор на следующую запись таблицы и выводим
                                                  -- значение в переменную @a
END; 
CLOSE @MyCursor;                                  -- закрываем курсор
DEALLOCATE @MyCursor;                             -- освобождаем память 
GO
</code>

====== 14. Встроенные функции ======
Встроенные функции, имеющиеся в распоряжении пользователей при работе с SQL, можно условно разделить на следующие группы: 
  * математические функции; 
  * строковые функции; 
  * функции для работы с датой и временем; 
  * функции конфигурирования; 
  * функции системы безопасности; 
  * функции управления метаданными; 
  * статистические функции.

===== Использование функций для работы со строковыми переменными =====
===== Краткий обзор строковых функций =====

^ Название функций  ^ Действие, выполняемое функцией ^
|ASCII| Возвращает код ASCII левого символа строки|
|CHAR| По коду ASCII возвращает символ|
|CHARINDEX| Определяет порядковый номер символа, с которого начинается вхождение подстроки в строку|
|DIFFERENCE| Возвращает показатель совпадения строк|
|LEFT| Возвращает указанное число символов с начала строки|
|LEN| Возвращает длину строки|
|LOWER| Переводит все символы строки в нижний регистр|
|LTRIM| Удаляет пробелы в начале строки|
|NCHAR| Возвращает по коду символ Unicode|
|PATINDEX| Выполняет поиск подстроки в строке по указанному шаблону|
|REPLACE| Заменяет вхождения подстроки на указанное значение|
|QUOTENAME| Конвертирует строку в формат Unicode|
|REPLICATE| Выполняет тиражирование строки определенное число раз|
|REVERSE| Возвращает строку, символы которой записаны в обратном порядке|
|RIGHT| Возвращает указанное число символов с конца строки|
|RTRIM| Удаляет пробелы в конце строки|
|SOUNDEX| Возвращает код звучания строки|
|SPACE| Возвращает указанное число пробелов|
|STR| Выполняет конвертирование значения числового типа в символьный формат|
|STUFF| Удаляет указанное число символов, заменяя новой подстрокой|
|SUBSTRING| Возвращает для строки подстроку указанной длины с заданного символа|
|UNICODE| Возвращает Unicode-код левого символа строки|
|UPPER| Переводит все символы строки в верхний регистр|

===== Использование функций для работы с числами =====
===== Краткий обзор математических функций =====

^ Название функций  ^ Действие, выполняемое функцией ^
|ABS| Вычисляет абсолютное значение числа|
|ACOS| Вычисляет арккосинус|
|ASIN| Вычисляет арксинус|
|ATAN| Вычисляет арктангенс|
|ATN2| Вычисляет арктангенс с учетом квадратов|
|CEILING|Выполняет округление вверх|
|COS| Вычисляет косинус угла|
|COT| Переводит все символы строки в нижний регистр|
|DEGREES|Преобразует значение угла из радиан в градусы|
|EXP| Возвращает экспоненту|
|FLOOR| Выполняет округление вниз|
|LOG| Вычисляет натуральный логарифм|
|LOG10| Вычисляет десятичный логарифм|
|PI| Возвращает значение "пи"|
|POWER| Возводит число в степень|
|RADIANS| Преобразует значение угла из градуса в радианы|
|RAND| Возвращает случайное число|
|ROUND| Выполняет округление с заданной точностью|
|SIGN| Определяет знак числа|
|SIN| Вычисляет синус угла|
|SQUARE| Выполняет возведение числа в квадрат|
|SQRT| Извлекает квадратный корень|
|TAN| Возвращает тангенс угла|

===== Использование функций для работы с типом дата/время =====
===== Краткий обзор основных функций для работы с датой и временем =====

|DATEADD| Добавляет к дате указанное значение дней, месяцев, часов и т.д.|
|DATEDIFF| Возвращает разницу между указанными частями двух дат|
|DATENAME| Выделяет из даты указанную часть и возвращает ее в символьном формате|
|DATEPART| Выделяет из даты указанную часть и возвращает ее в числовом формате|
|DAY| Возвращает число из указанной даты|
|GETDATE|Возвращает текущее системное время|
|ISDATE| Проверяет правильность выражения на соответствие одному из возможных форматов ввода даты|
|MONTH| Возвращает значение месяца из указанной даты|
|YEAR|Возвращает значение года из указанной даты|
|MINUTE| Возвращает значение минут из указанной даты/времени|
|HOUR| Возвращает значение часов из указанной даты/времени|
|SECOND| Возвращает значение секунд из указанной даты/времени|


===== Варианты заданий к лабораторной работе №4 =====
==== Общие сведения ====

Для получения более подробной информации о работе тех или иных операторов или функций можно запустить утилиту Books Online из состава
MS SQL Server и в разделе «Указатель» набрать искомый ключевой элемент.

=== Специальные знаки и простейшие операторы в Transact SQL ===

** Пример.** Проверить работу описанной установки SET QUOTED_IDENTIFIER

Напишем следующий запрос (объявим строковую переменную и значение её заключим в двойные кавычки)

<code sql>
DECLARE @A CHAR(30)
SET @A = "ПРИВЕТ"
</code>

Если предварительно было установлено SET QUOTED_IDENTIFIER ON , то запрос завершится ошибкой. Чтобы запрос сработал нужно выставить SET QUOTED_IDENTIFIER OFF:

<code sql> 
SET QUOTED_IDENTIFIER OFF
DECLARE @A CHAR(30)
SET @A = "ПРИВЕТ"
</code>

**Задание**.

1.  Проверить работу описанной установки SET DATEFORMAT.

=== Объявление переменных ===

** Пример. ** Объявить переменную Perem1 типа денежный, а переменную Perem2 типа число с целой частью равной 8 и дробной частью равной 2.

<code sql> 
DECLARE @a MONEY, @b numeric(10,2) 
DECLARE @str CHAR(20)
</code> 

Money - тип данных, представляющие денежные (валютные) значения. Диапазон От -922 337 203 685 477,5808 до 922 337 203 685 477,5807 (8 байт).
Numeric(n,m) - тип данных с фиксированной запятой (n - общее число десятичных разрядов, m - количество знаков после запятой). 

** Задания. ** 

2. Объявить переменную Perem1 типа строка длиной 100, а переменную Perem2 типа длинное целое.

3. Объявить переменную Perem1 типа динамическая строка с максимальной длиной 1000, а переменную Perem2 типа целое число.

4. Объявить переменную Perem1 типа строка длиной 30, а переменную Perem2 типа число с целой частью равной 10 и дробной частью равной
3.

5. Объявить переменную Perem1 типа дата/ время, а переменную Perem2 типа число в диапазоне от 0 до 255.

=== Присвоение значений переменным и вывод значений на экран ===
** Задания. **
 
6. Подсчитать среднюю цену закупленных книг (с помощью запроса
SELECT) и умножить ее на значение 123,34, которое необходимо сохранить в отдельной переменной, вывести значение переменной на экран.

7. Подсчитать суммарную цену всех закупок книг, результат поместить в переменную, вывести значение переменной на экран.

8. Подсчитать количество книг в справочнике книг, результат поместить в переменную, вывести значение переменной на экран.

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

=== Сочетание ключевых слов SET и SELECT ===

10. Подсчитать количество поставщиков книг, результат поместить в переменную.

11. Подсчитать сумму закупок книг, результат поместить в переменную.

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

13. Подсчитать максимальную стоимость книг в закупке, результат
поместить в переменную.

=== Работа с датой и временем ===

14. Определить переменную Date1 типа дата/время. Присвоить ей значение даты 31.12.2006 в формате dd.mm.yyyy.

15. Определить переменную Date1 типа дата/время. Присвоить ей значение даты 31.12.2006 в формате mm.dd.yyyy.

16. Определить переменную Date1 типа дата/время. Присвоить ей значение даты 31.12.2006 в формате yyyy.mm.dd.
Создание временной таблицы через переменную типа TABLE

=== Использование локальных переменных ===

** Пример. ** Создать локальную таблицу с названием TEMP и полями типа дата/время, длинное целое, строка. Добавить в нее две записи с данными и вывести результат на экран.
<code sql>
DECLARE @temp TABLE (                       
    id INT, 
    date DATETIME, 
    num BIGINT DEFAULT 0,                                  
    myname CHAR(50) DEFAULT 'Введите имя'       
    )                                            
INSERT INTO @temp(id,date) VALUES (2,'25.11.2001')   
INSERT INTO @temp(id,date, myname) VALUES (3,'26.11.2001', 'Кирилл')            
SELECT * FROM @temp
</code>

** Задания. **

17. Создать локальную таблицу с названием TEMP и полями типа длинное целое, строка и значением по умолчанию «введите что-нибудь», денежный. Добавить в нее две записи с данными и вывести результат на экран.

18. Создать локальную таблицу с названием TEMP и полями типа целое, динамическая строка, бит со значением по умолчанию «1». Добавить в
нее две записи с данными и вывести результат на экран.

19. Создать локальную таблицу с названием TEMP и полями типа дата/время, длинное целое, строка. Добавить в нее две записи с данными и
вывести результат на экран.

20. Создать локальную таблицу с названием TEMP и полями типа дата/время, длинное целое с автонаращиванием, динамическая строка. Добавить в нее две записи с данными и вывести результат на экран.

=== Преобразование типов переменных ===

**Пример.** Объявить переменные типа BIT, NVARCHAR, DATETIME. Присвоить значения, соответствующие типам. Выполнить преобразование переменных типа BIT, NVARCHAR, DATETIME в FLOAT, INT, BIGINT соответственно и вывести результат на экран.
<code sql>
DECLARE @d BIT, @str NVARCHAR(30),@dat DATETIME   
SET @d = 'True'
SET @dat = '31.01.2005 13:23:15'    
SET @str = '1111111111'      
SELECT CAST(@d AS FLOAT) AS float1
SELECT CAST(@dat AS BIGINT) AS bigint1
SELECT CAST(@str AS INT) AS int1
</code>

** Задания. **

21. Объявить переменные типа FLOAT, CHAR, TINYINT. Присвоить значения, соответствующие типам. Выполнить преобразование переменных типа FLOAT, CHAR, TINYINT в INT, DATETIME, BIT соответственно и вывести результат на экран.

22. Объявить переменные типа INT, DATETIME, BIT. Присвоить значения, соответствующие типам. Выполнить преобразование переменных
типа INT, DATETIME, BIT в FLOAT, CHAR, TINYINT соответственно и вывести результат на экран.

23. Объявить переменные типа NUMERIC, VARCHAR, DATETIME. Присвоить значения, соответствующие типам. Выполнить преобразование
переменных типа NUMERIC, VARCHAR, DATETIME в FLOAT, CHAR, BIGINT соответственно и вывести результат на экран.

=== Условная конструкция IF ===

** Задания. **

24. Подсчитать количество поставщиков в таблице Deliveries. Если их в таблице от 2 до 5, то ничего не сообщать, в противном случае вывести сообщение вида "В таблице ... поставщиков" (вместо многоточия поставить точное количество поставщиков).

25. Подсчитать сумму закупок книг в таблице покупок. Если полученная сумма в диапозоне от 1000 до 5000, то ничего не сообщать, в противном случае вывести сообщение вида "Сумма закупок = …" (вместо многоточия поставить точную сумму).

26. Подсчитать среднюю стоимость закупки книг в таблице покупок. Если полученная стоимость в диапозоне от 1000 до 5000, то ничего не сообщать, в противном случае вывести сообщение вида "Средняя стоимость закупки = …" (вместо многоточия поставить точную среднюю стоимость).

27. Определить минимальную стоимость закупки книг в таблице покупок. Если полученная стоимость в диапозоне от 200 до 300, то ничего не
сообщать, в противном случае вывести сообщение вида "Минимальная стоимость закупки = …" (вместо многоточия поставить точную стоимость).

=== Цикл WHILE ===
**Пример.** Определить количество записей в таблице Authors. Пока записей меньше 100, делать в цикле добавление записи в таблицу с автоматическим наращиванием значения ключевого поля, а вместо имени автора ставить значение 'Автор не известен'.

<code sql>
Use DB_book
DECLARE @a INT 
DECLARE @b INT             
set @a=(SELECT count(Authors.Code_author) from authors)
set @b=@a+1           
WHILE @b < 100
BEGIN 
    INSERT INTO  Authors(code_author, name_author) VALUES (@b, 'Автор неизвестен')
    SET @b=@b+1                 -- цикл работает пока значение переменной b<100
END

SELECT * FROM Authors 
</code>

** Задания. **

28. Определить количество записей в таблице издательств. Пока записей меньше 20, делать в цикле добавление записи в таблицу с автоматическим наращиванием значения ключевого поля, а вместо названия издательства ставить значение 'не известно'.

29. Определить количество записей в таблице поставщиков. Пока записей меньше 17, делать в цикле добавление записи в таблицу с автоматическим наращиванием значения ключевого поля, а вместо названия поставщика ставить значение 'не известен'.

=== Объявление курсора ===

** Пример. ** Создать статический курсор по данным таблицы Books с полями Code_book, Title_book.
<code sql>
DECLARE MyCursor1  INSENSITIVE SCROLL CURSOR FOR (SELECT Code_book, Title_book FROM Books) 
OPEN MyCursor1 
FETCH FIRST FROM MyCursor1 
CLOSE MyCursor1 
DEALLOCATE MyCursor1
</code>

** Задания. **

30. Создать динамический курсор по данным таблицы поставщиков (таблица Deliveries) с полями Name_delivery, Name_company.

31. Создать статический курсор по данным таблицы Books и Authors с полями Code_book, Title_book, Name_author.

32. Создать статический курсор по данным таблицы Books и Publishing_house с полями Code_book, Title_book, Publish.

=== Операторы для работы с курсором ===

** Пример. ** Создать динамический курсор для чтения по данным таблицы Deliveries с полями Code_delivery, Name_delivery. Вывести данные 3-й записи.

<code sql>
DECLARE MyCursor1 SCROLL CURSOR FOR (SELECT Code_delivery, Name_delivery FROM  Deliveries) 
DECLARE @i bigint, @s char(30)
OPEN MyCursor1 
FETCH FIRST FROM MyCursor1 INTO @i, @s
FETCH NEXT FROM MyCursor1 INTO @i, @s
FETCH NEXT FROM MyCursor1 INTO @i, @s
PRINT @i 
PRINT @s 
CLOSE MyCursor1 
DEALLOCATE MyCursor1
</code>

** Задания. **

33. Сделать текущей БД db_books. Поместить в курсор данные таблицы Purchases. Перебрать все записи таблицы Purchases. Просуммировать
значения произведений полей Cost и Amount и результат сохранить в переменной Sum_table, которую после суммирования вывести на экран. Закрыть и удалить из памяти курсор.

34. Объявить статический курсор по данным таблиц Authors и Books. Вывести данные 5-й записи.

=== Использование функций для работы со строковыми переменными ===

Базовый текст дан в отдельном файле по вариантам. Для выполнения этого блока заданий в начале программы, которую вы создаете, объявите
переменную типа varchar и присвойте ей в качестве значения строку с любым базовым текстом, который будет анализироваться и/или справляться в заданиях.

**Пример.** Удалить в тексте лишние пробелы. Лишними считаются те, которые идут непосредственно за пробелом. Подсчитать количество исправлений.
<code sql>
Declare @text varchar(100)           -- Объявляем переменную типа динамической строки длинною до 100 символов
declare @i int                       -- Счетчик цикла
declare @c char (1)                  -- Отдельный символ строки
declare @ctemp char(1)               -- Предыдущий по отношению к @c символ строки
declare @a int                       -- Количество лишних пробелов
set @a=0;
set @text = ' съешь  еще  этих мягких  французских булок с изюмом '
set @ctemp = substring (@text,1, 1 ) -- Полагаем @ctemp равным первому символу строки 
set @i = 2
While  @i < len (@text)+1
begin 
    Set @c = substring (@text,@i, 1 )
    -- Если два соседние символа - пробелы, удаляем один из них
    if ((@ctemp= ' ') and (@c= ' '))
    begin
        set @text = (select stuff (@text,@i-1,1,''))
        set @a=@a+1
    end 
    set @ctemp = @c
    set @i=@i+1
end
select @text
select @a
</code>

** Задания. **

35. Подсчитать количество встреч каждой из следующих букв: "а", "в", "и", "п" в базовом тексте.

36. Подсчитать доли процентов встречи следующих букв: "е", "о", если суммарный процент встречаемости всех этих букв равен 100% или процент встречаемости е% + о% равен 100%.

37. По правилам оформления машинописных текстов перед знаками .,!?:; пробелы не ставятся, но обязательно ставятся после этих знаков. Удалите лишние пробелы. Подсчитать количество исправлений.

38. По правилам оформления машинописных текстов перед знаками .,!?:; пробелы не ставятся, но обязательно ставятся после этих знаков. Расставьте недостающие пробелы. Подсчитать количество исправлений.

39. Найти из исходного текста второе предложение и вернуть его в переменную Perem, а также вывести на экран весь исходный текст и найденное предложение.

40. Удалить из базового текста 2, 4, 6, 8 слова.

41. Удалить из базового текста 3, 5, 7, 10 слова.

42. Вставить в базовый текст вместо букв «а» - «АА».

43. Вставить в базовый текст вместо букв «е» и «о» - «ББ».
44. Поменять местами первое и последнее слова в базовом тексте.

=== Использование функций для работы с типом дата/время ===

** Пример. ** Вывести на экран название текущего месяца и текущее время. Записать в таблицу Purchases в поле Date_order одинаковую дату поступления, которая равна 12.03.2000.

<code sql>
SELECT DATENAME(MONTH,GETDATE())+ ', '+ CAST(DATEPART(HOUR,GETDATE())AS CHAR(4))+
':' + CAST (DATEPART(MINUTE,GETDATE())AS CHAR(4))+':'+ CAST (DATEPART(SECOND,GETDATE())AS CHAR(4))
UPDATE PURCHASES
SET DATE_ORDER = '12.03.2000'
</code>

** Задания. **

45. Разобрать на отдельные составляющие текущую дату и время и вывести значения на экран в следующем порядке (вместо многоточий): "Сегодня: День = …, Месяц = …, Год = …, Часов = …, Минут = …, Секунд= …"

46. В исходный текст, сохраненный в переменной Perem, после слова "
время " вставить текущее время. Результат сохранить в той же переменной
Perem и вывести на экран.


\ 

Назад: [[workroom:pib22-2013:comp:index:lab3]]

{{tag>}}