Материал: SQL в вопросах и задачах
81
3.2.
И спол ь з ование
подз апросов
в
INSERT
Применение
оп ера тора
INSERT
с
п од з а п росом
п оз в оляет
з а гружа ть
сраз у
несколько
строк
в
од ну
та блицу
,
исп ользуя
информа цию
из
д ругой
та блицы
.
В
то
в ремя
ка к
оп ера тор
INSERT
,
использующий
VALUES
д оба в ляет
только
од ну
строку
,
INSERT
с
п од з а п росом
д оба в ляет
в
та блицу
столько
строк
,
сколько
п од з а п рос
из в лека ет
из
д ругой
та блицы
.
Пр и
э том
колич еств о
и
тип
в оз в ращ а емых
п од з а п росом
столбцов
д олжно
соотв етств ов а ть
колич еств у
и
тип у
столбцов
та бл ицы
,
в
которую
в ста в ляются
д а нные
.
Н а п ример
,
п усть
та блица
STUDENT1
имеет
структуру
,
п олностью
сов п а д а ющ ую
со
структурой
та блицы
STUDENT
.
За п рос
,
п оз в ол яющ ий
з а п олнить
та блицу
STUDENT1
з а п исями
из
та блицы
STUDENT
обо
в сех
студ ента х
из
М оскв ы
,
в ыгляд ит
след ующ им
обра з ом
.
INSERT
INTO
STUDENT1
SELECT
*
FROM
STUDENT
WHERE
CITY
=
‘
М о ск
ва ’
;
Д ля
того
же
,
ч тобы
д оба в ить
в
та блицу
STUDENT1
св ед ения
обо
в сех
студ ента х
,
которые
уча т ся
в
М оскв е
,
можно
исп ользов а ть
в
п ред ложении
WHERE
соотв етств ующ ий
п од з а п рос
.
Н а п ример
,
INSERT
INTO
STUDENT1
SELECT
*
FROM
STUDENT
WHERE
UNIV
_
ID
IN
(
SELECT
UNIV
_
ID
FROM
UNIVERSITY
WHERE
CITY
=
‘
М о ск
ва ’
);
82
3.2.1.
И спол ь з ование
подз апросов
,
основанны х
на
т абл иц ах
внеш них
з апросов
Пред п оложим
,
сущ еств ует
та блица
SSTUD
,
в
которой
хра нятся
св ед ения
о
студ ента х
,
обуч а ющ ихся
в
том
же
город е
,
в
котором
они
жив ут
.
М ожно
з а п олнить
э ту
та блицу
д а нными
из
та блицы
STUDENT
,
исп ользуя
св яз а нные
п од з а п росы
,
след ующ им
образ ом
:
INSERT
INTO
SSTUD
SELECT
*
FROM
STUDENT
A
WHERE
CITY
IN
(
SELECT
CITY
FROM
UNIVERSITY
B
WHERE
A
.
UNIV
_
ID
=
B
.
UNIV
_
ID
);
Пред п оложим
,
ч то
требуется
в ыбрать
сп исок
студ ентов
,
имеющ их
ма ксима льный
ба лл
на
ка жд ый
д ень
сд а ч и
э кз а менов
,
и
р а з местить
его
в
д ругой
та блице
с
именем
EXAM
.
Э то
можно
осущ еств ить
с
п омощ ью
з а п р оса
INSERT
INTO
EXAM
SELECT
EXAM
_
ID
,
STUDENT
_
ID
,
SUBJ
_
ID
,
MARK
,
EXAM
_
DATE
FROM
EXAM
_
MARKS
A
WHERE
MARK
=
(SELECT MAX
(
MARK
)
FROM
EXAM
_
MARKS
B
WHERE
A.EXAM
_
DATE
=
B
.
EXAM
_
DATE
);
3.2.2.
И спол ь з ование
подз апросов
с
DELETE
Пусть
филиа л
унив ерситета
в
Н ью
-
Ва сюка х
ликв ид иров а н
,
и
требуется
уд а лить
из
та блицы
STUDENT
з а п иси
о
студ ента х
,
которые
та м
уч ились
.
Э ту
оп ерацию
можно
в ып олнить
с
п омощ ью
з а п роса
DELETE
FROM
STUDENT
WHERE
UNIV
_
ID
IN
(
SELECT
UNIV
_
ID
FROM
UNIVERSITY
WHERE
CITY
=
‘
Н ью
-
Ва сюк
и
’
);
83
В
п ред ика те
п ред ложения
FROM
(
п од з а п роса
)
нельзя
ссыла ться
на
та блицу
,
из
которой
осущ еств ляется
уд а ление
.
О д на ко
можно
ссыла ться
на
текущ ую
строку
из
та блицы
,
яв ляющ уюся
ка нд ид а том
на
уд а ление
,
то
есть
на
строку
,
котора я
в
на стоящ ее
в ремя
п ров еряется
в
основ ном
п ред ика те
.
DELETE
FROM
STUDENT
WHERE
EXISTS
(
SELECT
*
FROM
UNIVERSITY
WHERE
RATING
=
401
AND
STUDENT
.
UNIV
_
ID
=
UNIVERSITY
.
UNIV
_
ID
);
Ч а сть
AND
п ред ика та
в нутреннего
з а п роса
ссыла ется
на
та блицу
STUDENT
.
К ома нд а
уд а ляет
д а нные
о
студ ента х
,
которые
уч а тся
в
унив ерситета х
с
рейтингом
ра в ным
401.
Сущ еств уют
и
д ругие
сп особы
решения
э той
з а д а ч и
.
DELETE
FROM
STUDENT
WHERE
401
IN
(
SELECT
RATING
FROM
UNIVERSITY
WHERE
STUDENT
.
UNIV
_
ID
=
UNIVERSITY
.
UNIV
_
ID
);
Пусть
нужно
на йти
на именьшее
з на ч ение
оценки
,
п олуч енной
в
ка жд ый
д ень
сд а ч и
э кз а менов
,
и
уд а лить
из
та блицы
св ед ения
о
студ енте
,
который
п олуч ил
э ту
оценку
.
За п рос
буд ет
иметь
в ид
DELETE
FROM
STUDENT
WHERE
STUDENT
_
ID
IN
(
SELECT
STUDENT
_
ID
FROM
EXAM
_
MARKS
A
WHERE
MARK
=
(
SELECT
MIN
(
MARK
)
FROM
EXAM
_
MARKS
B
WHERE
A
.
EXAM
_
DATE
=
B
.
EXAM
_
DATE
));
Та к
ка к
столбец
STUDENT
_
ID
яв ляется
п ерв ич ным
ключ ом
,
то
уд а ляется
ед инств енна я
строка
.
Е сли
в
ка кой
-
то
д ень
сд а в а лся
только
од ин
э кз а мен
(
то
есть
,
п ол уч ена
только
од на
минима льна я
оценка
),
и
п о
ка кой
-
либо
п рич ине
з а п ись
,
в
84
которой
на ход ится
э та
оценка
,
требуется
оста в ить
,
то
решение
буд ет
иметь
в ид
:
DELETE
FROM
STUDENT
WHERE
STUDENT
_
ID
IN
(
SELECT
STUDENT
_
ID
FROM
EXAM
_
MARKS
A
WHERE
MARK
=
(
SELECT
MIN
(
MARK
)
FROM
EXAM
_
MARKS
B
WHERE
A
.
EXAM
_
DATE
=
B
.
EXAM
_
DATE
AND
1
<
(
SELECT
COUNT
(
SUBJ
_
ID
)
FROM
EXAM
_
MARKS
B
WHERE
A
.
EXAM
_
DATE
=
B
.
EXAM
_
DATE
)));
3.2.3.
И спол ь з ование
подз апросов
с
UPDATE
С
п омощ ью
кома нд ы
UPDATE
можно
п рименять
п од з а п росы
в
любой
форме
,
п риемлемой
д ля
кома нд ы
DELETE
.
Н а п ример
,
исп ользуя
св яз а нные
п од з а п росы
,
можно
ув елич ить
з на ч ение
раз мер а
стип енд ии
на
20
в
з а п исях
студ ентов
,
сд а в ших
э кз а мены
на
4
и
5.
UPDATE
STUDENT1
SET
STIPEND
=
STIPEND
+
20
WHERE
4
<=
(
SELECT
MIN
(
MARK
)
FROM
EXAM
_
MARKS
WHERE
EXAM
_
MARKS
.
STUDENT
_
ID
=
STUDENT1
.
STUDENT
_
ID
);
Д ругой
з а п рос
:
“У меньшить
в елич ину
стип енд ии
на
20
в сем
студ ента м
,
п олуч ив шим
на
э кз а мене
минима льную
оценку”
.
UPDATE
STUDENT1
SET
STIPEND
=
STIPEND
–
20
WHERE
STUDENT
_
ID
IN
(
SELECT
STUDENT
_
ID
FROM
EXAM
_
MARKS
A
85
WHERE
MARK
=
(
SELECT
MIN
(
MARK
)
FROM
EXAM
_
MARKS
B
WHERE
A
.
EXAM
_
DATE
=
B
.
EXAM
_
DATE
));
УП Р АЖ НЕ НИ Я
91.
Пусть
сущ еств ует
та блица
с
именем
STUDENT1
,
оп ред еления
столбцов
которой
п олностью
сов п а д а ют
с
оп ред елениями
столбцов
та блицы
STUDENT
.
Вста в ить
в
э ту
та блицу
св ед ения
о
студ ента х
,
усп ешно
сд а в ших
э кз а мены
более
ч ем
п о
п яти
п ред мета м
обуч ения
.
92.
Н а п ишите
кома нд у
,
уд а ляющ ую
из
та блицы
SUBJECT1
св ед ения
о
п ред мета х
обуч ения
,
п о
которым
студ ента ми
не
п олуч ено
ни
од ной
оценки
.
93.
Н а п ишите
з а п рос
,
ув елич ив а ющ ий
д а нные
о
в елич ине
стип енд ии
на
20%
в сем
студ ента м
,
у
которых
общ а я
сумма
ба ллов
п рев ыша ет
з на ч ение
50.