Работа с базами данных Microsoft SQL Server: сценарии практических занятий

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ 
ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ ОБРАЗОВАТЕЛЬНОЕ 
УЧРЕЖДЕНИЕ ВЫСШЕГО ОБРАЗОВАНИЯ 
«МОСКОВСКИЙ ГОСУДАРСТВЕННЫЙ ПСИХОЛОГО-ПЕДАГОГИЧЕСКИЙ  
УНИВЕРСИТЕТ» 
ФАКУЛЬТЕТ «ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ» 
КАФЕДРА «ПРИКЛАДНАЯ ИНФОРМАТИКА 
И МУЛЬТИМЕДИЙНЫЕ ТЕХНОЛОГИИ» 
 
 
А. И. Митин 
 
 
 
 
 
РАБОТА С БАЗАМИ ДАННЫХ 
MICROSOFT SQL SERVER 
 
 
Сценарии практических занятий 
 
 
Рекомендовано к изданию  
кафедрой «Прикладная информатика 
и мультимедийные технологии» 
 
 
 
 
 
 
 

 
Москва 
Берлин 
2020 

УДК 075.8:002 
ББК 73.6 
         М66 

Рецензент: 
Кулик С. Д. — доктор технических наук, профессор 

 Митин, А. И. 
М66  
Работа с базами данных Microsoft SQL Server: сценарии 
практических занятий / А. И. Митин. — Москва ; Берлин : 
Директ-Медиа, 2020. — 142 с. 

ISBN 978-5-4499-0420-1 

В пособии рассматриваются технологические приемы, позволяющие выполнить ряд практических заданий по работе с базами данных 
в среде популярной СУБД Microsoft SQL Server. Пособие может быть 
рекомендовано в качестве методического руководства на семинарских 
занятиях и при подготовке к сдаче зачетов и экзаменов по курсам, связанным с базами данных. 

Текст приводится в авторской редакции. 

УДК 075.8:002 
ББК 73.6 

ISBN 978-5-4499-0420-1
© Митин А. И. , текст, 2020
© Издательство «Директ-Медиа», оформление, 2020 

 

Содержание 

Введение ...................................................................................................... 5 

1. Практическое занятие 1. Изучение основных понятий 
структуры БД ............................................................................................ 10 
1.1. Упражнение «Определение главных объектов 
в БД SQL Server» ................................................................................. 11 
1.2. Упражнение «Определение требований к БД. 
Сценарий БД (книжный магазин)» ................................................... 15 
1.3. Упражнение «Разработка логической модели данных» ......... 25 
1.4. Упражнение «Создание и управление базой данных» ........... 31 
1.5. Упражнение «Определение типов данных для столбцов» ..... 34 
1.6. Упражнение «Создание таблиц и управление ими» ............... 36 
1.7. Упражнение «Определение свойств, гарантирующих 
целостность данных» .......................................................................... 40 
1.8. Упражнение «Добавление ограничений 
в существующие таблицы» ................................................................ 41 

2. Практическое занятие 2. Язык Transact SQL .................................... 48 
2.1. Упражнение «Работа с редактором запросов» ......................... 48 
2.2. Упражнение «Создание и исполнение 
операторов DDL, DCL и DML» ........................................................ 51 
2.3. Упражнение «Создание сценариев с помощью 
синтаксических элементов языка T-SQL» ....................................... 54 

3. Практическое занятие 3. Выборка и модификация данных ........... 58 
3.1. Упражнение «Использование оператора SELECT 
для выборки данных» ......................................................................... 58 
3.2. Упражнение «Извлечение данных с помощью 
усложненных методик работы с запросами» .................................. 61 
3.3. Упражнение «Модификация данных в БД SQL Server» ........ 67 

4. Практическое занятие 4. Управление и манипулирование 
данными ..................................................................................................... 70 
4.1. Упражнение «Импорт и экспорт данных» ............................... 70 
4.2. Упражнение «Создание новой БД, экспорт 
и импорт таблиц (ODBC)» ................................................................. 72 
4.3. Упражнение «Применение распределенных запросов 
для доступа к внешним данным» ...................................................... 76 

 
 

3

 

4.4. Упражнение «Создание курсора для извлечения данных» ... 78 

5. Практическое занятие 5. Хранимые процедуры .............................. 80 
5.1. Упражнение «Изучение хранимых процедур» ........................ 80 
5.2. Упражнение «Работа с хранимыми процедурами» ................ 81 
5.3. Упражнение «Программирование хранимой процедуры 
для добавления и извлечения данных» ............................................ 85 
5.4. Упражнение «Создание хранимых процедур 
в Management Studio» ......................................................................... 92 

6. Практическое занятие 6. Представления .......................................... 95 
6.1. Упражнение «Создание и модификация представления» ..... 95 
6.2. Упражнение «Доступ к данным с помощью 
представления AuthorNames» ........................................................... 97 

7. Практическое занятие 7. Триггеры .................................................. 100 
7.1. Упражнение «Применение ограничений каскадной 
ссылочной целостности» ................................................................. 100 
7.2. Упражнение «Создание триггеров и управление ими» ........ 103 
7.3. Упражнение «Создание триггера для обновления 
значения столбца» ............................................................................ 106 

8. Практическое занятие 8. Проектирование и реализация 
системы безопасности SQL Server ...................................................... 111 
8.1. Упражнение «Реализация системы безопасности 
для БД BookShopDB» ....................................................................... 111 

9. Практическое занятие 9. Бизнес-аналитика в SQL Server ............ 117 
9.1. Упражнение «Создание и заполнение хранилища 
данных» .............................................................................................. 117 
9.2. Упражнение «Создание многомерной базы данных» .......... 126 
9.3. Упражнение «Работа с многомерным хранилищем 
данных» .............................................................................................. 130 

Список литературы и интернет-источников ...................................... 132 

Приложение. Диаграммы (схемы) баз данных, 
использующихся в курсе ...................................................................... 135 
 

ВВЕДЕНИЕ 

Настоящее пособие описывает технологии работы с популярной системой управления базами данных Microsoft SQL 
Server, широко использующейся для поддержки читающихся в 
МГППУ курсов по разработке и эксплуатации баз данных и информационных систем. 
Актуальность пособия обусловлена не только сравнительной новизной и сложностью технологии «клиент – сервер», лежащей в основе Microsoft SQL Server, но и необходимостью систематического изложения особенностей работы с нею с определенным методическим подтекстом, важным с точки зрения 
автора. Конечно, в наше время несложно получить информацию 
о технологиях работы с любыми базами данных (как в виде 
опубликованного пособия, так и в Интернете). Однако в основу 
настоящего пособия положен именно сценарный подход (как 
указано в подзаголовке), что делает пособие не просто источником информации, а своеобразным «руководством к действию» 
при решении практических задач. Кроме того, автор при написании пособия часто обращал внимание на методику использования апробированных на практических занятиях технологических нюансов Microsoft SQL Server, без которых работа с СУБД 
теряла бы свою эффективность. 
Структура пособия соответствует тематическому плану 
дисциплины «Основы разработки информационных систем и 
баз данных» (специальность «Математическое обеспечение и 
администрирование информационных систем»). Материал пособия может оказаться полезным и при изучении других курсов 
этой специальности и других специальностей, связанных с базами данных («Базы данных», «Эксплуатация информационных 
систем и баз данных» и т.п.). 
Изложение приемов работы с Microsoft SQL Server  в пособии ведется на основе достаточно автономных упражнений, 
которые студенты могут выполнять как в рамках семинарских 
занятий, так и самостоятельно (например, при подготовке к сдаче зачета или экзамена). Большинство упражнений представляет 
собой «классические» упражнения сертификационных курсов 

5

фирмы Microsoft [14]; содержание некоторых дополнительных 
упражнений взято из книг [9] и [10]. Файлы данных для упражнений располагаются в папке SQL_Server файлового сервера 
локальной сети.  
Упражнения практически не дублируют друг друга, но допускается возврат к ранее пройденным упражнениям с целью 
вспомнить особенности их выполнения или выполнить их еще 
раз после изучения некоторых новых возможностей Microsoft 
SQL Server  (такие возвраты в явном виде описываются в сценариях). 
Конечно, сценарный подход предполагает определенную 
строгость выполнения действий студентами (в этом залог успешности выполнения упражнения в целом), но элемент творчества (в 
разумных пределах) сценарии не исключают (иногда по этому 
поводу в сценариях даже даются дополнительные рекомендации 
и предлагаются факультативные варианты действий).  
Следует иметь в виду, что для выполнения подавляющего 
большинства упражнений принято не использовать достаточно 
развитый графический интерфейс Microsoft SQL Server (Management Studio), а вводить тексты соответствующих запросов 
непосредственно на языке Transact SQL (T-SQL). Этот подход 
позволяет подвести единую лингвистическую и технологическую базу под достаточно разнородные процессы, описываемые 
в упражнениях (создание разнообразных объектов; выборка из 
баз данных; модификация данных; работа с представлениями, 
триггерами, курсорами, процедурами; управление ходом выполнения команд и т.п.). Кроме того, использование такого вербального подхода сильно упрощает работу студентов с ограниченными возможностями здоровья. 
Графический интерфейс Management Studio в этом случае 
удобно использовать для проверки правильности выполнения 
запросов. 
При работе с языком T-SQL следует учесть следующие 
моменты: 

 Большого объема ввода при работе с редактором запросов можно избежать, если использовать специальные текстовые заготовки, которые хранятся в папке SQL_Server. Практи
6

____________________________________________________________________________________________________
Введение

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

Установленные в компьютерных классах версии Microsoft SQL Server работают в режиме нечувствительности к регистру клавиатуры. Таким образом, команды, служебные слова 
и идентификаторы можно вводить как заглавными, так и строчными буквами. 

 Очень часто результат выполнения операторов создания 
или модификации объектов (баз данных, таблиц, представлений 
и т.п.) не виден сразу же после выполнения. Для отображения 
результата надо в дереве объектов обозревателя объектов Management Studio для соответствующего типа объектов выполнить 
команду Обновить контекстного меню (или выделить тип 
объектов в дереве и нажать [F5]). 

 В случае, если по тем или иным причинам конкретная 
база данных не существует или испорчена, ее можно восстановить выполнением соответствующего сценария (файла с расширением .sql), хранящегося в подпапке Восстановление БД папки SQL_Server. Диаграммы (схемы) баз данных, использующихся в пособии, содержатся в Приложении. 

Для большей лаконичности описания технологических 
приемов в пособии используется специализированный язык, заимствованный из книги [7]. Он позволяет быстро и однозначно 
описывать работу с меню или панелями инструментов с возможностью встраивания «технологических кусков» в текст на 
естественном языке. Синтаксис этого языка (табл. 1) ориентирован на работу с меню (для обучения это наиболее естественная 
работа). Если же возникает необходимость использовать пиктографическую кнопку панели инструментов или диалогового окна, то в тексте пособия эта кнопка будет просто изображаться. 
Изложение материала ориентировано на текущую версию 
Microsoft SQL Server, установленную в компьютерных классах 
МГППУ (конкретно, на локализованный вариант Microsoft SQL 
Server 2005). Однако большинство сценарных действий применимо и для других версий этого программного продукта.  

7

____________________________________________________________________________________________________
Введение

Таблица 1 — Язык описания технологических приемов 

Используемое
обозначение

Выполняемое действие

 Название окна
 Название подокна

Активизация окна (подокна), то есть фиксация 
курсора в области окна (подокна) для его активизации.

 Название вкладки
Выбор ярлычка  вкладки, то есть фиксация 
курсора на указанном ярлычке.

Название типа объекта 
Название 

объекта

Движение на один уровень вниз по дереву 
объектов обозревателя объектов Management
Studio (как правило, с помощью кнопки 
).

 Название переключателя

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

 Название флажка
Отметка флажка с указанным названием в активном диалоговом окне, то есть фиксация 
курсора на указанном флажке. Для сброса 
флажка используется словесное указание
 Название флажка (сбросить)

 Название команды
Выбор в текущем меню команды с указанным 
названием, то есть фиксация курсора на указанной команде.

 Название кнопки
Фиксация курсора на кнопке с указанным 
названием на панели инструментов, в строке 
состояния, или активном диалоговом окне.

Изображение кнопки 
Название кнопки

Фиксация курсора на кнопке с указанным 
названием на панели инструментов, в строке 
состояния или в активном диалоговом окне.

Изображение вкладки
Название вкладки

Фиксация курсора на вкладке с указанным 
названием на панели команд.

Изображение пиктограммы  Название 
пиктограммы

Двойная фиксация курсора на указанной пиктограмме.

 Название  раскрывающегося списка 
или пункта спискаменю

Развертывание раскрывающегося списка, то 
есть фиксация курсора на кнопке раскрытия 

списка 
, раскрытие списка-меню.

8

____________________________________________________________________________________________________
Введение

Используемое 
 обозначение 

Выполняемое действие 

 Слово-ссылка или 
изображение пиктограммы 

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

 Фрагмент текста, 
ячейка или интервал 
ячеек электронной 
таблицы, элемент 
списка или раскрывающегося списка 

Выделение указанного фрагмента текста, интервала или элемента списка с помощью движения мыши при нажатой и удерживаемой 
левой кнопке либо с помощью клавиш управления курсором при нажатой и удерживаемой 
клавише [Shift]. 

Название поля := 
значение 

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

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

[Клавиша] 
Нажатие соответствующей клавиши на клавиатуре. 

[Клавиша1] + [Клавиша2] 

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

[Клавиша1] , [Клавиша2] 

Последовательное нажатие сначала первой, 
затем второй клавиши на клавиатуре. 

{Текст примечания} 
Примечание к данному выполняемому действию или к параметрам этого действия. 

* В некоторых случаях в качестве вводимого значения указывается не конкретный текст, а смысловое описание этого текста, вместо которого пользователь должен ввести подходящее по контексту конкретное значение. В
таком случае смысловое описание заключается в угловые скобки, например, Файл := <имя файла>

Окончание табл. 1 

____________________________________________________________________________________________________
Введение

§ 1. ПРАКТИЧЕСКОЕ ЗАНЯТИЕ 1
Изучение основных понятий структуры БД

Занятие начинается со знакомства с интерфейсом Management Studio и «воспоминаний» (на основе информации пройденных курсов) о нормализации БД, таблицах SQL Server, связях между ними и т.п. (упражнение «Определение главных объектов в БД SQL Server» (§ 1.1)).  
Последующие упражнения этого весьма емкого занятия, 
по сути, следуют логике проектирования и разработки реальной 
базы данных (конкретно, базы данных книжного магазина – 
BookShopDB): 

 формирование подходов к концептуальному проектированию БД, анализ сценария (постановки) задачи (упражнение 
«Определение требований к БД. Сценарий БД (книжный магазин)» (§ 1.2)); 

 логическое проектирование БД (сущности / связи / атрибуты)  (упражнение «Разработка логической модели данных» 
(§ 1.3)); 

  создание БД, ее просмотр (всех объектов) и удаление 
(упражнение «Создание и управление базой данных» (§ 1.4)); 

 определение типов данных для всех столбцов таблиц 
(упражнение «Определение типов данных для столбцов» 
(§ 1.5)); 

 создание, модификация и удаление таблиц (упражнение 
«Создание таблиц и управление ими» (§ 1.6)); 

 учет соображений по видам целостности данных (конструкции DEFAULT, IDENTITY, NOT NULL) (упражнение 
«Определение свойств, гарантирующих целостность данных» 
(§ 1.7)); 

 добавление первичных и внешних ключей (конструкции 
PRIMARY KEY, FOREIGN KEY), ограничений CHECK в существующие таблицы (упражнение «Добавление ограничений в существующие таблицы» (§ 1.8)). 

10