Логические выражения раздела WHERE

Синтаксически логическое выражение раздела WHERE определяется как булевское выражение (boolean_value_expression), правила построения которого обсуждались в предыдущей лекции. Основой логического выражения являются предикаты. Предикат позволяет специфицировать условие, результатом вычисления которого может быть true, false или unknown. В языке SQL:1999 допустимы следующие предикаты:1)

Далее мы будем последовательно обсуждать разные виды предикатов и приводить примеры запросов с использованием базы данных СЛУЖАЩИЕ-ОТДЕЛЫ-ПРОЕКТЫ, определения таблиц которой на языке SQL были приведены в лекции 12. Для удобства повторим здесь структуру таблиц.

EMP:

EMP_NO : EM_NO

EMP_NAME : VARCHAR

EMP_BDATE : DATE

EMP_SAL : SALARY

DEPT_NO : DEPT_NO

PRO_NO : PRO_NO

DEPT:

DEPT_NO : DEPT_NO

DEPT_NAME : VARCHAR

DEPT_EMP_NO : INTEGER

DEPT_TOTAL_SAL : SALARY

DEPT_MNG : EMP_NO

PRO:

PRO_NO : PRO_NO

PRO_TITLE : VARCHAR

PRO_SDATE : DATEP

PRO_DURAT : INTERVAL

PRO_MNG : EMP_NO

PRO_DESC : CLOB

Столбцы EMP_NO, DEPT_NO и PRO_NO являются первичными ключами таблиц EMP, DEPT и PRO соответственно. Столбцы DEPT_NO и PRO_NO таблицы EMP являются внешними ключами, ссылающимися на таблицы DEPT и PRO соответственно (DEPT_NO указывает на отделы, в которых работают служащие, а PRO_NO - на проекты, в которых они участвуют; оба столбца могут принимать неопределенные значения). Столбец DEPT_MNG является внешним ключом таблицы DEPT (DEPT_MNG указывает на служащих, которые исполняют обязанности руководителей отделов; у отдела может не быть руководителя, и один служащий не может быть руководителем двух или более отделов). Столбец PRO_MNG является внешним ключом таблицы PRO (PRO_MNG указывает на служащих, которые являются менеджерами проектов, у проекта всегда есть менеджер, и один служащий не может быть менеджером двух или более проектов).

Логические выражения раздела WHERE

EMP:

EMP_NO : EM_NO

EMP_NAME : VARCHAR

EMP_BDATE : DATE

EMP_SAL : SALARY

DEPT_NO : DEPT_NO

PRO_NO : PRO_NO

DEPT:

DEPT_NO : DEPT_NO

DEPT_NAME : VARCHAR

DEPT_EMP_NO : INTEGER

DEPT_TOTAL_SAL : SALARY

DEPT_MNG : EMP_NO

PRO:

PRO_NO : PRO_NO

PRO_TITLE : VARCHAR

PRO_SDATE : DATEP

PRO_DURAT : INTERVAL

PRO_MNG : EMP_NO

PRO_DESC : CLOB

Предикат between

Предикат позволяет специфицировать условие вхождения в диапазон значений. Операндами являются строки:

between_predicate ::= row_value_constructor [ NOT ] BETWEEN row_value_constructor AND row_value_constructor

Все три строки-операнды должны иметь одну и ту же степень. Типы данных соответствующих значений строк-операндов должны быть совместимыми.

Пусть X, Y и Z обозначают первый, второй и третий операнды. Тогда по определению выражение X NOT BETWEEN Y AND Z эквивалентно выражению NOT (X BETWEEN Y AND Z). Выражение X BETWEEN Y AND Z по определению эквивалентно булевскому выражению X >= Y AND X <= Z.

Предикат between

Предикат позволяет специфицировать условие вхождения в диапазон значений. Операндами являются строки:

between_predicate ::= row_value_constructor [ NOT ] BETWEEN row_value_constructor AND row_value_constructor

Предикат distinct

Предикат позволяет проверить, являются ли две строки дубликатами. Условие определяется следующим синтаксическим правилом:

distinct_predicate ::= row_value_constructor IS DISTINCT FROM row_value_constructor

Строки-операнды должны быть одинаковой степени. Типы данных соответствующих значений строк-операндов должны быть совместимы.

Напомним, что две строки s1 с именами столбцов c1, c2, …, cn и s2 с именами столбцов d1, d2, …, dn считаются строками-дубликатами, если для каждого i ( i = 1, 2, …, n ) либо ci и di не содержат NULL, и (ci = di) = true, либо и ci, и di содержат NULL. Значением условия s1 IS DISTINCT FROM s2 является true в том и только в том случае, когда строки s1 и s2 не являются дубликатами. В противном случае значением условия является false.

Заметим, что отрицательная форма условия - IS NOT DISTINCT FROM - в стандарте SQL не поддерживается. Вместо этого можно воспользоваться выражением NOT s1 IS DISTINCT FROM s2.

Предикат distinct

distinct_predicate ::= row_value_constructor IS DISTINCT FROM row_value_constructor

Предикат exists

Предикат exists определяется следующим синтаксическим правилом:

exists_predicate ::= EXISTS (query_expression)

Значением условия EXISTS (query_expression) является true в том и только в том случае, когда мощность таблицы-результата выражения запросов больше нуля, иначе значением условия является false.

Предикат exists

Предикат exists определяется следующим синтаксическим правилом:

exists_predicate ::= EXISTS (query_expression)

Предикат in

Предикат позволяет специфицировать условие вхождения строчного значения в указанное множество значений. Синтаксические правила следующие:

in_predicate ::= row_value_constructor [ NOT ] IN in_predicate_value in_predicate_value ::= table_subquery | (value_expression_comma_list)

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

Пусть X обозначает строку-первый операнд, а S - множество строк второго операнда. Обозначим через s строку-элемент этого множества. Тогда по определению условие X IN S эквивалентно булевскому выражению ORsS (X = s). Другими словами, X IN S принимает значение true в том и только в том случае, когда во множестве S существует хотя бы один элемент s, такой, что значением предиката X = s является true. X IN S принимает значение false в том и только том случае, когда для всех элементов s множества S значением операции сравнения X = s является false. Иначе значением условия X IN S является unknown. Заметим, что для пустого множества S значением X IN S является false.

По определению условие X NOT IN S эквивалентно NOT (X IN S).

Предикат in

in_predicate ::= row_value_constructor [ NOT ] IN in_predicate_value in_predicate_value ::= table_subquery | (value_expression_comma_list)

По определению условие X NOT IN S эквивалентно NOT (X IN S).

Предикат like

Формально предикат like определяется следующими синтаксическими правилами:

like_predicate ::= source_value [ NOT ] LIKE pattern_value [ ESCAPE escape_value ] source_value ::= value_expression pattern_value ::= value_expression escape_value ::= value_expression

Все три операнда (source_value, pattern_value и escape_value) должны быть одного типа: либо типа символьных строк, либо типа битовых строк1). В первом случае значением последнего операнда должна быть строка из одного символа, во втором - строка из 8 бит. Второй операнд, как правило, задается литералом соответствующего типа. В обоих случаях значение предиката равняется true в том и только в том случае, когда исходная строка (source_value) может быть сопоставлена с заданным шаблоном (pattern_value).

Если обрабатываются символьные строки, и если раздел ESCAPE условия отсутствует, то при сопоставлении шаблона со строкой производится специальная интерпретация двух символов шаблона: символ подчеркивания ('_') обозначает любой одиночный символ; символ процента ('%') обозначает последовательность произвольных символов произвольной длины (длина последовательности может быть нулевой). Если же раздел ESCAPE присутствует и специфицирует некоторый одиночный символ x, то пары символов "x_" и "x%" представляют одиночные символы "_" и "%" соответственно.

В случае обработки битовых строк сопоставление шаблона со строкой производится восьмерками соседних бит (октетами). В соответствии со стандартом SQL:1999, при сопоставлении шаблона со строкой производится специальная интерпретация октетов со значениями X'25' и X'5F' (коды символов подчеркивания и процента в кодировке ASCII). Первый октет обозначает любой одиночный октет, а второй - последовательность произвольной длины произвольных октетов (длина может быть нулевой). В разделе ESCAPE указывается октет, отменяющий специальную интерпретацию октетов X'25' и X'5F'.

Значение предиката like есть unknown, если значение первого или второго операндов является неопределенным. Условие x NOT LIKE y ESCAPE z эквивалентно условию NOT x LIKE y ESCAPE z.

Предикат like

Формально предикат like определяется следующими синтаксическими правилами:

like_predicate ::= source_value [ NOT ] LIKE pattern_value [ ESCAPE escape_value ] source_value ::= value_expression pattern_value ::= value_expression escape_value ::= value_expression

Все три операнда (source_value, pattern_value и escape_value) должны быть одного типа: либо типа символьных строк, либо типа битовых строк5). В первом случае значением последнего операнда должна быть строка из одного символа, во втором - строка из 8 бит. Второй операнд, как правило, задается литералом соответствующего типа. В обоих случаях значение предиката равняется true в том и только в том случае, когда исходная строка (source_value) может быть сопоставлена с заданным шаблоном (pattern_value).

Предикат match

Предикат позволяет сформулировать условие соответствия строчного значения результату табличного подзапроса. Синтаксис определяется следующим правилом:

match_predicate ::= row_value_constructor MATCH [ UNIQUE ] [ SIMPLE | PARTIAL | FULL ] query_expression

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

Пусть x обозначает строку-первый операнд. Тогда:

Если отсутствует спецификация вида сопоставления или специфицирован тип сопоставления SIMPLE, то:если значение некоторого столбца x является неопределенным, то значением условия является true;если в x нет неопределенных значений, то:если не указано UNIQUE, и в результате выражения запроса существует (возможно, не уникальная) строка s в такая, что x = s, то значением условия является true;если указано UNIQUE, и в результате выражения запроса существует уникальная строка s, такая, что x = s, то значением условия является true;в противном случае значением условия является false.Если в условии присутствует спецификация PARTIAL, то:если все значения в x являются неопределенными, то значение условия есть true;иначе:если не указано UNIQUE, и в результате выражения запроса существует (возможно, не уникальная) строка s, такая, что каждое отличное от неопределенного значение x равно соответствующему значению s, то значение условия есть true;если указано UNIQUE, и в результате выражения запроса существует уникальная строка s, такая, что каждое отличное от неопределенного значение x равно соответствующему значению s, то значение условия есть true;в противном случае значение условия есть false.Если в условии присутствует спецификация FULL, то:если все значения в x неопределенные, то значение условия есть true;если ни одно значение в x не является неопределенным, то:если не указано UNIQUE, и в результате выражения запроса существует (возможно, не уникальная) строка s, такая, что x = s, то значение условия есть true;если указано UNIQUE, и в результате выражения запроса существует уникальная строка s, такая, что x = s, то значение условия есть true;в противном случае значение условия есть false.в противном случае значение условия есть false.

Предикат match

match_predicate ::= row_value_constructor MATCH [ UNIQUE ] [ SIMPLE | PARTIAL | FULL ] query_expression

Пусть x обозначает строку-первый операнд. Тогда:

Предикат null

Предикат null позволяет проверить, являются ли неопределенными значения всех элементов строки-операнда:

null_predicate ::= row_value_constructor IS [ NOT ] NULL

Пусть X обозначает строку-операнд. Если значения всех элементов X являются неопределенными, то значением условия X IS NULL является true; иначе - false. Если ни у одного элемента X значение не является неопределенным, то значением условия X IS NOT NULL является true; иначе - false.

Замечание: условие X IS NOT NULL имеет то же значение, что условие NOT X IS NULL для любого X в том и только в том случае, когда степень X равна 1. Полная семантика предиката null приведена в таблице 14.1.

Таблица 14.1.

Вид операндаВид условия

X IS X NULL	IS NOT NULL	NOT X IS NULL	NOT X IS NOT NULL
Степень 1: значение NULL	true	false	false	true
Степень 1: значение отлично от NULL	false	true	true	false
Степень > 1: у всех элементов значение NULL	true	false	false	true
Степень > 1: у некоторых(не у всех) элементов значение NULL	false	false	true	true
Степень > 1: ни у одного элемента нет значения NULL	false	true	true	false

Предикат null

Предикат null позволяет проверить, являются ли неопределенными значения всех элементов строки-операнда:

null_predicate ::= row_value_constructor IS [ NOT ] NULL

Таблица 14.1.

Вид операндаВид условия

X IS X NULL	IS NOT NULL	NOT X IS NULL	NOT X IS NOT NULL
Степень 1: значение NULL	true	false	false	true
Степень 1: значение отлично от NULL	false	true	true	false
Степень > 1: у всех элементов значение NULL	true	false	false	true
Степень > 1: у некоторых(не у всех) элементов значение NULL	false	false	true	true
Степень > 1: ни у одного элемента нет значения NULL	false	true	true	false

Предикат overlaps

Этот предикат служит для проверки перекрытия во времени двух событий. Условие определяется следующим синтаксисом:

overlaps_predicate ::= row_value_constructor OVERLAPS row_value_constructor

Степень каждой из строк-операндов должна быть равна 2. Тип данных первого столбца каждого из операндов должен быть типом даты-времени, и типы данных первых столбцов должны быть совместимы. Тип данных второго столбца каждого из операндов должен быть типом даты-времени или интервала. При этом:

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

Пусть D1 и D2 - значения первого столбца первого и второго операндов соответственно. Если второй столбец первого операнда имеет тип дата-время, то пусть E1 обозначает его значение. Если второй столбец первого операнда имеет тип INTERVAL, то пусть I1 -его значение, а E1 = D1 + I1. Если D1 является неопределенным значением или если E1 < D1, то пусть S1 = E1 и T1 = D1. В противном случае, пусть S1 = D1 и T1 = E1. Аналогично определяются S2 и T2 применительно ко второму операнду. Результат условия совпадает с результатом вычисления следующего булевского выражения:

(S1 > S2 AND NOT (S1 >= T2 AND T1 >= T2)) OR (S2 > S1 AND NOT (S2 >= T1 AND T2 >= T1)) OR (S1 = S2 AND (T1 <> T2 OR T1 = T2))

Предикат overlaps

overlaps_predicate ::= row_value_constructor OVERLAPS row_value_constructor

(S1 > S2 AND NOT (S1 >= T2 AND T1 >= T2)) OR (S2 > S1 AND NOT (S2 >= T1 AND T2 >= T1)) OR (S1 = S2 AND (T1 <> T2 OR T1 = T2))

Предикат similar

Формально предикат similar определяется следующими синтаксическими правилами:

similar_predicate ::= source_value [ NOT ] SIMILAR TO pattern_value [ ESCAPE escape_value ] source_value ::= character_expression pattern_value ::= character_expression escape_value ::= character_expression

Все три операнда (source_value, pattern_value и escape_value) должны иметь тип символьных строк. Значением последнего операнда должна быть строка из одного символа. Второй операнд, как правило, задается литералом соответствующего типа. В обоих случаях значение предиката равняется true в том и только в том случае, когда шаблон (pattern_value) должным образом сопоставляется с исходной строкой (source_value).

Основное отличие предиката similar от рассмотренного ранее предиката like состоит в существенно расширенных возможностях задания шаблона, основанных на использовании правил построения регулярных выражений. Регулярные выражения предиката similar определяются следующими синтаксическими правилами:

Поскольку в синтаксических правилах регулярных выражений символы "|", "[" и "]", используемые нами в качестве метасимволов в BNF, являются терминальными символами, они изображены как vertical_bar, left_bracket и right_bracket соответственно.

Создаваемое по приведенным правилам регулярное выражение представляет собой символьную строку, содержащую все символы, которые требуется явно сопоставлять с символами строки-источника. В строке могут находиться специальные символы, представляющие собой заменители обычных символов ("%" и "_"), обозначения операций ("|"), показатели числа возможных повторений ("*" и "+") и т. д. При вычислении регулярного выражения образуются все возможные символьные строки, не содержащие специальных символов и соответствующие исходному шаблону. Тем самым, значением предиката similar является true в том и только в том случае, когда среди всех символьных строк, генерируемых по регулярному выражению pattern_value, найдется символьная строка, совпадающая с source_value.

Рассмотрим несколько примеров регулярных выражений.

Выражение '(This is string1)|(This is string2)' производит две символьные строки: '(This is string1)' и '(This is string2)'. В общем случае в круглых скобках могут находиться произвольные регулярные выражения rexp1 и rexp2. Результатом вычисления '(rexp1)|(rexp2)' является множество символьных строк, генерируемых выражением rexp1, объединенное с множеством символьных строк, генерируемых выражением rexp2.

Выражение 'This is string [12]*' генерирует символьные строки 'This is string ', 'This is string 1', 'This is string 2', 'This is string 11', 'This is string 22', 'This is string 12', 'This is string 22', 'This is string 111' и т. д. Конструкция в квадратных скобках представляет собой один из вариантов определения набора символов (regular_character_set). В данном случае символы, входящие в определяемый набор, просто перечисляются. При вычислении регулярного выражения в каждой из генерируемых символьных строк конструкция в квадратных скобках заменяется одним из символов соответствующего набора.

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

Другая форма определения набора символов иллюстрируется регулярным выражением 'This is string [:DIGIT:]'. В этом случае конструкция в квадратных скобках представляет любой одиночный символ, изображающий десятичную цифру. Другими допустимыми в SQL идентификаторами наборов символов (regular_charset_id) являются ALPHA (любой символ алфавита), UPPER (любой символ верхнего регистра), LOWER (любой символ нижнего регистра) и ALNUM (любой алфавитно-цифровой символ).

Определяемый набор символов может задаваться нижней и верхней границей диапазона значений кодов допустимых символов. Например, в регулярном выражении 'This is string [3-8]' конструкция в квадратных скобках представляет собой любой одиночный символ, изображающий цифры от 3 до 8 включительно. Заметим, что при задании диапазона можно использовать любые символы, но требуется, чтобы значение кода символа левой границы диапазона было не больше значения кода символа правой границы.

Наконец, имеется еще одна возможность определения набора символов. Соответствующая конструкция позволяет указать, какие символы из общего набора символов SQL не входят в определяемый набор символов. Например, регулярное выражение '_S[^t]*ing%' генерирует все символьные строки, у которых вторым символом является "S", за которым (не обязательно непосредственно) следует подстрока "ing", но между "S" и "ing" отсутствуют вхождения символа "t".

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

В заключение данного пункта вернемся к отложенному в разделе "Скалярные выражения" лекции 13 обсуждению функции SUBSTRING ... SIMILAR ... ESCAPE. Напомним, что вызов этой функции определяется следующим синтаксисом:

SUBSTRING (character_value_expression SIMILAR character_value_expression ESCAPE character_value_expression)

Предположим, что в разделе ESCAPE (который должен присутствовать обязательно) задан символ "x". Тогда символьная строка, задаваемая во втором операнде, должна иметь вид 'rexp1x"rexp2x"rexp3', где rexp1, rexp2 и rexp3 являются регулярными выражениями. Функция пытается разделить символьную строку первого операнда на три раздела, первый из которых определяется путем сопоставления начала строки со строками, генерируемыми rexp1, второй - путем сопоставления оставшейся части строки первого операнда с rexp2 и третий - путем сопоставления конца этой строки с rexp3. Возвращаемым значением функции является средняя часть символьной строки первого операнда.

Вот пример вызова функции:

SUBSTRING ( 'This is string22' SIMILAR 'This is\"[:ALPHA:]+\"[:DIGIT:]+' ESCAPE '\' )

Результатом будет строка 'string'.

Предикат similar

Формально предикат similar определяется следующими синтаксическими правилами:

Рассмотрим несколько примеров регулярных выражений.

SUBSTRING (character_value_expression SIMILAR character_value_expression ESCAPE character_value_expression)

Вот пример вызова функции:

SUBSTRING ( 'This is string22' SIMILAR 'This is\"[:ALPHA:]+\"[:DIGIT:]+' ESCAPE '\' )

Результатом будет строка 'string'.

Предикат сравнения

Этот предикат предназначен для спецификации сравнения двух строчных значений. Синтаксис предиката следующий:

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

Пусть X и Y обозначают соответствующие элементы строк-операндов, а xv и yv - их значения. Тогда:

если xv и/или yv являются неопределенными значениями, то значение условия X comp_op Y -unknown;в противном случае значением условия X comp_op Y является true или false в соответствии с естественными правилами применения операции сравнения.

При этом:

Числа сравниваются в соответствии с правилами алгебры.

Сравнение двух символьных строк производится следующим образом:если длина строки X не равна длине строки Y, то для выравнивания длин строк более короткая строка расширяется символами набивки (pad symbol); если для используемого набора символов порядок сортировки явным образом не специфицирован, то в качестве символа набивки используется пробел;далее производится лексикографическое сравнение строк в соответствии с предопределенным или явно определенным порядком сортировки символов.

Сравнение двух битовых строк Xи Y основано на сравнении соответствующих бит. Если Xi и Yi - значения i-тых бит X и Y соответственно и если lx и ly обозначает длину в битах X и Y соответственно, то:X равно Y тогда и только тогда, когда lx = ly и Xi = Yi для всех i;X меньше Y тогда и только тогда, когда (a) lx < ly и Xi = Yi для всех i меньших или равных lx, или (b) Xi = Yi для всех i < n и Xn = 0, а Yn =1 для некоторого n меньшего или равного min (lx, ly).

Сравнение двух значений типа дата-время производится в соответствии с видом интервала, который получается при вычитании второго значения из первого. Пусть X и Y - сравниваемые значения, а H - наименее значимое поле даты-времени X и Y. Результат сравнения X comp_op Y определяется как (X - Y) H comp_ op INTERVAL (0) H. (Два значения типа дата-время сравнимы только в том случае, если они содержат одинаковый набор полей даты-времени.)

Предикат сравнения

Пусть X и Y обозначают соответствующие элементы строк-операндов, а xv и yv - их значения. Тогда:

При этом:

Числа сравниваются в соответствии с правилами алгебры.

Сравнение двух значений анонимного строкового

Сравнение двух значений анонимного строкового типа производится следующим образом. Пусть Rx и Ry обозначают строки-операнды, а Rxi и Ryi - i-тые элементы Rx и Ry соответственно. Вот как определяется результат сравнения Rx comp_op Ry:Rx = Ry есть true тогда и только тогда, когда Rxi = Ryi есть true для всех i;Rx <> Ry есть true тогда и только тогда, когда Rxi <> Ryi есть true для некоторого i;Rx < Ry есть true тогда и только тогда, когда Rxi = Ryi есть true для всех i < n, и Rxn < Ryn есть true для некоторого n;Rx > Ry есть true тогда и только тогда, когда Rxi = Ryi есть true для всех i < n, и Rxn > Ryn есть true для некоторого n;Rx <= Ry есть true тогда и только тогда, когда Rx = Ry есть true или Rx < Ry есть true;Rx >= Ry есть true тогда и только тогда, когда Rx = Ry есть true или Rx > Ry есть true;Rx = Ry есть false тогда и только тогда, когда Rx <> Ry есть true;Rx <> Ry есть false тогда и только тогда, когда Rx = Ry есть true;Rx < Ry есть false тогда и только тогда, когда Rx >= Ry есть true;Rx > Ry есть false тогда и только тогда, когда Rx <= Ry есть true;Rx <= Ry есть false тогда и только тогда, когда Rx > Ry есть true;Rx >= Ry есть false тогда и только тогда, когда Rx < Ry есть true;Rx comp_op Ry есть unknown тогда и только тогда, когда Rx comp_op Ry не есть true или false.

Сравнение двух значений анонимного строкового

Предикат сравнения с квантором

Этот предикат позволяет специфицировать квантифицированное сравнение строчного значения и определяется следующим синтаксическим правилом:

quantified_comparison_predicate ::= row_value_constructor comp_op { ALL | SOME | ANY } query_expression

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

Обозначим через x строку-первый операнд, а через S - результат вычисления выражения запроса. Пусть s обозначает произвольную строку таблицы S. Тогда:

условие x comp_op ALL S имеет значение true в том и только в том случае, когда S пусто, или значение условия x comp_op s равно true для каждой строки s, входящей в S. Условие x comp_op ALL S имеет значение false в том и только в том случае, когда значение предиката x comp_op s равно false хотя бы для одной строки s, входящей в S. В остальных случаях значение условия x comp_op ALL S равно unknown;условие x comp_op SOME S имеет значение false в том и только в том случае, когда S пусто, или значение условия x comp_op s равно false для каждой строки s, входящей в S. Условие x comp_op SOME S имеет значение true в том и только в том случае, когда значение предиката x comp_op s равно true хотя бы для одной строки s, входящей в S. В остальных случаях значение условия x comp_op SOME S равно unknown;условие x comp_op ANY S эквивалентно условию x comp_op SOME S.

Предикат сравнения с квантором

quantified_comparison_predicate ::= row_value_constructor comp_op { ALL | SOME | ANY } query_expression

Предикат unique

Этот предикат позволяет сформулировать условие отсутствия дубликатов в результате запроса:

unique_predicate ::= UNIQUE (query_expression)

Результатом вычисления условия UNIQUE (query_expression) является true в том и только в том случае, когда в таблице-результате выражения запросов отсутствуют какие-либо две строки, одна из которых является дубликатом другой. В противном случае значение условия есть false.

Предикат unique

Этот предикат позволяет сформулировать условие отсутствия дубликатов в результате запроса:

unique_predicate ::= UNIQUE (query_expression)

DEPT_NO FROM EMP WHERE

SELECT DISTINCT EMP. DEPT_NO FROM EMP WHERE EMP.EMP_NAME = 'Smith';

Пример 14.1. Найти номера отделов, в которых работают служащие с фамилией 'Smith'.

Закрыть окно

FROM EMP WHERE

SELECT EMP.DEPT_NO, COUNT(*) FROM EMP WHERE EMP.NAME = 'Smith' GROUP BY EMP.DEPT_NO;

Пример 14.1.1.

Закрыть окно

GROUP BY

SELECT EMP.DEPT_NO, COUNT(*)
FROM EMP
WHERE EMP.NAME = 'Smith'
GROUP BY EMP.DEPT_NO;

SELECT DISTINCT

SELECT DISTINCT EMP.DEPT_NO
FROM EMP
WHERE EMP.EMP_NAME = 'Smith';

и номера отделов служащих, родившихся

SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO FROM EMP WHERE EMP.EMP_BDATE > DATE '1965-04-15';

Пример 14.2. Найти номера, имена и номера отделов служащих, родившихся после 15 апреля 1965 г.

Закрыть окно

gt; DATE

SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO
FROM EMP
WHERE EMP.EMP_BDATE & gt; DATE '1965-04-15';

SELECT DEPT_TOTAL_SAL FROM DEPT WHERE

SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO FROM EMP WHERE EMP.EMP_SAL > 0.1 * ( SELECT DEPT_TOTAL_SAL FROM DEPT WHERE DEPT.DEPT_NO = EMP.DEPT_NO);

Пример 14.3. Найти номера, имена и номера отделов служащих, размер заработной платы которых составляет больше одной десятой объема фонда заработной платы их отделов.

Закрыть окно

DEPT_NO FROM EMP, DEPT WHERE

SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP. DEPT_NO FROM EMP, DEPT WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND EMP.EMP_SAL > 0.1 * DEPT.TOTAL_SAL;

Пример 14.3.1.

Закрыть окно

FROM EMP,

SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO
FROM EMP, DEPT
WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND
EMP.EMP_SAL > 0.1 * DEPT.TOTAL_SAL;

Пример SELECT EMP.EMP_NO

SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO
FROM EMP
WHERE EMP.EMP_SAL > 0.1 *
(SELECT DEPT_TOTAL_SAL
FROM DEPT
WHERE DEPT.DEPT_NO = EMP.DEPT_NO);

EMP_NAME FROM EMP AS EMP1,

SELECT EMP1.EMP_NO, EMP1.EMP_NAME, EMP1.DEPT_NO, EMP2. EMP_NAME FROM EMP AS EMP1, EMP AS EMP2, DEPT WHERE EMP1.EMP_SAL < 15000.00 AND EMP1.DEPT_NO = DEPT.DEPT_NO AND DEPT.DEPT_MNG = EMP2.EMP_NO;

Пример 14.4. Найти номера, имена, номера отделов и имена руководителей отделов служащих, размер заработной платы которых меньше 15000 руб.

Закрыть окно

SELECT EMP_NAME FROM EMP WHERE

SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO, ( SELECT EMP_NAME FROM EMP WHERE EMP_NO = DEPT_MNG) FROM EMP, DEPT WHERE EMP.EMP_SAL < 15000.00 AND EMP.DEPT_NO = DEPT.DEPT_NO;

Пример 14.4.1.

Закрыть окно

WHERE EMP_NO

SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO,
(SELECT EMP_NAME
FROM EMP
WHERE EMP_NO = DEPT_MNG)
FROM EMP, DEPT
WHERE EMP.EMP_SAL < 15000.00 AND
EMP.DEPT_NO = DEPT.DEPT_NO;

FROM EMP AS EMP1, EMP

SELECT EMP1.EMP_NO, EMP1.EMP_NAME,
EMP1.DEPT_NO, EMP2.EMP_NAME
FROM EMP AS EMP1, EMP AS EMP2, DEPT
WHERE EMP1.EMP_SAL < 15000.00 AND
EMP1.DEPT_NO = DEPT.DEPT_NO AND
DEPT.DEPT_MNG = EMP2.EMP_NO;

SELECT EMP_NO, EMP_NAME, EMP_SAL FROM

SELECT EMP_NO, EMP_NAME, EMP_SAL FROM EMP WHERE EMP_SAL BETWEEN 12000.00 AND 15000.00;

Пример 14.5. Найти номера, имена и размер зарплаты служащих, получающих зарплату в размере от 12000 до 15000 руб.

Закрыть окно

SELECT EMP_NO, EMP_NAME, EMP_SAL

SELECT EMP_NO, EMP_NAME, EMP_SAL
FROM EMP
WHERE EMP_SAL BETWEEN 12000.00 AND 15000.00;

SELECT EMP_NO, EMP_NAME, EMP_SAL FROM

SELECT EMP_NO, EMP_NAME, EMP_SAL FROM EMP WHERE EMP_SAL BETWEEN (SELECT AVG(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO) AND (SELECT EMP1.EMP_SAL FROM EMP EMP1 WHERE EMP1.EMP_NO = (SELECT DEPT.DEPT_MNG FROM DEPT WHERE DEPT.DEPT_NO = EMP.DEPT_NO));

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

Закрыть окно

SELECT EMP_NO, EMP_NAME, EMP_SAL

SELECT EMP_NO, EMP_NAME, EMP_SAL
FROM EMP
WHERE EMP_SAL BETWEEN
(SELECT AVG(EMP1.EMP_SAL)
FROM EMP EMP1
WHERE EMP.DEPT_NO = EMP1.DEPT_NO)
AND
(SELECT EMP1.EMP_SAL
FROM EMP EMP1
WHERE EMP1.EMP_NO =
(SELECT DEPT.DEPT_MNG
FROM DEPT
WHERE DEPT.DEPT_NO = EMP.DEPT_NO));

SELECT EMP_NO, EMP_NAME, EMP_SAL FROM

SELECT EMP_NO, EMP_NAME, EMP_SAL FROM EMP WHERE DEPT_NO IS NOT NULL AND EMP_SAL BETWEEN (SELECT AVG(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO) AND (SELECT EMP1.EMP_SAL FROM EMP EMP1 WHERE EMP1.EMP_NO = ( SELECT DEPT.DEPT_MNG FROM DEPT WHERE DEPT.DEPT_NO = EMP.DEPT_NO ) );

Пример 14.7.

Закрыть окно

WHERE DEPT_NO IS NOT NULL

SELECT EMP_NO, EMP_NAME, EMP_SAL
FROM EMP
WHERE DEPT_NO IS NOT NULL AND
EMP_SAL BETWEEN
(SELECT AVG(EMP1.EMP_SAL)
FROM EMP EMP1
WHERE EMP.DEPT_NO = EMP1.DEPT_NO)
AND
(SELECT EMP1.EMP_SAL
FROM EMP EMP1
WHERE EMP1.EMP_NO =
( SELECT DEPT.DEPT_MNG
FROM DEPT
WHERE DEPT.DEPT_NO = EMP.DEPT_NO ) );

SELECT EMP_NO, EMP_NAME FROM EMP

SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO IS NULL;

Пример 14.8. Найти номера и имена служащих, номер отдела которых неизвестен.

Закрыть окно

SELECT EMP_NO, EMP_NAME

SELECT EMP_NO, EMP_NAME
FROM EMP
WHERE DEPT_NO IS NULL;

SELECT EMP_NO, EMP_NAME, DEPT_NO FROM

SELECT EMP_NO, EMP_NAME, DEPT_NO FROM EMP WHERE DEPT_NO IN (15, 17, 19);

Пример 14.9. Найти номера, имена и номера отделов сотрудников, работающих в отделах 15, 17 и 19.

Закрыть окно

SELECT EMP_NO, EMP_NAME, DEPT_NO FROM

SELECT EMP_NO, EMP_NAME, DEPT_NO FROM EMP WHERE DEPT_NO = 15 OR DEPT_NO = 17 OR DEPT_NO = 19;

Пример 14.9.1.

Закрыть окно

SELECT EMP_NO, EMP_NAME,

SELECT EMP_NO, EMP_NAME, DEPT_NO
FROM EMP
WHERE DEPT_NO = 15
OR DEPT_NO = 17
OR DEPT_NO = 19;

SELECT EMP_NO, EMP_NAME,

SELECT EMP_NO, EMP_NAME, DEPT_NO
FROM EMP
WHERE DEPT_NO IN (15, 17, 19);

SELECT EMP_NO FROM EMP WHERE

SELECT EMP_NO FROM EMP WHERE EMP_NO NOT IN (SELECT DEPT_MNG FROM DEPT) AND EMP_SAL IN (SELECT EMP_SAL FROM EMP, DEPT WHERE EMP_NO = DEPT_MNG);

Пример 14.10. Найти номера сотрудников, не являющихся руководителями отделов и получающих заплату, размер которой равен размеру зарплаты какого-либо руководителя отдела.

Закрыть окно

SELECT DISTINCT EMP_NO FROM EMP,

SELECT DISTINCT EMP_NO FROM EMP, EMP EMP1, DEPT WHERE EMP_NO NOT IN (SELECT DEPT_MNG FROM DEPT) AND EMP_SAL = EMP1_SAL AND EMP1.EMP_NO = DEPT.DEPT_MNG;

Пример 14.10.1.

Закрыть окно

FROM EMP, EMP EMP1,

SELECT DISTINCT EMP_NO
FROM EMP, EMP EMP1, DEPT
WHERE EMP_NO NOT IN (SELECT DEPT_MNG FROM DEPT)
AND EMP_SAL = EMP1_SAL
AND EMP1.EMP_NO = DEPT.DEPT_MNG;

WHERE EMP_NO NOT IN

SELECT EMP_NO
FROM EMP
WHERE EMP_NO NOT IN (SELECT DEPT_MNG FROM DEPT)
AND EMP_SAL IN (SELECT EMP_SAL FROM EMP,
DEPT WHERE EMP_NO = DEPT_MNG);

SELECT PRO_TITLE FROM PRO WHERE

SELECT PRO_TITLE FROM PRO WHERE PRO_TITLE LIKE '%next%step%' OR PRO_TITLE LIKE 'Next%step%';

Пример 14.11. Найти номера проектов, в названии которых присутствуют слова 'next' и 'step'. Слова должны следовать именно в такой последовательности, но слово 'next' может быть первым в названии проекта.

Закрыть окно

SELECT PRO_TITLE FROM PRO WHERE

SELECT PRO_TITLE FROM PRO WHERE PRO_TITLE LIKE '%ext%step%';

Пример 14.11.1.

Закрыть окно

WHERE PRO_TITLE LIKE

SELECT PRO_TITLE
FROM PRO
WHERE PRO_TITLE LIKE '%ext%step%';

WHERE PRO_TITLE LIKE

SELECT PRO_TITLE
FROM PRO
WHERE PRO_TITLE LIKE '%next%step%'
OR PRO_TITLE LIKE 'Next%step%';

DEPT_NO FROM EMP, DEPT, PRO

SELECT DISTINCT DEPT. DEPT_NO FROM EMP, DEPT, PRO WHERE EMP.EMP_NO = PRO.PRO_MNG AND EMP.DEPT_NO = DEPT.DEPT_NO AND PRO.PRO_TITLE LIKE DEPT.DEPT_NAME || '%';

Пример 14.12. Найти номера отделов, сотрудники которых являются менеджерами проектов, и название каждого из этих проектов начинается с названия отдела.

Закрыть окно

DEPT_NO FROM DEPT WHERE

SELECT DEPT. DEPT_NO FROM DEPT WHERE DEPT.DEPT_NO IN (SELECT EMP.DEPT_NO FROM EMP WHERE EMP.EMP_NO IN (SELECT PRO.PRO_MNG FROM PRO WHERE PRO.PRO_TITLE LIKE DEPT.DEPT_NAME || '%'));

Пример 14.12.1.

Закрыть окно

FROM EMP

SELECT DEPT.DEPT_NO
FROM DEPT
WHERE DEPT.DEPT_NO IN
(SELECT EMP.DEPT_NO
FROM EMP
WHERE EMP.EMP_NO IN
(SELECT PRO.PRO_MNG FROM PRO
WHERE PRO.PRO_TITLE LIKE DEPT.DEPT_NAME || '%'));

FROM EMP, DEPT,

SELECT DISTINCT DEPT.DEPT_NO
FROM EMP, DEPT, PRO
WHERE EMP.EMP_NO = PRO.PRO_MNG
AND EMP.DEPT_NO = DEPT.DEPT_NO
AND PRO.PRO_TITLE LIKE DEPT.DEPT_NAME || '%';

SELECT DEPT_NO FROM DEPT WHERE

SELECT DEPT_NO FROM DEPT WHERE DEPT_NAME NOT LIKE 'Software%';

Пример 14.13. Найти номера отделов, названия которых не начинаются со слова 'Software'.

Закрыть окно

WHERE DEPT_NAME NOT LIKE

SELECT DEPT_NO
FROM DEPT
WHERE DEPT_NAME NOT LIKE 'Software%';

SELECT DEPT_NAME, DEPT_NO FROM DEPT

SELECT DEPT_NAME, DEPT_NO FROM DEPT WHERE DEPT_NAME SIMILAR TO '(HARD|SOFT)WARE%\_[:DIGIT:]+' ESCAPE '\';

Пример 14.14. Найти номера и названия отделов, название которых начинается со слов 'Hardware' или 'Software', а за ними (не обязательно непосредственно) следует последовательность десятичных цифр, предваряемых символом подчеркивания.

Закрыть окно

WHERE DEPT_NAME SIMILAR TO

SELECT DEPT_NAME, DEPT_NO
FROM DEPT
WHERE DEPT_NAME SIMILAR TO
'(HARD|SOFT)WARE%\_[:DIGIT:]+' ESCAPE '\';

SELECT DEPT_NAME, DEPT_NO FROM DEPT

SELECT DEPT_NAME, DEPT_NO FROM DEPT WHERE DEPT_NAME SIMILAR TO '[^1-9]+%';

Пример 14.15. Найти номера и названия проектов, название которых не начинается с последовательности цифр.

Закрыть окно

WHERE DEPT_NAME SIMILAR TO

SELECT DEPT_NAME, DEPT_NO
FROM DEPT
WHERE DEPT_NAME SIMILAR TO '[^1-9]+%';

DEPT_NO FROM DEPT WHERE EXISTS

SELECT DEPT. DEPT_NO FROM DEPT WHERE EXISTS (SELECT EMP.EMP_NO FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND EXISTS (SELECT PRO.PRO_MNG FROM PRO WHERE PRO.PRO_MNG = EMP.EMP_NO));

Пример 14.16. Найти номера отделов, среди сотрудников которых имеются менеджеры проектов.

Закрыть окно

DEPT_NO FROM DEPT WHERE EXISTS

SELECT DEPT. DEPT_NO FROM DEPT WHERE EXISTS (SELECT EMP.EMP_NO FROM EMP, PRO WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND PRO.PRO_MNG = EMP.EMP_NO);

Пример 14.16.1.

Закрыть окно

FROM EMP,

SELECT DEPT.DEPT_NO
FROM DEPT
WHERE EXISTS
(SELECT EMP.EMP_NO
FROM EMP, PRO
WHERE EMP.DEPT_NO = DEPT.DEPT_NO
AND PRO.PRO_MNG = EMP.EMP_NO);

DEPT_NO FROM DEPT WHERE EXISTS

SELECT DEPT. DEPT_NO FROM DEPT WHERE EXISTS (SELECT * FROM EMP, DEPT WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND PRO.PRO_MNG = EMP.EMP_NO);

Пример 14.16.2.

Закрыть окно

FROM EMP,

SELECT DEPT.DEPT_NO
FROM DEPT
WHERE EXISTS
(SELECT *
FROM EMP, DEPT
WHERE EMP.DEPT_NO = DEPT.DEPT_NO
AND PRO.PRO_MNG = EMP.EMP_NO);

DEPT_NO FROM DEPT WHERE

SELECT DEPT. DEPT_NO FROM DEPT WHERE (SELECT COUNT(*) FROM EMP, DEPT WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND PRO.PRO_MNG = EMP.EMP_NO ) >= 1;

Пример 14.16.3.

Закрыть окно

FROM EMP,

SELECT DEPT.DEPT_NO
FROM DEPT
WHERE (SELECT COUNT(*)
FROM EMP, DEPT
WHERE EMP.DEPT_NO = DEPT.DEPT_NO
AND PRO.PRO_MNG = EMP.EMP_NO ) >= 1;

Пример SELECT DEPT.DEPT_NO

SELECT DEPT.DEPT_NO
FROM DEPT
WHERE EXISTS
(SELECT EMP.EMP_NO
FROM EMP
WHERE EMP.DEPT_NO = DEPT.DEPT_NO
AND EXISTS
(SELECT PRO.PRO_MNG
FROM PRO
WHERE PRO.PRO_MNG = EMP.EMP_NO));

DEPT_NO FROM DEPT WHERE NOT

SELECT DEPT. DEPT_NO FROM DEPT WHERE NOT EXISTS (SELECT * FROM EMP EMP1, EMP EMP2 WHERE EMP1.EMP_NO = DEPT.DEPT_MNG AND EMP2.DEPT_NO = DEPT.DEPT_NO AND EMP2.EMP_SAL > EMP1.EMP_SAL);

Пример 14.17. Найти номера отделов, размер заработной платы сотрудников которых не превышает размер заработной платы руководителя отдела.

Закрыть окно

FROM EMP EMP1, EMP

SELECT DEPT.DEPT_NO
FROM DEPT
WHERE NOT EXISTS
(SELECT *
FROM EMP EMP1, EMP EMP2
WHERE EMP1.EMP_NO = DEPT.DEPT_MNG AND
EMP2.DEPT_NO = DEPT.DEPT_NO AND
EMP2.EMP_SAL > EMP1.EMP_SAL);

SELECT DEPT_NO FROM DEPT WHERE

SELECT DEPT_NO FROM DEPT WHERE UNIQUE (SELECT EMP_NAME, EMP_BDATE FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO);

Пример 14.18. Найти номера отделов, сотрудников которых можно различить по имени и дате рождения.

Закрыть окно

SELECT DEPT_NO FROM DEPT WHERE

SELECT DEPT_NO FROM DEPT WHERE NOT EXISTS (SELECT * FROM EMP, EMP EMP1 WHERE EMP1.EMP_NO <> EMP.EMP_NO AND EMP.DEPT_NO = DEPT.DEPT_NO AND EMP1.DEPT_NO = DEPT.DEPT_NO AND EMP1.EMP_NAME = EMP.EMP_NAME AND(EMP1.EMP_BDATE = EMP.EMP_BDATE OR (EMP.EMP_BDATE IS NULL AND EMP1.EMP_BDATE IS NULL)));

Пример 14.18.1.

Закрыть окно

FROM EMP, EMP

SELECT DEPT_NO
FROM DEPT
WHERE NOT EXISTS
(SELECT *
FROM EMP, EMP EMP1
WHERE EMP1.EMP_NO <> EMP.EMP_NO
AND EMP.DEPT_NO = DEPT.DEPT_NO
AND EMP1.DEPT_NO = DEPT.DEPT_NO
AND EMP1.EMP_NAME = EMP.EMP_NAME
AND(EMP1.EMP_BDATE = EMP.EMP_BDATE
OR (EMP.EMP_BDATE IS NULL
AND EMP1.EMP_BDATE IS NULL)));

SELECT DEPT_NO FROM DEPT WHERE

SELECT DEPT_NO FROM DEPT WHERE (SELECT COUNT (EMP_NAME) FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO) = (SELECT COUNT (DISTINCT EMP_NAME) FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO);

Пример 14.18.2.

Закрыть окно

SELECT COUNT

SELECT DEPT_NO
FROM DEPT
WHERE ( SELECT COUNT (EMP_NAME)
FROM EMP
WHERE EMP.DEPT_NO = DEPT.DEPT_NO) =
(SELECT COUNT (DISTINCT EMP_NAME)
FROM EMP
WHERE EMP.DEPT_NO = DEPT.DEPT_NO);

SELECT EMP_NAME,

SELECT DEPT_NO
FROM DEPT
WHERE UNIQUE
( SELECT EMP_NAME, EMP_BDATE
FROM EMP
WHERE EMP.DEPT_NO = DEPT.DEPT_NO);

SELECT PRO_NO FROM PRO WHERE

SELECT PRO_NO FROM PRO WHERE (PRO_SDATE, PRO_DURAT) OVERLAPS (DATE '2000-01-15', DATE '2002-12-31');

Пример 14.19. Найти номера проектов, которые выполнялись в период с 15 января 2000 г. по 31 декабря 2002 г.

Закрыть окно

PRO_SDATE, PRO_DURAT)

SELECT PRO_NO
FROM PRO
WHERE ( PRO_SDATE, PRO_DURAT) OVERLAPS
(DATE '2000-01-15', DATE '2002-12-31');

SELECT PRO_TITLE FROM PRO WHERE

SELECT PRO_TITLE FROM PRO WHERE (PRO_SDATE, PRO_DURAT) OVERLAPS (CURRENT_DATE, INTERVAL '1' YEAR);

Пример 14.20. Найти названия проектов, которые будут выполняться в течение следующего года.

Закрыть окно

PRO_SDATE, PRO_DURAT)

SELECT PRO_TITLE
FROM PRO
WHERE ( PRO_SDATE, PRO_DURAT) OVERLAPS
(CURRENT_DATE, INTERVAL '1' YEAR);

SELECT EMP_NO FROM EMP WHERE

SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL > SOME (SELECT EMP1.EMP_SAL FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);

Пример 14.21. Найти номера сотрудников отдела номер 65, зарплата которых в этом отделе не является минимальной.

Закрыть окно

SELECT EMP_NO FROM EMP WHERE

SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EXISTS(SELECT * FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_SAL > EMP1.EMP_SAL);

Пример 14.21.1.

Закрыть окно

WHERE DEPT_NO

SELECT EMP_NO
FROM EMP
WHERE DEPT_NO = 65
AND EXISTS(SELECT *
FROM EMP EMP1
WHERE EMP.DEPT_NO = EMP1.DEPT_NO
AND EMP.EMP_SAL > EMP1.EMP_SAL);

SELECT EMP_NO FROM EMP WHERE

SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL > (SELECT MIN(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);

Пример 14.21.2.

Закрыть окно

WHERE DEPT_NO

SELECT EMP_NO
FROM EMP
WHERE DEPT_NO = 65 AND
EMP_SAL > (SELECT MIN(EMP1.EMP_SAL)
FROM EMP EMP1
WHERE EMP.DEPT_NO = EMP1.DEPT_NO);

WHERE DEPT_NO

SELECT EMP_NO
FROM EMP
WHERE DEPT_NO = 65
AND EMP_SAL > SOME (SELECT EMP1.EMP_SAL
FROM EMP EMP1
WHERE EMP.DEPT_NO = EMP1.DEPT_NO);

SELECT EMP_NO, EMP_NAME FROM EMP

SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND EMP_NAME = SOME (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO);

Пример 14.22. Найти номера и имена сотрудников отдела 65, однофамильцы которых работают в этом же отделе.

Закрыть окно

SELECT EMP_NO, EMP_NAME FROM EMP

SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND EMP_NAME IN (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO);

Пример 14.22.1.

Закрыть окно

SELECT EMP_NO,

SELECT EMP_NO, EMP_NAME
FROM EMP
WHERE DEPT_NO = 65 AND
EMP_NAME IN (SELECT EMP1.EMP_NAME
FROM EMP EMP1
WHERE EMP.DEPT_NO = EMP1.DEPT_NO
AND EMP.EMP_NO <> EMP1.EMP_NO);

SELECT EMP_NO, EMP_NAME FROM EMP

SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND (SELECT COUNT(*) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO ) >= 1;

Пример 14.22.2.

Закрыть окно

SELECT EMP_NO,

SELECT EMP_NO, EMP_NAME
FROM EMP
WHERE DEPT_NO = 65 AND
(SELECT COUNT(*)
FROM EMP EMP1
WHERE EMP.DEPT_NO = EMP1.DEPT_NO
AND EMP.EMP_NO <> EMP1.EMP_NO ) >= 1;

EMP_NAME FROM EMP, EMP EMP1

SELECT DISTINCT EMP.EMP_NO, EMP. EMP_NAME FROM EMP, EMP EMP1 WHERE EMP.DEPT_NO = 65 AND EMP.EMP_NAME = EMP1.EMP_NAME AND EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO;

Пример 14.22.3.

Закрыть окно

FROM EMP, EMP

SELECT DISTINCT EMP.EMP_NO, EMP.EMP_NAME
FROM EMP, EMP EMP1
WHERE EMP.DEPT_NO = 65
AND EMP.EMP_NAME = EMP1.EMP_NAME
AND EMP.DEPT_NO = EMP1.DEPT_NO
AND EMP.EMP_NO <> EMP1.EMP_NO;

SELECT EMP_NO,

SELECT EMP_NO, EMP_NAME
FROM EMP
WHERE DEPT_NO = 65 AND
EMP_NAME = SOME (SELECT EMP1.EMP_NAME
FROM EMP EMP1
WHERE EMP.DEPT_NO = EMP1.DEPT_NO
AND EMP.EMP_NO <> EMP1.EMP_NO);

SELECT EMP_NO FROM EMP WHERE

SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL >= ALL(SELECT EMP1.EMP_SAL FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);

Пример 14.23. Найти номера сотрудников отдела номер 65, зарплата которых в этом отделе является максимальной.

Закрыть окно

SELECT EMP_NO FROM EMP WHERE

SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND NOT EXISTS (SELECT * FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_SAL < EMP1.EMP_SAL);

Пример 14.23.1.

Закрыть окно

AND NOT EXISTS

SELECT EMP_NO
FROM EMP
WHERE DEPT_NO = 65
AND NOT EXISTS (SELECT *
FROM EMP EMP1
WHERE EMP.DEPT_NO = EMP1.DEPT_NO
AND EMP.EMP_SAL < EMP1.EMP_SAL);

SELECT EMP_NO FROM EMP WHERE

SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL = (SELECT MAX(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);

Пример 14.23.2.

Закрыть окно

WHERE DEPT_NO

SELECT EMP_NO
FROM EMP
WHERE DEPT_NO = 65
AND EMP_SAL = (SELECT MAX(EMP1.EMP_SAL)
FROM EMP EMP1
WHERE EMP.DEPT_NO = EMP1.DEPT_NO);

WHERE DEPT_NO

SELECT EMP_NO
FROM EMP
WHERE DEPT_NO = 65
AND EMP_SAL >= ALL(SELECT EMP1.EMP_SAL
FROM EMP EMP1
WHERE EMP.DEPT_NO = EMP1.DEPT_NO);

SELECT EMP_NO, EMP_NAME FROM EMP

SELECT EMP_NO, EMP_NAME FROM EMP WHERE EMP_NAME <> ALL (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP1.EMP_NO <> EMP.EMP_NO);

Пример 14.24. Найти номера и имена сотрудников, не имеющих однофамильцев

Закрыть окно

SELECT EMP_NO,

SELECT EMP_NO, EMP_NAME
FROM EMP
WHERE EMP_NAME <> ALL (SELECT EMP1.EMP_NAME
FROM EMP EMP1
WHERE EMP1.EMP_NO <> EMP.EMP_NO);

SELECT EMP_NO, DEPT_NO FROM EMP

SELECT EMP_NO, DEPT_NO FROM EMP WHERE (DEPT_NO, EMP_BDATE) MATCH SIMPLE (SELECT EMP1.DEPT_NO, EMP1.EMP_BDATE FROM EMP EMP1 WHERE EMP1.EMP_NO <> EMP.EMP_NO);

Пример 14.25.

Закрыть окно

SELECT EMP_NO,

SELECT EMP_NO, DEPT_NO
FROM EMP
WHERE (DEPT_NO, EMP_BDATE) MATCH SIMPLE
(SELECT EMP1.DEPT_NO, EMP1.EMP_BDATE
FROM EMP EMP1
WHERE EMP1.EMP_NO <> EMP.EMP_NO);

SELECT EMP_NO, DEPT_NO FROM EMP

SELECT EMP_NO, DEPT_NO FROM EMP WHERE (DEPT_NO, EMP_BDATE) MATCH PARTIAL (SELECT EMP1.DEPT_NO, EMP1.EMP_BDATE FROM EMP EMP1 WHERE EMP1.EMP_NO <> EMP.EMP_NO);

Пример 14.26.

Закрыть окно

SELECT EMP_NO,

SELECT EMP_NO, DEPT_NO
FROM EMP
WHERE (DEPT_NO, EMP_BDATE) MATCH PARTIAL
(SELECT EMP1.DEPT_NO, EMP1.EMP_BDATE
FROM EMP EMP1
WHERE EMP1.EMP_NO <> EMP.EMP_NO);

SELECT EMP_NO, DEPT_NO FROM EMP

SELECT EMP_NO, DEPT_NO FROM EMP WHERE (DEPT_NO, EMP_BDATE) MATCH UNIQUE FULL (SELECT EMP1.DEPT_NO, EMP1.EMP_BDATE FROM EMP EMP1 WHERE EMP1.EMP_NO <> EMP.EMP_NO);

Пример 14.27.

Закрыть окно

SELECT EMP_NO,

SELECT EMP_NO, DEPT_NO
FROM EMP
WHERE (DEPT_NO, EMP_BDATE) MATCH UNIQUE FULL
(SELECT EMP1.DEPT_NO, EMP1.EMP_BDATE
FROM EMP EMP1
WHERE EMP1.EMP_NO <> EMP.EMP_NO);

SELECT EMP_NO, EMP_NAME FROM EMP

Пример 14.28. Найти номера и имена служащих отдела 65, которых можно отличить по данным об имени и дате рождения от руководителя отдела 65.

Закрыть окно

FROM EMP EMP1,

EMP_NO FROM EMP EMP1, EMP

SELECT EMP1.EMP_NO, EMP2. EMP_NO FROM EMP EMP1, EMP EMP2 WHERE EMP1.EMP_NO <> EMP2.EMP_NO AND NOT ((EMP1.EMP_NAME, EMP1.EMP_BDATE) DISTINCT FROM (EMP2.EMP_NAME, EMP2.EMP_BDATE));

Пример 14.29. Найти все пары номеров таких служащих отдела 65, которых нельзя различить по данным об имени и дате рождения.

Закрыть окно

FROM EMP EMP1, EMP

SELECT EMP1.EMP_NO, EMP2.EMP_NO
FROM EMP EMP1, EMP EMP2
WHERE EMP1.EMP_NO <> EMP2.EMP_NO
AND NOT ((EMP1.EMP_NAME, EMP1.EMP_BDATE) DISTINCT FROM
(EMP2.EMP_NAME, EMP2.EMP_BDATE));

в раздел SELECT, потому что

SELECT DISTINCT EMP.DEPT_NO FROM EMP WHERE EMP.EMP_NAME = 'Smith';
Пример 14.1. Найти номера отделов, в которых работают служащие с фамилией 'Smith'. (html, txt)
Мы добавили спецификацию DISTINCT в раздел SELECT, потому что в одном отделе могут работать несколько служащих с фамилией 'Smith', а их число нас в данном случае не интересует. Кстати, если бы нас интересовало число служащих с фамилией 'Smith' в каждом отделе, где такие служащие работают, то следовало бы, например, написать такой запрос (пример 14.1.1):
SELECT EMP.DEPT_NO, COUNT(*) FROM EMP WHERE EMP.NAME = 'Smith' GROUP BY EMP.DEPT_NO;
Пример 14.1.1.
(html, txt)
В этом варианте запроса спецификация DISTINCT не требуется, поскольку в запросе содержится раздел GROUP BY, группировка производится в соответствии со значениями столбца EMP.DEPT_NO, и строка результата соответствует одной группе.
SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO FROM EMP WHERE EMP.EMP_BDATE > DATE '1965-04-15';
Пример 14.2. Найти номера, имена и номера отделов служащих, родившихся после 15 апреля 1965 г. (html, txt)
В результате этого запроса дубликатов быть не может, поскольку в список выборки включен столбец, являющийся первичным ключом таблицы EMP. Должно быть ясно, что по этой причине все строки результата будут различными.
SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO FROM EMP WHERE EMP.EMP_SAL > 0.1 * (SELECT DEPT_TOTAL_SAL FROM DEPT WHERE DEPT.DEPT_NO = EMP.DEPT_NO);
Пример 14.3. Найти номера, имена и номера отделов служащих, размер заработной платы которых составляет больше одной десятой объема фонда заработной платы их отделов. (html, txt)
В этом SQL-запросе имеются две интересные особенности, которые мы до сих пор не обсуждали. Во-первых, второй операнд операции сравнения содержит подзапрос, возвращающий единственное значение, поскольку логическое выражение раздела WHERE этого подзапроса состоит из условия, однозначно определяющего значение первичного ключа таблицы DEPT. Во-вторых, в условии раздела WHERE подзапроса используется ссылка на столбец таблицы EMP, указанной в разделе FROM "внешнего" запроса. Подобные подзапросы в терминологии SQL традиционно называются корреляционными, и их следует понимать следующим образом1).
При выполнении внешнего запроса последовательно, строка за строкой, в некотором порядке, определяемом системой, производится проверка соответствия строк результирующей таблицы раздела FROM условию раздела WHERE. Если это условие включает корреляционные подзапросы, то внутри каждого из этих подзапросов ссылка на столбец внешней таблицы трактуется как ссылка на столбец текущей строки данной таблицы во внешнем цикле. Естественно, условие WHERE любого подзапроса может включать более глубоко вложенные подзапросы, на которые распространяется то же правило корреляции с внешними таблицами.
Кстати, эквивалентная формулировка на языке SQL примера 14.3 выглядит следующим образом (пример 14.3.1):

SELECT PRO_NO FROM PRO WHERE

SELECT PRO_NO FROM PRO WHERE (PRO_SDATE, PRO_DURAT) OVERLAPS (DATE '2000-01-15', DATE '2002-12-31');
Пример 14.19. Найти номера проектов, которые выполнялись в период с 15 января 2000 г. по 31 декабря 2002 г. (html, txt) SELECT PRO_TITLE FROM PRO WHERE (PRO_SDATE, PRO_DURAT) OVERLAPS (CURRENT_DATE, INTERVAL '1' YEAR);
Пример 14.20. Найти названия проектов, которые будут выполняться в течение следующего года. (html, txt)

SELECT EMP_NO FROM EMP WHERE

SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL > SOME (SELECT EMP1.EMP_SAL FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);
Пример 14.21. Найти номера сотрудников отдела номер 65, зарплата которых в этом отделе не является минимальной. (html, txt)
Одна из возможных альтернативных формулировок этого запроса может основываться на использовании предиката EXISTS (пример 14.21.1):
SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EXISTS(SELECT * FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_SAL > EMP1.EMP_SAL);
Пример 14.21.1.
(html, txt)
Вот альтернативная формулировка этого запроса, основанная на использовании агрегатной функции MIN (пример 14.21.2):
SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL > (SELECT MIN(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);
Пример 14.21.2.
(html, txt) SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND EMP_NAME = SOME (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO);
Пример 14.22. Найти номера и имена сотрудников отдела 65, однофамильцы которых работают в этом же отделе. (html, txt)
Заметим, что эта формулировка эквивалентна следующей формулировке (пример 14.22.1):
SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND EMP_NAME IN (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO);
Пример 14.22.1.
(html, txt)
Возможна формулировка с использованием агрегатной функции COUNT (пример 14.22.2):
SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND (SELECT COUNT(*) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO ) >= 1;
Пример 14.22.2.
(html, txt)
Наиболее лаконичным образом этот запрос можно сформулировать с использованием соединения (пример 14.22.3):
SELECT DISTINCT EMP.EMP_NO, EMP.EMP_NAME FROM EMP, EMP EMP1 WHERE EMP.DEPT_NO = 65 AND EMP.EMP_NAME = EMP1.EMP_NAME AND EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO;

SELECT EMP_NO, EMP_NAME FROM EMP

SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND (EMP_NAME, EMP_BDATE) DISTINCT FROM (SELECT EMP1.EMP_NAME, EMP1.EMP_BDATE FROM EMP EMP1, DEPT WHERE EMP1.DEPT_NO = EMP.DEPT_NO AND DEPT.DEPT_MNG = EMP1.EMP_NO);
Пример 14.28. Найти номера и имена служащих отдела 65, которых можно отличить по данным об имени и дате рождения от руководителя отдела 65. (html, txt) SELECT EMP1.EMP_NO, EMP2.EMP_NO FROM EMP EMP1, EMP EMP2 WHERE EMP1.EMP_NO <> EMP2.EMP_NO AND NOT ((EMP1.EMP_NAME, EMP1.EMP_BDATE) DISTINCT FROM (EMP2.EMP_NAME, EMP2.EMP_BDATE));
Пример 14.29. Найти все пары номеров таких служащих отдела 65, которых нельзя различить по данным об имени и дате рождения. (html, txt)

в раздел SELECT, потому что

SELECT DISTINCT EMP.DEPT_NO FROM EMP WHERE EMP.EMP_NAME = 'Smith';
Пример 14.1. Найти номера отделов, в которых работают служащие с фамилией 'Smith'.
Мы добавили спецификацию DISTINCT в раздел SELECT, потому что в одном отделе могут работать несколько служащих с фамилией 'Smith', а их число нас в данном случае не интересует. Кстати, если бы нас интересовало число служащих с фамилией 'Smith' в каждом отделе, где такие служащие работают, то следовало бы, например, написать такой запрос (пример 14.1.1):
SELECT EMP.DEPT_NO, COUNT(*) FROM EMP WHERE EMP.NAME = 'Smith' GROUP BY EMP.DEPT_NO;
Пример 14.1.1.
В этом варианте запроса спецификация DISTINCT не требуется, поскольку в запросе содержится раздел GROUP BY, группировка производится в соответствии со значениями столбца EMP.DEPT_NO, и строка результата соответствует одной группе.
SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO FROM EMP WHERE EMP.EMP_BDATE > DATE '1965-04-15';
Пример 14.2. Найти номера, имена и номера отделов служащих, родившихся после 15 апреля 1965 г.
В результате этого запроса дубликатов быть не может, поскольку в список выборки включен столбец, являющийся первичным ключом таблицы EMP. Должно быть ясно, что по этой причине все строки результата будут различными.
SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO FROM EMP WHERE EMP.EMP_SAL > 0.1 * (SELECT DEPT_TOTAL_SAL FROM DEPT WHERE DEPT.DEPT_NO = EMP.DEPT_NO);
Пример 14.3. Найти номера, имена и номера отделов служащих, размер заработной платы которых составляет больше одной десятой объема фонда заработной платы их отделов.
В этом SQL-запросе имеются две интересные особенности, которые мы до сих пор не обсуждали. Во-первых, второй операнд операции сравнения содержит подзапрос, возвращающий единственное значение, поскольку логическое выражение раздела WHERE этого подзапроса состоит из условия, однозначно определяющего значение первичного ключа таблицы DEPT. Во-вторых, в условии раздела WHERE подзапроса используется ссылка на столбец таблицы EMP, указанной в разделе FROM "внешнего" запроса. Подобные подзапросы в терминологии SQL традиционно называются корреляционными, и их следует понимать следующим образом2).
При выполнении внешнего запроса последовательно, строка за строкой, в некотором порядке, определяемом системой, производится проверка соответствия строк результирующей таблицы раздела FROM условию раздела WHERE. Если это условие включает корреляционные подзапросы, то внутри каждого из этих подзапросов ссылка на столбец внешней таблицы трактуется как ссылка на столбец текущей строки данной таблицы во внешнем цикле. Естественно, условие WHERE любого подзапроса может включать более глубоко вложенные подзапросы, на которые распространяется то же правило корреляции с внешними таблицами.
Кстати, эквивалентная формулировка на языке SQL примера 14.3 выглядит следующим образом (пример 14.3.1):

SELECT PRO_NO FROM PRO WHERE

SELECT PRO_NO FROM PRO WHERE (PRO_SDATE, PRO_DURAT) OVERLAPS (DATE '2000-01-15', DATE '2002-12-31');
Пример 14.19. Найти номера проектов, которые выполнялись в период с 15 января 2000 г. по 31 декабря 2002 г.SELECT PRO_TITLE FROM PRO WHERE (PRO_SDATE, PRO_DURAT) OVERLAPS (CURRENT_DATE, INTERVAL '1' YEAR);
Пример 14.20. Найти названия проектов, которые будут выполняться в течение следующего года.

SELECT EMP_NO FROM EMP WHERE

SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL > SOME (SELECT EMP1.EMP_SAL FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);
Пример 14.21. Найти номера сотрудников отдела номер 65, зарплата которых в этом отделе не является минимальной.
Одна из возможных альтернативных формулировок этого запроса может основываться на использовании предиката EXISTS (пример 14.21.1):
SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EXISTS(SELECT * FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_SAL > EMP1.EMP_SAL);
Пример 14.21.1.
Вот альтернативная формулировка этого запроса, основанная на использовании агрегатной функции MIN (пример 14.21.2):
SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL > (SELECT MIN(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);
Пример 14.21.2. SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND EMP_NAME = SOME (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO);
Пример 14.22. Найти номера и имена сотрудников отдела 65, однофамильцы которых работают в этом же отделе.
Заметим, что эта формулировка эквивалентна следующей формулировке (пример 14.22.1):
SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND EMP_NAME IN (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO);
Пример 14.22.1.
Возможна формулировка с использованием агрегатной функции COUNT (пример 14.22.2):
SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND (SELECT COUNT(*) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO ) >= 1;
Пример 14.22.2.
Наиболее лаконичным образом этот запрос можно сформулировать с использованием соединения (пример 14.22.3):
SELECT DISTINCT EMP.EMP_NO, EMP.EMP_NAME FROM EMP, EMP EMP1 WHERE EMP.DEPT_NO = 65 AND EMP.EMP_NAME = EMP1.EMP_NAME AND EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_NO <> EMP1.EMP_NO;
Пример 14.22.3.
В последней формулировке мы вынуждены везде использовать квалифицированные имена столбцов, потому что на одном уровне используются два вхождения таблицы EMP.
SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL >= ALL(SELECT EMP1.EMP_SAL FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);

SELECT EMP_NO, EMP_NAME FROM EMP

SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO = 65 AND (EMP_NAME, EMP_BDATE) DISTINCT FROM (SELECT EMP1.EMP_NAME, EMP1.EMP_BDATE FROM EMP EMP1, DEPT WHERE EMP1.DEPT_NO = EMP.DEPT_NO AND DEPT.DEPT_MNG = EMP1.EMP_NO);
Пример 14.28. Найти номера и имена служащих отдела 65, которых можно отличить по данным об имени и дате рождения от руководителя отдела 65.SELECT EMP1.EMP_NO, EMP2.EMP_NO FROM EMP EMP1, EMP EMP2 WHERE EMP1.EMP_NO <> EMP2.EMP_NO AND NOT ((EMP1.EMP_NAME, EMP1.EMP_BDATE) DISTINCT FROM (EMP2.EMP_NAME, EMP2.EMP_BDATE));
Пример 14.29. Найти все пары номеров таких служащих отдела 65, которых нельзя различить по данным об имени и дате рождения.

DEPT_NO FROM EMP, DEPT WHERE

SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP. DEPT_NO FROM EMP, DEPT WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND EMP.EMP_SAL > 0.1 * DEPT.TOTAL_SAL;
Пример 14.3.1.
(html, txt)
Мы видим, что в терминах реляционной алгебры этот запрос представляет собой ограничение (по условию EMP.EMP_SAL > 0.1 * DEPT.TOTAL_SAL) эквисоединения таблиц EMP и DEPT (по условию EMP.DEPT_NO = DEPT.DEPT_NO). Подобную операцию часто называют полусоединением (semijoin), поскольку в результирующей таблице используются столбцы только одного из операндов операции эквисоединения. Мы привели вторую формулировку запроса, преследуя две цели: (1) продемонстрировать, каким образом предикат сравнения можно использовать для задания условия соединения, и (2) показать, что запросы, содержащие вложенные запросы, часто могут быть переформулированы в запросы с соединениями.
SELECT EMP1.EMP_NO, EMP1.EMP_NAME, EMP1.DEPT_NO, EMP2.EMP_NAME FROM EMP AS EMP1, EMP AS EMP2, DEPT WHERE EMP1.EMP_SAL < 15000.00 AND EMP1.DEPT_NO = DEPT.DEPT_NO AND DEPT.DEPT_MNG = EMP2.EMP_NO;
Пример 14.4. Найти номера, имена, номера отделов и имена руководителей отделов служащих, размер заработной платы которых меньше 15000 руб. (html, txt)
Этот запрос представляет собой эквисоединение ограничения таблицы EMP (по условию EMP_SAL < 15000.00) с таблицами DEPT и EMP (по условиям EMP.DEPT_NO = DEPT.DEPT_NO и DEPT.DEPT_MNG = EMP2.EMP_NO соответственно). Таблица EMP участвует в качестве операнда операции эквисоединения два раза. Поэтому в разделе FROM ей присвоены два псевдонима - EMP1 и EMP2. Следуя предписанному стандартом порядку выполнения запроса, можно считать, что введение этих псевдонимов обеспечивает переименование столбцов таблицы EMP, требуемое для выполнения раздела FROM с образованием расширенного декартова произведения таблиц-операндов.2) Заметим также, что в данном случае мы имеем дело с полным эквисоединением трех таблиц (а не с полусоединением), поскольку в списке выборки присутствуют имена столбцов каждой из них.
Покажем способ формулировки этого запроса с использованием вложенного подзапроса в качестве элемента списка выборки (пример 14.4.1):

SELECT EMP_NAME FROM EMP WHERE

SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO, ( SELECT EMP_NAME FROM EMP WHERE EMP_NO = DEPT_MNG) FROM EMP, DEPT WHERE EMP.EMP_SAL < 15000.00 AND EMP.DEPT_NO = DEPT.DEPT_NO;
Пример 14.4.1.
(html, txt)
Как показывает последний пример, в условии выборки подзапроса, участвующего в списке выборки, можно использовать имена столбов таблиц внешнего запроса. Из этой возможности языка SQL видно, что в разделе "Общие синтаксические правила построения скалярных выражений" предыдущей лекции для облегчения понимания материала мы немного исказили семантику оператора выборки. Там было сказано следующее: "После выполнения раздела WHERE (если в запросе отсутствуют разделы GROUP BY и HAVING, случай (a)) или выполнения явно или неявно заданного раздела HAVING (случай (b)) выполняется раздел SELECT. При выполнении этого раздела на основе таблицы T1 в случае (a) или на основе сгруппированной таблицы T3 в случае (b) строится таблица T4, содержащая столько строк, сколько строк или групп строк содержится в таблицах T1 илиT3 соответственно". В действительности, в общем случае очередная строка таблицы T4 должна строиться в тот момент, когда очередная строка или группа строк заносится в таблицу T1 или T3 соответственно.

В последней формулировке мы вынуждены

Пример 14.22.3.
(html, txt)
В последней формулировке мы вынуждены везде использовать квалифицированные имена столбцов, потому что на одном уровне используются два вхождения таблицы EMP.
SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL >= ALL(SELECT EMP1.EMP_SAL FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);
Пример 14.23. Найти номера сотрудников отдела номер 65, зарплата которых в этом отделе является максимальной. (html, txt)
Одна из возможных альтернативных формулировок этого запроса может основываться на использовании предиката NOT EXISTS (пример 14.23.1):
SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND NOT EXISTS (SELECT * FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_SAL < EMP1.EMP_SAL);
Пример 14.23.1.
(html, txt)
Можно сформулировать этот же запрос с использованием агрегатной функции MAX (пример 14.23.2):
SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL = (SELECT MAX(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);
Пример 14.23.2.
(html, txt) SELECT EMP_NO, EMP_NAME FROM EMP WHERE EMP_NAME <> ALL (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP1.EMP_NO <> EMP.EMP_NO);
Пример 14.24. Найти номера и имена сотрудников, не имеющих однофамильцев (html, txt)
Этот запрос можно переформулировать на основе использования предиката NOT EXISTS или агрегатной функции COUNT (по причине очевидности мы не приводим эти формулировки), но, в отличие от случая в примере 14.22.3, формулировка в виде запроса с соединением здесь не проходит. Формулировка запроса
SELECT DISTINCT EMP_NO, EMP_NAME FROM EMP, EMP EMP1 WHERE EMP.EMP_NAME <> EMP1.EMP_NAME AND EMP1.EMP_NO <> EMP.EMP_NO;
эквивалентна формулировке
SELECT EMP_NO, EMP_NAME FROM EMP WHERE EMP_NAME <> SOME (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP1.EMP_NO <> EMP.EMP_NO);
Очевидно, что этот запрос является бессмысленным ("Найти сотрудников, для которых имеется хотя бы один не однофамилец").

DEPT_NO FROM EMP, DEPT WHERE

SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP. DEPT_NO FROM EMP, DEPT WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND EMP.EMP_SAL > 0.1 * DEPT.TOTAL_SAL;
Пример 14.3.1.
Мы видим, что в терминах реляционной алгебры этот запрос представляет собой ограничение (по условию EMP.EMP_SAL > 0.1 * DEPT.TOTAL_SAL) эквисоединения таблиц EMP и DEPT (по условию EMP.DEPT_NO = DEPT.DEPT_NO). Подобную операцию часто называют полусоединением (semijoin), поскольку в результирующей таблице используются столбцы только одного из операндов операции эквисоединения. Мы привели вторую формулировку запроса, преследуя две цели: (1) продемонстрировать, каким образом предикат сравнения можно использовать для задания условия соединения, и (2) показать, что запросы, содержащие вложенные запросы, часто могут быть переформулированы в запросы с соединениями.
SELECT EMP1.EMP_NO, EMP1.EMP_NAME, EMP1.DEPT_NO, EMP2.EMP_NAME FROM EMP AS EMP1, EMP AS EMP2, DEPT WHERE EMP1.EMP_SAL < 15000.00 AND EMP1.DEPT_NO = DEPT.DEPT_NO AND DEPT.DEPT_MNG = EMP2.EMP_NO;
Пример 14.4. Найти номера, имена, номера отделов и имена руководителей отделов служащих, размер заработной платы которых меньше 15000 руб.
Этот запрос представляет собой эквисоединение ограничения таблицы EMP (по условию EMP_SAL < 15000.00) с таблицами DEPT и EMP (по условиям EMP.DEPT_NO = DEPT.DEPT_NO и DEPT.DEPT_MNG = EMP2.EMP_NO соответственно). Таблица EMP участвует в качестве операнда операции эквисоединения два раза. Поэтому в разделе FROM ей присвоены два псевдонима - EMP1 и EMP2. Следуя предписанному стандартом порядку выполнения запроса, можно считать, что введение этих псевдонимов обеспечивает переименование столбцов таблицы EMP, требуемое для выполнения раздела FROM с образованием расширенного декартова произведения таблиц-операндов.3) Заметим также, что в данном случае мы имеем дело с полным эквисоединением трех таблиц (а не с полусоединением), поскольку в списке выборки присутствуют имена столбцов каждой из них.
Покажем способ формулировки этого запроса с использованием вложенного подзапроса в качестве элемента списка выборки (пример 14.4.1):

SELECT EMP_NAME FROM EMP WHERE

SELECT EMP.EMP_NO, EMP.EMP_NAME, EMP.DEPT_NO, ( SELECT EMP_NAME FROM EMP WHERE EMP_NO = DEPT_MNG) FROM EMP, DEPT WHERE EMP.EMP_SAL < 15000.00 AND EMP.DEPT_NO = DEPT.DEPT_NO;
Пример 14.4.1.
Как показывает последний пример, в условии выборки подзапроса, участвующего в списке выборки, можно использовать имена столбов таблиц внешнего запроса. Из этой возможности языка SQL видно, что в разделе "Общие синтаксические правила построения скалярных выражений" предыдущей лекции для облегчения понимания материала мы немного исказили семантику оператора выборки. Там было сказано следующее: "После выполнения раздела WHERE (если в запросе отсутствуют разделы GROUP BY и HAVING, случай (a)) или выполнения явно или неявно заданного раздела HAVING (случай (b)) выполняется раздел SELECT. При выполнении этого раздела на основе таблицы T1 в случае (a) или на основе сгруппированной таблицы T3 в случае (b) строится таблица T4, содержащая столько строк, сколько строк или групп строк содержится в таблицах T1 илиT3 соответственно". В действительности, в общем случае очередная строка таблицы T4 должна строиться в тот момент, когда очередная строка или группа строк заносится в таблицу T1 или T3 соответственно.

Найти номера сотрудников отдела номер

Пример 14.23. Найти номера сотрудников отдела номер 65, зарплата которых в этом отделе является максимальной.
Одна из возможных альтернативных формулировок этого запроса может основываться на использовании предиката NOT EXISTS (пример 14.23.1):
SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND NOT EXISTS (SELECT * FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO AND EMP.EMP_SAL < EMP1.EMP_SAL);
Пример 14.23.1.
Можно сформулировать этот же запрос с использованием агрегатной функции MAX (пример 14.23.2):
SELECT EMP_NO FROM EMP WHERE DEPT_NO = 65 AND EMP_SAL = (SELECT MAX(EMP1.EMP_SAL) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO);
Пример 14.23.2. SELECT EMP_NO, EMP_NAME FROM EMP WHERE EMP_NAME <> ALL (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP1.EMP_NO <> EMP.EMP_NO);
Пример 14.24. Найти номера и имена сотрудников, не имеющих однофамильцев
Этот запрос можно переформулировать на основе использования предиката NOT EXISTS или агрегатной функции COUNT (по причине очевидности мы не приводим эти формулировки), но, в отличие от случая в примере 14.22.3, формулировка в виде запроса с соединением здесь не проходит. Формулировка запроса
SELECT DISTINCT EMP_NO, EMP_NAME FROM EMP, EMP EMP1 WHERE EMP.EMP_NAME <> EMP1.EMP_NAME AND EMP1.EMP_NO <> EMP.EMP_NO;
эквивалентна формулировке
SELECT EMP_NO, EMP_NAME FROM EMP WHERE EMP_NAME <> SOME (SELECT EMP1.EMP_NAME FROM EMP EMP1 WHERE EMP1.EMP_NO <> EMP.EMP_NO);
Очевидно, что этот запрос является бессмысленным ("Найти сотрудников, для которых имеется хотя бы один не однофамилец").

Примеры запросов с использованием предиката between

SELECT EMP_NO, EMP_NAME, EMP_SAL FROM EMP WHERE EMP_SAL BETWEEN 12000.00 AND 15000.00;

Пример 14.5. Найти номера, имена и размер зарплаты служащих, получающих зарплату в размере от 12000 до 15000 руб. (html, txt)

В этом запросе можно выделить три интересных момента. Во-первых, диапазон значений предиката BETWEEN задан двумя подзапросами, результатом каждого из которых является единственное значение. Первый подзапрос выдает единственное значение, поскольку в списке выборки содержится агрегатная функция (AVG) и отсутствует раздел GROUP BY, а второй - потому что в его разделе WHERE присутствует условие, задающее единственное значение первичного ключа. Во-вторых, в обоих подзапросах таблица EMP получает псевдоним EMP1 (в формулировке этого запроса мы старались использовать как можно меньше вспомогательных идентификаторов). Поскольку подзапросы выполняются независимо один от другого, использование общего имени не вызывает проблем. Наконец, в условии второго подзапроса присутствует более глубоко вложенный подзапрос, и в условии его раздела WHERE используется ссылка на столбец таблицы из самого внешнего раздела FROM.

Примеры запросов с использованием предиката between

SELECT EMP_NO, EMP_NAME, EMP_SAL FROM EMP WHERE EMP_SAL BETWEEN 12000.00 AND 15000.00;

Пример 14.5. Найти номера, имена и размер зарплаты служащих, получающих зарплату в размере от 12000 до 15000 руб.

Примеры запросов с использованием предиката exists

SELECT DEPT.DEPT_NO FROM DEPT WHERE EXISTS (SELECT EMP.EMP_NO FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND EXISTS (SELECT PRO.PRO_MNG FROM PRO WHERE PRO.PRO_MNG = EMP.EMP_NO));

Пример 14.16. Найти номера отделов, среди сотрудников которых имеются менеджеры проектов. (html, txt)

Эту формулировку можно упростить, избавившись от самого вложенного запроса (пример 14.16.1):

SELECT DEPT.DEPT_NO FROM DEPT WHERE EXISTS (SELECT EMP.EMP_NO FROM EMP, PRO WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND PRO.PRO_MNG = EMP.EMP_NO);

Пример 14.16.1.

(html, txt)

Далее заметим, что по смыслу предикат предиката EXISTS список выборки во вложенном подзапросе является несущественным, и формулировку запроса можно изменить, например, следующим образом (пример 14.16.2):

SELECT DEPT.DEPT_NO FROM DEPT WHERE EXISTS (SELECT * FROM EMP, DEPT WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND PRO.PRO_MNG = EMP.EMP_NO);

Пример 14.16.2.

(html, txt)

Запросы с предикатом EXISTS можно также переформулировать в виде запросов с предикатом сравнения (пример 14.16.3):

SELECT DEPT.DEPT_NO FROM DEPT WHERE (SELECT COUNT(*) FROM EMP, DEPT WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND PRO.PRO_MNG = EMP.EMP_NO ) >= 1;

Пример 14.16.3.

(html, txt) SELECT DEPT.DEPT_NO FROM DEPT WHERE NOT EXISTS (SELECT * FROM EMP EMP1, EMP EMP2 WHERE EMP1.EMP_NO = DEPT.DEPT_MNG AND EMP2.DEPT_NO = DEPT.DEPT_NO AND EMP2.EMP_SAL > EMP1.EMP_SAL);

Пример 14.17. Найти номера отделов, размер заработной платы сотрудников которых не превышает размер заработной платы руководителя отдела. (html, txt)

Примеры запросов с использованием предиката exists

SELECT DEPT.DEPT_NO FROM DEPT WHERE EXISTS (SELECT EMP.EMP_NO FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND EXISTS (SELECT PRO.PRO_MNG FROM PRO WHERE PRO.PRO_MNG = EMP.EMP_NO));

Пример 14.16. Найти номера отделов, среди сотрудников которых имеются менеджеры проектов.

Эту формулировку можно упростить, избавившись от самого вложенного запроса (пример 14.16.1):

SELECT DEPT.DEPT_NO FROM DEPT WHERE EXISTS (SELECT EMP.EMP_NO FROM EMP, PRO WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND PRO.PRO_MNG = EMP.EMP_NO);

Пример 14.16.1.

SELECT DEPT.DEPT_NO FROM DEPT WHERE EXISTS (SELECT * FROM EMP, DEPT WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND PRO.PRO_MNG = EMP.EMP_NO);

Пример 14.16.2.

Запросы с предикатом EXISTS можно также переформулировать в виде запросов с предикатом сравнения (пример 14.16.3):

SELECT DEPT.DEPT_NO FROM DEPT WHERE (SELECT COUNT(*) FROM EMP, DEPT WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND PRO.PRO_MNG = EMP.EMP_NO ) >= 1;

Пример 14.16.3. SELECT DEPT.DEPT_NO FROM DEPT WHERE NOT EXISTS (SELECT * FROM EMP EMP1, EMP EMP2 WHERE EMP1.EMP_NO = DEPT.DEPT_MNG AND EMP2.DEPT_NO = DEPT.DEPT_NO AND EMP2.EMP_SAL > EMP1.EMP_SAL);

Примеры запросов с использованием предиката in

SELECT EMP_NO, EMP_NAME, DEPT_NO FROM EMP WHERE DEPT_NO IN (15, 17, 19);

Пример 14.9. Найти номера, имена и номера отделов сотрудников, работающих в отделах 15, 17 и 19. (html, txt)

Конечно, эта формулировка запроса эквивалентна следующей формулировке (пример 14.9.1):

SELECT EMP_NO, EMP_NAME, DEPT_NO FROM EMP WHERE DEPT_NO = 15 OR DEPT_NO = 17 OR DEPT_NO = 19;

Пример 14.9.1.

(html, txt)

SELECT EMP_NO FROM EMP WHERE EMP_NO NOT IN (SELECT DEPT_MNG FROM DEPT) AND EMP_SAL IN (SELECT EMP_SAL FROM EMP, DEPT WHERE EMP_NO = DEPT_MNG);

Запросы, содержащие предикат IN с подзапросом, легко переформулировать в запросы с соединениями. Например, запрос из примера 14.10 эквивалентен следующему запросу с соединениями (пример 14.10.1):

SELECT DISTINCT EMP_NO FROM EMP, EMP EMP1, DEPT WHERE EMP_NO NOT IN (SELECT DEPT_MNG FROM DEPT) AND EMP_SAL = EMP1_SAL AND EMP1.EMP_NO = DEPT.DEPT_MNG;

Пример 14.10.1.

(html, txt)

По поводу этой второй формулировки следует сделать два замечания. Во-первых, как видно, мы изменили только ту часть условия, в которой использовался предикат IN, и не затронули предикат NOT IN. Запросы с предикатами NOT IN запросами с соединениями так просто не заменяются. Во-вторых, в разделе SELECT было добавлено ключевое слово DISTINCT, потому что в результате запроса во второй формулировке для каждого сотрудника будет содержаться столько строк, сколько существует руководителей отделов, получающих такую же зарплату, что и данный сотрудник.

Примеры запросов с использованием предиката in

SELECT EMP_NO, EMP_NAME, DEPT_NO FROM EMP WHERE DEPT_NO IN (15, 17, 19);

Пример 14.9. Найти номера, имена и номера отделов сотрудников, работающих в отделах 15, 17 и 19.

Конечно, эта формулировка запроса эквивалентна следующей формулировке (пример 14.9.1):

SELECT EMP_NO, EMP_NAME, DEPT_NO FROM EMP WHERE DEPT_NO = 15 OR DEPT_NO = 17 OR DEPT_NO = 19;

Пример 14.9.1.

SELECT EMP_NO FROM EMP WHERE EMP_NO NOT IN (SELECT DEPT_MNG FROM DEPT) AND EMP_SAL IN (SELECT EMP_SAL FROM EMP, DEPT WHERE EMP_NO = DEPT_MNG);

SELECT DISTINCT EMP_NO FROM EMP, EMP EMP1, DEPT WHERE EMP_NO NOT IN (SELECT DEPT_MNG FROM DEPT) AND EMP_SAL = EMP1_SAL AND EMP1.EMP_NO = DEPT.DEPT_MNG;

Пример 14.10.1.

Примеры запросов с использованием предиката like

SELECT PRO_TITLE FROM PRO WHERE PRO_TITLE LIKE '%next%step%' OR PRO_TITLE LIKE 'Next%step%';

Это очень неудачный запрос, потому что его выполнение, скорее всего, вынудит СУБД просмотреть все строки таблицы PRO и для каждой строки выполнить две проверки столбца PRO_TITLE. Можно немного улучшить формулировку с небольшим риском получить неверный ответ (пример 14.11.1):

SELECT PRO_TITLE FROM PRO WHERE PRO_TITLE LIKE '%ext%step%';

Пример 14.11.1.

(html, txt)

SELECT DISTINCT DEPT.DEPT_NO FROM EMP, DEPT, PRO WHERE EMP.EMP_NO = PRO.PRO_MNG AND EMP.DEPT_NO = DEPT.DEPT_NO AND PRO.PRO_TITLE LIKE DEPT.DEPT_NAME || '%';

Пример 14.12. Найти номера отделов, сотрудники которых являются менеджерами проектов, и название каждого из этих проектов начинается с названия отдела. (html, txt)

Вот как может выглядеть формулировка этого запроса, если использовать вложенные подзапросы (пример 14.12.1):

SELECT DEPT.DEPT_NO FROM DEPT WHERE DEPT.DEPT_NO IN (SELECT EMP.DEPT_NO FROM EMP WHERE EMP.EMP_NO IN (SELECT PRO.PRO_MNG FROM PRO WHERE PRO.PRO_TITLE LIKE DEPT.DEPT_NAME || '%'));

Пример 14.12.1.

(html, txt) SELECT DEPT_NO FROM DEPT WHERE DEPT_NAME NOT LIKE 'Software%';

Пример 14.13. Найти номера отделов, названия которых не начинаются со слова 'Software'. (html, txt)

Примеры запросов с использованием предиката like

SELECT PRO_TITLE FROM PRO WHERE PRO_TITLE LIKE '%next%step%' OR PRO_TITLE LIKE 'Next%step%';

SELECT PRO_TITLE FROM PRO WHERE PRO_TITLE LIKE '%ext%step%';

Пример 14.11.1.

SELECT DISTINCT DEPT.DEPT_NO FROM EMP, DEPT, PRO WHERE EMP.EMP_NO = PRO.PRO_MNG AND EMP.DEPT_NO = DEPT.DEPT_NO AND PRO.PRO_TITLE LIKE DEPT.DEPT_NAME || '%';

Вот как может выглядеть формулировка этого запроса, если использовать вложенные подзапросы (пример 14.12.1):

SELECT DEPT.DEPT_NO FROM DEPT WHERE DEPT.DEPT_NO IN (SELECT EMP.DEPT_NO FROM EMP WHERE EMP.EMP_NO IN (SELECT PRO.PRO_MNG FROM PRO WHERE PRO.PRO_TITLE LIKE DEPT.DEPT_NAME || '%'));

Пример 14.12.1. SELECT DEPT_NO FROM DEPT WHERE DEPT_NAME NOT LIKE 'Software%';

Пример 14.13. Найти номера отделов, названия которых не начинаются со слова 'Software'.

Примеры запросов с использованием предиката match

Все примеры этого пункта основаны на запросе "Найти номера служащих и номера их отделов для служащих, для которых в отделе со "схожим" номером работает служащий со "схожей" датой рождения" c некоторыми уточнениями.

SELECT EMP_NO, DEPT_NO FROM EMP WHERE (DEPT_NO, EMP_BDATE) MATCH SIMPLE (SELECT EMP1.DEPT_NO, EMP1.EMP_BDATE FROM EMP EMP1 WHERE EMP1.EMP_NO <> EMP.EMP_NO);

Пример 14.25.

(html, txt)

Этот запрос вернет данные о служащих, про которых:

либо неизвестны номер отдела или дата рождения (или и то, и другое);либо в отделе данного служащего работает по крайней мере еще один человек с той же датой рождения.

Если использовать предикат MATCH UNIQUE SIMPLE, то мы получим данные о служащих, про которых:

либо неизвестны номер отдела или дата рождения (или и то, и другое);либо в отделе данного служащего работает еще один человек с той же датой рождения.SELECT EMP_NO, DEPT_NO FROM EMP WHERE (DEPT_NO, EMP_BDATE) MATCH PARTIAL (SELECT EMP1.DEPT_NO, EMP1.EMP_BDATE FROM EMP EMP1 WHERE EMP1.EMP_NO <> EMP.EMP_NO);

Пример 14.26.

(html, txt)

Этот запрос вернет данные о служащих, про которых:

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

Если использовать предикат MATCH UNIQUE PARTIAL, то мы получим данные о служащих, про которых:

либо неизвестны номер отдела и дата рождения;либо неизвестен номер отдела, но имеется еще один человек с той же датой рождения;либо неизвестна дата рождения, но в отделе данного служащего работает еще один человек;либо известны и номер отдела, и дата рождения, и в отделе данного служащего работает еще один человек с той же датой рождения.SELECT EMP_NO, DEPT_NO FROM EMP WHERE (DEPT_NO, EMP_BDATE) MATCH UNIQUE FULL (SELECT EMP1.DEPT_NO, EMP1.EMP_BDATE FROM EMP EMP1 WHERE EMP1.EMP_NO <> EMP.EMP_NO);

Пример 14.27.

(html, txt)

Этот запрос вернет данные о служащих, о которых:

либо неизвестны номер отдела и дата рождения;либо в отделе данного служащего работает по крайней мере еще один человек с той же датой рождения.

Если использовать предикат MATCH UNIQUE SIMPLE, то мы получим данные о служащих, о которых:

либо неизвестны номер отдела и дата рождения;либо в отделе данного служащего работает еще один человек с той же датой рождения.

Примеры запросов с использованием предиката match

SELECT EMP_NO, DEPT_NO FROM EMP WHERE (DEPT_NO, EMP_BDATE) MATCH SIMPLE (SELECT EMP1.DEPT_NO, EMP1.EMP_BDATE FROM EMP EMP1 WHERE EMP1.EMP_NO <> EMP.EMP_NO);

Пример 14.25.

Этот запрос вернет данные о служащих, про которых:

Если использовать предикат MATCH UNIQUE SIMPLE, то мы получим данные о служащих, про которых:

Пример 14.26.

Этот запрос вернет данные о служащих, про которых:

Если использовать предикат MATCH UNIQUE PARTIAL, то мы получим данные о служащих, про которых:

Пример 14.27.

Этот запрос вернет данные о служащих, о которых:

Если использовать предикат MATCH UNIQUE SIMPLE, то мы получим данные о служащих, о которых:

Примеры запросов с использованием предиката null

На самом деле, в нашей формулировке запроса из примера 14.6 есть одна неточность. Если у некоторого сотрудника номер отдела неизвестен (значение столбца EMP.DEPT_NO у соответствующей строки таблицы сотрудников является неопределенным), то бессмысленно вычислять средний размер зарплаты отдела этого сотрудника и находить размер зарплаты руководителя отдела. Формулировка из примера 14.6 приведет к правильному результату, но это неочевидно.1) Чтобы сделать формулировку более понятной (и, возможно, помочь системе выполнить запрос более эффективно), нужно воспользоваться предикатом IS NOT NULL и переписать запрос следующим образом:

Пример 14.7.

(html, txt)

SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO IS NULL;

Пример 14.8. Найти номера и имена служащих, номер отдела которых неизвестен. (html, txt)

Примеры запросов с использованием предиката null

На самом деле, в нашей формулировке запроса из примера 14.6 есть одна неточность. Если у некоторого сотрудника номер отдела неизвестен (значение столбца EMP.DEPT_NO у соответствующей строки таблицы сотрудников является неопределенным), то бессмысленно вычислять средний размер зарплаты отдела этого сотрудника и находить размер зарплаты руководителя отдела. Формулировка из примера 14.6 приведет к правильному результату, но это неочевидно.4) Чтобы сделать формулировку более понятной (и, возможно, помочь системе выполнить запрос более эффективно), нужно воспользоваться предикатом IS NOT NULL и переписать запрос следующим образом:

Пример 14.7.

SELECT EMP_NO, EMP_NAME FROM EMP WHERE DEPT_NO IS NULL;

Пример 14.8. Найти номера и имена служащих, номер отдела которых неизвестен.

Примеры запросов с использованием предиката similar

SELECT DEPT_NAME, DEPT_NO FROM DEPT WHERE DEPT_NAME SIMILAR TO '(HARD|SOFT)WARE%\_[:DIGIT:]+' ESCAPE '\';

Пример 14.15. Найти номера и названия проектов, название которых не начинается с последовательности цифр. (html, txt)

Примеры запросов с использованием предиката similar

SELECT DEPT_NAME, DEPT_NO FROM DEPT WHERE DEPT_NAME SIMILAR TO '(HARD|SOFT)WARE%\_[:DIGIT:]+' ESCAPE '\';

Пример 14.15. Найти номера и названия проектов, название которых не начинается с последовательности цифр.

Примеры запросов с использованием предиката unique

SELECT DEPT_NO FROM DEPT WHERE UNIQUE (SELECT EMP_NAME, EMP_BDATE FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO);

Пример 14.18. Найти номера отделов, сотрудников которых можно различить по имени и дате рождения. (html, txt)

Возможна альтернативная, но более сложная формулировка этого запроса с использованием предиката NOT EXISTS (пример 14.18.1):

Пример 14.18.1.

(html, txt)

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

SELECT DEPT_NO FROM DEPT WHERE (SELECT COUNT (EMP_NAME) FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO) = (SELECT COUNT (DISTINCT EMP_NAME) FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO);

Пример 14.18.2.

(html, txt)

Примеры запросов с использованием предиката unique

SELECT DEPT_NO FROM DEPT WHERE UNIQUE (SELECT EMP_NAME, EMP_BDATE FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO);

Пример 14.18. Найти номера отделов, сотрудников которых можно различить по имени и дате рождения.

Пример 14.18.1.

SELECT DEPT_NO FROM DEPT WHERE (SELECT COUNT (EMP_NAME) FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO) = (SELECT COUNT (DISTINCT EMP_NAME) FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO);

Пример 14.18.2.

Конструкции оператора SELECT языка SQL

Конструкции оператора SELECT языка SQL в значительной степени ортогональны. В частности, выбор способа указания ссылки на таблицы в разделе FROM никак не влияет на выбор варианта формирования условия выборки в разделе WHERE. Это полезное свойство языка позволяет нам абстрагироваться от обсуждавшегося в предыдущей лекции многообразия способов указания ссылки на таблицу и сосредоточиться на возможностях формирования запросов при использовании различных предикатов, допускаемых стандартом SQL:1999 в логических выражениях раздела WHERE.
В стандарте SQL:1999 специфицированы 12 разновидностей предикатов, причем некоторые из них в действительности представляют собой семейства (например, под общим названием предиката сравнения скрываются шесть видов предикатов). Набор допустимых предикатов в SQL явно избыточен, но тем не менее в языке SQL имеется явная тенденция расширения этого набора. В частности, в SQL:2003 в связи с введением генератора типов мультимножеств в дополнение ко всем разновидностям предикатов SQL:1999 появилось три новых вида предикатов: предикаты для проверки того, что заданное значение является элементом мультимножества (MEMBER); что одно мультимножество входит в другое мультимножество (SUBMULTISET) и что мультимножество не содержит дубликаты (IS A SET). В этом курсе мы не приводим подробного описания этих видов предикатов по нескольким причинам:
введение конструктора типов мультимножеств в стандарте SQL:2003 не означает, что достигнута общая цель разработчиков стандарта SQL по обеспечению полного набора типов коллекций; по всей видимости, в будущих версиях стандарта появятся дополнительные конструкторы типов коллекций, и набор видов предикатов изменится;
предикаты с мультимножествами трудно пояснять и иллюстрировать в отрыве от других объектно-реляционных средств языка SQL;включение подобного материала в данную лекцию заметно увеличило бы ее объем и затруднило понимание более традиционных конструкций.
В лекции содержится много примеров запросов с использованием различных видов предикатов. Для полного усвоения материала требуется тщательно проанализировать эти примеры.

Конструкции оператора SELECT языка SQL

В этой лекции мы обсудили

В этой лекции мы обсудили наиболее важные возможности языка SQL, связанные с выборкой данных. Даже простые примеры, приводившиеся в лекции, показывают исключительную избыточность языка SQL. Еще в то время, когда действующим стандартом языка был SQL/92, была опубликована любопытная статья, в которой приводилось 25 формулировок одного и того же несложного запроса. При использовании всех возможностей SQL:1999 этих формулировок было бы гораздо больше.
Можно спорить, хорошо или плохо иметь возможность формулировать один и тот же запрос десятками разных способов. На мой взгляд, это не очень хорошо, поскольку увеличивает вероятность появления ошибок в запросах (особенно в сложных запросах). С другой стороны, таково объективное состояние дел, и мы стремились обеспечить в этой лекции материал, достаточный для того, чтобы прочувствовать различные возможности формулировки запросов. Как показывают следующие две лекции, возможности, предоставляемые оператором SELECT, в действительности гораздо шире.

В этой лекции мы обсудили

1)
Мы не обсуждаем в этом курсе предикаты, основанные на использовании выражений типа мультимножества, введенные в стандарте SQL:2003.

2)
Здесь снова идет речь о семантике выполнения оператора SELECT. В стандарте, естественно, не требуется, чтобы в реализации языка запросы с корреляционными подзапросами выполнялись в точности так, как описывается ниже. Суть в том, что какой бы реальный алгоритм выполнения такого запроса не использовался, результат выполнения должен быть точно таким же, как если бы запрос выполнялся по описываемой схеме.

3)
Кстати, в этом случае можно было бы обойтись введением одного псевдонима, оставив в качестве неявного второго псевдонима имя таблицы - EMP.

4)
Покажем это в развернутой форме. Пусть s - текущая строка таблицы EMP, просматриваемой в цикле внешнего запроса, и пусть s.DEPT_NO содержит неопределенное значение. Тогда для строки s условие первого подзапроса будет иметь вид NULL = EMP1.DEPT_NO, и значением этого условия будет unknown для любой строки таблицы EMP(EMP1), просматриваемой в цикле этого подзапроса. Поскольку unknown не является разрешающим условием, результирующая таблица подзапроса будет пуста, и агрегатная функция AVG выдаст значение NULL. По этому поводу значением условия внешнего запроса будет unknown, и строка s не войдет в результирующую таблицу.

5)
В стандарте SQL:1999 разрешается применять предикат LIKE только для битовых строк типа BLOB. Битовые строки типов BIT и BIT VARYING не допускаются.