Материал: Базы данных и их безопасность. Плотников Д.Г

поставщиков, поставляющих детали для изделия номер J1.

4.Выдать список всех поставок, в которых количество деталей находится в диапазоне от 300 до 750 включительно.

5.Выдать список всех комбинаций «цвет детали— город, где хранится деталь», исключая дубликаты пар (цвет— город).

6.Выдать список всех поставок, в которых количество не является неопределенным значением.

7.Выдать номера изделий и города, где они изготавливаются, такие, что второй буквой названия города является «О».

Соединения

8.Выдать все триплеты «номер поставщика, номер детали и номер изделия», такие, что образующие каждый из них поставщик, деталь и изделие являются соразмещенными.

9.Выдать все триплеты «номер поставщика, номер детали и номер изделия», такие, что образующие каждый из них поставщик, деталь и изделие не являются соразмещенными.

10.Выдать все триплеты «номер поставщика, номер детали и номер изделия», такие, что в каждом триплете указанные поставщик, деталь и изделие не являются попарно соразмещенными.

11.Выдать номера деталей, поставляемых каким-либо поставщиком из Лондона, для изделия, изготавливаемого также

вЛондоне.

12.Выдать номера деталей, поставляемых каким-либо поставщиком из Лондона.

13.Выдать все пары названий городов, таких, что какойлибо поставщик из первого города поставляет детали для некоторого изделия, изготавливаемого во втором городе.

14.Выдать номера деталей, поставляемых для какоголибо изделия поставщиком, находящимся в том же городе, где изготавливается это изделие.

15.Выдать номера изделий, для которых детали поставляются, по крайней мере, одним поставщиком не из того

261

же самого города.

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

8.3. Использование подзапросов и функции выборки данных

В этой части мы завершаем обсуждение предложения SELECT языка SQL. План этой части следующий:

—Сначала вводится понятие подзапроса (Subquery) или вложенного предложения SELECT. Представляет исторический интерес тот факт, что именно возможность вкладывать одно предложение SELECT внутрь другого первоначально послужила мотивировкой использования прилагательного «структуризованный» в названии языка «структуризованный язык запросов» (Structured Query Language—SQL). Однако более поздние дополнения к языку привели к тому, что сами по себе вложенные предложения SELECT стали значительно менее важными.

—Затем рассматривается квантор существования EXISTS (существует), который вместе с соединением расценивается, по мнению автора, как одна из наиболее важных и фундаментальных, хотя, может быть, и не самых легких для использования, возможностей полного языка

SQL.

—Далее обсуждаются стандартные функции COUNT (число значений), SUM (сумма), AVG (среднее) и т. п. В нем описывается, в частности, использование в связи с этими функциями фраз GROUP BY (группировать по) и HAVING (имея).

—В завершение обсуждается оператор UNION (объединение).

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

262

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

Необходимо сделать еще одно заключительное вводное замечание, которое может быть не совсем понятным пока не будет прочитана вся часть. Несмотря на то, что наша задача состоит в исчерпывающем обсуждении языка, в данную главу намеренно не включено какое-либо детальное описание вариантов ANY (любой) и ALL (все) операторов сравнения (> ANY, =ALL и т. д.), если необходимо такое детальное описание, можно обратиться к фирменному руководству по системе. Нет такого запроса, сформулированного с их использованием, который нельзя было бы в равной степени хорошо, а на самом деле лучше, сформулировать, используя конструкцию EXISTS (существует). Более того, они запутывают и порождают потенциальную опасность ошибок. Например, корректное предложение:

SELECT S.НОМЕР_ПОСТАВЩИКА

FROM S

WHERE S.ГОРОД!= ANY (SELECT P.ГОРОД FROM P);

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

SELECT S.НОМЕР_ПОСТАВЩИКА

FROM S

WHERE EXISTS (SELECT P.ГОРОД FROM P WHERE P.ГОРОД! = S.ГОРОД);

263

(«выдать номера поставщиков таких, что существует некоторый город хранения деталей, который отличается от города, где находится данный поставщик»). Естественная интуитивная интерпретация! = ANY как «не совпадает с любыми» и некорректна и весьма обманчива. Подобная критика относится ко всем операторам, использующим ANY и ALL.

Подзапросы

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

Простой подзапрос

Выдать фамилии поставщиков, которые поставляют деталь Р2.

SELECT ФАМИЛИЯ

FROM S

WHERE НОМЕР_ПОСТАВЩИКА IN

(SELECT НОМЕР_ПОСТАВЩИКА

FROM SP

WHERE НОМЕР_ДЕТАЛИ = ’Р2’)

Результат:

ФАМИЛИЯ

Смит

Джонс

Блейк

Кларк

Пояснение. При обработке полного запроса система обрабатывает, прежде всего, вложенный подзапрос. Этот подзапрос возвращает множество номеров поставщиков,

264

которые поставляют деталь Р2, а именно множество (’S1’, ’S2’, ’S3’, ’S4’). Поэтому первоначальный запрос эквивалентен следующему простому запросу:

SELECT ФАМИЛИЯ

FROM S

WHERE НОМЕР_ПОСТАВЩИКА IN

(’S1’, ’S2’, ’S3’, ’S4’);

и, следовательно, получаем приведенный ранее результат. Неявное уточнение фамилии в этом примере требует

дополнительного обсуждения. Заметим, в частности, что «НОМЕР_ПОСТАВЩИКА» слева от IN неявным образом уточняется именем таблицы S, в то время как «НОМЕР_ПОСТАВЩИКА» в подзапросе неявно уточняется именем таблицы SP. Справедливо следующее общее правило: предполагается, что неуточненное имя поля должно уточняться именем таблицы (или псевдонимом таблицы), указанным в той фразе FROM, которая является непосредственной частью того же самого запроса или подзапроса. В случае поля НОМЕР_ПОСТАВЩИКА слева от IN этой фразой является «FROM S», а в случае поля НОМЕР_ПОСТАВЩИКА в подзапросе—это фраза «FROM SP». Для большей ясности повторим первоначальный запрос с явно указанными предполагаемыми уточнениями:

SELECT S.ФАМИЛИЯ FROM S

WHERE S.НОМЕР_ПОСТАВЩИКА IN

(SELECT SP.НОМЕР_ПОСТАВЩИКА

FROM SP

WHERE SP.НОМЕР_ДЕТАЛИ = ’P2’);

Неявные уточнения всегда можно отвергнуть путем задания явных уточнений.

Прежде чем перейти к нашему следующему примеру

265