С.Д. Кузнецов - Основы баз данных (1121716), страница 71
Текст из файла (страница 71)
Найти номера отделов, в которых средний размер зарплаты служащих равен максимальному размеру зарплаты служащих какого-либо другого отдела (другая формулировка для примера ! 5.7). ЯЕЬКСТ РЕРТ.РЕРТ МО ГНОМ РКРТ, ЕМР ИНЕВЕ РЕРТ.РЕРТ МО = ЕМР.РЕРТ МО СВОРР ВУ РЕРТ.РЕРТ МО НАЧ1МС ЕХ1ЯТЯ (ЯЕЬЕСТ * ГРОМ ЕМР ЕМР1 ХНЕВЕ ЕМР1.РЕРТ МО <> РЕРТ.РЕРТ МО СВОРР ВУ ЕМР1.РЕРТ МО НАЧ1МС МАХ (ЕМР1.ЕМР ЯАЬ)= АЧС (ЕМР.ЕМР ЯА))); В этой формулировке основной интерес представляет подзапрос, в котором корреляция с внешним запросом происходит через вызов агрегатной функции от группы строк внешнего запроса. Здесь также можно избавиться от разделов сВОРР Ву и МАч1мс во внешнем запросе (пример 15.9а): ЯЕЬЕСТ РЕРТ.РЕРТ МО РВОМ РЕРТ ХНЕВЕ ЕХ1ЯТЯ (ЯЕЬЕСТ ЕМР.РЕРТ МО ГНОМ ЕМР ИНЕВК ЕМР.РЕРТ МО <> РЕРТ.РЕРТ МО СВОРР ВУ ЕМР.РКРТ МО НАЧ1МС МАЛ (ЕМР.ЕМР ЯАЬ)= (ЯЕ1 ЕСТ АЧС (КМР1.ЕМР ЯАЬ) ГРОМ ЕМР ЕМР1 ХНЕВЕ ЕМР1.РКРТ МО = РЕРТ.РЕРТ МО)); Лекция 13 Группировка и условия раздела НАЧ(ИС, порождаемые и соединенные таблицы Предикат ио1ипе Пример 15.10.
Найти номера отделов и средний размер зарплаты сотрудников для таких отделов, где средний размер зарплаты служащих отличается от среднего размера зарплаты всех других отделов. ЯЕКЕГТ РКРТ.ОЕРТ ИО, АЧС (ЕМР.ЕМР ЯАЬ) ЯНОМ ОКРТ, ЕМР ХНЕЕЕ РКРТ.РКРТ ИО = ЕМР.ОЕРТ ИО СЕООР ВУ ОЕРТ.ОЕРТ ИО НАЧ1ИС ОИ1О1ОК (ЯК1 ЕСТ АЧС (ЕМР1.ЕМР ЯА1) РКСМ ЕМР ЕМР1 ХНЕЕЕ ЕМР1.ОКРТ ИО 13 ИОТ ИЖ? СКООР ВУ ЕМР1.ОЕРТ ИО НАЧ1ИГ АЧГ (ЕМР1.ЕМР ЯАЬ) = АЧС (ЕМР.ЕМР ЯА1)); Вот альтернативная формулировка этого запроса с использованием предиката иот кхтятЯ (пример 15.10а): ЯЕЬЕГТ ОЕРТ.ОЕРТ ИО, АЧС (ЕМР.ЕМР ЯА1 ) РКСМ ОЕРТ, ЕМР ХНЕЕЕ ОЕРТ.ОЕРТ ИО = ЕМР.ОКРТ ИО СЕООР ВУ РКРТ.ОЕРТ ИО НАЧ1ИС ИОТ ЕХ1ЯТЯ (ЯЕКЕГТ ЕМР1.ОЕРТ ИО РВОМ ЕМР ЕМР1 ХНЕЕК ЕМР1.ОЕРТ ИО <> ОЕРТ.ОЕРТ ИО СЕОУР ВУ ЕМР1.ОЕРТ ИО НАЧ1ИС АЧС (ЕМР1.ЕМР ЯА1)= АЧС (ЕМР.ЕМР ЯА1)); Предикаты сраанениа с каантором Пример 15.11.
Найти номера отделов и средний возрасг служащих для таких отделов, что найдется хотя бы один другой отдел, средний возраст служащих которого больше, чем в данном. ЯЕКЕГТ ОЕРТ ИО, АЧС (СОКЕЕИТ 1)АТŠ— ЕМР КОАТЕ) РВОМ ЕМР ХНКЕЕ ОЕРТ ИО 1Я ИОТ ИОН СЕОБР ВУ РКРТ ИО НАЧ1ИГ АЧС (ГОЕРЕИТ РАТŠ— ЕМР ВОАТК)< ЯОМЕ (ЯЕЬЕСТ АЧС (ГОКРЕИТ ВАТŠ— ЕМР1.ЕМР ВОАТЕ) РКСМ ЕМР ЕМР1 ХНКЕЕ ЕМР1.РКРТ ИО 1Я ИОТ ИОЫ СРО()Р ВХ ЕМР1.РКРТ ИО); 333 Основы баз данных Курс Напомним, что «ниладическая» функция СЛВВЕНТ ПАТЕ выдает текущую дату, и, следовательно, значением выражения СПВВент ЛАте ЕМР ВПАТЕ является интервал, представляющий текущий возраст служащего.
На наш взгляд, формулировка этого запроса несколько упрощается, если пользоваться предикатом ЕХ1ЯТЯ (пример 15.11а): ЯЕЬЕСТ ПЕРТ ИО, АЧС (СЛВВЕИТ ПАТŠ— ЕМР ВПАТЕ) РВОМ ЕМР ХНЕВЕ ПЕРТ ИО 1Я НОТ ЫЛЬ СВОПР ВУ ПЕРТ МО НАЧ1ИС ЕХ1ЯТЯ (ЯЕЬЕСТ ЕМР1.ПЕРТ ИО РВОМ ЕМР ЕМР1 ХНЕВЕ ЕМР1.ПЕРТ НО 1Я НОТ МОЛЬ СВОПР ВУ ЕМР1.ПЕРТ ИО НАЧ1ИС АЧС (СЛВВЕНТ ПАТŠ— ЕМР1.ЕМР ВЛАТЕ) > АЧС (СОВВЕИТ ПАТŠ— ЕМР.ЕМР НПАТЕ)); Пример 15.12. Найти номера отделов и средний возраст служа)цих для отделов с минимальным средним возрастом служащих.
ЯЕЬЕСТ ПЕРТ НО, АЧС (СПВКЕИТ ЛАТŠ— ЕМР ВПАТЕ) РЮМ ЕМР ХНЕКЕ ПЕРТ ИО 1Я НОТ НПЬЬ СВОБР ВУ ПЕРТ ИО НАЧ1ИС АЧС (СПВВЕНТ ПАТŠ— ЕМР ВПАТЕ) <= А1 Ь (ЯЕ1 ЕСТ АЧС (СОВВЕИТ ПАТŠ— ЕМР НПАТЕ) РВОМ ЕМР ХНЕВЕ ПЕРТ МО 1Я НОТ НЛЬ СВО()Р ВУ ЛЕРТ НО); Этот запрос легко формулируется в более понятном виде с использованием предиката нот ехьЯтЯ (пример 15.12а): ЯЕЬЕСТ ПЕРТ НО, АЧС (СОВВЕИТ ПАТŠ— ЕМР ВПАТЕ) РВОМ ЕМР ХНЕВЕ ПЕРТ ИО 1Я НОТ НПЬЬ СВОЛР ВУ ПЕРТ ИО НАЧТИС НОТ ЕХ1ЯТЯ (ЯЕЬЕСТ ЕМР1.ПЕРТ НО РЮМ ЕМР ЕМР1 ХНЕВЕ ЕМР1.ПЕРТ ИО 1Я НОТ НПЬЬ СЮЛР ВУ ЕМР1.ПЕРТ ИО НАЧ1НС АЧС (СУВВЕИТ ПАТŠ— ЕМР1.ЕМР ВПАТЕ) < АЧС (СОВВЕИТ ПАТŠ— ЕМР.ЕМР ВПАТЕ)); 334 Лекция 15 Группу ка и условия вздела НАИМС, порождаемые и соединенные таблицы Лредикат к(1всйдсс Пример 15.13.
Найти номера отделов, которые можно отличить от любого другого отдела по дате рождения руководителя и среднему размеру зарплаты. ЯЕЬЕСТ ОЕРТ.ОЕРТ ИО РВОМ ОЕРТ, ЕМР ЕМР1, ЕМР ЕМР2 ИНЕВЕ ОЕРТ.ОЕРТ ИО = ЕМР1.0ЕРТ ИО АИО 0ЕРТ.ОЕРТ МИС = ЕМР2.ЕМР ИО СВООР ВХ ОЕРТ.ОЕРТ ИО, ЕМР2.ЕМР ВОАТЕ НАЧ1ИС (ЕМР2.ЕМР ВОАТЕ, АЧС (ЕМР1.ЕМР ЯАЬ)) 01ЯТ1ИСТ РВОМ (ЯЕЬКСТ ЕМР2.ЕМР ВОАТЕ, АЧС (ЕМР1.ЕМР ЯАЬ) РВОМ 0ЕРТ ОЕРТ1, КМР ЕМР1, ЕМР КМР2 ИНЕВЕ 0ЕРТ1.0ЕРТ ИО = ЕМР1.0ЕРТ ИО АИ0 ОЕРТ1.0ЕРТ МИГ = КМР2.ЕМР ИО АИ0 0КРТ1.0ЕРТ ИО <> ОЕРТ.ОЕРТ ИО СВООР ВХ 0ЕРТ.ОЕРТ ИО, ЕМР2,ЕМР ВОАТЕ); Ссылки на порождаемые таблицы в разделе РВОМ В этом разделе мы приведем несколько примеров запросов, в разделе ЕВОН которых содержатся выражения запросов (ссылки на порождаемые таблицы, см.
раздел «Общие синтаксические правила построения скалярных выражений» лекции 13). Ещв один способ Формулировки запросов Прежде всего, на простом примере покажем, как использование ссылок на порождаемые таблицы расширяет возможности формулировки запросов. Пример 15.14. Найти номера отделов и имена руководителей отделов, которые числятся в тех же отделах, которыми руководят, и получают зарплату размер которой является максимальным для служащих данного отдела.
ЯЕ(,ЕСТ МИС.ОЕРТ ИО, МИС.МИС МАМЕ РВОМ (ЯЕЬЕСТ 0ЕРТ.ОЕРТ ИО, ЕМР.ОЕРТ ИО, ЕМР МАМЕ, ЕМР ЯАЬ РВОМ 0ЕРТ, ЕМР ИНЕВК 0КРТ.ОЕРТ МИС = ЕМР.ЕМР ИО) АЯ МИС (0ЕРТ ИО 1, 0ЕРТ ИО 2, МИС ИАМЕ, МИС ЯАЬ) ИНЕВЕ 0ЕРТ ИО 1 = ОЕРТ ИО 2 АИО МИС ЯА) = (ЯЕЬЕСТ МАХ (ЕМР ЯАЬ) РВОМ ЕМР ИНЕВЕ ЕМР.ОЕРТ ИО = 0ЕРТ ИО 1); 335 Основы баэ данных Курс В этом запросе порождаемая таблица ммс содержит по одной строке для каждого служащего, являющегося руководителем отдела. Первый столбец этой таблицы — юерт МО 1 — содержит номер отдела, которым руководит данный служащий.
В столбце ЮЕРТ НО 1 хранятся номера отделов, в которых числятся руководители отделов, а в столбцах ЕМР МАМЕ и ЕМР ЯАЬ содержатся имя служащего-руководителя отдела и размер его заработной платы соответственно. Конечно, этот запрос можно сформулировать и без использования ссылки на порождаемую таблицу в разделе РНОМ, например, следующим образом (пример 15.14а): ЯЕЬЕСТ ЮЕРТ.ЮЕРТ МО, ЕМР.ЕМР МАМЕ ЯНОМ ЮЕРТ, ЕМР ХНЕНЕ ЮЕРТ.ЮЕРТ ММС = ЕМР.ЕМР МО АМЮ ЮЕРТ.ЮЕРТ МО = ЕМР.ЮЕРТ МО АМЮ ЕМР.ЕМР ЯА1 = (ЯЕЬЕСТ МАХ(ЕМР ЯА1) РЕОМ ЕМР ХНЕНЕ ЕМР.ЮЕРТ МО = ЮЕРТ.ЮЕРТ МО); А вот как можно сформулировать тот же запрос с использованием раздела х1 тн (пример 15.14Ь): Х1ТН ММС (ЮЕРТ МО 1, ЮЕРТ МО 2, ММС МАМЕ, ММС ЯАЬ) АЯ (ЯЕ1,ЕСТ ЮЕРТ.ЮЕРТ МО, ЕМР.ЮЕРТ МО, ЕМР МАМЕ, ЕМР ЯАЬ РВОМ ЮЕРТ, ЕМР ХНЕЕЕ ЮЕРТ.ММС МО = ЕМР.ЕМР МО), МАХ ЮЕРТ ЯА1 (МАХ ЯА1, ЮЕРТ МО) АЯ (ЯЕЬЕСТ МАХ (ЕМР ЯАЬ), ЮЕРТ МО РНОМ ЕМР ХНЕНЕ ЮЕРТ МО 1Я МОТ МОЬЬ СНОЮР ВУ ЮЕРТ МО) ЯЕ1 ЕСТ ЮЕРТ МО 1, ММС МАМЕ РВОМ ММС ХНЕНЕ ЮЕРТ МО 1 = ЮЕРТ МО 2 АМЮ ММС ЯАЬ = (ЯЕ) ЕСТ МАХ ЯА1 РЕОМ МАХ ЮЕРТ ЯАЬ ХНЕНЕ МАХ ЮЕРТ ЯАЬ.ЮЕРТ МО = ЮЕРТ МО 1); Случаи, в которых без порождаемых таблиц обойтись невозможно На самом деле, пример 15.14 демонстрирует лишь возможность альтернативных формулировок запросов с использованием ссылок на порождаемые таблицы в разделе РНОМ.
Но в некоторых случаях без подобных конструкций просто невозможно обойтись. Вот простой пример. Ззб Лекция 15 Группировка и условие раздела НАЧ(М6, поролцгаемые и соединенные таблицы Пример 15.15. Найти общее число служащих и максимальный размер зарплаты в отделах с одинаковым максимальным размером зарплаты. БЕЬЕСТ ЯОМ (ТОТАЬ ЕМР), МАХ БАЬ ГРОМ (ЯЕ1 ЕСТ МАХ (ЕМР ЯАЬ), СООМТ (*) РВОМ ЕМР ННЕВЕ ОЕРТ НО 1Я НОТ МО 1 6РО()Р ВУ БЕРТ МО ) АЯ РЕРТ МАХ ЯАЬ (МАХ ЯАЬ, ТОТАЬ ЕМР) 6ВООР ВУ МАХ ЯАЬт И в этом случае выражение запросов, содержащееся в разделе ГНОМ, можно перенести в раздел нгтн (пример 15.15а): Н1ТН РЕРТ МАХ ЯАЬ (МАХ ЯАЬ, ТОТАЬ ЕМР) АЯ (ЯЕ1 ЕСТ МАХ (ЕМР ЯА1 ), СООМТ (*) ГНОМ ЕМР ННЕВЕ ОЕРТ МО 1Я НОТ НОЬЬ 6ВООР ВУ ОЕРТ МО) БЕ(ЕСТ ЯОМ (ТОТАЬ ЕМР), МАХ БАЬ РВОМ РЕРТ МАХ ЯАЬ ОВООР ВУ МАХ ЯАЬг Здесь мы не можем обойтись «одноуровневой» конструкцией запроса, поскольку требуется двойная группировка, причем вторая группировка должна быть получена в соответствии с результатами первой.
Еще один пример. Пример 15.16. Найти число проектов, дату их завершения и средний размер зарплаты служащих, участвующих в проекте, для проектов с одной и той же датой завершения и одним и тем же средним размером зарплаты служащих, участвующих в проекте. ЯЕЬЕСТ СООМТ (*), РЮ ЕОАТЕ, АЧ6 ЯАЬ ГЮМ (БЕ) ЕСТ РВО БРАТЕ, АЧ6 (ЕМР ЯА).) ГНОМ (ЯЕЬЕСТ РВО ЯОАТЕ » РКО ООВАТ, РВО МО ГНОМ РВО) АЯ РВ01 (РВО ЕОАТЕ, РВО МО), ЕМР ННЕВЕ РВ01.РВО МО = ЕМР.РВО МО 6РООР ВУ РВ01.РЮ НО ) АЯ РЮ А/6 ЯАЬ (РВО ЕРАТЕ, АЧ6 ЯА1) 6ВООР ВУ РВО ЕОАТЕ, АЧ6 БАЬ; Заметим, что выражение запросов на третьей и четвертой строках примера необходимо только по той причине, что нам требуется группировка по дате окончания проектов, соответствующий столбец в таблице РВО отсутствует, а в списке группировки можно использовать только имена столбцов. Для упрощения вида формулировки зто выражение разумно вынести в раздел игтн (пример 15.1ба): 337 Основы бвз данных Курс (Я1ТН РВ01 (РВО ЕРАТЕ, РКО ИО) АЯ (ЯЕРЕСТ РВО ЯРАТЕ ь РВО РОКАТ, РВО МО РЮМ РВО) ЯЕт ЕСТ СОПЯТ (*) РКО ЕРАТЕ Ат)С ЯА1 РВОМ (ЯЕЬЕСТ РВО ЕРАТЕ, АУЯ (ЕМР ЯА1) РВОМ РК01, ЕМР ИНЕКЕ РК01.РКО ИО = ЕМР.РКО МО ОЮРР ВУ РВ01 РКО МО) АЯ РВО АУЯ ЯАт (РЮ хРАтЕ РГО ЯА1,) ОКООР ВУ РКО ЕРАтЕ Ау Я ЯАт Более сложные конструкции оператора выборки В ятом разделе мы обсудим возможности языка $(.)1., касающиеся явного задания выражений с соединениями и порождаемых таблиц с горизонтальной связью (1атега1 т(ег1ует( таЫе).
Начнем с соединений. Соединенные таблицы В примерах предыдущей и данной лекций присутствовало много запросов с соединениями двух или более таблиц. Условия соединения задавались предикатами сравнения столбцов таблиц, специфицированных в разделе РВОМ, и входили в состав логических выражений раздела (Янеке (или, реже, раэдЕЛа НА)т1МО). ПОСКОЛЬКУ На ПраКтИКЕ трЕбуЮтСя раЗНЫЕ ВИДЫ СОЕдИНЕНИй, в стандарте Я(.)) /92 появилась альтернативная возможность спецификации соединений — соединенная таблица (Яо1пет) гаЫе). Соответствующая конструкция может использоваться в разделе РВОМ выражения запросов и фактически позволяет строить выражения соединений таблиц. Синтаксические правила построения таких выражений выглядят следующим образом: 1о1пет) 'аЫе::= егозя )о1п с(па1111еН )о1п патпта1 зо1п пп1оп зо1п егозя >отп т:=- таЫе ге1егепсе СЮЯЯ 101И таЫе рг1аагу т)иа1111ет) зоря::= .аЫе ге1етепсе ( 1оуп уре ! 001И гаЫе рг1аату )оуп ярес111саг1оп патпта1 зо1п тт= таЫе те1егепсе МАТОВАЯ ( 1огп туре .10тн ПаЫе рг1лтату ппуоп Зо1п т т= ГаЫе геТетепсе (ЛЯ10М 101Н ГаЫе ртуаату то1п Гуре ::= 1ННЕВ ( ( т ЕРТ ( ВТОНТ ( РРРР ) [ ОРТЕВ ) зогп ярес'11саттоп т:= ОМ сопоз 1опа1 ехргеяя1оп РЯ1МО (со1шпп солтпа 11ят) Напомним, что синтаксические правила для гаЫе геТетепсе и гаЫе рттаагу были показаны в лекции 13.
зза Лекция 15 Группировка и условия раздела НАЧ!Нп, порождаемые и соединенные таблицы Как показывает сводка синтаксических правил, в Я;>Е поддерживается много вариантов соединений. Чтобы объяснить особенности разных видов соединений на неформальном уровне, требуется очень большой объем текста с большим числом повторений. Поэтому сначала мы приведем достаточно формальное описание порядка определения заголовка и тела результирующей таблицы для всех разновидностей соединений.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
