Материал: SQL в вопросах и задачах
121
УП Р АЖ НЕ НИ Я
110.
Соз д а йте
п ред ста в ление
д ля
п олуч ения
св ед ений
обо
в сех
студ ента х
,
имеющ их
только
отлич ные
оценки
.
111.
Соз д а йте
п ред ста в ление
д ля
п олуч ения
св ед ений
о
колич еств е
студ ентов
в
ка жд ом
город е
.
112.
Соз д а йте
п ред ста в ление
д ля
п олуч ения
св ед ений
п о
ка жд ому
студ енту
:
его
ид ентифика тор
,
фа милию
,
имя
,
сред ний
и
общ ий
ба ллы
.
113.
Соз д а йте
п ред ста в ление
д ля
п олуч ения
св ед ений
о
колич еств е
э кз а менов
,
которые
сд а в а л
ка жд ый
студ ент
.
5.11.
И з м енение
з начений
в
представл ения х
К а к
уже
гов орилось
,
исп ользов а ние
кома нд
мод ифика ции
яз ыка
SQL
–
INSERT (
ВСТАВИТЬ
)
,
UPDATE (
ЗАМЕНИТЬ
)
,
и
DELETE (
УДАЛИТЬ
)
–
п рименительно
д ля
п ред ста в лений
имеет
ряд
особенностей
.
В
д оп олнение
к
а сп екта м
,
рассмотренным
в ыше
,
след ует
отметить
,
ч то
не
в се
п ред ста в ления
могут
мод ифициров а ться
.
Е сли
кома нд ы
мод ифика ции
могут
в ып олняться
в
п р ед ста в лении
,
то
п ред ста в ление
яв ляется
обнов ляемым
(
мод ифицируемым
);
в
п р отив ном
случ а е
оно
п ред на з на ч ено
только
д ля
ч тения
п ри
з а п росе
.
К а ким
образ ом
можно
оп ред елить
,
яв ляется
ли
п ред ста в ление
мод ифицируемым
?
К р итерии
обнов ляемости
п ред ста в ления
можно
сформулиров а ть
след ующ им
обра з ом
.
•
Пред ста в ление
строится
на
основ е
од ной
и
только
од ной
ба з ов ой
та блицы
.
•
Пред ста в ление
д олжно
сод ержа ть
п ерв ич ный
ключ
ба з ов ой
та блицы
.
•
Пред ста в ление
не
д олжно
иметь
ника ких
п олей
,
которые
п ред ста в ляют
собой
а грегирующ ие
функции
.
•
Пред ста в ление
не
д олжно
сод ержа ть
DISTINCT
в
св оем
оп ред елении
.
•
Пред ста в ление
не
д олжно
исп ользов а ть
GROUP BY
или
HAVING
в
св оем
оп ред елении
.
•
Пред ста в ление
не
д олжно
исп ользов а ть
п од з а п росы
.
•
Пред ста в ление
м о ж ет
бы т ь
исп ользов а но
в
д ругом
п р ед ста в лении
,
но
э то
п ред ста в ление
д олжно
быть
та кже
мод ифицир уемыми
.
•
Пред ста в ление
не
д олжно
исп ользов а ть
в
ка ч еств е
п олей
в ыв од а
122
конста нты
или
в ыражения
з на ч ений
.
Суть
э тих
огранич ений
в
том
,
ч то
обнов ляемые
п ред ста в ления
фа ктич ески
п од обны
окна м
в
ба з ов ых
та блица х
.
О ни
п ока з ыв а ют
информа цию
из
ба з ов ой
та блицы
,
огранич ив а я
оп ред еленные
ее
строки
(
исп ользов а нием
соотв етств ующ их
п ред ика тов
)
или
сп ециа льно
именов а нные
столбцы
(
с
исключ ениями
).
Н о
п ри
этом
п ред ста в ления
в ыв од ят
з на ч ения
без
и х
о бр а бо т ки
с
исп ользов а нием
а грегир ующ их
функций
и
груп п иров ки
.
О ни
та кже
не
срав нив а ют
строки
та блиц
д руг
с
д ругом
(
ка к
э то
имеет
место
в
объед инениях
и
п од з а п роса х
,
или
п ри
исп ользов а нии
DISTINCT
).
Ра з лич ия
межд у
мод ифицируемыми
(
обнов ляемыми
)
п ред ста в лениями
и
п ред ста в лениями
“
т о лько
д ля
чт ени я
”
не
случ а йны
.
О бнов ляемые
п ред ста в ления
в
основ ном
исп ользуются
а на логич но
ба з ов ым
та блица м
.
Пользов а тели
могут
д а же
не
з на ть
,
яв ляется
ли
з а п рашив а емый
ими
объект
ба з ов ой
та блицей
или
п ред ста в лением
.
Э то
п рев осход ный
меха низ м
з а щ иты
д ля
скр ытия
ч а стей
та блицы
,
которые
яв ляются
конфид енциа льными
ил и
не
п ред на з на ч ены
д а нному
п ользов а телю
.
Н е
мод ифицируемые
п ред ста в ления
,
с
д ругой
стороны
,
п оз в оляют
более
рациона льно
п олуч а ть
и
п ереформа тиров а ть
д а нные
.
С
их
п омощ ью
формируются
библиотеки
сложных
з а п росов
,
которые
могут
з а тем
исп ользов а ться
в
з а п р оса х
д ля
п олуч ения
информа ции
са мостоятельно
(
на п ример
,
в
объед инениях
).
Э ти
п ред ста в ления
могут
та кже
иметь
з на ч ение
п ри
решении
з а д а ч
з а щ иты
и
безоп а сности
д а нных
.
Н а п р имер
,
можно
п ред оста в ить
некоторым
п ользов а телям
в оз можность
п олуч ения
а грега тных
д а нных
(
та ких
,
ка к
усред ненное
з на ч ение
оценки
студ ента
),
не
п ока з ыв а я
конкретных
з на ч ений
оценок
и
,
тем
более
,
не
п оз в оляя
их
мод ифициров а ть
.
5.12.
П рим еры
обновл я ем ы х
и
не
обновл я ем ы х
представл ений
Пример
1
CREATE VIEW
DATEEXAM
(EXAM_DATE
,
QUANTITY)
AS
SELECT
EXAM
_
DATE
,
COUNT
(*)
FROM
EXAM
_
MARKS
GROUP
BY
EXAM
_
DATE
;
123
Д а нное
п ред ста в ление
яв ляется
не
о бно в ляем ы м
из
-
з а
п рисутств ия
в
нем
а грегир ующ ей
функции
и
GROUP
BY
.
Пример
2
CREATE
VIEW
LCUSTT
AS
SELECT
*
FROM
UNIVERSITY
WHERE
CITY
=
‘
М о сква
’
;
Э то
–
о бно вляем о е
п ред ста в ление
.
Пример
3
CREATE
VIEW
SSTUD
(
SURNAME1
,
NUMB
,
KUR
)
AS
SELECT
SURNAME
,
STUDENT
_
ID
,
KURS*2
FROM
STUDENT
WHERE
CITY
=
‘
М о сква
’
;
Э то
п ред ста в ление
–
не
м о д и ф и ци р уем о е
из
-
з а
на лич ия
в ыражения
“
KURS
*
2
”
.
Пример
4
CREATE VIEW
STUD3
AS SELECT *
FROM
STUDENT
WHERE
STUDENT
_
ID
IN
(
SELECT
MARK
FROM
EXAM
_
MARKS
WHERE
EXAM
_
DATE
=
‘
10/02/1999
’
);
Пред ста в ление
не
м о д и ф и ци р ует ся
из
-
з а
п рисутств ия
в
нем
п од з а п роса
.
В
некоторых
п рогра мма х
э то
может
быть
п р иемлемым
.
Пример
5
CREATE
VIEW
SOMEMARK
AS
SELECT
STUDENT
_
ID
,
SUBJ
_
ID
,
MARK
FROM
EXAM
_
MARKS
WHERE
EXAM
_
DATE
IN
(
‘
10/02/1999
’
,
‘
10/06/1999
’
);
Э то
–
о бно вляем о е
п ред ста в ление
.
124
5.13.
П редставл ения
,
баз ирую щ иеся
на
других
представл ения х
О тносительно
исп ользов а ния
п ред ложения
WITH
CHECK
OPTION
след ует
отметить
,
ч то
в
ста нд а р те
SQL
э то
п ред ложение
не
п ред усма трив а ет
ка ска д ного
из менения
,
то
есть
оно
п рименяется
только
в
п ред ста в лениях
,
в
которых
оно
оп ред елено
,
но
не
расп ространяется
на
д ругие
п ред ста в ления
,
основ а нные
на
э том
п ред ста в лении
.
Н а п р имер
,
в
п ред ыд ущ ем
п римере
CREATE
VIEW
HIGH
_
RATING
AS
SELECT
UNIV
_
ID
,
RATING
FROM
UNIVERSITY
WHERE
RATING
>=
400
WITH
CHECK
OPTION
;
п оп ытка
в ста в ить
или
обнов ить
з на ч ения
п оля
RATING
,
отлич ные
от
400,
буд ет
отв ергнута
,
п оскольку
п рисутств ует
ука з а ние
WITH CHECK
OPTION
.
О д на ко
,
если
соз д а ется
в торое
п ред ста в ление
(
с
тем
же
сод ержа нием
),
основ а нное
на
п ерв ом
:
CREATE
VIEW
MYRATING
AS
SELECT
*
FROM
HIGH
_
RATING
;
то
в в од
в
п оле
RATING
с
п омощ ью
нижеп р ив ед енного
з а п роса
з на ч ений
,
отлич а ющ ихся
от
400,
уже
не
буд ет
отв ер гнуто
ка к
ошибоч ное
.
То
есть
след ующ ий
з а п рос
UPDATE
MYRATING
SET
RATING
=
200
WHERE
UNIV
_
ID
=
18
;
не
буд ет
отв ергнут
ка к
не
корректный
,
и
,
п осле
его
в ып олнения
,
строки
с
обнов ленными
д а нными
исч ез нут
из
ка к
из
п ред ста в ления
MYRATING
,
та к
и
из
п ред ста в ления
HIGH
_
RATING
.
Пред ложение
WITH
CHECK
OPTION
п росто
га р а нтирует
,
ч то
любое
обнов ление
в
п ред ста в лении
осущ еств ляется
в
соотв етств ии
со
з на ч ениями
,
ука з а нными
именно
д ля
эт о го
п ред ста в ления
.
О бнов ление
д ругих
п ред ста в лений
,
ба з ир ующ ихся
на
п ер в ом
текущ ем
,
п ри
э том
д оп устимым
,
если
э ти
п ред ста в ления
не
з а щ ищ ены
п ред ложениями
WITH
CHECK
OPTION
,
з а д а нными
именно
д ля
них
.
Пред ложения
WITH
CHECK
OPTION
п р ов еряют
125
п ред ика ты
только
того
п ред ста в ления
,
в
котором
они
сод ержа тся
.
Пр и
э том
не
яв ляется
в ыход ом
из
п оложения
и
соз д а ние
п ред ста в ления
MYRATING
с
п омощ ью
з а п роса
CREATE
VIEW
MYRATING
AS
SELECT
*
FROM
HIGH
_
RATING
WITH
CHECK
OPTION
;
УП Р АЖ НЕ НИ Я
114.
К а кие
из
п ред ста в ленных
ниже
п ред ста в лений
яв ляются
обнов ляемыми
?
a)
CREATE
VIEW
DAILYEXAM
AS
SELECT
DISTINCT
STUDENT
_
ID
,
SUBJ
_
ID
,
MARK
,
EXAM
_
DATE
FROM
EXAM
_
MARKS
;
b)
CREATE VIEW
CUSTALS
AS
SELECT
SUBJECT
.
SUBJ
_
ID
,
SUM
(
MARK
)
AS
MARK1
FROM
SUBJECT
,
EXAM
_
MARKS
WHERE
SUBJECT
.
SUBJ
_
ID
=
EXAM
_
MARKS
.
SUBJ
_
ID
GROUP
BY
SUBJECT
.
SUBJ
_
ID
;
c)
CREATE VIEW
THIRDEXAM
AS
SELECT
*
FROM
DAILYEXAM
WHERE
EXAM
_
DATE
=
‘
10/02/1999
’
;
d)
CREATE VIEW
NULLCITIES
AS
SELECT
STUDENT
_
ID
,
SURNAME
,
CITY
FROM
STUDENT
WHERE
CITY
IS
NULL
OR
SURNAME
BETWEEN
‘
А
’
AND
‘
Д
’
;
115.
Соз д а йте
п ред ста в ление
та блицы
STUDENT
с
именем
STIP
,
в ключ а ющ ее
п оля
STIPEND
и
STUDENT
_
ID
и
п оз в оляющ ее
в в од ить
или
из менять
з на ч ение
п оля
STIPEND
(
стип енд ия
),
но
только
в
п р ед ела х
от
100
д о
200.