С.Д. Кузнецов - Основы баз данных (1121716), страница 70
Текст из файла (страница 70)
ЯЕЬЕСТ ОЕРТ НО РВОМ ЕМР ХНЕВЕ ОЕРТ ХО 1Я НОТ Н(1Ы ОВО1)Р ЯУ ЕЕРТ ХО НАЧ1НО ЯОМ(ЕМР ЯА1,) '< (ЯЕ1 ЕСТ Я()М(ЕМР1.ЕМР ЯА1.) РВОМ ЕМР ЕМР1, ОЕРТ ХНЕВЕ ЕМР1.ЕМР МО = ОЕРТ %46); И в этом случае возможна формулировка без использования разделов ОВООР Ву и НА)(ТМО (пример 15.3а). Эта формулировка является более сложной, чем в случае двух предыдущих примеров, но и к ней применимы приведенные выше замечания. ЯЕЬЕСТ 01ЯТ1ХСТ ОЕРТ НО РВОМ ЕМР ХНЕВЕ (ЯЕЬЕСТ ЯБМ(ЕМР1.ЕМР ЯАЫ РВОМ ЕМР ЕМР1 ХНЕВЕ ЕМР1.ОЕРТ ХО = ЕМР.РЕРТ НО) < (ЯЕЬЕСТ ЯОМ(ЕМР1.ЕМР ЯАЬ) РВОМ ЕМР ЕМР1, ОЕРТ ХНЕВЕ ЕМР1.ЕМР НО = ОЕРТ ММО); 326 Лекция (б Группировка и условия раздела НАЧ!ИС, порождаемые и соединенные таблицы Пример 15.4.
Для каждого отдела найти его номер, имя руководителя, число служащих, минимальный, максимальный и средний размеры зарплаты служаших. БЕЬЕСТ ОЕРТ.РЕРТ ИО, ЕМР.ЕМР МАМЕ, СОПИТ(*), М1И(ЕМР1.ЕМР БАЬ), МАХ(ЕМР1.ЕМР БАЬ), АЧС(ЕМР1.ЕМР БА1) РКСМ РЕРТ, ЕМР, ЕМР ЕМР1 ЯНЕКЕ РЕРТ.РЕРТ ИО = ЕМР1.РКРТ ИО СКОПР ВУ РКРТ.ПЕРТ ИО, ПЕРТ.ПЕРГ МИС, ЕМР.ЕМР ИО, ЕМР.ЕМР МАМЕ НАЧТИС РКРТ.РКРТ МИС = ЕМР.ЕМР ИО; Этот запрос иллюстрирует несколько интересных особенностей языка 5О) . Во-первых, это первый пример запроса с соединениями, в котором присутствуют разделы скэпр ву и НАчтис. Во-вторых, одно условие соединения находится в разделе ЯНЕКЕ, а другое — в разделе НАЧ1ИС. На самом деле, можно было бы перенести в раздел ЯНЕКЕ и второе условие соединения, и, скорее всего, на практике использовалась бы формулировка, приведенная в примере 15.4а: БЕ( ЕСТ ОЕРТ.
ОЕРТ ИО, ЕМР. ЕМР ИАМЕ, СОПИТ ( * ), МТИ(ЕМР1.ЕМР БАЬ), МАХ(ЕМР1.ЕМР БА( ), АЧС(ЕМР1.ЕМР БА( ) РКСМ ОЕРТ, ЕМР, ЕМР ЕМР1 ЯНЕКЕ РКРТ.ПЕРТ ИО = ЕМР1.ОЕРТ ИО АИР РЕРТ.РКРТ МИС = ЕМР.ЕМР ИО СКОПР ВУ РЕРТ.РЕРТ ИО, ЕМР.ЕМР МАМЕ; Но первая формулировка тоже верна, поскольку второе условие соединения определено на столбцах группировки.
Наконец, легко видеть, что по сушеству группировка производится по значениям столбца перт.перт иО. Остальные столбцы, указанные в списке столбцов группировки, функционально определяются столбцом РЕЕТ.РКРТ ИО. Тем не менее, в первой формулировке мы включили в этот список столбцы Оерт. Оерт мис и емр. емР ио, чтобы их имена можно было использовать в условии раздела нАчтис, и столбец емр. емр идме, чтобы можно было использовать его имя в списке выборки раздела БЕЬЕСТ.
Другими словами, мы вынуждены расширять запрос избыточными данными, чтобы выполнить формальные синтаксические требования языка. Как видно, во второй формулировке мы смогли удалить из списка группировки два столбца. Кстати, не следует думать, что многословие первой формулировки помешает СУБД выполнить запрос настолько же эффективно, как запрос во второй формулировке. Грамотно построенный оптимизатор ЯП. сам приведет первую формулировку ко второй. 327 Основы баз данных Курс Наконец, и этот запрос можно сформулировать без использования раздела ЕВОПР Ву за счет использования подзапросов в списке раздела Яе(.ест (пример 15.4Ь): яеьест ОКРт.веРТ МО, емР,емР МАме, (ЯЕ1 КСТ СООНТ (*) РВОМ ЕМР хнеВе емР.ОеРт МО = ОКРт.пеРТ МО), (яеьест м1н(емР ЕАЗА) РВОМ ЕМР ХНЕВЕ ЕМР.РКРТ НО = ОЕРТ.ВЕРТ МО), (яе) ест НАМ(емР яА) ) РВОМ ЕМР ХНЕВЕ ЕМР.ОЕРТ НО = РЕРТ.ОЕРТ НО), (ЯКЬЕСТ АЧО(ЕМР ЯАЫ РВОМ ЕМР ХНЕВЕ ЕМР.ОЕРТ ХО = РЕРТ.ОЕРТ НО) РВОМ ОЕРТ, ЕМР ХНЕВЕ ЭЕРТ.ПЯРТ ИЖ = ЕМР.ЕМР НО; Здесь мы снова имеем замаскированную группировку строк по значениям столбца РКРТ.ОЕРТ НО и вычисление агрегатных функций для каждой группы.
Формально группа строится каждый раз заново при вызове каждой агрегатной функции. Хороший компилятор 591 должен привести формулировку 15.4Ь к виду 15.4а. И последнее замечание. Во всех приведенных формулировках в результат не попадуг данные об отделах, в которых отсутствует руководитель (столбец ОКРТ. ОЕРТ ММО может содержать неопределенное значение). Вообще говоря, это не противоречит условию запроса, но если бы мы хотели выдавать в результате жЛ.К в качестве имени руководителя отдела с отсугствукнцим руководителем, то можно было немного усложнить формулировку запроса, например, следующим образом (пример 15.4с): ЯЕЬЕСТ РКРТ.ОЕРТ НО, САЯЕ ХНЕН ОЕРТ.ОЕРТ МНО 1Я МОКЬ ТНЕН НОКК ЕЬЯЕ (ЯЕЬЕСТ ЕМР.ЕМР ХАМЕ ГНОМ ЕМР ХНЕВЕ ЕМР.ЕМР НО = ОЕРТ.ОЕРТ РК(6), СООНТ(*), Мтн(ЕМР1.ЕМР ЯА)), МАХ(ЕМР1.ЕМР ЯА).)', АЧО(ЕМР1.ЕМР ЯА).) РВОМ БЕРТ, ЕМР, ЕМР ЕМР1 ХНЕВЕ БЕРТ.РКРТ НО = ЕМР1.РКРТ НО ОВООР Ву РКРТ.РКРТ НО; 328 Лекция 15 Г ппи евка и славия аздела НАН(МО, порождаемые и соединенные таблицы Првдмквт ьееиеел Пример 15.5.
Найти номера отделов и минимальный и максимальный размер зарплаты служащих для отделов, в которых средний размер зарплаты служащих не меньше среднего размера зарплаты служащих во всей компании и не больше 30000 руб. БЕЬЕСТ РЕРТ ИО, М1М(ЕМР БАЫ, МАХ(ЕМР БАЫ РВОМ ЕМР ХНЕВЕ РЕРТ МО 1Я НОТ ИРЬЬ ОВОУР ВУ РЕРТ НО НАЧ1МБ АЧО(ЕМР ЯАЬ) ВЕТХЕЕИ (ЯЕЬЕСТ АЧО(ЕМР ЯАЫ РВОМ ЕМР) АНР 30000.00р Еше раз приведем возможную формулировку этого запроса без использования разделов ОВОьтР Ву и НАНТХБ (пример 15.5а): ЯЕЬЕСТ 01БТ1ИСТ РЕРТ МО, (ЯЕЬЕСТ М1И(ЕМР1.ЕМР ЯАЬ) РВОМ ЕМР ЕМР1 ХНЕВЕ ЕМР1.0ЕРТ НО = ЕМР.РЕРТ ИО), (ЯЕЬЕСТ МАХ(ЕМР1.ЕМР ЯАЬ) РВОМ ЕМР ЕМР1 хнеВе емРТ.РВРт мо = емР.Рерт МО) РВОМ ЕМР ХНЕВЕ (ЯЕ1 ЕСТ АЧО(ЕМР1.ЕМР БАЬ) РВОМ ЕМР ЕМР1 ХНЕВЕ ЕМР1.0ЕРТ НО = ЕМР.РЕРТ ИО) ВЕТХЕЕИ (ЯЕ(ЕСТ АЧО(ЕМР ЯА1 ) РВОМ ЕМР) АНР 30000.00; Как видно, отказ от использования раздела ОВОДОВ Ву приводит к размножению однотипных подзапросов, строящих одну и ту же группу строк, над которой вычисляется агрегатная функция.
Првдикат ди11 Пример 15.6. Найти номера и число служащих отделов, данные о руководителях которых не содержат номер отдела (конечно, в этом случае нас интересуют только те отделы, у которых имеется руководитель). БЕЬЕСТ РЕРТ.РЕРТ НО, СОЬтнт(*) РВОМ РЕРТ, ЕМР ЕМР1, ЕМР ЕМР2 ХНЕВЕ РЕРТ.РЕРТ ИО = ЕМР2.РЕРТ ИО Основы баз данных Курс АН0 ОЕРТ.ОЕРТ ИНС = ЕИР1. ЕМР НО СВООР ВУ ОЕРТ.ОЕРТ НО, ЕМР1.0ЕРТ МО НАЧ1НС ЕМР1.0ЕРТ НО 1Я НОЬ?; Как и в примере 15.4, условие раздела нАУТНС можно переместить в раздел ХНКВЕ и получить вторую формулировку (пример 15.6а): ЯЕЬЕСТ ОЕРТ.ОЕРТ ИО, СО(1ИТ(*) РВОМ ОЕРТ, ЕМР ЕМР1, ЕМР ЕМР2 ХНЕВЕ ОЕРТ.ОЕРТ ИО = ЕМР2.0ЕРТ ИО АИ0 ОЕРТ.ОЕРТ МИС = ЕМР1.ЕМР НО АИ0 ЕМР1.0ЕРТ НО 1Я 1ЖЬЬ СВООР ВУ ОЕРТ.ОЕРТ НО; Кстати, в этом случае, поскольку в запросе присутствует только один вызов агрегатной функции, формулировка без использования раздела сВООР ву оказывается более понятной и не менее эффективной (даже при следовании предписанной семантике выполнения оператора ЯЕЬЕСТ), что показывает пример 15.6Ь: ЯЕЬЕСТ 0ЕРТ.ОЕРТ МО, (ЯЕЬЕСТ СООНТ(*) КРОМ ЕМР ХНЕВЕ ОЕРТ.ОЕРТ ИО = ЕМР.ОЕРТ НО) РВОМ ОЕРТ, ЕМР ХНЕВЕ ОЕРТ.ОЕРТ ИИС = ЕМР.ЕМР ИО АЛО КМР.ОКРТ НО 1Я 1ЛЛ 1; Предикат 1п Пример 15.7.
Найти номера отделов, в которых средний размер зарплаты служащих равен максимальному размеру зарплаты служащих какого-либо другого отдела. ЯЕЬЕСТ ОЕРТ.ОЕРТ НО РВОМ ОЕРТ, ЕМР ХНЕВК 0ЕРТ.ОЕРТ ИО = ЕМР.ОЕРТ НО СВООР ВУ 0ЕРТ.ОКРТ ИО НАЧ1НС АЧС(ЕИР.ЕМР ЯАЬ) 1Н (ЯЕЬЕСТ МАХ(ЕМР1.ЕМР ЯА1) РВОМ ЕМР, 0ЕРТ 0ЕРТ1 ХНЕВЕ ЕМР.ОЕРТ НО = 0ЕРТ1.0ЕРТ НО АН0 ОЕРТ1.0ЕРТ НО <> ОЕРТ.ОЕРТ ИО СВООР ВУ ОЕРТ.ОЕРТ НО); 330 Лекция 15 Группировка и условия раздела НАЧ1ИО, поровдаемые и соединенные таблицы Этот запрос, помимо прочего, демонстрирует наличие в условии раздела НАчгис вложенною подзапроса с корреляцией.
Как и раньше, можно избавиться от разделов скООР ву и ни Тис во внешнем запросе (пример 15.7а): ЯЕЬЕСТ РЕРТ.РКРТ ИО РКСМ РКРТ ИНЕКЕ (ЯЕЬЕСТ АЧС(ЕМР ЯАЬ) РКСМ ЕМР ЯНЕКЕ ЕМР.РКРТ ИО = СЕРТ.СЕРТ ИО) 1И (ЯЕЬЕСТ МАХ(ЕМР1.ЕМР ЯА1 ) РКСМ ЕМР, ВЕРТ СЕРТ1 ХНЕКЕ ЕМР.ОЕРТ ИО = СЕРТ1.РЕРТ ИС АИС ОЕРТ1.ВЕРТ ИО <> ОЕРТ.()ЕРТ ИО СКОСР ВУ РЕРТ.ОЕРТ ИО)т Но в данном случае мы не можем отказаться от раздела скООР ву во втором вложенном запросе, поскольку без этого невозможно получить множество значений результатов вызова агрегатной функции.
Предикат 11)ге Пример!5.8. Во всех отделах найти имена и число служаших, у которых в данном отделе имеются однофамильцы и фамилии которых начинаются со строки символов, изображавшей фамилию руководителя отдела. ЯЕ( ЕСТ ЕМР ИАМЕ, СС(Л(Т(*) РКСМ ЕМР, ОЕРТ ИНЕКЕ ЕМР.РКРТ ИО = РЕРТ.РЕРТ ИО СКОСР ВУ Г)ЕРТ.СЕРТ ИО, ЕМР МАМЕ НАЧ1ИС СС(Л(Т(*) > 1 АИС ЕМР.ЕМР МАМЕ Ь1КЕ (ЯЕЬЕСТ ЕМР1.ЕМР МАМЕ РКСМ ЕМР ЕМР1 ИНЕКЕ ЕМР1.ЕМР ИО = Г)ЕРТ.РЕРТ МИС) )( 'л'; Конечно, и в этом случае условие с предикатом ьгке можно переместить из раздела НАЧТИС в раздел ИНЕКЕ. Этот запрос можно переформулировать в виде, лишенном разделов сксОР Ву и ндчтиа (пример 15.8а), но вряд ли это разумно, поскольку формулировка является менее понятной и сушественно более сложной.
ЯЕЬЕСТ ЕМР МАМЕ, (ЯЕЬЕСТ СС()ИТ(*) РКСМ ЕМР ЕМР1 ИНЕКЕ ЕМР1.РЕРТ ИО = ЕМР.РКРТ ИО АИС ЕМР1.ЕМР МАМЕ = ЕМР.ЕМР МАМЕ 331 Курс Основы баз данных АМР ЕМР1.ЕМР МО <> ЕМР.ЕМР МО) в 1 РВОМ ЕМР ХНЕВЕ (ЯЕЬЕСТ СООМТ(*) ГВОМ ЕМР ЕМР1 ХНЕВК ЕМР1.РЕРТ МО = ЕМР.РЕРТ МО АМР ЕМР1.ЕМР МАМЕ = ЕМР.ЕМР МАМЕ АМР ЕМР1.ЕМР МО <> ЕМР.ЕМР МО) > 1 АМР ЕМР МАМЕ Ь|КЕ (ЯЕЬЕСТ ЕМР1.ЕМР МАМЕ РЮМ ЕМР ЕМР1, РЕРТ ИНЕВЕ КМР.РЕРТ МО = РЕРТ.РЕРТ МО АМР ЕМР1.ЕМР МО = РЕРТ.РЕРТ ММС) )) 'Ф'; Предикат ехдвее Пример 15.9.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
