6. Оператор SELECT. Часть 2

Наличие определителя NULL в строках таблицы проверяется с помощью такой конструкции:

ПРИМЕР 6.17

Выбирите коды и названия книг, которые не имеют УДК (табл. 6.15).

Таблица 6.15

6.14.1. Рассчеты итоговых значений в операторе SELECT

Расчет итоговых значений в операторе SELECT осуществляется с помощью агрегатных или статистических функций: COUNT(), SUM(), AVG(), МАХ(), МIN().

Функция COUNT(<выражение>) подсчитывает число вхождений значений выражения во все записи результирующего набора данных. Она работает с данными любого типа: числовые, символьные или типа «дата/время».

ПРИМЕР 6.18

Определите, стоимость скольких книг превышает 25 грн. (табл. 6.16).

Таблица 6.16

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

ПРИМЕР 6.19

Определите, сколько различных библиотекарей выдавали книги (табл. 6.17).

Таблица 6.17

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

Функция SUM(<выражение>) рассчитывает сумму всех значений столбца или выражения. При этом столбец должен состоять из числовых данных (содержать целые числа, числа с плавающей запятой или денежные величины). Результат, возвращаемый этой функцией, имеет тот же тип данных, что и столбец или выражение, однако точность (разрядность) результата может быть выше. Например, если применить функцию SUM() к столбцу, содержащему 16-разрядные целые числа, то она может вернуть 32-разрядное целое число.

ПРИМЕР 6.20

Определите общую стоимость книг библиотечного фонда (табл. 6.18).

Таблица 6.18

Функции MIN(<выражение>), MAX(<выражение>) и AVG(<выражение>) позволяют рассчитать соответственно меньшее, большее и среднее значение в столбце или выражении. Функции MIN(), MAX() работают с числовыми, строковыми и данными типа «дата/время». AVG() работает только с числовыми данными. Результат, возвращаемый этими функциями, имеет такой же тип данных, как и выражение.

ПРИМЕР 6.21

Определите минимальную, максимальную и среднюю стоимость книг (табл. 6.19).

Таблица 6.19

6.14.2. Группировка

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

GROUP BY столбец[, столбец ...]

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

ПРИМЕР 6.22

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

Нет смысла включать имена столбцов BookCode и Cost в список фразы GROUPBY, поскольку в списке предложения SELECT они используются только в обобщающих функциях. Однако столбец FundCode в списке предложения SELECT не связан ни с одной обобщающей функцией, а потому обязательно должен быть указан во фразе GROUP BY .

Таблица 6.20

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

Таблица 6.21

Строки таблицы BookInventoryNumbers делятся на группы согласно значениям кода фонда (FundCode). В нашем примере будут созданы две группы, по количеству уникальных значений в столбце FundCode . Для каждой из групп рассчитывается общее число строк, равное количеству кодов книг, а также сумма их стоимости (столбец Cost), которая нас интересует. Затем генерируется единая сводная строка для всей группы исходных строк. Наконец, полученные строки результирующей таблицы перегруппировывается в порядке возрастания номера кода фонда, указанного в столбце FundCode.

6.14.3. Условие отбора сгруппированных записей

Условие отбора сгруппированных записей приводится в предложении

HAVING <условия поиска>

Здесь условия поиска задают согласно тем же правилам, что и в предложении WHERE. HAVING и WHERE можно совместно использовать в запросе. Сначала предложение WHERE исключает из расчета строки, не соответствующие ее условиям поиска. Дальше работают условия поиска предложения HAVING. Они отфильтровывают из результирующего набора данных группы строк, где итоговые значения не соответствуют им.

Стандарт ISO требует, чтобы имена столбцов, используемых в предложении HAVING, обязательно были в списке GROUP BY или обрабатывались агрегатными функциями. На практике условия поиска в предложении HAVING всегда включают, по меньшей мере, одну агрегатную функцию. Условия поиска, не содержащие агрегатных функций, должны входить в предложения WHERE и применяться для отбора отдельных строк. В условиях поиска предложения WHERE применять агрегатные функции запрещено.

ПРИМЕР 6.23

Для каждого библиотечного фонда, где хранится более шести книг, определите их количество и суммарную стоимость (табл. 6.22).

Этот пример аналогичен предыдущему, но здесь используются дополнительные ограничения, указывающие на то, что нас интересуют сведения только о тех фонды, в которых хранится более шести книг. Подобное требование накладывается на группы, поєтому в запросе следует использовать фразу HAVING.

Таблица 6.22

6.15.1. Подзапрос

Подзапрос - это инструмент создания временной таблицы, содержимое которой обрабатывается внешним оператором. Внутренние запросы могут быть использованы для вычисления значений после предложения SELECT и в условиях поиска после операторов сравнения (=, <>,> =, <=, <<) или IN в предложениях WHERE и HAVING внешнего оператора SELECT. Кроме того, подзапросы могут применяться в операторах INSERT, UPDATE и DELETE. Текст подзапроса берется в скобки.

Существует три вида подзапросов:

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

Для подзапросов устанавливаются такие правила и ограничения:

1. В подзапросах не должна использоваться фраза ORDER BY, хотя она может быть во внешнем запросе.
2. Список в предложении SELECT должен состоять из имен отдельных столбцов или составленных из них выражений, за исключением случая, когда подзапрос является операндом оператора EXISTS.
3. По умолчанию имена столбцов в подзапросе относятся к таблице, имя которой есть в предложении FROM. Однако допускается ссылаться и на столбцы таблицы, указанной в FROM внешнего запроса, для чего используются квалифицированные имена столбцов. Такие подзапросы называются соотнесенными.
4. Если подзапрос является одним из двух операндов, участвующих в операции сравнения, то он приводиться в правой части этой операции.

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

6.15.2. Скалярные подзапросы

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

ПРИМЕР 6.24

Выведите на экран все даты возвратов книг читателем с номером читательского билета 28 (табл. 6.23).

Внутренний оператор

предназначен для определения кода читателя с номером билета 28. После этого работает внешний запрос. Иначе говоря, внутренний оператор SELECT возвращает таблицу, которая имеет единственное значение - Code = 2. В результате внешний оператор SELECT приобретает следующий вид:

Таблица 6.23

Таблицу с одним столбцом и одной строкой можно получить, если после SELECT указать одну агрегатную функцию без использования группировки записей в предложении GROUP BY. В этом случае условия отбора приведенные в WHERE и HAVING, не играют несколь роли.

ПРИМЕР 6.25

Составьте список инвентарных номеров книг, стоимость которых выше средней. Укажите, насколько их стоимость выше средней стоимости всех книг библиотеки (табл. 6.24).

Таблица 6.24

Следует отметить, что нельзя употреблять «WHERE Cost > AVG(Cost)», поскольку использовать агрегатные функции в предложении WHERE запрещено. Для достижения желаемого результата необходимо создать подзапрос, в котором рассчитывают среднюю стоимость книг, а затем применить его во внешнем операторе SELECT, который предназначен для выборки сведений о тех книгах, стоимость которых превышает среднюю. Иначе говоря, подзапрос возвращает среднюю стоимость книг библиотеки: 38,27 грн. Результат выполнения этого скалярного подзапроса используется во внешнем операторе SELECT как для расчета отклонения стоимости средней, так и для отбора сведений о книгах. Поэтому внешний оператор SELECT имеет следующий вид:

6.15.3. Подзапросы, возвращающие множество значений

Подзапросы, возвращающие множество значений, используются в качестве операнда предиката IN. Например, табличный подзапрос можно использовать вместо внутреннего соединения таблиц. Для иллюстрации этого вернемся к ПРИМЕРУ 6.7: «Создайте список читателей, которые когда-либо принимали книги в библиотеке». С табличным подзапросом и псевдонимами таблиц оператор SELECT будет выглядеть так:

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

6.16.1. Оператор EXISTS

Оператор EXISTS применяется тогда, когда в условиях поиска необходимо указать, что из таблицы нужно выбрать только те записи, для которых подзапрос возвращает одно или более значений:

[NOT] EXISTS(<подзапрос>)

Итак, оператор EXISTS применяется только с подзапросами. Он возвращает TRUE, если в результирующем наборе данных подзапроса есть одна или более записей. Иначе он возвращает FALSE. Для NOT EXISTS правила обратные.

ПРИМЕР 6.26

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

Таблица 6.25

6.16.2. Оператор SINGULAR

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

[NOT] SINGULAR(<подзапрос>)

ПРИМЕР 6.27

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

Таблица 6.26

6.16.3. Операторы ALL, SOME, ANY

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

Здесь подзапрос может возвращать несколько значений. Оператор выполняет операцию сравнения (>, >=, < и т.д.). Отношение между сравниваемым значением и значениями, возвращаются подзапросом, определяются с помощью операторов ALL и SOME (ANY). Итак,

вернет TRUE, если текущее значение атрибута «STOLBEZ» будет больше всех значений в атрибуте «POLE» отношения «TABLIZA»;

вернет TRUE, если текущее значение атрибута «STOLBEZ» будет больше хотя бы одного значения в атрибуте «POLE» отношения «TABLIZA».

ПРИМЕР 6.28

Найдите инвентарные номера всех книг, стоимость которых выше, по крайней мере, стоимости одной книги фонда под номером «1» (табл. 6.27).

В этом случае внутренний подзапрос вернет следующие значения: {15.56, 22.33, 34.01, 12.99, 36.05, 74.20, 36.69, 48.13, 27.99}, а внешний - выберет сведения о тех книгах, стоимость которых превышает по крайней мере одно из значений в этом списке (фактически, больше минимального значения - 12.99).

Таблица 6.27

ПРИМЕР 6.29

Найдите инвентарные номера книг, стоимость которых выше стоимости любой книги книжного фонда под номером «1» (табл. 6.28).

Здесь внешний запрос выбирает сведения о книгах, стоимость которых больше максимального значения множества {15.56, 22.33, 34.01, 12.99, 36.05, 74.20, 36.69, 48.13, 27.99}.

Таблица 6.28

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

ПРИМЕР 6.30

В библиотеку экземпляры одной книги могут поступать в разное время и иметь разную стоимость. Определите код и среднюю стоимость экземпляров книг, у которых средняя стоимость одного экземпляра выше средней стоимости экземпляров других книг (табл. 6.29).

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

Таблица 6.29

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

Внешнее соединение определяется в предложении FROM оператора SELECT. Оно записывается в следующем формате:

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

LEFT – (левое внешнее соединение), когда базовой является таблица, расположенная слева от названия типа соединения;

RIGHT – (правое внешнее соединение), когда базовой является таблица, расположенная справа от типа соединения;

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

ПРИМЕР 6.31

Выполните внешнее левое и правое соединение таблиц 6.30 и 6.31 по атрибутам Р2 и Р1 соответственно.

Таблица 6.30

Таблица 6.31

Запишем оператор SELECT для левого соединения, обозначив таблицы 6.30 и 6.31 как А и В соответственно.

Серым цветом изображены столбцы базовой таблицы А. Как видим, для строки таблицы А, где столбец А.Р1 имеет значение «D», нет парных записей в таблице В, которые удовлетворяли бы условие поиска А.Р2 = В.Р1. Поэтому данная запись таблицы А соединена с пустой записью (табл. 6.32).

Таблица 6.32

Запишем оператор SELECT для правого соединения:

Серым цветом показан столбец базовой таблицы В. Как видим, для строки таблицы В, где столбец В.Р1 имеет значение «z», а столбец В.Р2 имеет значение «2» , нет парных записей в таблице А, для которых выполнялась бы условие поиска А.Р2 = В.Р1. Поэтому данная строка таблицы В соединен с пустой записью (табл. 6.33).

Таблица 6.33

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

ПРИМЕР 6.32

Объединить результаты выполнения трех запросов:

1) Паспорта с серией «АА» (табл. 6.34).

Таблица 6.34

2) Паспорта, выданные в 2000 году (табл. 6.35).

Таблица 6.35

3) Паспорта, номера которых начинаются на 4 (табл. 6.36).

Таблица 6.36

Объединим три набора данных (табл. 6.37).

Таблица 6.37

Операция || соединяет два строчных значения, которые могут быть представлены выражениями: <строчное выражение1> || <строчное выражение2>.

Операцию || можно использовать как после предложения SELECT для указания возвращаемых значений, так и в предложении WHERE.

ПРИМЕР 6.33

Поместите фамилия, имя, отчество и место работы читателей в одну колонку, причем место работы взять в круглые скобки (табл. 6.38).

Таблица 6.38

В одном запросе можно использовать таблицы из разных БД. В этом случае в СУБД Interbase имя таблицы представляется в формате

:Псевдоним БД :Имя таблицы

Под псевдонимом БД понимают псевдоним, который определен в утилите BDE Administrator. Ниже приведен пример обращения в одном запросе к таблицам БД InterВase (псевдоним «MONITOR») и Oracle (псевдоним «DWH»).

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

1. Какая конструкция в операторе SELECT отвечает за проверку наличия определителя NULL?
2. Что общего и отличного между известными Вам функциями, которые рассчитывают итоговые значения в операторе SELECT?
3. Для чего используется предложение GROUP BY в операторе SELECT?
4. Какая существует разница между условиями поиска предложений HAVING и WHERE?
5. С какой целью и где применяют подзапросы?
6. Чем отличаются результирующие наборы данных, возвращаемые подзапросами известных Вам типов?
7. Какие правила и ограничения формирования подзапросов Вам известны?
8. Каким образом в условиях поиска предложения WHERE подзапрос позволяет использовать агрегатные функции?
9. Какие значения и при каких условиях возвращают операторы EXISTS и SUNGULAR?
10. Как в условиях поиска можно сравнить скалярное значение со всеми значениями множества, возвращаемого подзапросом?
11. Какая существует разница между известными Вам типами внешних соединений отношений?
12. Какая существует разница между внешними и внутренними соединениями отношений?
13. Каким условиям должны отвечать результирующие наборы данных операторов SELECT, объединяемых с помощью оператора UNION?
14. Как в результирующем наборе данных оператора SELECT объединить в одном столбце данные, расположенные в разных столбцах исходных таблиц?
15. Можно ли в пределах одного оператора SELECT работать с таблицами, которые расположены в разных БД?