Материал: SQL в вопросах и задачах
41
яз ыка
SQL (
в оз можно
,
с
ука з а нием
д лины
,
точ ности
и
ма сшта ба
)
или
быть
NULL
-
з на ч ением
.
•
любое
ч ислов ое
в ыражение
может
быть
яв но
п реобраз ов а но
в
любой
д ругой
ч ислов ой
тип
.
•
симв ольное
в ыра жение
может
быть
п реобраз ов а но
в
любой
ч ислов ой
тип
.
При
этом
в
рез ульта те
симв ольного
в ыражения
отсека ются
на ч а льные
и
конеч ные
п робелы
,
а
оста льные
симв олы
п реобраз уются
в
ч ислов ое
з на ч ение
п о
п ра в ила м
яз ыка
SQL.
•
если
яв но
з а д а нна я
д лина
симв ольного
тип а
нед оста точ на
и
п реобраз ов а нное
з на ч ение
не
раз мещ а ется
в
нем
,
то
результа тив ное
з на ч ение
усека ется
сп рав а
.
•
в оз можно
яв ное
п реобр а з ов а ние
симв ольного
тип а
в
симв ольный
с
д ругой
д линой
.
Е сли
д лина
результа та
больше
д лины
а р гумента
,
то
з на ч ение
д оп олняется
п робела ми
;
если
меньше
,
то
усека ется
.
•
NULL
-
з на ч ение
п реобраз уется
в
NULL
-
з на ч ение
соотв етств ующ его
тип а
.
•
ч ислов ое
в ыра жение
может
быть
п реобраз ов а но
в
симв ольный
тип
.
Пример
.
SELECT CAST
STUDENT
_
ID
AS
CHAR
(
10
)
FROM
STUDENT
;
УП Р АЖ НЕ НИ Я
1.
Соста в ьте
з а п рос
д ля
та блицы
STUDENT
та ким
образ ом
,
ч тобы
в ыход на я
та блица
сод ержа ла
од ин
столбец
,
сод ержа щ ий
п ослед ов а тельность
р а з д еленных
симв олом
“
;
”
(
точ ка
с
з а п ятой
)
з на ч ений
в сех
столбцов
э той
та блицы
,
и
п р и
э том
текстов ые
з на ч ения
д олжны
отобража ться
п роп исными
симв ола ми
(
в ерхний
регистр
),
то
есть
быть
п ред ста в ленными
в
след ующ ем
в ид е
:
10
;
КУЗНЕЦОВ
;
БОРИС
;
0
;
БРЯНСК
;
8/12/1981
;
10
.
2.
Соста в ьте
з а п рос
д ля
та блицы
STUDENT
та ким
образ ом
,
ч тобы
в ыход на я
та блица
сод ержа ла
в сего
од ин
столбец
в
след ующ ем
в ид е
:
Б
.
КУЗНЕЦОВ
;
место
жительства
-
БРЯНСК
;
родился
-
8
.
12
.
81
.
3.
Соста в ьте
з а п рос
д ля
та блицы
STUDENT
та ким
образ ом
,
ч тобы
в ыход на я
та блица
сод ержа ла
в сего
од ин
столбец
в
след ующ ем
в ид е
:
42
б
.
кузнецов
;
место
жительства
-
брянск
;
родился
:
8
-
дек
-
1981
.
4.
Соста в ьте
з а п рос
д ля
та блицы
STUDENT
та ким
образ ом
,
ч тобы
в ыход на я
та блица
сод ержа ла
в сего
од ин
столбец
в
след ующ ем
в ид е
:
Борис
Кузнецов
родился
в
1981
году
.
5.
Выв ести
фа милии
,
имена
студ ентов
и
в елич ину
п ол уч а емых
ими
стип енд ий
,
п ри
этом
з на ч ения
стип енд ий
д олжны
быть
ув елич ены
в
100
р а з
.
6.
То
же
,
ч то
и
в
з а д а ч е
4,
но
только
д ля
студ ентов
1, 2
и
4-
го
курсов
и
та ким
образ ом
,
ч тобы
фа милии
и
имена
были
в ыв ед ены
п роп исными
букв а ми
.
7.
Соста в ьте
з а п рос
д ля
та блицы
UNIVERSITY
та ким
образ ом
,
ч тобы
в ыход на я
та блица
сод ержа ла
в сего
од ин
столбец
в
след ующ ем
в ид е
:
Код
-10
;
ВГУ
-
г
.
ВОРОНЕЖ
;
Рейтинг
=296
.
8.
Тоже
,
ч то
и
в
з а д а ч е
7,
но
з на ч ения
рейтинга
требуется
округлить
д о
п ерв ого
з на ка
(
на п р имер
,
з на ч ение
382
округляется
д о
400).
2.4.
Агрегирование
и
групповы е
ф ункц ии
А грегирующ ие
функции
п оз в оляют
п олуч а ть
из
та блицы
св од ную
(
а грегиров а нную
)
информа цию
,
в ып олняя
оп ер а ции
на д
груп п ой
строк
та блицы
.
Д ля
з а д а ния
в
SELECT-
з а п росе
а грегирующ их
оп ераций
исп ользуются
след ующ ие
ключ ев ые
слов а
:
•
COUNT
оп ред еляет
колич еств о
строк
или
з на ч ений
п оля
,
в ыбранных
п осред ств ом
з а п роса
,
и
не
яв ляющ ихся
NULL
-
з на ч ениями
;
•
SUM
–
в ыч исляет
а рифметич ескую
сумму
в сех
в ыбранных
з на ч ений
д а нного
п оля
;
•
AVG
в ыч исляет
сред нее
з на ч ение
д ля
в сех
в ыбранных
з на ч ений
д а нного
п оля
;
•
MAX
в ыч исляет
на ибольшее
из
в сех
в ыбра нных
з на ч ений
д а нного
п оля
;
•
MIN
в ыч исляет
на именьшее
из
в сех
в ыбранных
з на ч ений
д а нного
п ол я
.
В
SELECT-
з а п р осе
а грегир ующ ие
функции
исп ользуются
а на логич но
имена м
п олей
,
п ри
э том
п ослед ние
(
имена
п олей
)
исп ользуются
в
ка ч еств е
а р гументов
э тих
функций
.
Функция
AVG
п ред на з на ч ена
д ля
п од сч ета
сред него
з на ч ения
п оля
на
множеств е
з а п исей
та блицы
.
43
Н а п ример
,
д ля
оп р ед еления
сред него
з на ч ения
п оля
MARK
(
оценки
)
п о
в сем
з а п исям
та блицы
EXAM
_
MARKS
можно
исп ользов а ть
з а п рос
с
функцией
AVG
след ующ его
в ид а
:
SELECT AVG
(
MARK
)
FROM
EXAM
_
MARKS
;
Д ля
п од сч ета
общ его
колич еств а
строк
в
та блице
след ует
исп ользов а ть
функцию
COUNT
со
з в ез д оч кой
.
SELECT COUNT
(*)
FROM
EXAM
_
MARKS
;
А ргументы
DISTINCT
и
ALL
п оз в оляют
,
соотв етств енно
,
исключ а ть
и
в ключ а ть
д ублика ты
обр а ба тыв а емых
функцией
COUNT
з на ч ений
,
п ри
этом
необход имо
уч итыв а ть
,
ч то
п ри
исп ользов а нии
оп ции
ALL
з на ч ения
NULL
в се
рав но
не
в ойд ут
в
ч исло
п од сч итыв а емых
з на ч ений
.
SELECT COUNT
(
DISTINCT
SUBJ
_
ID
)
FROM
SUBJECT
;
Пред ложение
GROUP BYGROUP BY
(
ГРУППИРОВАТЬ
ПО
)
п оз в оляет
груп п ир ов а ть
з а п иси
в
п од множеств а
,
оп ред еляемые
з на ч ениями
ка кого
-
либо
п оля
,
и
п рименять
а грегирующ ие
функции
уже
не
ко
в сем
з а п исям
та блицы
,
а
раз д ельно
к
ка жд ой
сформиров а нной
гр уп п е
.
Пред п оложим
,
требуется
на йти
ма ксима льное
з на ч ение
оценки
,
п олуч енной
ка жд ым
студ ентом
.
За п рос
буд ет
в ыгляд еть
след ующ им
образ ом
:
SELECT
STUDENT
_
ID
,
MAX(
MARK
)
FROM
EXAM
_
MARKS
GROUP
BY
STUDENT
_
ID
;
Выбираемые
из
та блицы
EXAM
_
MARKS
з а п иси
груп п ируются
п о
з на ч ениям
п оля
STUDENT
_
ID
,
ука з а нного
в
п ред ложении
GROUP BY
,
и
д ля
ка жд ой
груп п ы
на ход ится
ма ксима льное
з на ч ение
п оля
MARK
.
Пред ложение
GROUP BY
п оз в оляет
п р именять
а грегирующ ие
функции
к
ка жд ой
груп п е
,
оп ред еляемой
общ им
з на ч ением
п оля
(
или
п олей
),
ука з а нных
в
э том
п ред ложении
.
В
п рив ед енном
з а п росе
рассма трив а ются
гр уп п ы
з а п исей
,
сгруп п иров а нные
п о
ид ентифика тора м
студ ентов
.
44
В
конструкции
GROUP BY
д ля
груп п иров а ния
может
быть
исп ользов а но
более
од ного
столбца
.
Н а п ример
:
SELECT
STUDENT
_
ID
,
SUBJ
_
ID
,
MAX(
MARK
)
FROM
EXAM
_
MARKS
GROUP
BY
STUDENT
_
ID
,
SUBJ
_
ID
;
В
э том
случ а е
строки
в на ч а ле
груп п ируются
п о
з на ч ениям
п ер в ого
столбца
,
а
в нутри
этих
груп п
–
в
п од груп п ы
п о
з на ч ениям
в торого
столбца
.
Та ким
образ ом
,
GROUP BY
не
только
уста на в лив а ет
столбцы
,
п о
котор ым
осущ еств ляется
груп п иров а ние
,
но
и
ука з ыв а ет
п оряд ок
ра з биения
столбцов
на
груп п ы
.
След ует
иметь
в
в ид у
,
ч то
в
п р ед ложении
GROUP BY
д олжны
быть
ука з а ны
в се
в ыбира емые
столбцы
,
п рив ед енные
п осле
ключ ев ого
слов а
SELECT
,
кр оме
столбцов
,
ука з а нных
в
ка ч еств е
а р гумента
в
а грегир ующ ей
функции
.
При
необход имости
ч а сть
сформиров а нных
с
п омощ ью
GROUP BY
груп п
может
быть
исключ ена
с
п омощ ью
п ред ложения
HAVING
.
Пред ложение
HAVING
оп ред еляет
кр итерий
,
п о
которому
груп п ы
след ует
в ключ а ть
в
в ыход ные
д а нные
,
п о
а на логии
с
п ред ложением
WHERE
,
которое
осущ еств ляет
э то
д ля
отд ельных
строк
.
SELECT
SUBJ
_
NAME
,
MAX
(
HOUR
)
FROM
SUBJECT
GROUP BY
SUBJ
_
NAME
HAVING MAX
(
HOUR
) >=
72;
В
услов ии
,
з а д а в а емом
п ред ложением
HAVING
,
ука з ыв а ют
только
п оля
или
в ыражения
,
которые
на
в ыход е
имеют
ед инств енное
з на ч ение
д ля
ка жд ой
в ыв од имой
груп п ы
.
УП Р АЖ НЕ НИ Я
9.
Н а п ишите
з а п рос
д ля
п од сч ета
колич еств а
студ ентов
,
сд а в а в ших
э кз а мен
п о
п ред мету
обуч ения
с
ид ентифика тором
,
рав ным
20.
10.
Н а п ишите
з а п рос
,
который
п оз в оляет
п од сч ита ть
в
та блице
EXAM
_
MARKS
колич еств о
раз лич ных
п ред метов
обуч ения
.
11.
Н а п ишите
з а п рос
,
который
в ып олняет
в ыборку
д ля
ка жд ого
студ ента
45
з на ч ения
его
ид ентифика тора
и
минима льной
из
п олуч енных
им
оценок
.
12.
Н а п ишите
з а п рос
,
который
в ып олняет
в ыборку
д ля
ка жд ого
студ ента
з на ч ения
его
ид ентифика тора
и
ма ксима льной
из
п олуч енных
им
оценок
.
13.
Н а п ишите
з а п рос
,
в ып олняющ ий
в ыв од
фа милии
п ерв ого
в
а лфа в итном
п оряд ке
(
п о
фа милии
)
студ ента
,
фа милия
которого
на ч ина ется
на
букв у
“И”
.
14.
Н а п ишите
з а п рос
,
который
в ып олняет
в ыв од
д л я
ка жд ого
п ред мета
обуч ения
на именов а ние
п ред мета
и
ма ксима льное
з на ч ение
номера
семестр а
,
в
котором
э тот
п р ед мет
п реп од а ется
.
15.
Н а п ишите
з а п рос
,
который
в ып олняет
в ыв од
д а нных
д ля
ка жд ого
конкретного
д ня
сд а ч и
э кз а мена
о
колич еств е
студ ентов
,
сд а в а в ших
э кз а мен
в
э тот
д ень
.
16.
Н а п ишите
з а п р ос
д ля
п олуч ения
сред него
ба лла
д ля
ка жд ого
курса
п о
ка жд ому
п р ед мету
.
17.
Н а п ишите
з а п рос
д ля
п олуч ения
сред него
ба лла
д ля
ка жд ого
студ ента
.
18.
Н а п ишите
з а п рос
д ля
п олуч ения
сред него
ба лла
д ля
ка жд ого
э кз а мена
.
19.
Н а п ишите
з а п рос
д ля
оп ред еления
колич еств а
студ ентов
,
сд а в а в ших
ка жд ый
э кз а мен
.
20.
Н а п ишите
з а п рос
д ля
оп ред еления
колич еств а
из уч а емых
п ред метов
на
ка жд ом
курсе
.
2.5.
П уст ы е
з начения
(
NULL
)
в
агрегирую щ их
ф ункц ия х
Н а лич ие
п устых
(
NULL
)
з на ч ений
в
п ол ях
та блицы
на кла д ыв а ет
особенности
на
в ып олнение
а грегир ующ их
оп ераций
на д
д а нными
,
которые
след ует
уч итыв а ть
п р и
их
исп ользов а нии
в
SQL-
з а п роса х
.
2.5.1.
В л ия ние
NULL
– з начений
в
ф ункц ии
COUNT
Е сли
а р гумент
функции
COUNT
яв ляется
конста нтой
или
столбцом
без
п устых
з на ч ений
,
то
функция
в оз в ра щ а ет
колич еств о
строк
,
к
которым
п рименимо
оп ред еленное
услов ие
или
груп п иров а ние
.