Лабораторная работа № 4 – СозданиеSql- запросов в среде sql Server Management Studio

Цель работы: ознакомиться с основными конструкциями языка SQL для манипулирования данными.

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

Указание: При выполнении работы используйте «Справочные материалы по SQL», которые расположены в дополнительном файле. (расположение файла спросить у преподавателя)

Ход работы:

1. Если необходимо, запустите SSMS, подключитесь к своему экземпляру SQL Server, используя технологию 1.

2. Откройте окно нового запроса. Измените контекст на базу данных МММ_вашеФИО, используя технологию 6.

3. Заполните таблицы Магазин, Заказ, Состав_заказа, Готовый продукт своими данными (не менее 5 строк в каждой таблице)

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

   1. Извлечь все данные из таблицы Модель (запрос SELECT)

   2. С помощью запроса добавить в таблицу Готовый_продукт одну запись с данными (запрос INSERT)

      Серийный номер	Код модели	Дата производства
      0076AA-Key	1	01.03.2009

   3. С помощью запроса удалить из таблицы Модель запись о модели с кодом =2. (запрос DELETE)

   4. Извлечь из таблицы Модель те названия моделей, чья цена >100 (запрос SELECT)

   5. Посчитать с помощью запроса среднюю цену всех моделей (запрос SELECT)

   6. Извлечь из таблиц Модель, Заказы, Магазины следующие данные – Заказанные названия моделей, количество моделей, названия магазинов (запрос SELECT – для объединения таблиц)

Задания для самостоятельной работы:

7. Вывести названия магазинов, начинающихся с буквы 'М'(запрос SELECT, условие LIKE)

8. Подсчитать количество готовых продуктов для каждой модели (запрос SELECT с Group by, агрегатная функция COUNT)

9. Для каждого магазина посчитать среднюю стоимость всех заказов за все время сотрудничества. (запрос SELECT с Group by, агрегатная функция AVG)

Лабораторная работа № 5 - Программирование с помощью встроенного языка transact sql в Microsoft Sql Server

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

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

Так же при выполнении работы можно использовать материалы презентации к лекции (см. файл Презентация_Обзор SQL Server. О расположении файла спросить у преподавателя).

Задание№1: Необходимо ознакомится с основами языка Transact-SQL.

Ход работы:

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

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

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

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

5. Если необходимо, запустите SSMS, подключитесь к своему экземпляру SQL Server, используя технологию 1.

6. Откройте окно нового запроса. Измените контекст на базу данных МММ_вашеФИО, используя технологию 6.

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

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

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

Присвоение значений переменным и вывод значений на экран

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

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

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

Цикл WHILE

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

Задание№2: Необходимо научиться создавать и использовать хранимые процедуры на сервере БД.

Ход работы:

1. Если необходимо, запустите SSMS, подключитесь к своему экземпляру SQL Server, используя технологию 1.

2. Откройте окно нового запроса. Измените контекст на базу данных МММ_вашеФИО, используя технологию 6.

3. Создайте, наберите, исполните и сохраните текст запроса для создания хранимой процедуры с помощью оператора Create procedure, причем самостоятельно определить имя процедуры в вашей БД: Хранимая процедура должна «Вывести информацию о моделях со стоимостью больше указанного числа, отсортированных по названию моделей».

4. При создании хранимой процедуры воспользуйтесь описанием синтаксиса команды и примерами из файла Презентация_Обзор SQL Server.

5. В SQL Server Management Studio в разделе БД МММ Программирование хранимые процедуры проверить наличие вашей процедуры.

6. Выполнить (вызвать) вашу процедуру с входящей ценой = 50, проверить результат выполнения.

Задание№3: Необходимо научиться создавать и использовать триггеры на сервере БД.

Ход работы:

1. Если необходимо, запустите SSMS, подключитесь к своему экземпляру SQL Server, используя технологию 1.

2. Откройте окно нового запроса. Измените контекст на базу данных МММ_вашеФИО, используя технологию 6.

3. В вашей БД МММ создать таблицу модель_безопасность, состоящую из 3-ех столбцов (название_модели (тип – символьный (50)), имя_пользователя (тип – символьный (50)), дата_добавления (тип – дата-время)) – без ключевого поля и без связей с другими таблицами.

4. Создать триггер «вставка_модель» для таблицы Модель c помощью оператора Create Trigger. Данный триггер должен при добавлении записи в таблицу Модели, добавлять запись безопасности в таблицу модель_безопасность. Подробное описание работы триггера приведено далее.

В МММ любые добавления в таблицу моделей (каталог_моделей) происходят редко и выполняются под строгим контролем. При этом отслеживается название добавленной модели, дата внесения добавлений и имя пользователя, который внес это изменение.

Поэтому необходимо реализовать триггер на таблице Модель (каталог_моделей) запускающийся при выполнении операции INSERT и протоколирующий информацию о добавленных моделях в таблицу модель_безопасность. То есть необходимо создать триггер, который при добавлении новой модели в каталог создавал бы в таблице модель_безопасность строку, в которой бы фиксировалось значение названия, дата внесения этого названия в таблицу и имя пользователя, который произвел добавление.

5. В SQL Server Management Studio в разделе БД МММТаблица Модели (Каталог моделей) триггеры проверить наличие вашего триггера.

6. Проверить действие триггера.