ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 02.04.2021
Просмотров: 3470
Скачиваний: 5
51
След ует
обратить
в нима ние
,
ч то
п рив ед енный
в ыше
з а п рос
корректен
только
в
том
случ а е
,
если
в
результа те
в ып олнения
ука з а нного
в
скобка х
по д
з а п роса
в оз в р а щ а ется
ед и нст в енно е
зна чени е
.
Е сли
в
рез ульта те
в ып олнения
п од з а п роса
буд ет
в оз в ра щ ено
несколько
з на ч ений
,
то
э тот
п од з а п рос
буд ет
ошибоч ным
.
В
д а нном
п римере
это
п р оиз ойд ет
,
если
в
та блице
STUDENT
буд ет
несколько
з а п исей
со
з на ч ениями
п оля
SURNAME
=
‘
Петр о в’
.
В
некоторых
случ а ях
д ля
га р а нтии
п олуч ения
ед инств енного
з на ч ения
в
результа те
в ып олнения
п од з а п роса
исп ользуется
DISTINCT
.
О д ним
из
в ид ов
функций
,
которые
а в тома тич ески
в сегд а
в ыд а ют
в
рез ульта те
ед инств енное
з на ч ение
д ля
любого
колич еств а
строк
,
яв ляются
а грегирующ ие
функции
.
О п ера тор
IN
та кже
широко
п рименяется
в
п од з а п роса х
.
О н
з а д а ет
сп исок
з на ч ений
,
с
которыми
сра в нив а ются
д ругие
з на ч ения
д ля
оп ред еления
истинности
з а д а в а емого
э тим
оп ератором
п ред ика та
.
Д а нные
обо
в сех
оценка х
(
та блица
EXAM
_
MARKS
)
студ ентов
из
Воронежа
можно
в ыбр а ть
с
п омощ ью
след ующ его
з а п роса
:
SELECT
*
FROM
EXAM
_
MARKS
WHERE
STUDENT
_
ID
IN
(
SELECT
STUDENT
_
ID
FROM
STUDENT
WHERE
CITY
=
‘
Во р о н еж
’
);
Под з а п росы
можно
п р именять
в нутр и
п ред ложения
HAVING
.
Пусть
требуется
оп ред елить
колич еств о
п ред метов
обуч ения
с
оценкой
,
п рев ыша ющ ей
ср ед нее
з на ч ение
оценки
студ ента
с
ид ентифика тором
301:
SELECT
COUNT
(
DISTINCT
SUBJ
_
ID)
,
MARK
FROM
EXAM
_
MARKS
GROUP BY
MARK
HAVING
MARK
>
(
SELECT AVG
(
MARK
)
FROM
EXAM
_
MARKS
WHERE
STUDENT
_
ID
=
301
);
52
2.9.
Ф орм ирование
свя з анны х
подз апросов
При
исп ользов а нии
п од з а п р осов
в о
в нутреннем
з а п росе
можно
ссыла ться
на
та блицу
,
имя
которой
ука з а но
в
п ред ложении
FROM
в нешнего
з а п роса
.
В
э том
случ а е
та кой
св яза нны й
п од з а п рос
в ып олняется
п о
од ному
раз у
д ля
ка ж д о й
строки
та блицы
основ ного
з а п роса
.
П ример
:
в ыбрать
св ед ения
обо
в сех
п ред мета х
обуч ения
,
п о
котор ым
п ров од ился
э кз а мен
20
янв а р я
1999
г
.
SELECT
*
FROM
SUBJECT
SU
WHERE
‘
20/01/1999
’
IN
(
SELECT
EXAM
_
DATE
FROM
EXAM
_
MARKS
EX
WHERE
SU
.
SUBJ
_
ID
=
EX
.
SUBJ
_
ID
);
В
некоторых
СУ Б Д
д ля
в ып ол нения
э того
з а п р оса
,
в оз можно
,
п отребуется
п реобраз ов а ние
з на ч ения
д а ты
в
симв ольный
тип
.
В
п рив ед енном
з а п росе
SU
и
EX
яв ляются
п сев д онима ми
(
а лиа са ми
),
то
есть
сп ециа льно
в в од имыми
имена ми
,
которые
могут
быть
исп ользов а ны
в
д а нном
з а п росе
в место
на стоящ их
имен
.
В
п рив ед енном
п римере
они
исп ользуются
в место
имен
та блиц
SUBJECT
и
EXAM
_
MARKS
. .
Э ту
же
з а д а ч у
можно
решить
с
п омощ ью
оп ера ции
соед инения
та блиц
:
SELECT
DISTINCT
SU
.
SUBJ_ID
,
SUBJ
_
NAME
,
HOUR
,
SEMESTER
FROM
SUBJECT
FIRST
,
EXAM
_
MARKS
SECOND
WHERE
FIRST
.
SUBJ
_
ID
=
SECOND
.
SUBJ
_
ID
AND
SECOND
.
EXAM
_
DATE
=
’
20/01/1999
’
;
В
э том
в ыражении
а лиа са ми
та блиц
яв ляются
имена
FIRST
и
SECOND
.
М ожно
исп ользов а ть
п од з а п росы
,
св яз ыв а ющ ие
та блицу
со
св оей
собств енной
коп ией
.
Н а п ример
,
на д о
на йти
ид ентифика торы
,
фа милии
и
стип енд ии
студ ентов
,
п олуч а ющ их
стип енд ию
в ыше
сред ней
на
курсе
,
на
котором
они
уч а тся
.
SELECT
DISTINCT
STUDENT
_
ID
,
SURNAME
,
STIPEND
FROM
STUDENT E1
WHERE
STIPEND
>
(SELECT AVG
(
STIPEND
)
FROM
STUDENT E2
53
WHERE
E1
.
KURS
=
E2
.
KURS
);
Тот
же
результа т
можно
п олуч ить
с
п омощ ью
след ующ его
з а п роса
:
SELECT DISTINCT
STUDENT
_
ID
,
SURNAME
,
STIPEND
FROM
STUDENT E1
,
(SELECT
KURS
,
AVG
(
STIPEND
)
AS
AVG
_
STIPEND
FROM
STUDENT E2
GROUP BY
E2
.
KURS
)
E3
WHERE
E1
.
STIPEND
>
AVG
_
STIPEND
AND
E1
.
KURS
=
E3
.
KURS
;
О братите
в нима ние
–
в торой
з а п р ос
буд ет
в ып ол нен
гораз д о
быстрее
.
Д ело
в
том
,
ч то
в
п ерв ом
в а р иа нте
з а п роса
а грегирующ а я
функция
AVG
в ып олняется
на д
та блицей
,
ука з а нной
в
п од з а п росе
,
д ля
ка ж д о й
строки
в нешнего
з а п роса
.
В
д ругом
в а риа нте
в торая
та блица
(
а лиа с
E2
)
обраба тыв а ется
а грегирующ ей
функцией
од ин
р а з
,
в
рез ульта те
ч его
формируется
в сп омога тельна я
та блица
(
в
з а п росе
она
имеет
а лиа с
E3
),
со
строка ми
которой
з а тем
соед иняются
строки
п ер в ой
та блицы
(
а лиа с
E1
).
След ует
иметь
в
в ид у
,
ч то
реа льное
в ремя
в ып олнения
з а п роса
в
большой
степ ени
з а в исит
от
оп тимиз а тор а
з а п росов
конкретной
СУ Б Д
.
2.10.
С вя з анны е
подз апросы
в
HAVING
В
раз д еле
2.4
ука з ыв а лось
,
ч то
п ред ложение
GROUP
BY
п оз в оляет
груп п ир ов а ть
в ыв од имые
SELECT
-
з а п росом
з а п иси
п о
з на ч ению
некоторого
п оля
.
Исп ользов а ние
п ред ложения
HAVING
п оз в ол яет
п ри
в ыв од е
осущ еств лять
фильтрацию
та ких
груп п
.
Пред ика т
п ред ложения
HAVING
оценив а ется
не
д ля
ка жд ой
строки
рез ульта та
,
а
д ля
ка жд ой
груп п ы
в ыход ных
з а п исей
,
сформиров а нной
п ред ложением
GROUP
BY
в нешнего
з а п роса
.
Пусть
,
на п ример
,
необход имо
п о
д а нным
из
та блицы
EXAM
_
MARKS
оп ред елить
сумму
п олуч енных
студ ента ми
оценок
(
з на ч ений
п оля
MARK
),
сгруп п иров а в
з на ч ения
оценок
п о
д а та м
э кз а менов
и
исключ ив
те
д ни
,
когд а
ч исло
студ ентов
,
сд а в а в ших
в
теч ение
д ня
э кз а мены
,
было
меньше
10.
SELECT
EXAM
_
DATE
,
SUM
(
MARK
)
FROM
EXAM
_
MARKS
A
GROUP
BY
EXAM
_
DATE
54
HAVING
10
<
(
SELECT
COUNT
(
MARK
)
FROM
EXAM
_
MARKS
B
WHERE
A
.
EXAM
_
DATE
=
B
.
EXAM
_
DATE
);
Под з а п рос
в ыч исл яет
колич еств о
строк
с
од ной
и
той
же
д а той
,
сов п а д а ющ ей
с
д а той
,
д ля
которой
сформиров а на
оч еред на я
груп п а
основ ного
з а п роса
.
УП Р АЖ НЕ НИ Я
26.
Н а п ишите
з а п рос
с
п од з а п росом
д ля
п олуч ения
д а нных
обо
в сех
оценка х
студ ента
с
фа милией
“
Ива н о в
”
.
Пред п оложим
,
ч то
его
п ерсона льный
номер
не
из в естен
.
Всегд а
л и
та кой
з а п рос
буд ет
корректным
?
27.
Н а п ишите
з а п рос
,
в ыбира ющ ий
д а нные
об
имена х
в сех
студ ентов
,
имеющ их
п о
п ред мету
c
ид ентифика тором
101
ба лл
в ыше
общ его
сред него
ба лла
.
28.
Н а п ишите
з а п рос
,
который
в ып олняет
в ыборку
имен
в сех
студ ентов
,
имеющ их
п о
п ред мету
c
ид ентифика тором
102
ба лл
ниже
общ его
сред него
ба лла
.
29.
Н а п ишите
з а п рос
,
в ып ол няющ ий
в ыв од
колич еств а
п ред метов
,
п о
которым
э кз а менов а лся
ка жд ый
студ ент
,
сд а в а в ший
более
20-
ти
п ред метов
.
30.
Н а п ишите
кома нд у
SELECT
,
исп ользующ ую
св яз а нные
п од з а п росы
и
в ып олняющ ую
в ыв од
имен
и
ид ентифика торов
студ ентов
,
у
которых
стип енд ия
сов п а д а ет
с
ма ксима льным
з на ч ением
стип енд ии
д ля
гор од а
,
в
котором
жив ет
студ ент
.
31.
Н а п ишите
з а п рос
,
котор ый
п оз в оляет
в ыв ести
имена
и
ид ентифика торы
в сех
студ ентов
,
д ля
которых
точ но
из в естно
,
ч то
они
п рожив а ют
в
город е
,
гд е
нет
ни
од ного
унив ер ситета
.
32.
Н а п ишите
д в а
з а п роса
,
которые
п оз в оляют
в ыв ести
имена
и
ид ентифика торы
в сех
студ ентов
,
д ля
которых
точ но
из в естно
,
ч то
они
п рожив а ют
не
в
том
город е
,
гд е
расп оложен
их
унив ерситет
.
О д ин
з а п рос
с
исп ользов а нием
соед инения
,
а
д ругой
–
с
исп ользов а нием
св яз а нного
55
п од з а п роса
.
2.11.
И спол ь з ование
операт ора
EXISTS
Исп ользуемый
в
SQL
оп ератор
EXISTS
(
СУЩЕСТВУЕТ
)
генер ирует
з на ч ение
и сти н а
или
ло ж ь
,
п од обно
булев у
в ыражению
.
Исп ользуя
п од з а п росы
в
ка ч еств е
а р гумента
,
э тот
оп ератор
оценив а ет
р езульта т
в ып олнения
п од з а п роса
ка к
истинный
,
если
э тот
п од з а п рос
генерирует
в ыход ные
д а нные
,
то
есть
в
случ а е
сущ ест в о в а ни я
(
в оз в р а та
)
хотя
бы
од ного
на йд енного
з на ч ения
.
В
п ротив ном
сл уч а е
р езульта т
п од з а п роса
–
ложный
.
О п ератор
EXISTS
не
может
п ринима ть
з на ч ение
unknown
(
неизвестно
).
Пусть
,
на п р имер
,
нужно
из в леч ь
из
та блицы
EXAM_MARKS
д а нные
о
студ ента х
,
п олуч ив ших
хотя
бы
од ну
неуд ов летв орительную
оценку
.
SELECT DISTINCT
STUDENT_ID
FROM
EXAM_MARKS A
WHERE EXISTS
(
SELECT
*
FROM
EXAM_MARKS B
WHERE
MARK
<
3
AND
B
.
STUDENT
_
ID
=
A
.
STUDENT
_
ID
)
;
При
исп ользов а нии
св яз а нных
п од з а п росов
п ред ложение
EXISTS
а на лиз ирует
ка жд ую
стр оку
та блицы
,
на
которую
имеется
ссылка
в о
в нешнем
з а п росе
.
Г ла в ный
з а п рос
п олуч а ет
стр оки
-
ка нд ид а ты
на
п ров ерку
услов ия
.
Д ля
ка жд ой
строки
-
ка нд ид а та
в ып олняется
п од з а п рос
.
К а к
только
п од з а п рос
на ход ит
строку
,
гд е
в
столбце
MARK
з на ч ение
уд ов летв оряет
услов ию
,
он
п рекращ а ет
в ып олнение
и
в оз в ращ а ет
з на ч ение
и сти н а
в нешнему
з а п росу
,
который
з а тем
а на лиз ирует
св ою
строку
-
ка нд ид а та
.
Н а п ример
,
тр ебуется
п олуч ить
ид ентифика тор ы
п ред метов
обуч ения
,
э кз а мены
п о
которым
сд а в а лись
не
од ним
,
а
несколькими
студ ента ми
: