nikolaeva_n.a._yazyk_strukturirovannyh_zaprosov..


Чтобы посмотреть этот PDF файл с форматированием и разметкой, скачайте его и откройте на своем компьютере.

ФЕДЕРАЛЬНОЕ АГЕНТСТВ
О ПО ОБРАЗОВАНИЮ


УХТИНСКИЙ ГОСУДАРСТВ
ЕННЫЙ ТЕХНИЧЕСКИЙ УН
ИВЕРСИТЕТ










Н.А. Николаева, Т.Ю. Калинина








Язык структурированных запросов


Лабораторные работы



















Ухта 2006



Учебное издание


Надежда Александровна
Николаева

Татьяна Юрьевна Калинина


Язык структурированных запросов

Лабораторные работы




УДК 658.012.011.56.005:681.3.016


Н 63


Николаева, Н.А. Язык структурированных запросов. Лабораторные работы [Текст] /
Н.А.

Николаева, Т.Ю.

Калинина.


Ухта
: УГТУ, 2006.


124 с.: ил.


IS
В
N

5
-
88179
-
419
-
2



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

Пособие предназначено для студентов, обучающихся по направлению 654700 Инфо
р-
мационные системы (специал
ь
ность 230201 Информацио
нные системы и технологии).


Рецензенты:

доцент кафедры программного обеспечения, вычислительной техники и автом
а-
ти
зирова
н
ных систем НГУ им. Ярослава Мудрого кандидат физико
-
математических наук Е.И.

Зайцева; программист
I

категории ООО ©Северга
з-
торгª О.В.

Перцева.



Редактор Ю.В. Гурьева

Технический редактор Л.П. Коровкина



© Ухтинский государственный технический университет, 2006

© Николаева Н.А., Калинина Т.Ю., 2006


IS
В
N

5
-
88179
-
419
-
2


Ухтинский государственный технический университет.

169300, г. Ухта,

ул. Первомайская, 13.

Отдел оперативной полиграфии УГТУ.

169300, г. Ухта, ул. Октябрьская, 13.


План 2005 г., позиция 13. Подписано в печать 31.05.2006.

Компьютерный набор. Гарнитура
Times

New

Roman

Cyr
.

Формат 60х84 1/16. Бумага офсетная. Печать трафарет
ная.

Усл. печ. л. 7,2. Уч.
-

изд. л. 6,7. Тираж 120 экз. Заказ № 200.




3

ВВЕДЕНИЕ

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

Язык SQL
-

Structure Query Language (Язык структуриров
анных запр
о-
сов) является одним из доминирующих языков, используемых в реляционных
базах данных. В качестве стандартного языка баз данных он принят такими о
р-
ганизациями, как American National Standards Insnitute


ANSI (Америка
н
ский
национальный институт ст
андартов) и International Standards Organization


ISO
(Международная организация стандартов). SQL является непроцедурным яз
ы-
ком, что позволяет ему обрабатывать наборы записей и автоматизировать пр
о-
цесс управления данными. SQL не требует от пользователя сп
ецификации
метода доступа к данным, пользователь вообще не до
л
жен описывать, как
должна выполняться та или иная операция над данными, он просто описывает,
что должно быть сделано с данными, предоставляя право системе решать, как
это сделать, тем он и привл
екателен. Синтаксис языка
SQL

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

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

и работу со всеми объектами б
а-
зы данных. Впрочем, авторы, к сожалению, не встречали ни одной исчерп
ы
в
а-
ющей работы в этом направлении, хотя хороших работ очень много и каждая из
них хороша по своему и реализует поставленные перед ней цел
и. Целью н
а
ш
е-
го пособия является заложить фундамент, кот
о
рый позволил бы студенту в
дальнейшем неограниченно совершенствовать свои знания в области
SQL
. А
в-
торы уверены, что это занятие весьма увлекательно. Неслучайно такие потр
я-
сающие программисты и аналит
ики, как Джо Селко, Денис Шаша, Кен
Хендерсон, Роббер Вьера, Майкл Оутей, Поль Конте и многие другие, перечи
с-
ление имен которых заняло бы не одну стр
а
ницу, все время возвращаются к
этому языку, находя все новые и новые а
с
пекты и нюансы его использования.


4

Н
есомненно, данное пособие должно помочь студенту закрепить осно
в-
ные положения из курса управление данными по направлению 654700 (спец
и-
альность 230201). Однако наиболее полно освоить данный курс ст
у
дент может,
только используя все пособия по данной дисципли
не, входящие в комплект, а
также литературу, список которой приведен авторами в заключ
и
тельной части
пособия.

Лабораторные работы могут выполняться с использованием СУБД
M
i-
cr
o
soft

SQL
, а также любой СУБД, поддерживающей стандарт ANSI/ISO SQL3.

Первая лабор
аторная работа посвящена командам
DDL
.

Вторая


командам ввода, удаления модификации данных (командам
языка
DML
).

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

Четвертая лабораторная посвя
щена изучению самой интересной к
о
манде
языка
DML



SELECT

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

Шестая лабораторная работа связана с созданием одного из наиболее
спорных объектов базы данных
-

представлений.

Сед
ьмая лабораторная работа посвящена созданию хранимых процедур,
одного из мощнейших инструментов современных СУБД.

Восьмая лабораторная работа дает представление о создании тригг
е
ров.

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

сложными. Авторы р
е-
комендуют не пренебрегать ни теми, ни другими.

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


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

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

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


будь, что будет. Но мы весьма призн
а-
тельны за все замечания, сделанные нашими студентами и ко
л
легами.


5

ЛАБОРАТОРНАЯ РАБОТА
№ 1

Цель занятия
: Приобретение навыков настройки и работы в с
реде
Query

An
a-
lyzer
. Изучение синтаксиса команды SQL: CREATE TABLE.

Результат занятия:

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

ОПИСАНИЕ УЧЕБНОГО ПР
ИМЕРА

В пособии порядок выполнения заданий лабораторных ра
бот продемо
н-
стрирован на примере базы данных ©Успеваемость студентовª, которая вкл
ю-
чает в себя следующие таблицы:



Студент (Student)



Преподаватель (Teacher)



Успеваемость (Progress)



Учебный план (
U
Plan)



Предмет (Subject)



Отчетность (Report)



Вид занятий (SWor
k)



Группа (
SGroup
)

Таблица Студент (Student).

Таблица содержит данные о студенте. Каждый студент имеет уникал
ь-
ный номер зачетки, который является первичным ключом отношения. Схема
отношения имеет вид: R(Student)=
N
R
ecord
B
ook
, SName, ID
G
roup,
SPasport
,
NPas
port
,
DataPasport
,
NameDeptPasport
,
INN
, что аналогично

R(Студент)=
N зачетки
, Имя студента, Код группы, Серия паспорта, Номер
паспорта, Наим
е
нование организации, ИНН. Внешним ключом является
ID
G
roup (Код гру
п
пы). Комбинация атрибутов
SPasport
,
NPasport
,
DataPasport
,
NameDeptPasport

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

Таблица Преподаватель (Teacher).

Таблица содержит данные о преподавателе. Каждый преподаватель имеет
уника
льный идентификационный номер, который является первичным ключом о
т-
ношения. Схема отношения имеет вид: R(Teacher)=
PIN
, T
eachN
ame, Dept
N
name,
T
each
Post, Date
H
ire, что аналогично R(Преподаватель) = 
Идентификационный н
о-
мер преподавателя
, Имя преподавателя,

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


6

Таблица Успеваемость (Progress).

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

в ведомости фиксируется только факт сдачи зачета
(©зачетª

или ©незачетª). Договоримся в поле Mark (Оценка) зан
о
сить значение 1,
если ©зачетª и 0


если ©незачетª. Первичным ключом в отношении являются а
т-
рибуты: № зачетки, Код предмета, Код вида отчетности, № cеместра. Схема о
т-
ношения имеет вид: R(Progress)=
N
R
ec
ord
B
ook,

PIN,
ID
S
ubject, ID
R
eport, N
T
erm,

Mark, что соответствует схеме R(Успеваемость)=
№ зачетки,

Идентификацио
н-
ный номер преподавателя,
Код предмета, Код вида отчетности, № семестра,

Оценка. Внешние ключи прив
е
дены ниже (см.
Таблица
1
).

Таблица
1

Внешние ключи отношения Успеваемость (Progress)

Внешний ключ

Ссылочное отношение


зачетки

(
NRecordBook
)

Студент

(Student)

Идентификационный номер преподавателя (PIN)

Преподаватель (Teacher
)

Код предмета(
ID
S
ubject
)

Предмет (Subject)

Код вида отчетности (
ID
R
eport
)

Отчетность (Report)

Таблица Учебный план (UPlan).

В таблице содержится информация о предметах, которые изучают ст
у-
денты той или иной группы, о количестве часов, отводящихся для т
ого или
иного вида занятий (лекций, практических, лабораторных и т.п.). Ключ отн
о-
шения выделен на схеме. R(
U
Plan)={
ID
S
ubject, ID
W
ork, ID
G
roup, N
Te
rm
,
C
lock,
PIN
) или
-

R(План)=
Код предмета, Код вида занятия, Код группы, Семестр
,
Кол
-
во часов,
Идентификаци
онный номер преподавателя
. Вне
ш
ние ключи
отношения План (
U
Plan) приведены ниже (см.
Таблица
2
).

Таблица
2

Внешние ключи отношения План (
UPlan
)

Внешний ключ

Ссылочное отношение

Код вида
занятия (ID
W
ork)

Вид занятий (
Work
)

Идентификационный номер преподавателя (
PIN
)

Преподаватель (
Teacher
)

Код предмета (ID
S
ubject)

Предмет (Subject)

Код группы (ID
G
roup)

Группа (
SGroup
)


7

Таблица Предмет (Subject).

Таблица представляет собой справочник пре
дметов, изучаемых студе
н
том.
Первичным ключом отношения является Код предмета (ID
S
ubject). Схема отн
о-
шения имеет вид: R(Subject)=
ID
S
ubject
, Name
S
ubject, аналог этой схемы
R(Предмет)=
Код предмета
, Название предмета. Атрибут Name
S
ubject (Н
а
звание
предмет
а) должен быть уникальным. Таблица не содержит внешних ключей.

Таблица Отчетность (Report).

Таблица представляет собой справочник видов отчетности. Домен атр
и-
бута Название вида отчетности будет включать в себя экзамен, зачет, ку
р
совой
проект и т.п. R(Repor
t)={
IDR
eport
, Name
R
eport, или R(Отчетность)=
Код вида
отчетности
, Название вида отчетности. Атрибут Name
R
eport (Название вида
отчетности) должен быть уникальным. Таблица не содержит внешних ключей.

Таблица Вид занятий (SWork).

Таблица представляет собой
справочник видов занятий. Домен атриб
у
та
Название вида занятия будет включать в себя следующие значения: ле
к
ция,
практическое занятие, лабораторное занятие и т.п. R(SWork)=
IDW
ork
,
Name
W
ork, или R(Виды занятий)=
Код вида занятия
, Название вида зан
я
тия.
А
трибут Name
W
ork (Название вида занятия) должен быть уникальным. Табл
и-
ца не содержит внешних ключей.

Таблица Группа (SGroup).

Таблица представляет информацию о группах обучающихся в ВУЗе. Пе
р-
вичным ключом отношения является
IDGroup

(Код группы). Домен атр
и
б
ута
Название группы будет включать в себя все названия групп ВУЗа.
R(
SGroup
)={
IDGroup
, Name
Group
, или R(Группа)=
Код группы
, Название
группы. Атрибут Name
Group

(Название группы) должен быть уникальным.
Таблица не содержит внешних ключей.

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

Server

2000, да
ны в пр
и-
ложении (см. Пр
и
ложение 1).

Структура таблиц учебного примера приведена ниже (см. Таблица 3


Та
б-
лица 9), где первичные ключи выделены цветом и подчеркнуты.


8

Таблица
3

Таблица Студент (Student)

Название атрибута

Имя поля

Тип поля

Размер

Ограничения

№ зачетной книжки

NRecordBook

Varchar

6

Not null

Имя студента

SName

Varchar

35


Код группы

IDGroup

INT


Foreign key

Серия паспорта

SPasport

Varchar

4

Not null

Unique

Номер паспорта

NPasport

Varchar

6

Дата выдачи

DataPaspo
rt



Наименование

орган
и
зации

NameDeptPasport

Varchar

35

ИНН

INN

Varchar

10

Unique

Таблица
4

Таблица Преподаватель (Teacher)

Название атрибута

Имя поля

Тип поля

Размер

Ограничения

Идентификационный
номер преподават
еля

PIN

INT


Not Null

Имя преподавателя

T
eachN
ame

Varchar

35


Название кафедры

DeptName

Varchar

35


Должность

T
eachP
ost

Varchar

35


Дата приема на работу

DateHire




Таблица

5

Таблица Успеваемость (Progress)

Название

атрибута

Имя поля

Тип поля

Размер

Ограничения

Код вида отчетности

IDReport

INT


Not null, Foreign key

Код предмета

IDSubject

INT


Not null, Foreign key


зачетки

NRecordBook

Varchar

6

Not null, Foreign key


семестра

NTerm

Numeric

2

Not

null

Идентифи
кационный н
о-
мер преподавателя (PIN)

PIN

INT


Foreign key

Оценка

Mark

SmallInt





9

Таблица
6

Таблица Учебный план (
UPlan
)

Название атрибута

Имя поля

Тип поля

Размер

Ограничения

Код предмета

IDSubject

INT


Not null, Foreign key


семестра

NTerm

Varchar

2

Not

null

Код вида занятия

IDWork

INT


Not null, Foreign key

Код группы

IDGroup

INT


Not null, Foreign key

Идентификационный
номер преподавателя

PIN

INT


Not null, Foreign key

Количество часов

Clock

Numeric

3


Таблица
7

Таблица Предмет (Subject)

Название атрибута

Имя поля

Тип поля

Размер

Ограничения

Код предмета

IDSubject

INT


Not null

Название предмета

NameSubject

Varchar

35

Unique

Таблица

8

Таблица Отчетность (Report)

Название атрибута

Имя поля

Тип поля

Размер

Ограничения

Код отчетности

IDReport

INT


Not null

Название вида

отчетн
о
сти

NameReport

Varchar

35

Unique

Таблица
9

Таблица Вид занятий (SWork)

Название атрибута

Имя поля

Тип поля

Ра
змер

Ограничения

Код вида занятий

IDWork

INT


Not null

Название вида занятия

NameWork

Varchar

35

Unique

Таблица
10

Таблица Группа (
SGroup
)

Название атрибута

Имя поля

Тип поля

Размер

Ограничения

Код группы

IDGroup

INT


Not n
ull

Название группы

NameGroup

Varchar

15

Unique


Для определения
SQL

операторов здесь и далее применяется расшире
н-
ная форма
BNF
-
нотации (
Backus

Naur

Form
).


10

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

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

Вертикальная строка (|) указывает на необходимость выбора одного из
нескольких приведенных значений.

Фигурные скобки определяют обязательный элемент


например,
{
SELECT
}.

Угловые скобки (< >) означают, что вместо пара
метра обозначенного в
них, должно проставляться его конкретное значение, при этом угловые ско
б
ки
аннулируются.

Квадратные скобки определяют необязательный элемент [
DISTINCT
].

Многоточие (…) используется для указания необязательной возможн
о
сти
повторения к
онструкции. Наименьшее допустимое количество итераций равно
нулю. Например,
CREATE

TABLE

<имя таблицы>

(<имя столбца> <тип данных> (<размер>) [<ограничение для стол
б
ца>]

[, <имя столбца> <тип данных> (<размер>) [<ограничение для стол
б-
ца>]… ])

[,<ограничен
ие для таблицы>];

Рекомендации по написанию операторов SQL:



Каждая фраза в операторе должна начинаться с новой строки.



Начало каждой фразы должно начинаться с той же позиции, что и н
а
чало
предыдущей фразы.



Желательно, чтобы каждая часть фразы начиналась с
новой строки с н
е-
которым отступом относительно начала фразы. Последнее позволяет ук
а-
зать подчиненность фраз.

С помощью команды
CREATE

создается база данных и все ее объе
к
ты.

CREATE DATABASE Student

ON PRIMARY



(Name=' Student ',



Filename='C:
\
Program F
iles
\
Microsoft SQL Server
\
MSSQL
\
Data
\

St
u-
dent.mdf')

Далее в рамках данной лабораторной работы рассматривается создание
таблиц с помощью команды
CREATE

TABLE
. С ее же помощью устанавлив
а-
ются ограничения на данные, перечисленные ниже.



NULL/NOT NULL


разреша
ет или не разрешает неопределенность зн
а-
чений атрибутов;



UNIQUE


разрешает только уникальные значения атрибутов;


11



PRIMARY KEY


определяет первичный ключ отношения (в каждом о
т-
ношении может иметь место только один первичный ключ);



FOREIGN KEY


определяет
внешний ключ отношения (в одном отнош
е-
нии может быть несколько внешних ключей);



CHECK


задает ограничения на значения атрибутов.

УДАЛЕНИЕ ТАБЛИЦ

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

Синтаксис команды:

DROP

TABLE
<имя таблицы> [,<имя таблицы>]

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

СОЗДАНИЕ ТАБЛИЦ

Синтаксис команды
CREATE

TABLE

для создания таблицы без ограничений:

CREATE

TABLE

[<имя базы данных>.[<имя владельца>| <имя владельца>]

<имя таблицы> (<имя столбца> <тип

данных> (<размер>)) ;

Пример
1

Задача.


Создать таблицу
Subject

1

c
о столбцами
IDSubject
,
NameSubject
.

Решение
.

CREATE TABLE
Subject



(IDSubject INT,



NameSubject

VARCHAR

(35));

Здесь и всегда сообщение об успешном выполнени
и команды
CREATE

имеет вид:
The

command
(
s
)
completed

successfully
.

Пример
2

Задача.


Создать таблицу
Subject

c
о столбцами
IDSubject

(значение которого ген
е-
рируется автоматически) и
NameSubject
.




1

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

TABLE

Subject
.


12

Решение
.

CREATE TABLE
Subject



(IDS
ubject INT IDENTITY,



NameSubject

VARCHAR

(35));


Автоматическая генерация значения столбца IDSubject достигается за
счет использования свойства IDENTITY, по умолчанию начальное значение,
генерируемое с помощью IDENTITY равно 1, так же, как и его приращен
ие.
Таким образом, следующее значение будет равно 2. Значения в IDENTITY
-
столбцах обязательно последовательные, то есть если приращение положител
ь-
ное, то следующее значение всегда больше предыдущего, если приращение о
т-
рицательное, то


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

Пример
3

Задача.


Создать таблицу Subject cо столбцами IDSubject (значение которого ген
е-
рируется автоматически,

начиная со значения 2 с шагом 2) и NameSubject.

Решение
.

CREATE TABLE Subject



(IDSubject INT IDENTITY (2,2),



NameSubject VARCHAR (35));


Не нуждается в доказательстве, что таблица без ограничений не имеет
смысла. Например, в таблице
Subject



IDSubjec
t

является первичным кл
ю
чом,
другой атрибут
NameSubject



потенциальным. В первом случае нео
б
ходимо
наложить ограничение
PRIMARY

KEY
, во втором


UNIQUE
, кроме того, ц
е-
лесообразно наложить ограничение
NOT

NULL

на оба атрибута.



Синтаксис команды CREATE TA
BLE для создания таблицы c ограничениями:

CREATE

TABLE

[<имя базы данных>.[<имя владельца>] <имя табл
и
цы>

(<имя столбца> <тип данных> (<размер>) [<ограничение для стол
б-
ца>]

[,…
n
])

[,<ограничение для таблицы>];


13

Ограничения на уровне таблицы целесообраз
но вводить, если они имеют
отношения к нескольким столбцам. Ограничения для одного столбца рекоме
н-
дуется устанавливать на уровне столбца. Все ограничения, за исключением
о
г
раничения
NULL
/
NOT

NULL
, создаются как на уровне столбца, так и на
уровне таблицы. К
аждому созданному ограничению, за исключением огран
и-
чений
NULL
/
NOT

NULL
, присваивается уникальное имя. Если вы хотите сами
пр
и
своить имя тому или иному ограничению, то необходимо при создании
огран
и
чения воспользоваться предложением CONSTRAINT. Синтаксис п
ре
д-
л
о
жения CONSTRAINT, задающего в команде CREATE TABLE ограничения,
как на уровне таблицы, так и на уровне столбца, приведен н
и
же.

Синтаксис предложения CONSTRAINT:

[CONSTRAINT <имя ограничения>]

[PRIMARY KEY (
имя

столбца
>[, …n]) ]

[UNIQUE (
имя

столбца
>[, …n]) ]


[CHECK (
условие
�)]

[FOREIGN KEY (
имя

столбца
,
ссылающейся

таблицы
>[, …n])

REFERENCES <имя ссылочной таблицы> [(Имя столбца ссылочной

таблицы [, …
n
])]]


Обратите внимание на то, что при задании ограничения на уровне табл
и-
цы перед предло
жением CONSTRAINT должна стоять запятая.

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

ИМЕНА ОГРАНИЧЕНИЙ

Договоримся, назн
ачая ограничение
PRIMARY

KEY
, использовать ша
б-
лон: <имя таблицы><тип ограничения>, поскольку ограничение
PRIMARY

KEY

может быть в таблице только одно. Для ограничений
FOREIGN

KEY

б
у-
дем использовать шаблон


<имя ссылающейся таблицы><имя ссылочной та
б-
лицы><
тип ограничения>. Для ограничения
CHECK

<имя таблицы><имя
столбца><тип ограничения>, а для ограничения UNIQUE <имя таблицы><имя
одного из столбцов><тип ограничения>.

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

!


14

ОГРАНИЧЕНИЯ NULL И N
OT NULL

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

Пример
4

Задача.


Создать таблицу
Subject

c
о столбцами
IDSubject

(значение которого ген
е-
рируется автоматически) и
NameSubject
, исключив возможность неопределе
н-
ных значений
для обоих атрибутов.

Решение
.

CREATE TABLE
Subject



(IDSubject INT IDENTITY NOT NULL,



NameSubject VARCHAR (35) NOT NULL);

ОГРАНИЧЕНИЕ
PRIMARY

KEY

Ограничение PRIMARY KEY задает значение первичного ключа на
уро
в
не столбца или таблицы.

Особенности при с
оздании ограничения PRIMARY KEY:



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



Первичный ключ не может иметь атрибутов с NULL признаком. Одн
а
ко
при задании первичного ключа в команде
CREATE

ограничение
NOT

NULL

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



Одна и та же комбинация столбцов не может быть объявлена одновр
е-
менно в качестве уникального и первичного ключа.

Cинтаксис предложения CONSTRAINT, задающего ограничение
PRIMARY

KEY

на уровне таблицы.


[,СONSTRAINT <имя ограниче
ния>] PRIMARY KEY (<список столбцов>)

Пример
5

Задача.

Создать таблицу Subject, установив ограничение первичного ключа на
уровне столбца IDSubject.


15

Решение
.

CREATE TABLE Subject


(IDSubject INT IDENTITY



CONSTRAINT SubjectPrima
ry PRIMARY KEY,




NameSubject VARCHAR (35) NOT NULL )

В данном случае на атрибут IDSubject можно дополнительно не наклад
ы-
вать ограничение NOT NULL, это произойдет автоматически при налож
е
нии
ограничения PRIMARY KEY.

Пример
6

Задач
а.

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

Решение
.

CREATE TABLE Progress

(NRecordBook VARCHAR(6),


PIN INT,


IDSubject INT,


IDReport INT,


NTerm INT,


Mark SMALLINT


CONSTRAINT ProgressPrimary PRIMA
RY KEY (NRecordBook,


IDSubject, IDReport, NTerm));

C
интаксис предложения
CONSTRAINT
, задающего ограничение
PRIMARY

KEY

на уровне столбца

[
С
ONSTRAINT
имя

ограничения
�] PRIMARY KEY

ОГРАНИЧЕНИЕ
UNIQUE

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

C
интаксис предложения
CONSTRAINT
, задающего ограничение
UNIQUE

на
уровне столбца

[СONSTRAINT <имя ог
раничения>] UNIQUE


16

Пример
7

Задача.

Создать ограничение UNIQUE в таблице Subject для столбца

Name
S
ubject .

Решение.

Ограничение затрагивает только один столбец, и оно может быть зад
а
но
на уровне столбца.

CREATE TABLE Subject


(
IDSubject INT IDENTITY




CONSTRAINT SubjectPrimary PRIMARY KEY,


NameSubject VARCHAR(35) NOT NULL


CONSTRAINT SubjectNameSubjectUnique UNIQUE)
;

В этом случае целесообразно установить ограничение
NOT

NULL

на а
т-
рибут
NameSubject
, так как автоматически
оно не устанавливается.

Синтаксис на уровне таблицы


[, СONSTRAINT <имя ограничения>] UNIQUE (<список столбцов> )

Пример
8

Задача.


Установить ограничения UNIQUE в таблице S
tudent
.

Решение.

При установлении ограничений следует
обратить внимание на следу
ю-
щее: в отношении студент существует несколько множеств атрибутов, которые
могут однозначно идентифицировать любую строку в таблице. Во
-
первых, это
ИНН, во
-
вторых, Номер зачетки, в
-
третьих, так называемые паспортные да
н-
ные, которы
е включают в себя Номер паспорта, Серию паспорта, Наименов
а-
ние организации, выдавшей паспорт и Дату выдачи. В качестве первичного
ключа был выбран Номер зачетки, поскольку именно значение этого атрибута
не меняется в процессе жизни базы данных, по крайней
мере, его значение
о
п
ределяется внутренними требованиями ВУЗа и может быть ими же отрегул
и-
р
о
вано. Вероятность изменения ИНН, как это не грустно, существует, а что к
а-
сается третьего множества, то первой причиной того, чтобы отвергнуть его
использование в ка
честве первичного ключа, является то, что оно составное, во
-
вторых, вероятность того, что его значения будут меняться в процессе жизни
базы данных, очень велика. Для этого студентке нужно просто выйти замуж и
сменить фамилию, или кому
-
то из студентов потер
ять паспорт. Таким образом,

17

создавая таблицу
Student
, целесообразно кроме ограничения
PRIMARY

KEY

создать два ограничения
UNIQUE
.

CREATE TABLE Student


(NRecordBook VARCHAR (6)


CONSTRAINT StudentPrimary PRIMARY KEY,


INN VARCHAR(10)



CONSTRAINT StudentINNUnique UNIQUE,


SName VARCHAR(35),


IDGroup INT NOT NULL,


SPasport Varchar(4)NOT NULL,


NPasport Varchar(6)NOT NULL,


DataPasport DateTime NOT NULL,


NameDeptPasport Varchar(35)NOT NULL,



CONSTRAINT Studen
tSPasportUnique

UNIQUE (SPasport, NPasport, DataPasport, NameDeptPasport));

Обратим внимание, что атрибут
INN
, имеющий ограничение
UNIQUE
, не
имеет ограничения
NOT

NULL
.

Задание
1

Создать таблицы Report, Teacher, SGroup, установив все необходимые
о
г
раничения.

ОГРАНИЧЕНИЕ
FOREIGN

KEY

Синтаксис на уровне т
аблицы


[, СONSTRAINT <имя ограничения>]
FOREIGN

KEY

(<список столбцов> )

REFERENCES

<имя ссылочной таблицы> [(<список столбцов> )]

[ON DELETE {CASCADE|NO ACTION}]

[ON UPDATE {CASCADE|NO ACTION}]

Ограничение FOREIGN KEY устанавливает внешний ключ и органи
з
у
ет
ссылку по внешнему ключу на заданное в предложении
REFERENCES

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


18

Пример
9

Задача.

Назнач
ить в таблице Успеваемость (Progress) внешний ключ и организ
о-
вать ссылку по внешнему ключу на все ссылочные таблицы.

Решение.

Ссылочными по отношению к отношению
Progress

являются таблицы
Student
,
Teacher
,
Subject
,
Report
, следовательно, в таблице будет че
тыре вне
ш-
них ключа.

CREATE TABLE Progress


(NRecordBook
VARCHAR
(6),


PIN INT,


IDSubject INT,


IDReport INT,


NTerm
VARCHAR
(2),


Mark SMALLINT,



CONSTRAINT ProgressPrimary PRIMARY KEY


(NrecordBook,IDSubject,IDReport,NTerm),

CONSTRAINT Progr
essStudentForeign FOREIGN KEY

(NRecor
d-
Book) REFERENCES Student,



CONSTRAINT ProgressSubjectForeign FOREIGN KEY

(IDSu
b
ject)
REFERENCES Subject,




CONSTRAINT ProgressReportForeign FOREIGN KEY (IDR
e
port)
REFERENCES Report,



CONSTRAINT Progre
ssTeacherForeign FOREIGN KEY
(PIN)
REFERENCES Teacher )

Внешние ключи являются по своей сути двунаправленными. По умолч
а-
нию
SQL

Server

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

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

UPDATE

CASCADE

позволяет при мо
дификации строки в родительской таблице модифицировать
соответствующие строки в дочерних таблицах. По умолчанию устанавливается
опция
NO

ACTION
, запрещающая в одном случае удаление, в другом


реда
к-
тирование.


19

Синтаксис на уровне столбца


[
С
ONSTRAINT
имя

ограничения
�] FOREIGN KEY

REFERENCES

<имя ссылочной таблицы> [(<имя столбца> )]

[ON DELETE {CASCADE|NO ACTION}]

[ON UPDATE {CASCADE|NO ACTION}]

Пример
10

Задача.

Обеспечить каскадное удаление в таблице Progress при удалении соот
ве
т-
ствующих записей в ссылочных таблицах.

Решение
.

CREATE TABLE Progress


(NRecordBook
VARCHAR
(6),


PIN INT,


IDSubject INT,


IDReport INT,


NTerm
VARCHAR
(2),


Mark SMALLINT,



CONSTRAINT ProgressPrimary PRIMARY KEY


(NrecordBook,IDSubject,ID
Report,NTerm),

CONSTRAINT ProgressStudentForeign FOREIGN KEY

(NRecor
d-
Book) REFERENCES Student




CONSTRAINT ProgressSubjectForeign FOREIGN KEY

(IDSu
b-
ject) REFERENCES Subject





CONSTRAINT Progress
ReportForeign FOREIGN KEY

(IDR
e-
port) REFERENCES Report




CONSTRAINT ProgressTeacherForeign FOREIGN KEY
(PIN) REFERENCES Teacher


Обратим внимание на тот факт, что опция
ON


CASCADE

ук
а-
зывается для каждого ограничения
FOREIGN

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

ОГРАНИЧЕНИЕ
CHECK

С помощью о
граничения CHECK задаются ограничения на значения а
т-
рибутов.


20

Cинтаксис ограничения CHECK на уровне столбца

[СONSTRAINT <имя ограничения>] CHECK (<условие>)

Пример
11

Задача.

Создать таблицу Progress, назначить ограничения
PRIMARY

KEY

и з
а-
претить ввод в таблицу оценок, отличных от 2,3,4,5, т.е. создать ограничение
для значений столбца
Mark

в таблице
Progress
.

Решение
.

CREATE TABLE Progress


(NRecordBook
VARCHAR
(6)


CONSTRAINT ProgressStudentForeign FOREIGN KEY


REFERENCE
S Student,


PIN INT,


IDSubject INT,


IDReport INT,


NTerm
VARCHAR
(2),


Mark SMALLINT


CONSTRAINT ProgressMarkCheck CHECK(Mark BETWEEN 2
AND 5 ),


CONSTRAINT ProgressPrimary PRIMARY KEY


(NrecordBook,IDSubject,IDReport,Nterm),



CONSTRAINT ProgressSubjectForeign FOREIGN KEY (IDSu
b-
ject) REFERENCES Subject ON DELETE CASCADE,




CONSTRAINT ProgressReportForeign FOREIGN KEY


(IDReport) REFERENCES Report ON DELETE CASCADE,




CONSTRAINT

ProgressTeacherForeign FOREIGN KEY


(PIN) REFERENCES Teacher ON DELETE CASCADE )

Команда
CHECK

также не может ссылаться на значения столбцов в др
у-
гих строках.

Задание
2

Удалить все созданные таб
лицы. Повторить создание учебного прим
е
ра
2

согласно описаниям, приведенным выше.




2

Чтобы избежать корректировки таблиц, целесообразно предварительно пр
о
думать
порядок создания таблиц: сначала создаются родительские (те, которые не содержат
внешних ключей), затем дочерние таблицы (Progress и UPlan и т.д.).


21

Если таблицы созданы и связаны правильно, то на диаграмме, созда
н
ной
в
Enterprise

Manager
, это будет выглядеть так, как показано на следующем р
и-
сунке (см.
Рисунок
1
).



Рисунок
1

ВОПРОСЫ ДЛЯ САМОКОНТ
РОЛЯ К ЛАБОРАТОРНОЙ
РАБОТЕ № 1

1.

К какой группе команд
SQL

следует отнести команду
CREATE
?

2.

Пусть даны два отношения R=

AB


и R=

A
C

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









22

3.

С какой целью устанавливаются ограничения?

4.

Какие ограничения могут быть установлены в отношении?

5.

С какой целью используется предложение
ON

DELETE

CASCADE
?

6.

С какой

целью используется предложение
ON

UPDATE

CASCADE
?

7.

Какова область действия ограничений в таблице?

8.

Какие требования предъявляются к именам объектов базы данных?

9.

Когда целесообразно устанавливать ограничения на уровне таблицы?

10.

Когда целесообразно устанавлива
ть ограничения на уровне столбца?

11.

В чем отличие ограничений
PRIMARY

KEY

и
UNIQUE
?

12.

Каких правил следует придерживаться при назначении имен огранич
е
ний?

13.

При каком определении ограничения
FOREIGN

KEY

строка в ссылочной
таблице не может быть удалена?

14.


На какие

столбцы запрещена ссылка в выражении ограничения
CHECK
?

15.


Для каких столбцов обязательно должно быть установлено ограничение
NOT

NULL
?

16.

При создании какого из ограничений автоматически накладывается огран
и-
чение
NOT

NULL

на атрибуты этого ограничения?

17.

Для ка
ких типов данных ширина столбца не является обязательным пар
а-
метром?

18.

Какой тип данных может быть присвоен только единственному столбцу в
таблице?






23

ЛАБОРАТОРНАЯ РАБОТА
№ 2

Цель занятия
: Изучение синтаксиса команд языка манипулирования данн
ы
ми
(Data Mani
pulation Language
-

DML).

Результат занятия:

Заполнение базы данных ©Успеваемостьª корректными
данными.

Три хорошо известные операции над кортежами:



Добавление



Правка



Удаление

реализуются в SQL с помощью команд



INSERT



UPDATE



DELETE

КОМАНДА

ВСТАВКИ

-

INSERT

Команда языка DML
-

INSERT используется для ввода новых строк в
таблицу.

Синтаксис команды:

INSERT INTO <имя таблицы>[(<имя столбца> [псевдоним] [, …
n
]]
|[<подзапрос>]

VALUES (<значение>[,…
n
]);

При реализации команды INSERT необходимо отслеживать, что
бы



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



Заполнялись все столбцы с признаком
NOT

NULL
.

Пример
12

Задача.

Ввести в таблицу S
Group

значения названия групп ИСТ
-
03 и АИС
-
03.

Решен
ие
.

INSERT INTO SGroup (NameGroup)

VALUES('
ИС
T
-
03');

INSERT INTO SGroup (NameGroup)

VALUES('
АИС
-
03');


24

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

1 row(s) affected

Примечание.

Обратим внимание на то, что поле
IDGroup

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

INSERT INTO SGroup (IDGroup, NameGroup)

VALUES(3,'
ИС
T
-
02');

Приведет к сообщению об ошибке.

Server: Msg 544, Level 16, State 1, Line 1

Cannot insert explicit value for i
dentity column in table 'SGroup' when
IDENTITY_INSERT is set to OFF.

Однако если вы все
-
таки хотите ввести код группы вручную, вам необх
о-
димо отключить действие
IDENTITY

с помощью команды
SET

IDENTITY
_
INSERT
.

Синтаксис

команды
:

SET IDENTITY_INSERT
имя

таблицы
� { ON | OFF }

Опция
ON

отключает процесс автоматического присвоения
IDENTITY
-
значений,
OFF

-

включает.

Пример
13

Задача.

Ввести в таблицу S
Group

значения названия групп ИСТ
-
02, присвоив
столбцу
IDGroup

значение 3.

Решени
е
.

SET IDENTITY_INSERT SGroup ON
3

INSERT INTO SGroup (IDGroup, NameGroup)

VALUES(3,'
ИС
T
-
02');

Несмотря на то, что в команде INSERT список столбцов является необ
я-
зательным, его рекомендуется указывать явно. Последнее позволяет и
з
бежать
недиагностируемых оши
бок при заполнении столбцов. Если вы все
-
таки хотите



3

Пос
ле такой принудительной вставки целесообразно снова выполнить команду
SET

IDENTITY
_
INSERT
, но уже с опцией
OFF
.




25

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

Пример
14

Задача.

Ввести дан
ные о студенте Иванове в таблицу Student (см.
Приложение
2
.
Пример заполнения таблиц
).

Решение
.

INSERT INTO Student

VALUES('050001','1111111111','
Иванов

И
.
И
.',3,'8701','192355','01.06.2002',
'
УВД

г
.
Ухты
');

Примеча
ние.

Выполнение операции ввода так, как это описано в предыдущем пр
и
мере
(см.
Пример
14
), может привести к некорректному заполнению базы данных
или сообщению об ошибке.

Например, попытка ввода этой же строки с помо
щью приведенной н
и
же
команды

INSERT INTO Student

VALUES('050001','Иванов И.И.',3,'8701','192355','01.06.2002','ГОВД
г.Ухты','1111111111')

даст сообщение об ошибке:

Syntax error converting datetime from character string

Ошибка вызвана несоответствием типо
в данных, причиной же ее посл
у-
жило нарушение порядка столбцов в списке
VALUES
. (Надо отметить, что п
о-
явление сообщения об ошибке является наилучшим исходом. Дела о
б
стояли бы
хуже, если бы не возникло конфликта, вызванного несоответствием типа да
н-
ных или ра
змера данных. В этом случае результатом стала бы некорректно з
а-
полненная таблица.) Избежать ошибки можно, перечислив все столбцы после
имени таблицы в том же порядке, в котором они следуют в предложении
VALUES
.

INSERT INTO Student (NRecor
d-
Book,SName,IDGro
up,SPasport,NPasport,DataPasport,

NameDeptPasport,INN)

VALUES('050002', '
Петров

П
.
П
',3,'8702','191256','11.20.2002',
'
УВД

г
.
Сосногорск
', '1111111112')

Этот вариант более надежный, кроме того, он позволяет вводить не все
данные. Например,

если столбец имеет признак
NULL
, то его значения м
о
гут

26

не вводиться
-

в следующем примере не вводится значение атрибута
INN
,
имеющего признак
NULL
.

INSERT INTO Student (NRecor
d-
Book,SName,IDGroup,SPasport,NPasport,DataPasport,

NameDeptPasport)

VALUES('0500
03','Сидоров С.С.', 2,'8703','192457','11.26.2002',
'УВД г.Ухты')

Данные в таблицу можно внести и из других таблиц, используя запрос
SELECT
. Реакцией системы, как и в предыдущем случае, при успешном выпо
л-
нении команды будет фраза
N row
affect
ed

(создано
N

строк), где в кач
е
стве N
указывается количество созданных строк.

Задание
3

Создать таблицу Student1, аналогичную таблице
Student

и заполнить ее
данными из таблицы (см.
Приложение
2
. Пример заполнения таблиц
).

Пример
15

Задача.


Ввести в таблицу Student записи из таблицы Student1.

Решение
.

INSERT INTO Student


SELECT *



FROM Student1;

Задание
4

Заполнить таблицу Subject в

соответствии с таблицей (см.)

КОМАНДА ОБНОВЛЕНИЯ
-

UPDATE

Команда
UPDATE

предназначена для редактирования данных в табл
и
це.

Синтаксис команды:

UPDATE <имя таблицы>

[SET (<имя столбца>)] = <выражение> [,…
n
]|<подзапрос>]

WHERE <условие>;

В случае успеш
ного выполнения команды выдается сообщение
(
N

row(s) affected
) (
N

записей задействовано), где N
-

количество редакт
и
р
у-
емых зап
и
сей.


27

Пример
16

Задача.

В отношении
Subject

заменить название дисциплин в соответствии с пр
и-
веденной ниж
е таблицей (см.
Таблица
11
).

Таблица
11

Заменяемое значение

Новое значение

УД

Управление данными

БД

Базы данных

УД

Управление данными

Решение.

UPDATE Subject


SET NameSubject='Управле
ние данными'


WHERE NameSubject='УД';

UPDATE Subject


SET NameSubject='Базы данных'


WHERE NameSubject='БД';

UPDATE Subject


SET NameSubject='
Системы

управления

базами

данных
'


WHERE NameSubject='
СУБД
';

Выполнение следующего запроса позволит вывести н
а экран содерж
а
ние
всей таблицы и проверить результат предыдущей операции.

SELECT * FROM Subject;

Результат выполнения команды UPDATE.


Задание
5

Восстановить данные в таблице Subject (см.)

Если предложение WHERE не задано, то ис
правления вносятся во все
строки таблицы. Будьте внимательны при выполнении команд DELETE,
UPDATE.


28

КОМАНДА УДАЛЕНИЯ
-

DELETE

Синтаксис команды:

DELETE FROM{
имя

таблицы


WHERE <условие>;

Команда DELETE удаляет записи в таблице. В случае успешного выпо
л-
не
ния команды выдается сообщение:
N

row(s) affected

(
N

записей задействов
а-
но), где N
-

количество удаленных записей. Еще раз следует напомнить, что,
как и в случае с командой UPDATE, не следует забывать про предложение
WHERE, в противном случае вы удалите вс
е содержимое та
б
лицы.

Пример
17

Задача.


Удалить запись, в которой значение атрибута имя студента (SName)
Митькин.

Решение
.

DELETE FROM Student



WHERE SName='
Митькин

М
.
М
.';

Пример
18

ВНИМАНИЕ! Если вы го
товы удалить все содержимое вашей табл
и
цы,
то можете опробовать следующую команду.

Задача
.

Удалить все записи из таблицы Student.

Решение
.

DELETE FROM Student;

Следует указать, что существует еще одна команда, позволяющая уд
а-
лить записи из таблицы, это ком
анда
TRUNCATE
. Отличие команды DELETE
от команды TRUNCATE состоит в том, что после ее выполнения действие этой
команды можно отменить посредством команды ROLLBACK
4
.

КОМАНДА УДАЛЕНИЯ
-

TRUNCATE

Синтаксис команды:

TRUNCATE
<имя таблицы> ;

Команда позволяет

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



4

Подробно о выполнении транзакции смотрите в пособии ТРАНЗАКЦИИ.


29

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

счетчик сбрасывается в исходное состо
я
ние.

Задание
6



Удалить данные из таблицы
SWork

командой

и восстан
о
вить их
вновь.


Удалить данные из таблицы
SWork

командой
TRUNCATE

и восстан
о
вить
их вновь.

За
дание
7

Удалить данные из ранее созданных таблиц и заполнить таблицы в соо
т-
ветствии с приложением (см.
Приложение
2
. Пример заполнения таблиц
).

Задание
8

Внести данные в таблицу

USPEV

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

17 недель. Пола
гая
также, что студенты специальности АИС изучают дисциплину Базы данных, а
ИСТ


Управление данными и СУБД. Высшую математику изуч
а
ют все.

ВОПРОСЫ ДЛЯ САМОКОНТ
РОЛЯ К ЛАБОРАТОРНОЙ
РАБОТЕ № 2

1.

Как называется язык, к которому относятся команды
INSERT
,
UPDATE
,
DELETE
?

2.

Какие ошибки могут иметь место в случаи использования краткого синта
к-
сиса команды INSERT?

3.

Чему будет равна мощность таблицы после выполнения команды

без использования предложения
WHERE
?

4.

Какие предложения являются обязательными в команде
IN
SERT
?

5.

Какие предложения являются обязательными в команде DELETE?

6.

Какие предложения являются обязательными в команде UPDATE?

7.

Как удалить из таблицы повторяющиеся строки?

8.

При каком синтаксисе команды
UPDATE

корректируются значения во всех
записях отношения?

9.

В чем отличие команд

и
TRUNCATE
?


30

ЛАБОРАТОРНАЯ РАБОТА
№3

Цель занятия
: Изучить команды
DDL
:
ALTER

TABLE
,
DROP

TABLE
.

Результат занятия:

Модификация базы данных ©Успеваемостьª, назнач
е
ние
ограничений, позволяющих поддерживать целостность базы данных

деклар
а-
тивным способом.

КОМАНДА ALTER TABLE

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

TABLE
.

Команда ALTER TABLE позволяет



Добавлять и удалять столбцы.



Добавлять и изменять описания столбцов.



Добавлять, удалять, отключать ограничения.

При успешном завершении команды выдается сообщение
The

com
mand
(
s
)
completed

successfully
. (Команда выполнена успешно).

МОДИФИКАЦИЯ ОГРАНИЧЕ
НИЙ

Синтаксис команды:

ALTER TABLE
имя

таблицы


ALTER| ADD| DROP {CONSTRAINT
имя

ограничения
�}

FOREIGN KEY [REFERENCES
имя

таблицы
� (
имя

столбца
> [,…n])] |
PRIMARY KEY | UN
IQUE | CHECK (
имя

столбца
> [,…n])

[ON DELETE CASCADE]

Если необходимо наложить дополнительное ограничение на значение а
т-
рибута, то следует использовать команду ALTER совместно с опцией ADD, к
о-
торая позволяет добавлять столбец или новое ограничение в таб
л
и
цу, в свою
очередь опция
DROP

даст возможность удалить то или иное ограничение,
н
а
ложенное на данные в таблице. Если ограничение необходимо исправить
(
FOREIGN

KEY
,
PRIMARY

KEY
,
UNIQUE
,
CHECK
), то его сн
а
чала удаляют, а
затем создают снова. Для того чтобы

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

EXEC SP_HELP
имя

таблицы



31

Пример
19

Задача.

Ввести ограничение на столбец
DataPasport
, полагая, что

значение эт
о
го
атрибута должно превышать значение 01.01.2000.

Решение
.

ALTER TABLE Student


ADD

CONSTRAINT StudentYearBeginCheck


CHECK
(DataPasport�'01.01.2000');

Примечание.

При попытке вставить следующую строку

INSERT INTO Student (NRe
cor
d-
Book,SName,IDGroup,SPasport,NPasport,DataPasport,

NameDeptPasport,INN)

VALUES('050004','Митькин М.М.', 2,'8701','192417','11.26.1999',
'УВД г.Ухты','1111111114')

система генерирует следующее сообщение об ошибке:

Server: Msg 547.

INSERT statem
ent conflicted with COLUMN CHECK constraint 'StudentYearB
e
gi
n-
Check'. The conflict occurred in database 'Student', table 'Student', column
'DataPasport'.

The statement has been terminated.

При запуске же команды:

INSERT INTO Student (NRecor
d-
Book,SName,IDGro
up,SPasport,NPasport,DataPasport,

NameDeptPasport,INN)

VALUES('050004','Митькин М.М.', 2,'8701','192417','11.26.2000','УВД г.Ухты',
'1111111114')

система генерирует сообщение:

(1
row
(
s
)
affected
)

Пример
20

Задача.

Ввести ограничен
ия FOREIGN KEY для таблицы
Uplan
. Таблица
Uplan

ссылается на таблицу
S
ubject

по атрибуту
IDSu
bject.

Решение
.

ALTER TABLE UPlan


32

ADD CONSTRAINT PlanSubjectForeign FOREIGN KEY

(IDSu
b-
ject)

REFERENCES Subject(IDSubject);

Обратите внимание
, что когда ограничение FOREIGN KEY задается т
а-
ким образом, что ни одна строка в отношении S
ubject

не может быть уд
а
лена,
до тех пор пока в отношении
U
P
lan

есть строки, ссылающиеся на уд
а
ляемый
предмет. Как избежать этой коллизии смотрите в следующем пр
и
ме
ре
(см.

Пример
21
).

Пример
21

Задача.

Ввести ограничения FOREIGN KEY для таблицы
UPlan

таким образом,
чтобы при удалении из таблицы S
ubject

записей по тому или иному предм
е
ту,
были бы удал
ены и соответствующие этим предметам записи из таблицы
UPlan
.

Решение
.

ALTER TABLE UPlan

ADD CONSTRAINT PlanSubjectForeign

FOREIGN KEY (IDSubject) REFERENCES Subject(IDSubject)


ON DELETE CASCADE;

Проблему каскадного удаления
соответствующих строк в ссылающе
й
ся
таблице (
Progress
) при удалении строк в ссылочной таблице (
Subject
) р
е
шает
опция ON DELETE CASCADE. Для того чтобы удалить ограничение, необх
о-
димо указать его имя.

Пример
22

Задача.

Удалить огра
ничение FOREIGN KEY
PlanSubjectForeign

на атрибуте

I
D
Subject

в таблице
Uplan
.

Решение.

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

sp
_
help

UPLAN
.

ALTER TABLE UPlan


DROP

CONSTRAINT

PlanSubjectForeign

;

!


33

ДОБАВЛЕНИЕ ОГРАНИЧЕН
ИЙ С ОГРАНИЧЕННОЙ ОБ
ЛАСТЬЮ
ПРОВЕРКИ

Синтаксис команды:

ALTER TABLE <имя таблицы>

[WITH

CHECK| WITH NOCHECK]

ADD

{
CONSTRAINT

<имя ограничения>

FOREIGN

KEY

[
REFERENCES
<имя таблицы> (<имя столбца> [,…
n
])] |
CHECK

(<имя столбца> [,…
n
])}

[ON DELETE CASCADE]

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

KEY

и
UNIQUE
.

Таким образом, если мы вводим новое ограничение и хотим, чтобы SQL
Server 2000 проверил все существующие данные на соответствие этому огран
и-
чению, следует добавить это ограничение с опцией WITH CHECK, в проти
в
ном
случае
-

с опцией WIT
H NOCHECK

Временное отключение ограничений и использование опции
WITH

NOCHECK

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

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

Пример
23

Задача.

Добавить ограничение FOREIGN KEY для столбца IDGroup в таблице
Student
5
.

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

Решение
.

ALTER TABLE Student

WITH CHECK

ADD CONSTRAINT StudentIDGroupForeign

FOREIGN KEY (IDGroup) REFERENCES

SGroup
(
IDGroup
)




5

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


34

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

Server: Msg 547 ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
constraint 'StudentIDGroupForeign'. The conflict occurred in database 'Student',

t
a
ble 'SGroup', column 'IDGroup'.

Пример
24

Задача.

Добавить ограничение FOREIGN KEY для столбца IDGroup в таблице
Student. Все данные в таблице Student, введенные на момент создания огран
и-
чения, проверке не подлежат.

Решение
.

ALTE
R TABLE Student

WITH NOCHECK

ADD CONSTRAINT StudentIDGroupForeign

FOREIGN KEY (IDGroup) REFERENCES

SGroup
(
IDGroup
)

В этом случае независимо от содержания таблиц результат будет сл
е
д
у-
ющим.

The command(s) completed successfully.

Задание
9

Ввести ограничение на оценку в отношении Успеваемость. Оценка не
должна превышать 5 баллов. Номер семестра не должен превышать 10.

Задание
10

Создать внешние ключи во всех таблицах, используя опцию
FOREIGN
KEY
, при этом ус
тановить опцию каскадного удаления там, где это необх
о
димо.

Задание
11

Удалить первичный ключ в отношении Student.

Задание
12

Проследить за изменением ограничения
FOREIGN KEY

в отношениях,
связанных с отн
ошением Student. Еще раз восстановите все удаленные огр
а-
н
и
чения.


35

ОТКЛЮЧЕНИЕ И ПОДКЛЮЧ
ЕНИЕ ОГРАНИЧЕНИЙ

Отключить можно как отдельное ограничение, указав его имя, так и все,
используя опцию ALL

Синтаксис команды:

ALTER TABLE
имя

таблицы


[CHECK| NOCHECK]

{C
ONSTRAINT
имя

ограничения
�| ALL}

FOREIGN KEY [REFERENCES
имя

таблицы
� (
имя

столбца
> [,…n])] |
CHECK (
имя

столбца
> [,…n])

Пример
25

Задача.

Отключить все ограничения в таблице
Progress
.

Решение
.

ALTER TABLE Progress

NOCHECK CO
NSTRAINT ALL

Пример

26

Задача.

Подключить все ограничения в таблице
Progress
.

Решение
.

ALTER TABLE Progress

CHECK CONSTRAINT ALL

Пример

27

Задача.

Отключить ограничение в ProgressMarkCheсk для столбца
M
ark

табл
и
цы
Progress
.

Решение
.

ALTER TABLE Progress

NOCHECK

CONSTRAINT

ProgressMarkChe
с
k

После выполнения последней команды вы сможете ввести, например, 1
(единицу) в столбец Mark. Если Вы не намерены проверять ранее введенные
данные, то синтаксис команды

должен быть аналогичным синтаксису следу
ю-
щего примера (см.
Прим
ер
28
), в противном случае синтаксис команды должен
быть аналогичен следующему примеру (см.
Пример

29
)


36

Прим
ер
28

Задача.

Подключить ограничение в ProgressMarkChe
c
k для столбца
Mark

та
б
лицы
Progress
.

Решение
.

ALTER TABLE Progress

С
HECK CONSTRAINT ProgressMarkCheck

Пример

29

Задача.

Подключить ограничение в
Prog
ressMarkCheck

для столбца
Mark

та
б
лицы
Progress

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

Решение
.

ALTER TABLE Progress


WITH CHECK


CHECK CONSTRAINT ProgressMarkCheck

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

Задание
13

Отключите ограничения внешнего ключа в таблице
Student
. Введите в
таблицу
Student

студента Васькина В.В. из несуществ
ующей группы. Попыта
й-
тесь подключить ранее отключенное ограничение.

Выполните все необходимые действия для того, чтобы вновь подкл
ю
чить
ограничение, а все данные в отношении
Student

соответствовали условиям ц
е-
лостности базы данных.

Задание
14

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

ПРАВИЛА ДЛЯ ИЗМЕНЕНИ
Я И МОДИФИКАЦ
ИИ ОПИСАНИЯ СТОЛБЦОВ

При корректировке таблиц нельзя:




добавлять новый столбец с опцией NOT NULL;




добавлять к столбцу опцию NOT NULL, если в нем есть пустые значения;


37




уменьшить размер поля или изменить его тип, если в нем содержатся к
а
кие
-
либо данные;



уд
алить столбец из таблицы, если на этот столбец были установлены к
а
кие
-
либо ограничения кроме NOT NULL| NULL.

ДОБАВЛЕНИЕ СТОЛБЦА

Синтаксис команды:

ALTER TABLE
имя

таблицы


ADD

<имя столбца> <тип данных> <ширина столбца>

[
DEFAULT

<значение>] [,…
n
];

где
DEF
AULT



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

При добавлении столбца он автоматически становится последним в та
б-
лице. Изменить положение столбца в таблице не представляется возмо
ж
ным.

Пример
30

Задача.

Добавить столбец Year
B
egi
n (год начала учебы в институте) в таблицу
Student, задав тип данных
Datetime
.

Решение
.

ALTER TABLE Student

ADD YearBegin

Datetime;

Пример
31

Задача.

Добавить столбец
IDFacultet

(Код факультета) в таблицу S
Group
, задав
тип данных
INT

и определив значение по умолчанию
1.

Решение
.

ALTER TABLE SGroup

DEFAULT

1;

Сразу обратим внимание, что
DEFAULT

реализовано в
SQL

Server

2000
как ограничение и поэтому при дальнейшей попытке удалить этот столбец,
н
е
обходимо будет с
начала удалить ограничение, а затем уже уд
а
лить столбец.

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

Если в таблице уже есть данные, то добавление столбца осуществляе
т
ся
или в 3 э
тапа (см.
Пример
32
):


38



добавляется столбец, допускающий неопределенность значений



столбец заполняется



столбцу присваивается свойство NOT NULL.

Или в один этап, но задав ему значение по умолчанию или определив его
к
ак столбец
IDENTITY
.

Если в таблице нет данных, то добавление столбца осуществляется в

2 этапа:



добавляется столбец, допускающий неопределенность значений



столбцу присваивается свойство NOT NULL.

Пример
32

Задача.

Добавить в табл
ицу
SGroup
, содержащую данные, столбец
,
у
с
тановив для него ограничение
NOT

NULL
.

Решение.

Добавляем столбец
:

ALTER TABLE SGroup


Заполняем столбец
IDFacultet
, присвоив ему значение 1:

UPDATE SGroup SET IDFacultet
=1

Назначаем ограничение
NOT

NULL
:

ALTER TABLE SGroup


МОДИФИКАЦИЯ СТОЛБЦА

Синтаксис команды:

ALTER TABLE
имя

таблицы


ALTER

COLUMN

<имя столбца> <новый тип данных> <длина>

[
DEFAULT

<значение>]

[
NULL
|
NOT

NULL
] [,…
n
];

S
QL

Server

не разрешает изменять столбцы типа
TEXT
,
NTEXT
,
IMAGE
,
ROWVERSION
, вычисляемые столбцы, столбцы, используемые в репликации,
и столбцы, на которые имеются ссылки в выражениях вычи
с
ляемых столбцов
или ограничений, а также столбцы с установленным св
о
й
ством

39

ROWGUIDCOL
6
. Нельзя удалить или изменить столбец, имеющий значение по
умолчанию (ограничение
DEFAULT
). Однако можно увеличить размер стол
б-
цов переменой длины, которые используются в индексах, в о
г
раничениях
CHECK

или
UNIQUE
.

Пример
33

Задача.

Увеличить ширину столбца
NameGroup
, увеличив ее до 20 символов.

Решение
.

ALTER TABLE SGroup

ALTER COLUMN NameGroup VARCHAR(20)

The command(s) completed successfully.

Если теперь сделать попытку вернуться к предыдущему размеру столбца,
то она будет неудачной, поскольку столбец имеет ограничение
UNIQUE
.

Пример
34

Задача.

Уменьшить размер столбца
NameGroup

до 15 символов (предполагае
т
ся,
что ранее она была увеличена до 20 символов).

Решение
.

ALTER TABLE SGroup

AL
TER COLUMN NameGroup VARCHAR(15)

На этот раз будет диагностирована ошибка.

Server: Msg 5074, The object 'SgroupNameGroupUnique' is dependent on co
l-
umn 'NameGroup'.

Server: Msg 4922, ALTER TABLE ALTER COLUMN NameGroup failed b
e-
cause one or more objects acce
ss this column.

Если столбец все
-
таки необходимо изменить, то сначала следует уд
а
лить
ограничение SgroupNameGroupUnique,

ALTER TABLE SGroup


DROP CONSTRAINT SgroupNameGroupUnique

Затем уменьшить ширину столбца.




6

ROWGUIDCOL



при определении в таблице столбца с этим свойством сервер авт
о-
матически использует для него ограничение по умолчанию, являющееся функц
и
ей
NEWID
(). Функция
NEWID
() генерирует новое

значение глобального уникал
ь
ного
идентиф
икатора (
GUID
), значение которого уникально в пределах планеты, и по
д-
ставляет его в добавляемую строку.


40

ALTER TABLE SGroup

ALTER COLUMN NameGroup VA
RCHAR(15)

(
2 row(s) affected)

Задание
15

Добавить в таблицу Student столбец Single, тип данных VARCHAR(3),
н
а
значив значение по умолчанию “Да”. Удалить столбец.

Задание
16

Добавить в таблицу Student столб
ец AVGMARK, тип Numeric (5,2). В столбце
будет храниться средняя оценка студента. Мы оставим этот столбец в базе данных
лишь для того, чтобы в дальнейшем продемонстрировать с помощью него работу н
е-
которых команд и процедур, написание которых как раз и буде
т обусловлено нал
и-
чием этого избыточного столбца. Отсюда вывод
-

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

Задание
17

Изменить длины полей
в соответствии с таблицей (см.
Таблица
12
). Выпо
л-
нить анализ
-

почему не удалось выполнить заданные операции с некоторыми
столбцами? Что необходимо предпринять, чтобы эти изменения всё же произв
е
сти?

Таблица
12

Имя поля

Тип поля

Размер

Ограничения

IDReport

Varchar

4


NameWork

Varchar

4


NameSubject

Varchar

4


DateHire

Smalldatetime



Mark

Numeric

2

NULL

DeptName

Varchar

4

NULL

NRecordBook

Varchar

6


NTerm

Numeric

2


NameReport

Va
rchar

35


NameSubject

Varchar

35


PIN

Varchar


4


TeachPost

Varchar

25

NULL

Clock

Numeric

5.2


SName

Varchar

35


TeachName

Varchar

35



41

УДАЛЕНИЕ СТОЛБЦА

Синтаксис команды:

ALTER TABLE
имя

таблицы


DROP COLOMN
имя

столбца


Ранее уже отмечалось, что

нельзя удалить столбец, если на него нал
о
жено
хотя бы одно ограничение за исключением
NULL
. Кроме этого следует отм
е-
тить, что нельзя удалить



Реплицированные столбцы.



Индексированные столбцы.



Столбцы, для которых определено правило (
rule
).

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

Пример
35

Задача.

Удалить столбец
Year
B
egin


Решение
.

ALTER TABLE

Student



DROP

COLUMN

YearBegin

УДАЛЕНИЕ ТАБЛИЦЫ ПРИ

НАЛИЧИИ НА НЕЕ ССЫЛО
К

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

Синтаксис команды:

DROP TABLE<имя таблицы>[, <имя таблицы>]

Пример
36

Задача.

Удалить таблицу Subject.


42

Решение.

На таблицу Subject ссылается как таблица Progress, так и таблица
U
Plan.
В связи с этим удалению
таблицы Subject должно предшествовать удаление
о
г
раничения ProgressSubjectForeign из таблицы Progress и огран
и
чения P
lan

SubjectForeign из таблицы
UPlan
.

ALTER TABLE Progress

DROP CONSTRAINT ProgressSubjectForeign


ALTER TABLE UPlan

DROP CONSTRAINT PlanSu
bjectForeign

И
,
наконец
,

DROP TABLE Subject

ПЕРЕИМЕНОВАНИЕ ТАБЛИ
ЦЫ

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

Синтаксис команды:

EXEC SP_RENAME 'старое имя',' новое имя' ;

Пример
37

Задача.

Переименовать таблицу S
ubject

в Su
bject05 и восстановить первоначал
ь-
ное имя таблицы.

Решение
.

EXEC SP_RENAME 'Subject','Subject05'
;

В ответ на выполнение этой команды система генерирует сообщение, где
предупреждает о возможных последствиях изменения имени:


Caution: Changing any part of a
n object name could break scripts and stored pr
o-
cedures. The object was renamed to 'Subject
0
5'.

EXEC SP_RENAME 'Subject0
5
','Subject'
;

Задание
18

Переименовать таблицу P
rogress

в таблицу P
rogress
1.


43

Задание
19

Восстановить прежнее название переименованной в предыдущем зад
а
нии
таблицы (см.
Задание
18
) таблицы (P
rogress
).

Задание
20

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

ВОПРОСЫ ДЛЯ САМОКОНТ
РОЛЯ К ЛАБОРАТОРНОЙ
РАБОТЕ № 3

1.

Каково основное назначение команды
ALTER
?

2.

Какие операции над ограничениями можно выполнить с помощью кома
н
ды
А
LTER
?

3.

Какие ограничени
я подлежат корректировке?

4.

Каковы правила назначения ограничения
NULL
/
NOT

NULL
?

5.

Когда нельзя изменить ширину столбца в таблице?

6.

Какие существуют ограничения на изменения параметров столбца?

7.

Как можно отменить действие ограничения?

8.

Для столбцов с каким типом

данных разрешены изменения их размера?

9.

Как можно удалить столбец, если на него наложено одно из ограничений?

10.

Как переименовать столбец в таблице? В каких случаях это возможно?

11.


В каких случаях используют отключение ограничений?

12.

Допустим, мы вводим новое о
граничение и хотим, чтобы SQL Server 2000
проверил все существующие данные на соответствие этому ограничению,
что следует для этого сделать
?

13.

Каковы могут быть последствия временного отключения ограничений?

14.

Какова последовательность действий при модификаци
и столбца, имеющ
е
го
ограничение
DEFAULT
?

15.

Какое место в таблице занимает вновь создаваемый столбец?

16.

Возможно ли изменить порядок следования столбцов в таблице, не испол
ь-
зуя операцию удаления?

17.

Какие из ограничений не могут быть временно отключены?


44

ЛАБОРАТО
РНАЯ РАБОТА № 4

Цель занятия
: Изучить команды формирования запросов к базе данных
(
SELECT
).

Результат занятия:

Приобретение навыков формирования запросов к базе
данных с использованием заданных критериев вывода информации.

Команда SELECT позволяет реализов
ывать все операторы реляцио
н
ной
алгебры над отношениями (таблицами) базы данных. Обязательными предл
о-
жениями команды SELECT являются предложения SELECT и FROM. В самой
простой форме, команда SELECT используется для того, чтобы извлечь инфо
р-
мацию из таблицы
. Не обязательно использовать все предлож
е
ния команды, но
обязательно соблюдать их порядок следования, то есть предложение ORDER
BY всегда завершает команду SELECT, а предложение HAVING всегда стоит
после GROUP BY, которое, в свою очередь, не может предшес
твовать предл
о-
жению WHERE и т.п.

Синтаксис команды SELECT:

SELECT [DISTINCT] *|<столбец> [<псевдоним>] [,<групповая функция>]
[,…
n
]


FROM <таблица>[, …
n
]| (<подзапрос>)


[WHERE <условие>]


[GROUP BY<выражение группировки]


[HAVING <условие отбора груп
пы>]


[ORDER BY
столбец

>[,…n]]

Команда SELECT позволяет извлечь определенную информацию из та
б-
лицы. Например, просмотреть содержимое одного или нескольких стол
б
цов,
присвоив столбцам вывода имена, отличные от имен атрибутов в табл
и
це, но
более информат
ивные. Чтобы вывести содержание всех столбцов, можно зам
е-
нить их перечисление знаком *. В этом случае столбцы будут в
ы
ведены в том
порядке, в котором они следуют в таблице. Если вы хотите и
з
менить порядок,
то придется перечислить имена столбцов в нужном по
рядке.

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

Пример
38

Задача.


Сформировать запрос для вывода всех записей из отношения Progress.


45

Решение
.

1
вариант
:

SELECT

*
FROM Progress;

Результат реализации запроса:


2
вариант
:

SELECT IDReport, IDSubject, NRecordBook, NTerm, PIN, Mark


FROM

Progress
;

Результат реализации запроса:



3 вариант:

SELECT

IDReport

[Отчетность],
IDSubject

[Дисциплина],
NRecor
d
Book

[№ зачетки],
NTerm

[№ семестра],
PIN
[№ преподавателя],
Mark
[Оценка]


FROM

Progress
;

Результат реализации запроса:


46


С помощью команды SELECT можно вывести не всю таблицу, а только
один или несколько ее столбцов.

Пример
39

Задача.

Сформировать
запрос для вывода названий предметов из таблицы Subject.

Решение
.

SELECT NameSubject


FROM Subject ;

Результат реализации запроса:



Пример
40

Задача.

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

Р
ешение.

SELECT

SName

ФИО,
NRecordBook

[№ зачетки]


FROM

Student
;

Результат реализации запроса:




47

ВЫБОРКА ДАННЫХ ИЗ НЕ
СКОЛЬКИХ ТАБЛИЦ

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

указываются в предлож
е
нии
FROM
.

Синтаксис предложения FROM:


FROM

<первая таблица> [[
AS
] <псевдоним>]


<тип объединения> <вторая таблица>[[
AS
] <псевдоним>]


[
ON

<условие объединения>]

Типы объединения приведены ниже в таблице (см.
Таблица
13
).

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

2000 перестает воспринимать действительное имя таблицы в
рамках данного запроса. Один и тот же псевдоним может быть присвоен ра
з-
личным таблицам в запросе и подзапросе. Однако, стремясь к совершенному
коду, надо отдавать себе отчет насколько это ц
елесообразно. Существует н
е-
сколько способов соединения таблиц (см.
Таблица
13
), наиб
о
лее применяемый
способ
INNER

JOIN
, который также может быть реализ
о
ван и с помощью
предложения
WHERE
.

Таблица
13



Варианты
JOIN

Назначение

1

2

3

1

INNER

JOIN

Внутреннее соединение, включающее
только совпадающие по условию

соединения записи из соединяемых
та
б
лиц

2

OUTER JOIN(LEFT


RIGHT)

Включающее все записи левой (пр
а-
вой) таблицы и совпадающие с ними

по условию соединения записи правой
(левой) таблицы

3

FULL

JOIN

Включение всех данных из соединя
е-
мых таблиц

4

CROSS

JOIN

Декартово произведение

В предложении
FROM

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


48

Пример
41

Задача.

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

Решение.

Необходимые для вывода данные хранятся в различных таблиц
ах (Student
и Sgroup), которые соединяются по атрибутам внешнего ключа. Исходя из
у
с
ловия целостности базы данных, в таблице
Student

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

JOIN
.

SELECT

NameGroup
,
NRecordBook
,
SName


FROM

SGroup

SG

INNER

JOIN

Student

St


ON SG.IDGroup=St.IDGroup

Результат реализации запроса:


Пример
42

Задача.

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

Решение.

Здесь, в отличие от предыдущего примера (см.
Пример
41
) предполагае
т-
ся, что имеют место группы, в которых еще не были записаны студенты.

SEL
ECT NameGroup,NRecordBook,SName


FROM SGroup SG LEFT OUTER JOIN Student St


ON SG.IDGroup=St.IDGroup

или

SELECT NameGroup,NRecordBook,SName


FROM Student St RIGHT OUTER JOIN SGroup SG


ON St.IDGroup =SG.IDGroup

Результат реализации запроса:


49



При
мер
43

Задача.

Сформировать запрос для вывода данных об успеваемости студентов.

Решение.

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

с таблицами
Student
,
Sgroup
,
Subject
,
Report
,
Teacher
.

SELECT


NameGroup [Группа],


S.NRecordBook [№ зачетки],


Sname

[Имя студента ],


NameSubject[
Дисциплина],


NameReport[
Отчетность
],


NTerm
[Семестр],

TName
[Имя преподавателя],

Mark[
Оценка
]


FROM Progress P INNER JOIN Student S


ON P.NRecordBook=S.NRecordBook


INNER JOIN SGroup SG



ON S.IDGroup=SG.IDGroup


INNER JOIN Subject SB



ON P.IDSubject=SB. IDSubject


INNER JOIN Report R



ON P.IDReport=R.IDReport


INNER JOIN Teacher T



ON P.Pin=T.Pin

Резул
ьтат реализации запроса:


50


Условие соединение таблиц может быть задано также и в предложении
WHERE. В этом случае, например, один из приведенных выше запросов б
у
дет
иметь вид


SELECT

NameGroup
,
NRecordBook
,
SName


FROM

SGroup

SG,

Student

St


WHERE SG.IDG
roup=St.IDGroup

Однако предложение WHERE позволяет реализовать только один вид с
о-
единения


INNER JOIN.

НАИБОЛЕЕ ЧАСТО ВСТРЕ
ЧАЮЩИЕСЯ ОШИБКИ ПРИ
РЕАЛИЗАЦИИ
ЗАПРОСОВ НА НЕСКОЛЬК
ИХ ТАБЛИЦАХ

Пример
44

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


SELECT S.NRecordBook, SName ФИО

FROM Student S,Progress P


WHERE Student.
NRecordBook

= Progress. NRecordBook

Server: Msg 107

The column prefix 'Student' does not match with a table name or alias

name
used in the query.

Server: Msg 107The column prefix 'Progress' does not match with a table
name or alias name used in the query.

Пример

45

SELECT NRecordBook, SName
ФИО

FROM Student, Progress


WHERE Student. NRecordBook

=
P
rogress
.
NRecordBook


Результат реализации запроса:

Ambiguous column name 'NRecordBook'.


51

Будет выдано сообщение об ошибке, так как атрибут NRecordBook вх
о-
дит в схемы двух отношений и необходимо конкретизировать, из какого отн
о-
шения выбирается предложением
SELECT атрибут NRecordBook.

Пример

46

SELECT Student.NRecordBook, SName
ФИО


FROM

Student
,
Progress

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

ОПРЕДЕЛЕНИЕ УСЛОВИЙ
ВЫБОРКИ В ПРЕДЛОЖЕНИ
И
WHERE
.

В

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

C
интаксис предложения
WHERE
:

WHERE

< имя столбца> <оператор условия> < имя столбца>| <константа>|
<список значений>

Условия отбора, задаваемые в предложении
WHERE
, могут быть как
простыми, так
и сложными. Для задания сложных условий могут быть испол
ь-
зованы предикаты
OR

и
AND
, которые в свою очередь можно сочетать в о
д
ном
логическом выражении. Порядок выполнения операторов приведен ниже
(см.

Таблица
14
).

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

Таблица
14

Порядок в
ы-
числения

Оператор

1

Все операторы сравнения и предикаты
IN
,
LIKE
,
IS

NULL
,
BETWEEN

(см.
Таблица

15
)

2

AND

3

OR


52

Таблица
15

№ п/п

Оператор

Смысл оператора

1.


=

Равно

2.


!= , ^= ИЛИ <>

Не равно

3.




Больше

4.


�=

Больше или равно

5.




Меньше

6.


=

Меньше или равно

7.


[NOT]
BETWEEN x
AND y

[Не] принадлежит диапазону значений [x;y
]

8.


IN

(СПИСОК)

Равно любому элементу списка

Эквивалентно “=ANY”

9.


NOT

IN

(СПИСОК)

Не равно ни одному элементу списка.

Эквивалентно “!=ALL”. Если хоть один из элеме
н-
тов списка NULL, то результатом будет FALSE.

10.


ANY

SOME

Сравнение с каждым из значений спис
ка или по
д-
запроса. Должно следовать за =, !=, >, <, <=, >=.
Принимает значение FALSE, если подзапрос не в
ы-
бирает ни одной строки

11.


ALL

Сравнение с каждым из значений списка или по
д-
запроса. Должно следовать за =, !=, >, <, <=, >=.
Принимает значение TRUE, е
сли подзапрос не в
ы-
бирает ни одной строки.

12.


IS [NOT]
NULL

Проверка на NULL. Это единственные операторы,
которые должны использоваться при проверке зн
а-
чений на NULL.

13.


X [NOT] LIKE
‘y’

TRUE
, если
x

[не]удовлетворяет шаблону ‘
y
’. В
шаблоне ‘
y
’ символ % с
оответствует любой строке
символов (возможно пустой);

символ '_'
-

одному символу;

[]
-

один символ в указанном диапазоне;

[^]
-

один символ вне указанного диапазона.



53

Таблица
16



Оператор

Описание

1.


!=, �

Не равно

2.


NOT

<имя

столбца> =

значение столбца не равно

3.


NOT

<имя столбца> >

значение столбца не больше чем

4.


NOT

<имя столбца> <

значение столбца не меньше чем

5.


NOT

BETWEEN

Не лежит в интервале

6.


NOT

IN

Не содержится в списке

7.


NOT

LIKE

Не содержит подстроки

8.


IS NOT NUL
L

Не содержит неопределенности

Пример
47

Задача.

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

Решение
.

SELECT SName ФИО


FROM Student St INNER JOIN SGroup SG



ON St.IDGroup =SG.IDGroup





WHERE NameGroup='
ИСТ
-
02' ;

Результат реализации запроса:



Пример
48

Задача.

Сформировать запрос для вывода
имени преподавателя, у которого ст
у-
денты в 5 семе
стре имели оценки выше 4.

Решение
.

SELECT TName ФИО


FROM Teacher, Progress


WHERE Teacher.PIN=Progress.PIN


AND Mar�k4 AND NTerm=5 ;

Результат реализации запроса:


54



В результате запроса имя одного и того же преподавателя было вы
ведено дв
а-
ж
ды. Очевидно, что преподаватель дважды в 5 семестре оценивал работу ст
у-
де
н
тов оценкой выше, чем 4. Здесь целесообразно вспомнить о предлож
е
нии
DISTINCT
, которое позволит убрать в результате запроса дубли. Теперь запрос
будет выглядеть следующим
образом

SELECT DISTINCT TName
ФИО


FROM Teacher, Progress


WHERE Teacher.PIN=Progress.PIN


AND Mar�k4 AND NTerm=5 ;

Результат реализации запроса:



В предложении WHERE используются различные предикаты (см.
Таблица
15
),
рассмотрим применение некоторых из них.

Предикат
IN

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

Пример
49

Задача.

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

Решение
.

SELECT DISTINCT TName ФИО


FROM Teacher INNER JOIN Progress


ON Teacher.PIN=Progress.PIN



WHERE DeptName IN ('
ИСТ
', '
АИС
');

Результат реализации запроса:





55

Пример
50

Задача.

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

Решение
.

SELECT DISTINCT TName ФИО


FROM Teacher INNER JOIN Progress


ON Teacher.PIN=Progress.PIN


WHERE DeptName NOT IN ('
ИСТ
', '
АИС
');

Результат реализации запроса:



Задание
21

Сформироват
ь запрос для вывода списка студентов, не обучающихся в
группах АИС
-
03 и ИС
T
-
03.

Задание
22

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

Предикат BETWEEN

Предикат BETWEEN работает с любыми
типами данных, которые можно
сравнивать, и встречается в запросах достаточно часть. Предикат
BETWEEN

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

конечные точки.

Пример
51

Задача.

Сформировать запрос для вывода имен преподавателей, оценивших хоть
один раз работу студента оценкой 4 или 5.

Решение
.

SELECT DISTINCT TName ФИО


FROM Teacher INNER JOIN Progress


ON
Teacher.PIN=Progress.PIN


WHERE MARK BETWEEN 2 AND 4 ;


56

Результат реализации запроса:



Наиболее часто встречающимся заблуждением является то, что пред
и
кат

BETWEEN работает только с числами, на самом деле предикат работает с л
ю-
быми типами
данных, которые можно сравнивать.

Пример
52

Задача.

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

Решение
.

SELECT TName


FROM Teacher


WHERE TName BETWEEN '
Волкова
' AND '
Николаева

'
;

Результат реализации запроса:


Для сравнения:

SELECT

TName



FROM Teacher


WHERE TName BETWEEN 'Волкова' AND 'Николаева Н.А.' ;

Результат реализации запроса:


Пример
53

Задача.

Сформировать запрос для вывода имен все
х преподавателей, которые
были приняты на работу в период с 1.12.1970 по 31.12.1995г.

Решение
.


57

SELECT TName ФИО


FROM Teacher


WHERE DataHire BETWEEN '12.01.1970' AND '12.31.1995';

Результат реализации запроса:



Обратите внимание, что в зап
росе атрибут
DateHire

имеет следующий
формат ‘ММ.ЧЧ.ГГГГ’.

Задание
23

Сформировать запрос для вывода списка студентов, получивших паспо
р-
та в период с 20.11.2002 по 26.11.2002 года.

Задание
24

Сформировать

запрос для вывода списка дисциплин, по которым сд
а
ется
экзамен студентами специальности ИСТ в период с 3 по 5 семестр.

Задание
25

Сформировать запрос для вывода списка студентов, получивших паспо
р-
та в период отличный от 20.11.200
2 по 26.11.2002 года.

Предикат
LIKE

Предикат
LIKE

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

должно соде
р-
жать строку. После ключевого слова
LIKE

должен стоять литерал, кот
о
рый и
является шаб
лоном для проверки соответствия. В шаблоне любой произвол
ь-
ный символ может быть заменен знаком подчеркивания ‘_’, а подстрока прои
з-
вольной длины может быть определена с помощью ‘%’. Все о
с
тальные
символы в подстроке поиска представляют самих себя. Например
, следующему
шаблону соответствует любая строка длиной хотя бы в два символа, содерж
а-
щая буквосочетание ‘БД’ в любой позиции, ’% БД %’. Если нас интересует
н
а
личие этого буквосочетание в строке, начиная со второго си
м
вола, то шаблон
следует привести к виду

’% _БД %’, если нас вообще инт
е
ресует наличие этих
двух букв в строке, то мы запишем шаблон следующим образом ’% Б%Д %’.
Для вывода списка групп из таблицы SGroup, в назв
а
ниях которых встречаются
буквы из диапазона ‘А
-
И’ создадим шаблон '% [А
-
И]%, помес
тив значение
диапазона в квадратные скобки. Точно также, е
с
ли в шаблоне надо указать знак

58

‘%’ или знак подчеркивания ‘_’ по своему прямому назначению, следует п
о
м
е-
стить их в квадратные скобки, например для вывода названий групп, в кот
о
рых
после знака подче
ркивания второй символ равен 5, следует использовать ша
б-
лон ‘%[_]_5%’ и т.п.

Пример
54

Задача.

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

Решение
.

SELECT NameSubject


FROM Su
bject


WHERE NameSubject LIKE '%
БД
%'

Результат реализации запроса:



Пример
55

Задача.

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

Решение
.

SELECT NameSubject


FROM Subjec
t


WHERE NameSubject LIKE '%
У
%
Д
%'

Результат реализации запроса:


Задание
26

Сформировать запрос для вывода списка преподавателей из таблицы
Teacher
, фамилии которых заканчиваются на ‘ова’.


59

Задание
27

Сформировать запрос для вывода списка преподавателей из таблицы
Teacher, фамилии которых начинаются на ‘К’ и заканчиваются на ‘а’.

Задание
28

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

Предикат IS NULL

Пример
56

Задача.

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

Решение
.

SELECT DISTINCT S.NRecordBook [№ зачёт
ки],


SName ФИО,

NameSubject Дисциплина


FROM Student S INNER JOIN Progress P


ON S.NRecordBook= P.NRecordBook


INNER JOIN Subject Sb


ON Sb.IDSubject= P.IDSubject


WHERE Mark IS NULL;

Результат реализации зап
роса:




Большая группа запросов включает в себя условия с отрицанием. Сущ
е-
ствует множество способов задания отрицания (см. Таблица 16). Мы уже встр
е-
чались с таким запросом (см.
Пример
50
). Однако сейчас о
братим вн
и
мание на
использование предиката
IS

[
NOT
]
NULL
.

Пример
57

Задача.

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

Решение
.

SELECT DISTINCT SName [Имя студента]


60


FROM Student S Lef
t JOIN Progress P


ON S. NRecordBook= P. NRecordBook


WHERE Mark IS NULL
;

Результат реализации запроса:



Наиболее часто встречающиеся ошибки связаны с попыткой написать
у
с
ловие отбора, например, следующим образом: Mark =NULL,
Mark �NULL
или Mark !=NULL. Сразу отметим, что существует единственно корректная
форма записи: Mark IS NULL (Mark IS NOT NULL)

Пример
58

Задача.

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

Решение
.

SELECT DISTINCT SName [Имя студента]


FROM Student S Left JOIN Progress P


ON S. NRecordBook= P. NRecordBook


WHERE Mark IS NOT NULL
;

Результат реализации запроса:



Использование других предикатов наиболее актуально

при работе с подзапр
о-
сами и будет рассмотрено в другом разделе (см. Лабораторная работа № 5).

ГРУППОВЫЕ ФУНКЦИИ И
ПРЕДЛОЖЕНИЕ
GROUP

BY


Групповые функции (см.
Приложение
3
. Агрегатные функции
) обрабат
ы-
вают множест
во строк и возвращают, как правило, один результат на группу.
По умолчанию все строки таблицы рассматриваются как одна группа. Для б
о-
лее детальной группировки строк используется предложение GROUP BY, кот
о-
рое делит строки на группы. В случае применения GROU
P BY предложение
SELECT должно содержать столбцы, идентифицирующие группы, остальные

61

столбцы могут быть использованы в предложении
SELECT

только как арг
у-
менты агрегатных функций.

Пример
59

Задача.


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

Решение
.

SELECT P.NRecordBook [№
зачетки
],


SName

[Имя студента],



MAX
(
Mark
) [Максимальная оценка],



MIN
(
Mark
) [Минимальная оценка],



AVG
(
Mark
)[Средня
я оценка]


FROM Progress P INNER JOIN Student S




ON P.NRecordBook=S.NRecordBook


GROUP BY P.NRecordBook, SName

Результат реализации з
апроса:


Для каждой группы будет выведена только одна строка.

С предложением GROUP BY могут быть использованы все остальные
команды SELECT, например, с помощью предложения WHERE можно искл
ю-
чить строки, которые не должны участвовать в запросе, допустим стр
оки с пр
и-
знаком NULL. Рассмотрим это на примере применения агрегированной
функции COUNT(). Функция COUNT() с аргументом отличным от *, игнорир
у-
ет строки с признаком NULL, в то время как функция вида COUNT(*) посчит
а-
ет все строки, в том числе и строки, в ко
торых есть атрибуты с признаком
NULL.

SELECT

COUNT
(*)

[Количество оценок],


SUM
(
Mark
)[Сумма баллов],


AVG
(
Mark
)[Средняя оценка]


FROM

Progress


Результат реализации запроса:




62

В этом случае функция
C
OUNT
() посчитает все строки, а функции
SUM
()
и
AVG
() будут учитывать только те строки, в которых оценка
NOT

NULL
.

SELECT
COUNT(Mark)

[
Количество

оценок
],


SUM
(
Mark
)[Сумма баллов],


AVG
(
Mark
)[Средняя оценка]



FROM

Progress


Результат реализации запроса:


Здесь функция
COUNT
() будет работать с теми же строками, что и фун
к-
ции
SUM
() и
AVG
(). Исключить возможные неоднозначности можно включив в
запрос условие отбора строк: предложение
WHERE

mark

NOT

NULL
.

SELECT

COUNT
(*)

[Количество оценок],


SUM
(
Mark
)[Сумма баллов],


AVG
(
Mark
)[Средняя оценка]


FROM Progress


WHERE Mark IS NOT NULL

Результат реализации запроса:


Однако использование в
предложении WHERE агрегированной фун
к
ции
приводит к ошибке (см.
Пример
60
).

Пример
60

Задача.


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

Решение
.

SELECT NameGroup, AVG(Mark)[Средняя оценка]


FROM Progress P INNER JOIN Student S



ON P.NRecordBook=S.NRecordBook


INNER JOIN SGroup SG


ON S.IDGroup=SG.IDGroup



WHERE
AVG(Mar
k)�3


GROUP

BY

NameGroup


63

Результат реализации запроса:

Server: Msg 147

An aggregate may not appear in the WHERE clause unless it is in a subquery co
n-
tained in a HAVING clause or a select list, and the column being aggregated is an

outer reference.

Пример
61

Задача.


Сформировать запрос для вывода названий студенческих групп, если
средняя оценка по студенческой группе выше 3. Значение оценки округлить до
одной десятой.

Решение
.

SELECT NameGroup, Round(AVG(Ma
rk),1)[
Средняя

оценка
]


FROM

Progress

P

INNER

JOIN Student S



ON P.NRecordBook=S.NRecordBook


INNER JOIN SGroup SG


ON S.IDGroup=SG.IDGroup


GROUP BY NameGroup


HAVING AVG(Mar�k)3

Ре
зультат реализации запроса:



Тем не менее, не следует использовать HAVING там, где достаточно
применения WHERE (см.
Пример
62
).

Пример
62

Задание.


Сформировать запрос для расче
та средней оценки по студенческой гру
п-
пе ИСТ
-
02.

Решение:

Задача может быть решена двумя способами. В первом случае произв
о-
дится расчет средней оценки в каждой группе, а затем выбирается оценка, с
о-
ответствующая группе ИСТ
-
02. Во втором случае сначала отсек
аются все
записи, соответствующие группе ИСТ
-
02, а только затем вычисляется сре
д
няя
оценка. Очевидно, не надо доказывать, что вторая реализация оптимал
ь
на.


64

Реализация с использованием HAVING

SELECT NameGroup, Round(AVG(Mark),1)[
Средняя

оценка
]


FROM Prog
ress P INNER JOIN Student S


ON P.NRecordBook=S.NRecordBook


INNER JOIN SGroup SG


ON S.IDGroup=SG.IDGroup


GROUP BY NameGroup


HAVING NameGroup='
ИСТ
-
02'

Реализация с использованием WH
ERE

SELECT NameGroup, Round(AVG(Mark),1)[Средняя оценка]


FROM Progress P INNER JOIN Student S


ON P.NRecordBook=S.NRecordBook


INNER JOIN SGroup SG


ON S.IDGroup=SG.IDGroup


WHERE NameGroup='ИСТ
-
02'



GROUP BY NameGroup
7

Результат реализации запроса:


НАИБОЛЕЕ ЧАСТО ВСТРЕ
ЧАЮЩИЕСЯ ОШИБКИ ПРИ
ВЫПОЛНЕНИИ
GROUP

BY

Одна из основных ошибок при применении предложения GROUP BY
связана с попыткой указать в предложении GROUP BY атрибут, которого нет
в
списке SELECT.

Пример
63

Задание.


Сформировать запрос для вывода названий всех дисциплин, по кот
о
рым
средняя оценка меньше 4.

Решение
:

SELECT
NameSubject,

ROUND(AVG(Mark),1)




7

Применение предложения GROUP BY является обязательным, в проти
в
ном случае
будет выдано сообщение об ошибке.

Server: Msg 8118

Column 'SG.NameGroup'
is invalid in the select list because it is not contained in an
aggregate function and there is no GROUP BY clause.


65



FROM Progress P,Subject S


WHERE P.IDSubject=S
.IDSubject


GROUP BY
P.IDSubject


HAVING AVG(Mark)4


Результат реализации запроса:

Server: Msg 8120

Column 'S.NameSubject' is invalid in the select list because it is not contained in e
i-
ther an aggregate function or the GROUP B
Y clause.

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

Пример

64

SELECT
NameSubject,

P.IDSubject, ROUND(AVG(Mark
),1)


FROM Progress P,Subject S



WHERE P.IDSubject=S.IDSubject



GROUP BY P.IDSubject




HAVING AVG(Mark)4

Результат реализации запроса:

Server: Msg 8120

Column 'S.NameSubject' is invalid in the select list because it is not co
ntained in e
i-
ther an aggregate function or the GROUP BY clause.

Выходом из положения может быть группировка как по атрибуту
NameSubject, так и по
IDSubject
.

ПРЕДЛОЖЕНИЕ
ORDER

BY

Предложение
ORDER

BY

позволяет выполнить сортировку результ
а
тов
запроса, оно в
сегда ставится в конец команды SELECT. Для изменения порядка
сортировки используются опции
ASC



возрастание или DESC


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

Пример
65

Задача.

Сформировать запрос для вывода списка имен студентов по возраст
а
нию
(убыванию).


66

Решение.

SELECT

SName

ФИО


FROM Student


ORDER

BY SName
;

Результат реализации запроса:



И, аналогично, по убыванию

SELECT SName
ФИО


FROM Student


ORDER BY SName
DESC
;

Результат реализации запроса:


Пример
66

Задача.

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

Решение
.

SELECT SName ФИО, Mark Оценка


FROM Student S INNER JOIN Progress P


ON S.NRecordBook= P.NRecordBook



ORDER BY
SName, Mark;

Результат реализации запроса:


67



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

Пример
67

Задача.

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

Решение
.

SELECT SName ФИО, Mark Оценка


FROM Student S INNER JOIN Progress P


ON S.NRe
cordBook= P.NRecordBook

INNER JOIN Subject Sb


ON Sb.IDSubject= P. IDSubject


ORDER BY
NTerm,NameSubject
;

Результат реализации запроса:





68

Задание
29

Сформировать запрос для вывода имен

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

Задание
30

Сформировать запрос для вывода названия дисциплин, по которым сд
а-
ется экзамен. Название дисциплин расположить в порядке возрастания.

Задание
31

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

Задание
32

Сформировать запрос для вывода имен преподавателей, которые преп
о-
дают как в 5, так и в
6 семестрах.

Задание
33

Сформировать запрос для вывода запланированных занятий в 5 сем
е
стре.

Задание
34

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

Задание
35

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



Объединение двух или более запросов с помощью UNION

UNION


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

Основные правила при реализации операций над множествами



запросы, соединяемые оператором UNION должны иметь одинак
о
вое
количество столбцов в предложен
ии SELECT;



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


69



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



по умолчанию режимом вывода для UNION явл
яется DISTINCT. Для
того, чтобы отключить режим по умолчанию, необходимо после оп
е-
ратора
UNION

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

Пример
68

Задача.

Получи
ть все записи о студентах, фамилии которых начинаются на букву
'М' или 'И'.

Решение
.

SELECT
NRecordBook,SName



FROM Student



WHERE SUBSTRING(SName,1,1)= '
М
'

UNION

SELECT
NRecordBook,SName


FROM Student


WHERE

SUBSTRING
8
(
SName
,1,1)= '
И
'

Результат выпо
лнения запроса.



Пример
69

Задача.

Получить все записи о студентах, которые сдали экзамен или по дисц
и-
п
лине БД (Б
а
зы данных) или по дисциплине ВМ (Высшая математика).

Решение.

Результатом выполнения операции
UNION

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

SELECT SName


FROM Student S

INNER JOIN Progress P


ON S.NRecordBook=P.NRecordBook




8

Функция
SUBSTRING

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


70

INNER JOIN Subject SB

ON SB.IDSubject=P.IDSubject

INNER JOIN Report R

ON R.IDReport=P.IDRepor
t


WHERE NameSubject='
БД
' AND NameReport='
Экзамен
'


UNION


SELECT SName


FROM Student S

INNER JOIN Progress P


ON S.NRecordBook=P.NRecordBook

INNER JOIN Subject SB

ON SB.IDSubject=P.IDSubject

INNER JOIN Report R

ON R.IDReport=P.IDReport


W
HERE NameSubject='
ВМ
' AND NameReport='
Экзамен
'

Результат выполнения запроса.


После выполнения операции
UNION

ALL

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

Результат выполнения запроса.


Предложение ORDER BY в операци
ях над множествами может стоять
только в последнем предложении запроса, при этом вместо имен столбцов и
с-
пользуются их номера из предложения SELECT.

Пример
70

Задача.

Вывести всех студентов, у которых есть отличные или хорошие оценк
и.

Решение
.

SELECT SName ФИО


71


FROM Student S INNER JOIN Progress P


ON S.NRecordBook=P.NRecordBook


WHERE Mark=4


UNION


SELECT SName ФИО


FROM Student S INNER JOIN Progress P


ON S.NRecordBook=P.NRecordBook



WHERE Mark=5


ORDER BY 1

Результат выполнения запроса.


Задание
36

Используя операторы над множествами, вывести имена всех студе
н
тов,
которые сдали экзамен по дисциплине УД или по дисциплине СУБД.

ВОПРОСЫ ДЛЯ САМОКОНТ
РОЛЯ К
ЛАБОРАТОРНОЙ РАБОТЕ
№ 4

1.

Какие предложения в команде S
ELECT

являются обязательными?

2.

Какое из предложений используется для исключения групп строк из резул
ь-
тата запроса?

3.

C помощью какого предложения создаются группы?

4.

В каком порядке в команде SELECT должны ра
сполагаться предложения
ORDER BY, GROUP BY, WHERE?

5.

В чем заключается различие между предложениями WHERE и H
AVING
?

6.

В каких случаях запрос, построенный с использованием предложений
H
AVING
, может быть преобразован в эквивалентный запрос без использ
о-
вания этог
о предложения?

7.

Какие предложения позволяют ограничить количество выводимых строк?

8.

В чем отличие простого запроса от сложного?

9.

С помощью каких ключевых слов в команде S
ELECT

осуществляется
проверка вхождения результата вычисления выражения в заданное мн
о-
жес
тво?

in, not in}.

10.

Какая функция используется для подсчета кортежей, отвечающих зада
н
ному
условию?

11.

Какой вид будет иметь команда S
ELECT
, реализующая операцию пр
о
екции
на атрибуты AB в отношении со схемой R=

ABCD

?


72

12.

Какое из предложений команды S
ELECT

наклад
ывает ограничение на о
т
бор
групп?

13.

Как следует сформировать запрос к таблице R1=

AB


и R2=

CD

, чтобы
получить их декартово произведение?

14.

Возможно ли, используя команду SELECT, реализовать операцию дел
е
ния?

15.

В каких случаях нельзя для осуществления операции

выбора использ
о
вать
предложение WHERE?

16.

Какие особенности следует учитывать при реализации оператора
UNION
?

17.

В каких случаях после реализации оператора
UNION

будет получено

отн
о
шение?


73

Подзапрос 1

Подзапрос 2

Подзапрос


n

Запрос

Рисунок
2

ЛАБОРАТОРНАЯ РАБОТА
№ 5

Цель занятия
: Изучить классификацию подзапросов

и правила их формиров
а
ния.

Результат занятия:

Приобретение навыков создания запросов с подзапр
о
сами.

ПОДЗАПРОСЫ

Подзапрос


это команда SELECT, вложенная в предложение другой к
о-
манды SQL. Подзапросы могут использоваться в командах SELECT, UPDATE,
INSERT,

DELETE, CREATE TABLE. Например, каждая команда SELECT м
о-
жет включать в себя несколько других команд SELECT
9
. При этом подзапрос
(внутренний запрос) генерирует значение, которое провер
я
ется в предикате
внешнего запроса. Подзапросы всегда в
ы-
полняются от вну
тренних к внешнему, если
только не являются коррелированными.
запрос может возвращать о
д
ну и более строк
или один и более столбцов.

1.

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

2.

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

3.

Подзапрос может задаваться в сложных
критериях поиска внешних запросов с и
с-
пользованием логических связок
AND

и
OR
.

4.

Предложение ORDER BY ставится п
о-
следним в основном запросе и

не может
содержаться в подзапросе.

5.

В команде SELECT подзапрос может стоять в предложениях FROM,
WHERE, HAVING.

6.

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

7.

Имена столбцов в предложении
SELECT

внутреннего запроса должны ст
о-
ять в той же последовате
льности, что и имена столбцов в левой части опер
а-
тора сравнения внешнего запроса. Типы столбцов должны попарно
соответствовать.




9

Допускается до 255 уровней вложенности подзапросов
.


74

8.

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

(
SOME
),
ALL
.

Подзапрос на уровне предложения WHERE

Пр
имер
71

Задача.

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

Решение
.

SELECT SName, Mark

FROM Student S,Progress P

WHERE S.NRecordBook=P. NRecordBook

AND Mark
=(SELECT AVG(Mark)



FROM

Progress
)

Результат реализации запроса:



Подзапрос вычисляет среднюю оценку и подставляет высчитанное знач
е-
ние в предложение WHERE внешнего запроса.

Коррелированные подзапросы на уровне предложения WHERE

Коррелированные подза
просы


это вложенные подзапросы. Они выпо
л-
няются для каждой строки главного запроса.

Последовательность выполнения коррелированного подзапроса (см.
Р
и-
сунок
3
):



внешний запрос выбирает строку;



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

з
а
проса;



результат выполнения внутреннего запроса возвращается во внешний з
а-
прос, где проверяется его соответствие выбранной строке;



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

При задании вложенных запросов
допускается применение операторов
АNY, EXIST
S
, ALL и логических операторов.


75

Пример
72

Задача.

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

Решение
:


SELECT DISTINCT SName,Mark


FROM Student s,Pro
gress p


WHERE S.NRecordBook=P.NRecordBook


AND Mar�k(SELECT AVG(Mark)


FROM Progress P1,Student S1


WHERE S1.IDGroup=S.IDGroup



AND S1.NRecordBook=P1.NRecordBook)

Результат реализации запроса:


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

Задание
37

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

Задание
38

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

по группе, в которой они обучаются.

Задание
39

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


76


Рисунок
3

Подзапрос

на уровне предложения HAVING

Пример
73

Задача.

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

Решение.

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

SELECT SName, AVG(Mark)


FROM Student s,Progress p


WHERE s.NRecordBook =p. NRecordBook


AND AVG(Mark�) (SELECT AVG(Mark)


FROM Progr
ess)





GROUP

BY

SName

Результат реализации запроса:

050001
, Иванов И.И.
,3 ,…,5

050003 , Сидоров С.С., 2 ,…4

050002 , Петров П.П., 1,…5


050002 , Петров П.П.,
3
,…4


050001 , Иванов И.И.,
3
,…3


050003 , Сидоров С.С., 2 ,…5

050002 , Петров П.П., 1,…5


050001 , Иванов И.И.
,3 ,…
5

050003 , Сидоров С.С., 2 ,…4

050002 , Петров П.П., 1,…5


050002 , Петров П.П.,
3
,…4


050001 , Ива
нов И.И.,
3
,…3


050003 , Сидоров С.С., 2 ,…5

050002 , Петров П.П., 1,…5


N
группы =
3

AVG(MARK)=4


77

Server
:
Msg

147

An aggregate may not appear in the WHERE clause unless it is in a subquery co
n-
tained in a HAVING clause or a select list, and the column being aggregated is an
outer reference.

Кор
ректный синтаксис команды будет следующим:

SELECT SName, AVG(Mark)[Средняя оценка]


FROM Student s,Progress p


WHERE s.NRecordBook =p. NRecordBook


GROUP BY SName


HAVING AVG(Mar�k) (SELECT AVG(Mark)



FROM

Progress
)

Результат реализации з
а
проса:


Наибольшее затруднение, как ни странно, вызывают функции по раб
о
те с
датами, поэтому ниже мы приводим наиболее употребимые функции (см. Пр
и-
ложение 5. Фу
нкции обработки дат и Приложение 6. Допустимые значения п
а-
раметра
часть Даты
).


Пример
74

Задача.

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

Решение:

SELECT

YEAR
(
DataHire
)[Год приема на работу]


FROM Teacher


GROUP BY YEAR (DataHire)


HAVING COUNT(YEAR (DataHire))=(SELECT MAX(d.aCount)


FROM (SELECT COUNT (YEAR (DataHire)) aCount


FROM Teacher



GROUP BY(YEAR (DataHire))) d)




78

Результат реализации запроса:



Подзапрос на уровне предложения FROM

В ряде случаев нам надо сравнить результаты агрегирования строк.
Выше был приведен пример коррелированного запроса, осуществляющего
вы
вод имен студентов, которые имели оценки выше, чем средняя оценка по
той гру
п
пе, в которой они учатся (см.
Пример
72
). Применение подзапроса
на уровне
FROM

позволяет значительно упростить реализацию этого запроса
(
см.
Пример
75
).

Пример
75

Задача.

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

Решение
:

SELECT DISTINCT S.SName,S.NRecordBook


FROM Progress

P


INNER JOIN Student S


ON P.NRecordBook=S.NRecordBook


INNER JOIN


(SELECT IDGroup,AVG(Mark) BMark


FROM Progress P


INNER JOIN Student S


ON P.NRecordBook=S.NRecordBook


GROUP BY IDGroup) b

O
N S.IDGroup=b.IDGroup


WHERE

mark

BMark

Результат реализации запроса:




79

Пример
76

Задача.

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

Решение:

SELECT

a
.
SName
,
a
.
NRecordBook
,
Amark

[
Средняя оценка студента],
Bmark

[Средняя оценка в группе]


FROM (
SELECT p.NRecordBook,SName,IDGroup,AVG(Mark) Amark



FROM Progress P



INNER JOIN Student s





ON P.NRecordBook=S.NRecordBo
ok


GROUP BY P.NrecordBook,SName,IDGroup) a


INNER JOIN


(SELECT IDGroup,AVG(Mark) Bmark



FROM Progress P


INNER JOIN Student s


ON

P.NRecordBook=S.NRecordBook


GROUP BY IDGroup) b



ON a.IDGroup=b.IDGroup


WHERE

Amark

Bmark

Результат реализации запроса:



Задание
40

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

Задание
41

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

Задание

42

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

5 семестре была максимальная нагрузка.


80

Задание
43

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

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

Задание
44

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


Подзапрос в команде INSERT

Задача.

Добавить в таблицу
Student

зап
иси из таблицы
Student
1.

Решение
:

INSERT INTO Student



SELECT NRecordBook, Sname, CodeGroup




FROM

Student
1;

Или, если порядок столбцов в обеих таблицах одинаковый,

INSERT INTO Student



SELECT *




FROM

Student
_1;

Подзапрос в команде UPDATE

Пример
77

Задача.

Внести в столбец MarkAVG таблицы Student среднюю оценку студе
н
та.
Предварительно нужно создать такой столбец в таблице Student с помощью к
о-
манды:

ALTER TABLE

S
tudent

ADD

MarkAVG
DECIMAL
(3,2)

Решение
:

UPDATE Stud
ent


SET

MarkAVG =




(SELECT AVG(Mark)




FROM Progress



GROUP BY NRecordBook



HAVING Student. NRecordBook= Progress. NRecordBook
) ;

Просмотрим результат с помощью запроса:

SELECT

S
Name

ФИО
,
MarkAVG

[
Средняя

оценка
]


81


FROM

Student


Результат реализации запроса:


Подзапрос в команде DELETE


Пример
78

Задача.

Удалить из таблицы

Sgroup

группы, в которые не зачислен ни один ст
у-
дент.

Решение
:

DELETE FROM SGROUP


WHERE IDGROUP IN


(SELECT ST.IDGROUP


FROM STUDENT ST



GROUP BY ST.IDGROUP



HAVING COUNT(ST.IDGR
OUP)=0 ) ;

Применение предикатов при работе с подзапросами.

Предикаты ANY и SOME.

Предикаты

ANY и SOME равноценны, но Some является ANSI совм
е-
с
тимым. Предикаты могут использоваться с любыми операторами сра
в
нения.
Так, сочетание >SOME означает больше минимума, например >SOME (3,4,5)
озн
а
чает больше 3. Сочетание =SOME (=ANY) равносильно оператору
IN.
!=Some
-

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

Пример

79

Задача.

Сформировать запрос для вывода имени преподавателя, который был
принят на работу в университет ранее, чем кто
-
либо из преподавателей кафе
д-
ры ИСТ.

Решение:


82

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

SELECT TName


FROM Teacher WHERE DEPTNam�e'
ИСТ
'

AND DataHireSOME

(SELECT DataHire





FROM Teacher

WHERE DEPTName='
ИСТ
')


Результат реализации запроса:


Этот же запрос можно было реализовать и иначе

SELECT TName


FROM Teacher WHERE DEPTNam�e'
ИСТ
'

AND DataHire

(SELECT MAX(DataHire)




FROM Teacher WHERE DEPTName='
ИСТ
')

Вы можете самосто
ятельно убедиться в идентичности результатов.

Предикат EXISTS.

При использовании EXISTS возвращаются не реальные данные, а бул
е
во
значение TRUE или FALSE, в зависимости от того, существуют ли строки, с
о-
ответствующие критериям, заданным в запросе с оператор
ом EXISTS. Ч
а
ще
всего EXISTS используется в коррелированных подзапросах.

Пример
80

Задача.

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

Решение
:

SELECT TName


FROM Teacher T

WHERE NOT EXISTS

(SELECT PIN


FROM Progress P


WHERE P.PIN=T.PIN )


Результат реализации запроса:



83

Задание
45

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

5 семестре не было запланировано никакой
нагрузки.

Задание
46

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

Задание
47

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

Задание
48

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

Задание
49

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

ВОПРОСЫ ДЛЯ САМОКОНТ
РОЛЯ К ЛАБОРАТОРНОЙ
РАБОТЕ № 5

1.

Что такое подзапрос?

2.

В каких

командах SQL могут быть использованы подзапросы?

3.

Какой порядок выполнения подзапроса?

4.

Когда невозможно использовать подзапрос?

5.

Какой порядок выполнения коррелированных подзапросов?

6.

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

7.

Сколько раз выполняется основной запрос при реализации коррелированн
о-
го подзапроса?

8.

Где расположен подзапрос в логическом выражении предложения осно
в
ного
запроса: до оператора сравнения, после оператора сравнения?

9.

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

10.

Какие существуют типы подзапросов?

11.

В каких командах
SQL

могут быть использованы подзапросы?

12.

В каких предложениях
SELECT

могут быть использованы подзапросы?

13.

Какие предложения
SELECT

нельзя использовать в

подзапросах?


84

14.

Где должен находиться подзапрос?

15.

Какие операторы сравнения работают с несколькими строками?

16.

Какие операторы сравнения работают только с одной строкой?

17.

В каких подзапросах вложенная команда
SELECT

выполняется первой?

18.

Какой принцип работы корре
лированного подзапроса?

19.

Какие требования предъявляются к таблицам, над которыми выполняются
операторы
UNION
?




85

ЛАБОРАТОРНАЯ РАБОТА
№6

Цель занятия:

Знакомство с объектами СХЕМЫ БАЗЫ ДАННЫХ. Создание и
использование представлений.

Результат занятия:

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

ПРЕДСТАВЛЕНИЯ

Представления


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

В словаре данных хр
а-
нится только предложение SELECT того или иного представл
е
ния.

Синтаксис команды:

CREATE VIEW <имя представления>

[(<имя столбца> [,…
n

])

[WITH {ENCRYPTION | SCHEMABINDING | VIEW_METADATA}]

AS


команда

SELECT�

[WITH CHECK OPTION];

WI
TH CHECK OPTION


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

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

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

Ранее отмечалось, что опция WITH CHECK OPTION может ограничить
действие команд
INSERT

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

Команда DELETE запрещена,
если представление содержит:



условие соединения


86



групповые функции или предложение GROUP BY



предложение DISTINCT



столбец со свойством
IDENTITY

Команда UPDATE запрещена всегда, когда запрещена команда DELETE,
а также в случае, когда столбцы содержат выражени
я.

Команда INSERT запрещена всегда, когда запрещена команда UPDATE, а
также тогда, когда какой
-
либо столбец таблицы NOT NULL не содержится в
представлении.

После успешного завершения команды CREATE VIEW выдается соо
б-
щение

View

created

(Представление созд
ано).

Представления используются для:



ограничения доступа к базе данных;



упрощения запросов;



сокрытия схемы базы данных.

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

Пример
81

Задача.

Создать представление, предотвращающее ввод информации в таблицу
Student
.

Решение.

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

NULL, например
IDGroup
.

CREATE VIEW VStudentNOINSERT

AS

SELECT


NRecordBook ,


INN ,


SName ,


SPasport ,


NPasport ,


DataPasport ,


Na
meDeptPasport


87

FROM Student

Попытка ввести данные через созданное представление

INSERT INTO VStudentNOINSERT

VALUES('050008','1111111117','
Ипатов

В
.
И
.','8701','192304','01.06.2002', '
УВД

г
.
Ухты
');

спровоцирует сообщение об ошибке:

Server: Msg 515

Cannot i
nsert the value NULL into column 'IDGroup', table 'Student.dbo.Student';
column does not allow nulls. INSERT fails.

The statement has been terminated.

Пример
82

Задача.

Создать представление, разрешающее просмотр, ввод и редактиро
в
а
ние
только данных о студентах группы ИСТ
-
03 (
IDGroup
=1) в таблице
St
u
dent
.

Решение.

Создадим представление
VStudentNOINSERTIST
03, используя опцию
WITH CHECK OPTION

CREATE VIEW VStudentNOINSERTIST03

AS

SELECT


NRecordBook ,


INN ,


SName ,


IDGroup,


S
Pasport ,


NPasport ,


DataPasport ,


NameDeptPasport


FROM Student

WHERE IDGroup =1

WITH

CHECK

OPTION

Теперь попытка ввести через представление
VStudentNOINSERTIST
03
данных о студенте группы АИС
-
03 (
IDGroup
=2)

INSERT INTO VStudentNOINSERTIST03


88

VALU
ES('050007','1111111117','
Васин

В
.
И
.', 2, '8701', '192314',
'01.06.2002', '
УВД

г
.
Ухты
');

приведет к появлению следующего сообщения об ошибке:

Server: Msg 550

WITH CHECK OPTION
or spans a view that specifies WITH CHECK OPTION and
one or more rows resulting from the operation did not qualify under the CHECK
OPTION constraint.

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

Пример
83

Задача.

Создать представление для вывода всех данных об успеваемости ст
у-
де
н
тов.

Решение.

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

CREATE VIEW VPROGRESS

AS

SELECT


NameGroup [Группа],


S.NRecordBook [№ зачетки],


SName

[Имя студента ],


NameSubject
[Название дисциплины],


NameReport
[Вид отчетности],


NTerm
[№ семестра],

PIN

[Персонифицированный номер преподавателя],

Mark[
Оценка
]


89


FROM Progress P INNER JOIN Student S


ON P.NRecordBook=S.NRecordBook


INNER JOIN SGroup SG


ON S.IDGroup=SG.IDGroup


INNER JOI
N Subject SB



ON P.IDSubject=SB. IDSubject


INNER JOIN Report R



ON P.IDReport=R.IDReport

Реализация запроса

SELECT

*


FROM

VProgress
;

к представлению

VProgress

позволит получить следующий результат:

Резул
ьтат выполнения запроса.


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

Приме
р
84

Задача.

Вывести успеваемость студентов по дисциплине БД в 5 семестре, испол
ь-
зуя представление
VProgress
.

Решение
.

SELECT *


FROM VProgress


WHERE

[Название дисциплины]='БД'


90


AND

[№ семестра]=5

Результат
выполнения запроса.


Нетрудно представить, как бы выглядел этот запрос, обращенный непосре
д-
с
т
венно к таблицам

SELECT NameGroup [Группа],


S.NRecordBook [№ зачетки],


SName

[Имя студента ],


NameSubject[Дисциплина],



NameReport [Отчетность],


NTerm [Семестр],

PIN [ПИН],

Mark[Оценка]


FROM Progress P INNER JOIN Student S


ON P.NRecordBook=S.NRecordBook


INNER JOIN SGroup SG


ON S.IDGroup=SG.IDGroup


INNER JOIN Subject SB



ON P.IDSubject=SB.IDSubject


INNER JOIN Report R



ON P.IDReport=R.IDReport

WHERE NameSubject ='БД'


AND NTerm=5

Пример
85

Задача.

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

Решение.

SELECT

Группа,
AVG
(Оценка) [Средняя оценка]


FROM VProgress


WHERE [Семестр]=5


GROUP

BY

Группа

Результат выполнения запроса.


91


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

Пример
86

Задача.

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

Решение
.

CREATE VIEW VPROGRESSTEACHER

AS

SELECT Группа,


[№ зачетки],


[Имя студента],


[Дисциплина],


[Отчетность],


[Семестр],


[ПИН],



TName

[Имя преподавателя],


Оценка


FROM VProgress P INNER JOIN Teacher T


ON P.[
ПИН
]=T.PIN

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

Задание
50

Создать представление для просмотра базы данных с целью определ
е
ния
успеваемости студентов группы АИС
-
03.

Задание
51

Созда
ть представление для вывода ведомостей успеваемости студе
н
тов.


92

Задание
52

Создать представление для вывода информации о предметах, изуча
е
мых в
3 семестре студентами специальности ИСТ.

Задание
53

Создать п
редставление для вывода информации о плановой нагрузке пр
е-
подавателей кафедры АИС.

Задание
54

Создать представление для вывода информации о плановой нагрузке по
дисциплине БД (Базы данных).

ВОПРОСЫ ДЛЯ САМОКОНТ
РОЛЯ К ЛАБОРАТОРНОЙ

РАБОТЕ № 6

1.

Что такое представление?

2.

С какой целью создаются представления?

3.

В чем отличие простых и сложных представлений?

4.

Какое предложение, включенное в команду
CREATE

VIEW
, позволяет с
о-
з
давать представление в отсутствии базовых таблиц?

5.

Какие предложения

не может содержать запрос, определяющий представл
е-
ния?

6.

Какие представления позволяют выполнять все без исключения операции
DML
?

7.

Когда через представления нельзя выполнить операции удаления?

8.

Как реализуется запрос на выполнение операции
DML

при создании пр
е
д-
ставления?

9.

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

10.

Допускается ли изменение представления?

11.

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

12.

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


93

ЛАБОРАТОРНАЯ РАБОТА
№7

Цель занятия:

Знакомство с объектами СХЕМЫ БАЗЫ ДАННЫХ. Создание и
использование хранимых процедур.

Результат занятия:
Приобре
тение навыков создания хранимых процедур и
использования основных языковых конструкций языка T
-
SQL.

ХРАНИМЫЕ ПРОЦЕДУРЫ

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

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

При создании процедур необходимо придерживаться следующих пр
а
вил:
во время выполнения хранимой процедуры
все объекты, на которые она ссыл
а-
ется, должны присутствовать в базе данных. В хранимых процедурах нельзя
применять операторы создания объектов: CREATE PROCEDURE, CREATE
TRIGGER
, CREATE
VIEW
.

Прежде чем выполнить хранимую процедуру,
SQL

Server

2000 генерир
у-
ет для нее план исполнения, выполняет оптимизацию хранимой процедуры и
компилирует ее. В дальнейшем этот план и откомпилированный код кэшир
у-
ются. При повторном вызове процедуры, сервер уже использует готовый план и
откомпилированный код процедуры, экономя
за счет этого ресурсы (см.

Рисунок

4
). Недостатком данного метода является то, что процедура м
о-
жет быть оптимизирована ©не на тех данныхª, и план запуска оптимальный на
начальном наборе данных, может быть не опти
мален на всех последу
ю
щих. В
этих случаях используется принудительная перекомпиляция при каждом запу
с-
ке процедуры.

Существует несколько типов хранимых процедур, реализуемых на
SQL

Server

2000

1.

Системные хранимые процедуры

2.

Пользовательские хранимые процедур
ы

3.

Локальные временные хранимые процедуры

4.

Глобальные временные хранимые процедуры.


94

Системные хранимые процедуры в своем имени имеют префикс
sp
_, хр
а-
нятся в системной базе данных
master

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

Пользовательские хранимые процедуры


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

Локальные и глобальные вре
менные хранимые процедуры хранятся в б
а-
зе данных tempdb. Имя локальных процедур начинается с #, имя глобал
ь
ных
процедур
-

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


из л
ю-
бо
го соединения сервера, на котором была создана эта процедура. Так как эти
процедуры временные, то они удаляются при перезапуске или остановке серв
е-
ра, при закрытии соединения, в конте
к
сте которого они были созданы.


Рисунок
4



Выполнение


Рекомпилируется

Оптимизируется и
компилируется






















Кэш п
а-
мять

Не первый запуск, процедура есть в кэше

Не первый запуск, процедуры нет в кэше о
т-
сутствует

Первый запуск
/

Рекомпил
яция

Добавляется к

sysobjects

sysdepends

syscomments


Разработка


95

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

Синтаксис команды:

CREATE PROC[EDURE ] имя_процедуры [ ; число ]


[  @параметр тип_данных 



[ VARYING ] [ = значение_по_умолчанию ]
[ OUTPUT ] ] [ ,...n ]


[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION }
]


AS

оператор_
SQL

[ ...
n

]

OUTPUT



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

VARYING



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

RECOMPILE



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

ENCRUPTION



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

AS



индикатор начала собственно кода процедуры.

Вызов процедуры (исключение

составляют триггеры) осуществляется по
команде
EXEC
.

Синтаксис команды EXEC

EXEC

<Имя процедуры> [<список параметров>]

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

Во
-
первых, познакомимся с понятием сценарий (
SCRIPT
), представля
ю-
щим собой последовательность действий, выполняемых целиком и сохране
н-
ных в файле. Примером сценария может быть любая команда языка
DDL

или
DML
.

Во
-
вторых, с понят
ием пакет (
BATH
), который есть совокупность проц
е-
дур, функций, переменных и операторов языка
SQL
, которые сгруппир
о
ваны
вместе и хранятся в виде единого программного блока. Все операторы внутри
пакета комбинируются в единый план исполнения (
execution

plan
)
, и пока все
операторы не будут успешно проанализированы синтаксическим анализатором,

96

ни один из операторов пакета не будет выполнен. Если нек
о
торый оператор в
пакете вызвал ошибку на этапе выполнения программы, то выполняются все
операторы до ошибочного.
Пакеты отделяются друг от друга с помощью опер
а-
тора
GO
, который не является командой
T
-
SQL
, это д
и
ректива, и на сервер не
пересылается. Оператор
GO

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

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

ОПЕРАТОР USE

Синтаксис оператора USE

С помощью оператора
USE

устанавливается текущая база данных.

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

ОПЕРАТОР DECLARE

Синтаксис оператора DECLARE

DECLARE

@<имя переменной><тип переменной>[,…]

DECLARE

@
IPIN

int

Одним оператором
DECLARE

может быть объявлена как одна, так и н
е-
сколько переменных. Объявленн
ое значение переменной не определено (
NULL
)
пока ей не будет присвоено значение. Присвоение переменной того или иного
значения осуществляется либо с использованием оператора
SET
, либо
SELECT
.

ОПЕРАТОРЫ SET И SELE
CT

Синтаксис оператора SET

SET <имя переменн
ой>=<значение переменной>

Синтаксис оператора SELECT

SELECT <имя переменной> = <значение переменной>

Пример
87

Задание.


Присвоить значения переменным, используя оператор SET

Решение
.

DECLARE

@AvgMark
INT

DECLARE

@TotalSumma
DECIMA
L

DECLARE

@MSumma
DECIMAL


97

SET @AvgMark=4

SET @[email protected]*1.8

SET

@
AvgMark
=(
SELECT

AVG
(
Mark
)
FROM

Progress
)

Различия между использованием SET и SELECT при присвоении знач
е-
ния переменной практически не существует. Когда применять тот или иной
операто
р решать пользователю. Однако, поскольку, при присвоении переме
н-
ной значения, полученного из запроса, код запроса несколько короче при и
с-
пользовании SELECT, то его в этом случае чаще всего и применяют. То есть,
SET используют для присвоения известного знач
ения переменной, SELECT


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

USE

Student

DECLARE @AvgMark
NUMERIC

(5,2)

SET @AvgMark=
AVG(Mark) FROM Progress

SELECT @AvgMark

SQL Server 2000
выдаёт

ошибку
:


Incorrect syntax near the keyword 'FROM'.


Но
!!!

USE S
TUDENT

DECLARE @AVGMARK NUM
ERIC (5,2)

SET @AVGMARK=(SELECT

AVG(MARK)



FROM PROGRESS)

SELECT @AVGMARK [
СРЕДНЯЯ

ОЦЕНКА
]

Результат выполнения запроса.



Наиболее часто применимый код

USE

student

DECLARE @AvgMark
NUMERIC

(5,2)

SELECT @
AvgMark= AVG(Mark) FROM Progress

SELECT

@
AvgMark

[СРЕДНЯЯ ОЦЕНКА]

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



@@IDENT
ITY



@@ERROR


98

ФУНКЦИЯ @@IDENTITY

Функция @@IDENTITY возвращает автоматически сгенерированное п
о-
следним оператором значение. Необходима в тех случаях, когда прим
е
няются
столбцы с уникальным сгенерированным значением и существует ссылка на
них в дочерних табли
цах. Хранит только последнее вставленное значение,
пр
е
дыдущее значение не будет сохранено, если его предварител
ь
но не записали
в переменную. Если при выполнении последней команды INSERT ни один
вста
в
ленный столбец не будет иметь уникального значения, то @@
IDENTITY
будет иметь признак NULL.

ФУНКЦИЯ @@ERROR

Функция @@ERROR содержит код ошибки последнего выполняемого в
текущем подключении оператора T
-
SQL. Код ошибки есть целое число
(
INTEGER
). Если ошибка отсутствует, то значение переменной равно 0. Жи
з-
ненный
цикл переменной ограничивается одним оператором. Если нео
б
ходимо
проверить ошибку в конкретном операторе, то это следует делать в операторе,
непосредственно следующем за данным, либо сохранить значение системной
переменной для дальнейшего использования

Пер
ечень ошибок хранится в базовой таблице SYSMESSAGES базы да
н-
ных MASTER. Для добавления собственного списка ошибок используют хр
а-
нимую процедуру sp_addmessage.


Пример
88

Задание.


Создать хранимую процедуру, которая создаёт список
студентов и их н
о-
меров зачёток и упорядочивает их по имени.

Решение
:

CREATE PROCEDURE
P
StudentSNameView

AS

SELECT SName ФИО, NRecordBook [№ зачётки]


FROM Student


ORDER BY SName

EXEC

PStudentView

Результат выполнения запроса:


99


ОБЪЯВЛЕНИЕ ПАРАМЕТРО
В

При объявлении параметров указывается следующая информация



Имя.



Тип данных.



Значение по умолчанию.



Направление (входная или выходная).

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

Пример
89

Задание.


Создать хранимую процедуру, которая выводит содержимое таблицы
Student

для заданной группы, по умолчанию выводятся сведения о студен
тах
группы АИС
-
03.

Решение
:

USE

STUDENT

GO

CREATE PROC PStudent



@NameGroup VARCHAR (20)='АИС
-
03'


AS


SELECT NRecordBook [№ зачетки],


Sname [Имя],


NameGroup [Группа]



FROM Student ST


INNER JOIN SGroup SG




ON ST. IDGroup=SG. IDGroup


WHERE [email protected]

При вызове
EXEC

PStudent

ИСТ
-
02

имеем,




100

Пример
90

Задание.


Создать

хранимую процедуру, которая выводит содержимое таблицы
Student

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

Решение
:

USE

STUDENT

GO

CREATE PROC PStudent


@NameGroup VARCHAR (20)


AS


SELECT NRecord
Book [№ зачетки],


Sname [Имя],


NameGroup [Группа]


FROM Student ST


INNER JOIN SGroup SG




ON ST. IDGroup=SG. IDGroup


WHERE Nam
[email protected]ameGroup

При вызове процедуры с помощью оператора
EXEC
PStudent
, будет в
ы-
дано сообщение об ошибке.

Server: Msg 201, Level 16, State 4, Procedure PSTUDENT, Line 0

Procedure 'PSTUDENT' expects parameter '@NameGroup', which was not su
p
plied.

При вызо
ве
EXEC

PSTUDENT

'ИСТ
-
02'

имеем,

Результат выполнения запроса:


Пример
91

Задание.


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

Решение
:

USE

Student

GO


CREATE PROC PStudentView


AS


SELECT

*


101


FROM STUDENT

Пример

92

Задание.


Создать хранимую процедуру, которая вводит данные в таблицу
St
u
dent
.

Решение
:

USE

Student

GO

CREATE PROC

PStudentINSERT



@NRecordBook
VARCHAR
(6),



@INN
VARCHAR
(10),


@SName
V
ARCHAR
(35),


@IDGroup
INT
,



@SPasport
VARCHAR
(4),



@NPasport
VARCHAR
(6),


@DataPasport
DATETIME
,


@NameDeptPasport
VARCHAR
(35)

AS



INSERT INTO Student



(NRecordBook,INN,SName,IDGroup,SPasport,NPasport,


DataPasport,NameDeptPasport)


VALUES


(@NRecordBook,@INN,@SName,@IDGroup,@SPasport,@NPasport,


@
DataPasport
,@
NameDeptPasport
)

Обратимся к вновь созданной процедуре:

EXEC PStudentINSERT

'050010',

'1111111110',

'Ипатов В.И.',

3,

'8701',

'192320',

'01.11.2002',

'ГОВД г.Ухты'

Увидеть резуль
тат вызова процедуры PStudentINSERT мы можем обр
а-
тившись к ранее созданной процедуре PStudentView (см.
Пример

91
)
EXEC
PstudentView
.


102

ИЗМЕНЕНИЕ ХРАНИМЫХ П
РОЦЕДУР

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

TABLE
. Различие
между использованием ALTER PROC и CREATE PROC заключ
а
ется в том, что
ALTER PROC



подразумевает, что хранимая процедура уже существует



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



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



оставляет неизменной любую информацию об отношениях с др
у-
гими объектами, которые могут вызвать изменяемую процедуру
(см.
Рисунок
5
).

Синтаксис команды ALTER TABLE полностью аналогичен синтаксису
команды CREATE TABLE. С той разницей, что опция CREATE заменена на
опцию ALTER.

Рисунок
5

УДАЛЕНИЕ ХРАНИМОЙ ПР
ОЦЕДУРЫ

Синтаксис команды

DROP

PROCEDURE
|
PROC

<имя процедуры>

А

B

B

DROP

CREATE

А

B

B

ALTER


103

Пример
93

Задача.

Удалить процедуру
PStudent

.

Решение
.

DROP PROC PStudent

Задание
55

Создать хранимую процедуру для ввода данных в таблицу
Progress
.

Задание
56

Создать хранимую процедуру для формирования номера зачетки студе
н-
та, в котором первые две цифры соответствуют последним двум цифрам года
зачисления студента в университет. Последние 4 цифры
-

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

Задание
57

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

Задание
58

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

ВОПРОСЫ ДЛЯ САМОКОНТ
РОЛЯ К ЛАБОРАТОРНОЙ
РАБОТЕ № 7

1.

В чём преимущества хранимых процедур? Для чего обычно используются
хранимые проце
дуры?

2.

Могут ли хранимые процедуры возвращать значения?

3.

Можно ли вызвать хранимую процедуру из триггера?

4.

Каким образом передаются параметры хранимой процедуре?

5.

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

6.

Какие действия необходимо
выполнить, если параметру не присвоено знач
е-
ние по умолчанию?

7.

Каково отличие команд
ALTER

PROC

и
CREATE

PROC

при модифик
а
ции
процедур?


104


ЛАБОРАТОРНАЯ РАБОТА
№8

Цель занятия:

Знакомство с объектами СХЕМЫ БАЗЫ ДАННЫХ. Создание и
использование триггеров.

Рез
ультат занятия:
Приобретение навыков создания триггеров.

Триггер



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

объект
базы данных, триггер создается с помощью команды
CREATE

CИНТАКСИС КОМАНДЫ СО
ЗДАНИЯ ТРИГГЕРА

CREATE TRIGGER
имя

триггера


ON

<имя представления или таблицы>

[WITH ENCRYPTION]

{{{FOR|AFTER}[DELETE][,][INSERT] [,][UPDATE]&#x[-6D;ӤL;ӤT;&#x-3E4;&#x]8[,;&#x-6]8;&#x[IN3;&#xSE5R;&#x-7T4;&#x]8 [;&#x,-4];[U4;&#xPD-3;¤T4;-3];耀}|INSTEAD OF}

[WITH APPEND
]

[NOT FOR REPLICATION]

AS


SQL


ОПЕРАТОР

….

….

….>,

где

ON

-

имя объекта, для которого триггер используется.

WITH

ENCRYPTION



кодирует текст представления.

WITH

APPEND



используется для совместимости с версией 6.5

NOT

FOR

REPLICATION



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

При срабатывании триггера создаются таблицы
INSERTED
,
DELETED
.
INSERTED



для хранения добавленных записей,
DELETED



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

Таблица может иметь произвольное количество триггеров любых т
и
пов
(INSERT, UPDATE,
DELETE
). По умолчанию триггер выполняется, когда и
з-
менение данных завершено; е
сли же указать опцию INSTEAD OF, то создаётся
триггер, выполняющийся вместо изменения данных.


105

Изменить триггер можно с помощью оператора ALTER TRIGGER.

В
Microsoft

SQL

Server

2000 используются два типа триггеров AFTER
(после) и INSTEAD OF (вместо), а такж
е три их типа: INSERT, UPDATE,
DELETE
. Триггеры вставки
INSERT

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

стартуют каждый раз при удалении из таблицы записи и, как
следствие этог
о, создается таблица
DELETED
. Триггер правки
UPDATE

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

INSERTED

и
DELETED
.

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

По умолчанию все т
риггеры (INSERT, UPDATE,
DELETE
) срабатыв
а
ют
после выполнения оператора изменения данных


это триггеры AFTER. Три
г-
геры А
FTER

не используются для представлений. Кроме того, в SQL Server
2000 используются триггеры INSTEAD OF, которые выполняются вместо оп
е-
р
атора предполагаемого изменения данных. Триггеры всегда с
о
ставляют часть
транзакции. Если триггер (или другая часть транзакции) те
р
пит неудачу, то
транзакция отменяется.

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

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

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



по деловому регламенту требуется ссылаться на данные из отдел
ь-
ной таблицы;



в соответствии с регламентом необходим
о проверять дельту (ра
з-
ницу между величинами до и после модификации);


1
06



возникает необходимость в написании собственных сообщений об
ошибках.

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

Пример
94

Задача.

Написать триггер, запрещающий корректировать данные в таблице
PROGRESS

между сессиями.

Решение
.

CREATE TRIGGER ProgressTerm


ON PROGRESS


FOR INSERT, U
PDATE, DELETE

AS


IF EXISTS

( SELECT 'TRUE'



FROM Progress


WHERE (DATEPART(mm,getDATE())�'01' AND NTerm %2=1)


OR (DATEPART(mm,getDATE()�)'06' AND NTerm %2=0))

BEGIN

RAISERROR('Нельзя исправлять оценку!!!',20,1)

/*
--

Откат транзакции в случае возникновения ошибки*/


ROLLBACK

TRAN

END

Теперь любая попытка вставить или изменить данные в период, отли
ч
ный
от оговоренного, например попытка 14 декабря выполнить действия

UPDATE Progress SET mark=2 WHERE NRecordBook='050001'

I
NSERT INTO progress VALUES ('050001',1,2,1,4,5)

вызовет сообщение:


Server
:
Msg

50000

Нельзя исправлять оценку!!!


107

Пример
95

Задача.

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

Решение
.

CREATE TRIGGER ProgressTerm


ON PROGRESS


FOR INSERT, UPDATE, DELETE

AS


IF EXISTS


( SELECT 'TRUE'



FROM progress





OR (DATEPART(mm

BEGIN


RAISERROR
('Сессия завершена! Правка запрещена !!!',16,1)

/*
--

Откат транзакции в случае возникновения ошибки*/


ROLLBACK

TRAN

END

Теперь попытка ввода или редактирования данных в период между се
с-
сиями:

UP
DATE

Progress SET mark=2 WHERE NRecordBook='050001'

INSERT INTO Progress


VALUES ('050001',1,2,1,4,5)


UPDATE Progress SET mark=2


WHERE

NRecordBook
='050001'

потерпит неудачу и будет выдано сообщение

Server
:
Msg

50000

Сессия завершена! Правка запрещена !!!

Пример
96

Задача.

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

Решение

CREATE TRIGGER StudentProgress


ON Student


108


FOR INSERT, UPDATE, DELETE

AS


DECLARE @COU
NT int


SELECT @COUNT=COUNT(*) FROM DELETED

--

Проверяем удалялись ли из главной таблицы
Student

какие
-
либо записи.

--

Если да, то удаление необходимо выполнить и из зависимой таблицы

IF @COU�NT0


BEGIN


DELETE FROM PROGRESS



FROM DELETED D



JOIN PROGRESS P


ON D.NRecordBook=P.NRecordBook


END

Пример
97

Задача.

Создать триггер, запрещающий исправлять оценку в отношении P
r
o
gress

на более высокую.

Решение

CREATE TRIGGER Update1Progress


ON PROGRESS
FOR UPDATE


AS


IF EXISTS


( SELECT 'TRUE'



FROM INSERTED I


LEFT JOIN DELETED D


ON D.NRecordBook=I.NRecordBook



WHERE I.mar�kD.mark)

BEGIN


RAISERROR
('Нельзя исправлять оценку!!!',16,1)

--

Откат транзакции в случа
е возникновения ошибки


ROLLBACK

TRAN

END

Теперь выполнение команды, пытающейся заменить оценку 3 на оценку 4,

UPDATE Progress

SET mark=4

WHERE

NRecordBook
='050001'

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


109

Server
:
Msg

50000

Нельзя исправлять оценку!!!

Триггеры м
ожно включать и выключать с помощью команды ALTER.

C
ИНТАКСИС

КОМАНДЫ

ALTER TABLE

ALTER TABLE
имя

таблицы


ENABLE|DISABLEäN4;ੋL;Ӥ|;&#x-4D4;&#xISA4; L-3; TRIGGER ALL|
имя

триггера
��

Задание
59

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

Зада
ние
60

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

Задание
61

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

Задание
62

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

ВОПРОСЫ ДЛЯ САМОКОНТ
РОЛЯ К ЛАБОРАТОРНОЙ
РА
БОТЕ № 8

1.

С каким объектом базы данных связан триггер и как запускается триггер?

2.

Какие существуют типы триггеров в SQL Server 2000 (по типу операции, по
поведению)?

3.

Сколько триггеров каждого типа можно создать для одной таблицы?

4.

Какие операции не могут выпо
лняться в триггере?

5.

С помощью какой команды изменяются параметры триггера?

6.

С помощью какой команды удаляется триггер, кто обладает правом удал
е-
ния триггера?

7.

Какие таблицы автоматически создаются при вызове триггера? От чего зав
и-
сит содержимое этих таблиц?

8.

В чём сходство и отличие триггера и хранимой процедуры?


110

ЗАДАНИЯ В ТЕСТОВОЙ Ф
ОРМЕ

I
.
Вашему вниманию предлагаются задания, в которых могут быть 1, 2, 3 и
более правильных ответов. Обвести кружком номера всех правильных о
т-
ветов.

Задание 1. В процессе жизни
базы данных наибольшим изменениям подверж
е
ны

1)

Внешний ключ

2)

Первичный ключ

3)

Мощность отношения

4)

Степень отношения

5)

Схема отношения

Задание 2. Даны схемы отношений трех отношений

R1=№ книги, Имя автора

R2=№ книги, Дата выдачи книги, № читательского билета

R3
=№ читательского билета, Имя читателя, № телефона

Внешние ключи не содержат

1)

1 и 2 отношения

2)

1 и 3 отношения

3)

2 и 3 отношения

4)

только 1 отношение

5)

только 2 отношение

Задание 3. Не

относится

к

Data Manipulation Language
команда

1)

INSERT

2)

UPDATE

3)

DROP

4)

DELETE

5)

SELEC
T

Задание 4.

Предложение, которое позволяет исключить неопределенность из
результата запроса

1)

Mark�NULL

2)

Mark NOT NULL

3)

Mark IS NOT NULL

4)

Mark IS NULL

5)

Mark = NULL

Задание 5. Операции удаления не препятствует наличие в предcтавлении

1)

столбцов с агрегатными фун
кциями.

2)

Столбцов с выражениями.


111

3)

Столбцов с признаком NOT NULL.

4)

Столбцов, входящих в ограничение PRIMARY KEY.

5)

Условий соединения.

Задание 6. Подзапрос в команде
SELECT

может иметь место в предложении

1)


FROM

2)


WHERE

3)


GROUP BY

4)


HAVING

5)


ORDER BY

Задание

7.
Тольк
о на уровне столбца действует ограничение

1)


CHECK

2)


UNIQUE

3)


NOT NULL

4)


PRIMARY KEY

5)


FOREIGN KEY

Задание 8. Агрегатные функции могут использоваться в предложении

1)

SELECT

2)

WHERE

3)

GROUP BY

4)

HAVING

5)

ORDER BY

Задание

9.
При старте триггера FOR UPDATE создаются таблицы

1)

INSERTED

2)

UPDATED

3)

DELETED

4)

INSERTED
и

DELETED

5)

UPDATED и DELETED

Задание 10. При реализации вывода имен и номеров зачеток студентов, не а
т-
тестованных по тем или иным предметам, ошибка в синтаксисе команды доп
у-
щена в строке

1)

SELECT Student.NRecordBook, S
t
name

ФИО

2)

FROM Student, Progress

3)

WHERE Student.
NRecordBook= Progress. NRecordBook

4)


AND MARK NULL;

Задание 11. Некорректной является команда


112

1)

SELECT * from Progress Where mark = ANY(Select mark from Pr
o-
gress Where CodeSubject=1)

2)

SELECT * from Progress Where mar
k ANY(Select mark from Pr
o
gress
Where CodeSubject=1)

3)

SELECT * from Progress Where mark IN (Select mark from Progress
Where CodeSubject=1)

4)

SELECT mark from Progress Where mar�kall(Select mark from Pr
o-
gress Where CodeSubject=1)

5)

SELECT * from Progress Where

mar�kall(Select mark from Pr
o
gress
Where CodeSubject=1)

Задание 12.

Предложение, которое не содержится в команде для вывода имен
студентов, чьи оценки выше, чем средняя оценка в их группе.

1)

FROM Student St,Progress Pr

2)

FROM Progress P1,Student S1

3)

GROUP BY C
odeGroup

4)

WHERE St.NRecordBook=Pr.NRecordBook

5)

AND mar�k

6)

WHERE S1.IDGroup=St.IDGroup

7)

SELECT AVG(mark)

8)


AND S1.NRecordBook=P1.NRecordBook

9)

SELECT Sname,Mark

Задание

13.
В команде, редактирующей столбец MarkAVG таблицы Student, не
используется предложение

1)

SELE
CT AVG(Mark)

2)

FROM Student

3)

HAVING Student. NRecordBook= Progress. NRecordBook

4)

UPDATE Student SET MarkAVG=

5)

GROUP BY NRecordBook

6)

FROM Progress

Задание 14. Агрегатные функции могут использоваться в предложении

1)

WHERE

2)

HAVING

3)

SELECT

4)

ORDER BY

5)

GROUP BY

Задание

1
5.
Операции редактирования не препятствует наличие в представл
е
нии

1)

столбцов с агрегатными функциями.

2)

Столбцов с выражениями.

3)

Столбцов с признаком
not

null
.

4)

Столбцов, входящих в ограничение
primary

key
.


113

5)

Условий соединения.

Задание 16. Выражение, являющееся
истинным

1)

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

2)

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

3)

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

4)

Одна и та же комбинация столбцов не может быть объявлена одн
о-
временно в качестве

уникального и первичного ключа.

5)

Одна и та же комбинация столбцов может быть объявлена одновр
е-
менно в качестве уникального и первичного ключа.

Задание 17. При добавлении столбца в таблицу корректна команда

1)

ALTER TABLE Progress ALTER COLUMN Mark SmallInt

NOT NULL;

2)

ALTER TABLE Progress ADD COLUMN Mark SmallInt NOT NULL;

3)

ALTER TABLE Progress ADD Mark SmallInt NOT NULL;

4)

ALTER TABLE Progress ADD Mark SmallInt NOT NULL;

5)

ALTER TABLE Progress ADD Mark SmallInt;

Задание 18. Команда, не являющаяся командой T
-
SQL

1)

GO

2)

SET

3)

USE

4)

EXEC

5)

SELECT

Задание 19. В запросе


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

1.

SELECT AVG(mark)

2.

WHERE St.NRecordBook=Pr.NRecordBook AND mark�

3.

WHERE S
1.IDGroup=St.IDGroup AND S1.NRecordBook=P1.NRecordBook

4.

FROM Student St,Progress Pr

5.

FROM Progress P1,Student S1

6.

SELECT Sname,Mark

1)

6;4;1;3;5;2

2)

6;5;1;3;2;4

3)

6;5;4;2;1;3

4)

6;4;2;1;5;3

5)

6;4;4;2;1;3

Задание 20. Таблица S
G
roup была создана командой

CREATE TABLE Sgr
oup (IDGroup INT IDENTITY

CONSTRAINT SgroupPrimary PRIMARY KEY ,


NameGroup Varchar(15)NOT NULL

CONSTRAINT SgroupNameGroupUnique UNIQUE)

Команда, позволяющая внести в таблицу данные о группе ИСТ
-
03, это


114

1)

INSERT INTO SGroup VALUES(
'ИСТ
-
03')

2)

INSERT INTO SGroup NameGroup VALUES('ИСТ
-
03')

3)

INSERT INTO SGroup (IDGroup,NameGroup) VALUES('ИСТ
-
03')

4)

INSERT INTO SGroup (IDGroup,NameGroup) VALUES('', 'ИСТ
-
03')

5)

INSERT INTO SGroup (IDGroup,NameGroup) VALUES(@@IDENTITY,
'ИСТ
-
03')

II
.
Установи
ть правильную последовательность
:

Задание 1. В запросе, выводящем имена студентов, чьи оценки выше, чем сре
д-
няя оценка в их группе

1)

FROM Student St,Progress Pr

2)

FROM Progress P1,Student S1

3)

WHERE St.NRecordBook=Pr.NRecordBook

4)

AND mar�k

5)

WHERE S1.IDGroup=St.IDG
roup

6)

SELECT AVG(mark)

7)


AND S1.NRecordBook=P1.NRecordBook

8)

SELECT StName,Mark

Задание

2.
В команде, редактирующей столбец MarkAVG таблицы Student

1)

SELECT AVG(Mark)

2)

HAVING Student. NRecordBook= Progress. NRecordBook

3)

UPDATE Student SET MarkAVG=

4)

GROUP BY NReco
rdBook

5)

FROM Progress

Задание 3. Действия при назначении внешнего ключа

1)

задать тип ограничения

2)

задать имя ограничения

3)

указать ссылочную таблицу

4)

перечислить столбцы, входящие во внешний ключ

5)

определить столбцы, являющиеся первичным ключом ссылочной
таблицы

Задание 4. Синтаксис оператора
SELECT

1)

SELECT

2)

ORDER BY

3)

HAVING

4)

GROUP BY

5)

СПИСОК СТОЛБЦОВ

6)

WHERE

7)

FROM


115

III
.
Установить соответствие
:


1.

Название

пр
о
цедуры

Назначение процедуры


1)

SP
_
HELP

2)

SP
_
HELPTEXT

3)

SP
_
DEPENDS

4)

SP
_
RENAME

A)

переименования представления


B)

возврата с
ведений о созданном представлении

C)

для получения исходного текста представления


D)

нахождения списка таблиц, от которого зависит
представление

ОТВЕТЫ: 1 _, 2 _, 3 _, 4 _.


2.

Тип данных

Описание


1) BINARY

A)

денежная величина

2)
VARCHAR

B)

данные времени и дат
ы

3)
INT

C)

точные числовые данные

4)
FLOAT

D)

числа с плавающей запятой

5)
NUMERIC

E)

целые значения

6)
DATETIME

F)

строковые данные

7)
MONEY

G)

битовая информация

ОТВЕТЫ: 1 _, 2 _, 3 _, 4 _, 5 _, 6 _, 7 _.


3.

Ограничение

целос
т
ности

Описание


1)

CHECK

2)

NULL

3)

UNIQUE

4)

PRIMARY KEY

5)

FOREIGN KEY

6)

CASCADE

7)

DEFAULT

A)

условие на значение

B)

на определённость значения

C)

значение по умолчанию

D)

уникальность значений

E)

ссылочная целостность данных

F)

обновление значений в зависимых та
б-
лицах


ОТВЕТЫ: 1 _, 2 _, 3 _, 4 _, 5 _, 6 _, 7 _.


4.

Агрегатная
функция

Результат действия


1)

SUM


2)

AVG

3)

MIN

4)

MAX

5)

COUNT

6)

COUNT(*)

A)

сумма значений столбца

B)

среднее всех значений столбца

C)

наименьшее среди всех значений столбца

D)

наибольшее среди всех значений столбца

E)

количество значений, содержащихся в столбце

F)

количе
ство строк в таблице результатов запроса

ОТВЕТЫ: 1 _, 2 _, 3 _, 4 _, 5 _, 6 _.




116

5.

Ключевое слово

Смысл ключевого слова


1)

PRIMARY KEY

2)

FOREIGN KEY


3)

UNIQUE

4)

CHECK


5)

REFERENCES


A)

столбец или столбцы, которые образуют пе
р-
вичный ключ таблицы

B)

столбец или столбцы,

которые образуют вне
ш-
ний ключ таблицы

C)

таблица, связь с которой создаёт внешний ключ

D)

условие уникальности значений столбцов


условие на проверку значения столбца

ОТВЕТЫ: 1 _, 2 _, 3 _, 4 _, 5 _.


IV
.

Дополнить:

1.

Для задания в столбце неопределённых

данных используется опция
__________.

2.

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

3.

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

значений в заданном списке.

4.

Избежать вывода дублирующих строк позволяет ключевое слово
_________________.

5.

Сортируются строки в наборе, возвращаемом запросом с помощью д
и-
рективы _______________.

6.

Условие включения группы в итоговый набор содержи
т директива
_________________.

7.

Объединение результатов двух или более запросов в один результ
и
р
у-
ющий набор достигается с помощью оператора__________.

8.

Обновляет значение поля в строке оператор ___________.

9.

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

10.

Таблица

Progress
содержит

столбцы

NRecordBook, IDSubject, IDReport,
NTerm, Mark.

Приведенная ниже процедура должна осуществлять ввод строки в та
б-
лицы
Subject
,
Report

и
P
rogress
.

1)

Declare @ICS SmallInt,

2)


@ICR SmallInt

3)

Insert INTO Subject (NAMESUBJECT) VALUES ('ООП')

4)

_____________________

5)

Insert INTO Report (NAMEReport) VALUES ('Экзамен')

6)

_____________________

7)

INSERT INTO PROGRESS VALUES('050002',0,@ICS,@ICR,1,
5)



117

ПРИЛОЖЕНИЯ

Приложение
1
. Типы данных используемые СУБД
SQL

Server

2000




Тип данных

Описание

1

2

3

1.


VARCHAR (
n
)

Символьная строка переменной длины,
n

задает макс
и-
мальную длину строки в байтах. Максимально допуст
и-
мый ра
змер строки 2000 символов. Минимальный



1 символ,
n

не является обязател
ь
ным параметром и по
умолчанию равно 1.

2.


CHAR(
n
)

Символьная строка фиксированной длины,
n

задает ма
к-
симальную длину строки в байтах. Максимально доп
у-
с
тимый размер строки 2000 символ
ов. Мин
и
мальный


1 символ.

3.


NUM
ERIC
(
p
,
s
)

DECIMAL(p,s)

Число, содержащее точные числовые данные: всего
p

символов из них
s

после запятой.

4.


REAL

(
FLOAT
)

Числовые значения с плавающей запятой обычной
(двойной) точности, состоят из двух частей: манти
с
сы и
поря
д
ка

5.


DATE
TIME


Дата и время в миллисекундах с 01.01.1753 по
31.12.9999.Сохраняет значение в 8 байтах

6.



Календарная дата с точностью до минуты. Сохраняет
значение в 4 байтах. Диапазон с 01.01.1900 по
06.06.2079

7.


MONEY

(
SMALLMONEY
)

Денеж
ный тип данных размером 8 (4) байт. Имеют ч
е-
тыре разряда после запятой.

8.


BINARY(n)

VARBINARY(n)

Последовательность 1 и 0, представленных в 16
-
ричной
системе счисления и организованных в пары

9.


INT

BIGINT

TINYINT

SMALLINT

Целочисленные значения (скалярные
величины). Разн
и-
ца между этими типами данных заключается в длине с
о-
хр
а
няемых значений

10.


BIT

Булевы значения 0 или 1

11.


TEXT

IMAGE

Битовая и текстовая информация до 2 Гбайт




118

Приложение
2
. Пример заполнения таблиц

Таблица
Studen
t

(Студент)


Таблица SWork (Виды работ
)


119

Таблица Subject (Предмет)



Таблица
Report

(Виды отчетности)


Таблица
SGroup

(Группа)


Таблица
Teacher

(Преподаватель)



Таблица Progress (Успеваемость)





120

Таблица
Uplan
(Учебный план)




Приложение
3
. Агрегатные функции



Синтаксис функции

Назначение функции

1

AVG([
ALL
|
DISTINCT
]
имя_столбца)

Среднее значение в группе для [всех | неод
и-
наковых] строк

2

COUNT([
ALL
|
DISTINC
T
]
имя
_
столбца
)

Количество [всех | неодинаковых] значений в

группе

3

COUNT
(*)

Количество полученных строк в группе

4

MAX(имя_столбца)

Максимальное значение в группе

5

MIN(имя_столбца)

Минимальное значение в группе

6

STDDEV(имя_столбца)

Среднеквадратичное отклонение всех знач
е-
ний

7

SUM([
ALL
|
DISTINCT
]
имя
_
столб
ца
)

Сумма значений для [всех | неодинаковых]
строк

8

VAR(имя_столбца)

Дисперсия всех значений



121

Приложение
4
. Функции обработки дат



Синтаксис функции

Назначение функции

1.


DATEADD
(
частьДаты
,
n
,
Date
)

Date



переменная типа
Datetime
. Фун
к-
ция добавляет к
Date

величину
частьД
а-
ты
, умноженную на
n

раз

2.


DATEDIFF (
частьДаты
,

Date1
,

Date2
)

Функция возвращает количество величин
частьДаты
между двумя датами

3.


DATENAME
(
частьДаты
,

Date
)

Функция возвращает название элемента
частьДаты
д
ля заданного

Date

4.


DATENAME
(
частьДаты
,

Date
)

Функция возвращает целое числовое зн
а-
чение элемента
частьДаты
для заданн
о-
го

Date

5.


DAY

(
Date
)

Функция возвращает целое число дней в
Date

6.


GETDATE

()

Функция возвращает текущие системные
дату и время

7.


MONTH

(
Date
)

Функция возвращает целое число мес
я-
цев в
Date

8.


YEAR

(
Date
)

Функция возвращает целое число лет в
Date


Параметр
частьДаты
указывает функции, с какой частью переменной типа
Datetime

следует выполнить требуемые операции.


Приложение
5
. Допустимые значения параметра
частьДаты



Значение параметра

Диапазон значений

1.


dd (day)

1
-
31

2.


dy (day of year)

1
-
366

3.


hh (hour)

0
-
23

4.


ms (millisecond)

0
-
999

5.


mi (minute)

0
-
59

6.


mm (month)

1
-
12

7.


qq (quarter)

1
-
4

8.


ss (second)

0
-
59

9.


wk

(week)

0
-
53

10.


dw (weekday)

1
-
7 (Sun


Sut)

11.


yy (year)

1753
-
9999



122

СПИСОК ЛИТЕРАТУРЫ


1.

Дейт К.

Введение в системы баз данных / К.

Дейт.
-

8
-
издание.
-
М.: Изд
а-
тельский дом ©Вильямсª, 2005.


1328 с.

2.

Вьейра

Р
. SQL Server 2000.
Программирование в 2 ч. / Р.

Вь
ейра.


М:
БИНОМ. Лаборатория знаний, 2004.

3.

Конноли Т. Базы данных. Проектирование, реализация и сопровождение.
Теория и практика / Т. Конноли, К. Бегг.
-

3
-
издание.


М.: Издательский
дом ©Вильямсª, 2003.

4.

Мейер М. Теория реляционных баз данных / М. Мейер
.
-

М.: Мир, 1987.


608 с.

5.

Веймаер Р. Освой самостоятельно Microsoft SQL Server 2000 за 21 день /
Р.

Веймаер, Р. Сотел.


М.: Издательский

дом ©Вильямсª, 2001.

6.

Роб П. Системы баз данных: проектирование, реализация и управление /
П.

Роб, К. Коронел.


5
-
из
дание.
-

СПб. БХВ


Петербург, 2004.
-

1040 с.

7.

Ульман Д. Введение в основы баз данных / Д. Ульман, Д. Уидом.
-

М.: И
з-
дательство ©Лориª, 2000


376
c
.

8.

Чен П. Модель "сущность
-
связь"
-

шаг к единому представлению о да
н
ных
//СУБД.


1995.


№3.
-

С. 137
-
158.


123

ОГЛАВЛЕНИЕ

Введение

................................
................................
................................
..................

3

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

................................
................................
....................

5

Опис
ание учебного примера.

................................
................................
...............

5

Удаление таблиц

................................
................................
.............................

11

Создание таблиц

................................
................................
.............................

11

И
мена ограничений

................................
................................
........................

13

Ограничения NULL и NOT NULL

................................
................................

14

Ограничение PRIMARY KEY

................................
................................
.......

14

Ограничение UNIQUE

................................
................................
...................

15

Ограничение FOREIGN KEY

................................
................................
........

17

Ограничение CHECK

................................
................................
.....................

19

Вопросы для самоконтроля к лабораторной работе № 1

............................

21

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

................................
................................
..................

23

Команда вставки
-

INSERT

................................
................................
...........

23

Команда обновления
-

UPDATE

................................
................................
..

26

Команда удаления
-

DELETE

................................
................................
.......

28

Ком
анда удаления
-

TRUNCATE

................................
................................
.

28

Вопросы для самоконтроля к лабораторной работе № 2

............................

29

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

................................
................................
...................

30

Команда ALTER TABLE

................................
................................
...............

30

Модификация ограничений

................................
................................
...........

30

Добавление ограничений с ограничен
ной областью проверки

.................

33

Отключение и подключение ограничений
................................
...................

35

Правила для изменения и модификации описания столбцов

....................

36

Добавление столбца

................................
................................
.......................

37

Модификация столбца

................................
................................
...................

38

Удаление столбца

................................
................................
...........................

41

Удаление таблицы при наличии на нее ссылок

................................
..........

41

Переименование таблицы

................................
................................
..............

42

Вопросы для самоконтроля к лабораторной работе № 3

............................

43

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

................................
................................
..................

44

Выборка данных из нескольких таблиц

................................
.......................

47

Наиболее часто встречающиеся ошибки при реализации запросов на
нескольких таблицах

................................
................................
......................

50

Определение условий выборки в предложении WHERE
.

..........................

51

Групповые функции и предложение GROUP BY

................................
.......

60

Наиболее часто встречающиеся ошибки при выполнении GROUP BY

...

64


124

Предложение ORDER BY

................................
................................
..............

65

Вопросы для самоконтроля к лабораторной работе № 4

............................

71

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

................................
................................
..................

73

Подзапросы

................................
................................
................................
.....

73

Вопросы для самоконтроля к лабораторной работе № 5

............................

83

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

................................
................................
...................

85

Представления

................................
................................
................................

85

Вопросы для самоконтроля к лабораторной работе № 6

............................

92

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

................................
................................
...................

93

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

................................
................................
....................

93

Оператор USE

................................
................................
................................
.

96

Оператор DECLARE

................................
................................
......................

96

Операторы SET и SELECT

................................
................................
............

96

Функция @@IDENTITY

................................
................................
................

98

Функция @@ERROR

................................
................................
.....................

98

Объявление параметров

................................
................................
.................

99

Изменение хранимых

процедур

................................
................................
..

102

Удаление хранимой процедуры

................................
................................
..

102

Вопросы для самоконтроля к лабораторной работе № 7

..........................

103

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

................................
................................
.................

104

Cинтаксис команды создания триггера

................................
......................

104

Cинтаксис команды ALTER
TABLE

................................
..........................

109

Вопросы для самоконтроля к лабораторной работе № 8

..........................

109

Задания в тестовой форме

................................
................................
...............

110

Приложения

................................
................................
................................
........

117

Список литературы

................................
................................
...........................

122




Приложенные файлы

  • pdf 18277483
    Размер файла: 1 MB Загрузок: 0

Добавить комментарий