Задание необходимо выполнить к 28.04.2020 к 9-00!! Так же сдать предыдущее задание.
Занятие 22.04.2020, 24.04.2020
Лекция
Тема: Вложенные и связанные SQL запросы.
Цель: изучение материала по теме
Задачи:
- изучение основ создания вложенных запросов
- изучение основ создания связанных запросов
- изучение правил построения связанных запросов
- изучение использования предикатов в подзапросах
Ход работы:
В ходе выполнения задания необходимо написать конспект по теме, Вложенные и связанные запросы, представленной ниже.
Необходимо подробно записывать все примеры выполнения запросов с таблицами-результатами.
После составления конспекта лекции в тетради необходимо самостоятельно придумать 10 примеров запросов по рассмотренным в лекции видам запросов. Запросы необходимо записать в тетрадь. Запросы нужно придумывать к базе данных, которая рассматривается в лекционных примерах.
В качестве отчета по выполненному заданию необходимо прислать фото написанных самостоятельных запросов к БД.
Задание необходимо выполнить к 28.04.2020 к 9-00!! Так же сдать предыдущее задание.
Готовые работы можно сдать:
- личным сообщением в ВК https://vk.com/lena_lexy
- отправить по электронной почте lexy 83@ mail . ru
Вложенные запросы
В SQL множество значений может задаваться не только перечислением входящих в него элементов, но и запросом. Так же, как и список элементов, запрос заключается в круглые скобки. Полученное множество используется так же, как и рассмотренное ранее. Множество содержит атомарные элементы, поэтому формирующий его запрос должен выбирать только один атрибут. В результате запроса множество может быть пустым, содержать один элемент или более одного.
Если множество содержит один элемент, оно может рассматриваться как обычное значение, и для него определены соответствующие операции сравнения. Для множества, содержащего более одного элемента, эти операции, естественно, недопустимы, определена лишь операция принадлежности элемента множеству.
Заметим, что в результате запроса может образоваться несколько одинаковых значений и, как и в случае обычного запроса, по умолчанию все они включаются в выборку. То есть, на самом деле речь идет не о множестве, а о мультимножестве (комплекте). Использование варианта Distinct приводит к исключению повторяющихся элементов.
Запрос, формирующий множество, внешне выглядит как обычный, его называют вложенным запросом. Вложенный запрос сам может иметь вложенный запрос.
Пример
1. Найти все заказы, которые выполняет продавец Сидоров:
Select * From Заказ
Where ном_прод=(Select ном_прод From Продавец
Where имя_прод=’Сидоров’);
Запрос корректен, если есть ровно один продавец Сидоров. Если такого нет – результат не определен, если их более одного – результат ошибочен.
2. Найти все заказы, сумма которых больше, чем средняя за 4 октября:
Select * From Заказ
Where сумма>(Select AVG(сумма) From Заказ Where дата=’04.10’);
3. Найти все заказы для продавцов из Москвы:
Select * From Заказ Where ном_прод in (Select ном_прод From Продавец Where город=’Москва’);
Такой же результат можно получить, используя операцию соединения:
Select a.* From Заказ a, Продавец b Where a.ном_прод = b.ном_прод and город=’Москва’;
4. Найти комиссионные всех продавцов, обслуживающих покупателей из Москвы:
Select комиссия From Продавец Where ном_прод in (Select ном_прод From Заказ Where ном_пок in (Select ном_пок From Покупатель Where город=’Москва’));
5. Определить количество покупателей, имеющих значимость, превышающую среднюю для покупателей из Тулы:
Select значимость, COUNT(Distinct ном_пок) From Покупатель Having значимость > (Select AVG(значимость) From Покупатель Where город=’Тула’) Group By значимость;
Конец примера
Соединение и вложенные запросы, приведенные в примере, выполняются разными способами, поэтому, несмотря на то, что выборка получается одинаковой, время, затраченное на выполнение запроса, может существенно разниться. Это же касается и необходимой памяти. Выбор варианта неоднозначен, он зависит как от размеров таблиц, включая промежуточные, так и от используемой СУБД.
Связанные запросы
Для реализации вложенного запроса может потребоваться информация из таблиц внешнего запроса. Запросы такого типа называются связанными.
Пример
Выбрать всех покупателей, которые сделали заказы 3 октября:
Select * From Покупатель a
Where ’03.10’ in (Select дата From Заказ b Where a.ном_пок=b.ном_пок);
Конец примера
При вычислении простого вложенного запроса внутренний подзапрос вычисляется автономно до выполнения внешнего запроса. Связанный запрос так выполнен быть не может: выполнение внутреннего подзапроса зависит от состояния таблиц, объявленных во внешнем запросе. Следовательно, внутренний подзапрос должен выполняться для каждой строки внешней таблицы, от которой он зависит.
Правила выполнения связанных запросов:
1. Выбрать очередную строку внешней таблицы, от которой зависит внутренний подзапрос, она называется строкой-кандидатом.
2. Сохранить ее под псевдонимом, указанном во внешней фразе From.
3. Выполнить подзапрос. Используемые в подзапросе значения строки-кандидата называются внешними ссылками.
4. Оценить результат внешнего запроса на основании результата подзапроса.
5. Повторить процедуру для следующих строк внешней таблицы.
Как и в случае вложенных запросов, связанный запрос можно заменить соединением. Здесь тоже нужно внимательно отнестись к выбору варианта, от этого может существенно зависеть производительность.
Пример
1. Реализовать предыдущий пример, используя соединение:
Select Distinct a.* From Покупатель a, Заказ b
Where a.ном_пок=b.ном_пок and дата=’03.10’;
Очевидно, Distinct в этом случае необходим, если хотя бы один покупатель сделал более одного заказа. В результате эта операция потребует значительного времени, что снизит эффективность запроса.
2. Выбрать имена и номера продавцов, обслуживающих более одного покупателя:
Select ном_прод, имя_прод From Продавец a
Where 1<(Select COUNT(Distinct ном_пок) From Заказ
Where ном_прод=a.ном_прод);
Конец примера
Связанный запрос во внутреннем подзапросе может содержать ту же таблицу, что и во внешнем, то есть таблица связывается сама с собой. В этом случае одна и та же таблица в разных подзапросах играет разные роли, но обработка идет по приведенным ранее правилам. Разумеется, одновременно с выборкой строки-кандидата фиксируется и состояние таблицы, чтобы восстановить его после выполнения подзапроса.
Пример
Для каждого покупателя выбрать все заказы на сумму, большую средней суммы его заказов:
Select * From Заказ a
Where сумма>(Select AVG(сумма) From Заказ b
Where a.ном_пок=b.ном_пок);
Конец примера
Так же, как и в случае вложенного запроса, связанный подзапрос может использоваться во фразе Having, если условие накладывается на группу записей.