Файл: 1. Введение в теорию баз данных Вопрос Основные понятия.docx
Добавлен: 05.12.2023
Просмотров: 562
Скачиваний: 2
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
Пример. Определить фирмы, у которых общее количество сделок превысило три.
SELECT Клиент.Фирма, Count(Сделка.Количество)
AS Количество_сделок
FROM Клиент INNER JOIN Сделка
ON Клиент.КодКлиента=Сделка.КодКлиента
GROUP BY Клиент.Фирма
HAVING Count(Сделка.Количество)>3
Пример. Вывести список товаров, проданных на сумму более 10000 руб.
SELECT Товар.Название,
Sum(Товар.Цена*Сделка.Количество)
AS Стоимость
FROM Товар INNER JOIN Сделка
ON Товар.КодТовара=Сделка.КодТовара
GROUP BY Товар.Название
HAVING Sum(Товар.Цена*Сделка.Количество)>10000
Пример. Вывести список товаров, проданных на сумму более 10000 без указания суммы.
SELECT Товар.Название
FROM Товар INNER JOIN Сделка
ON Товар.КодТовара=Сделка.КодТовара
GROUP BY Товар.Название
HAVING Sum(Товар.Цена*Сделка.Количество)>10000
Вопрос 6. Построение вложенных подзапросов.
Понятие подзапроса.
Часто невозможно решить поставленную задачу путем одного запроса. Это особенно актуально, когда при использовании условия поиска в предложении WHERE значение, с которым надо сравнивать, заранее не определено и должно быть вычислено в момент выполнения оператора SELECT. В таком случае приходят на помощь законченные операторы SELECT, внедренные в тело другого оператора SELECT. Внутренний подзапрос представляет собой также оператор SELECT, а кодирование его предложений подчиняется тем же правилам, что и основного оператора SELECT. Внешний оператор SELECT использует результат выполнения внутреннего оператора для определения содержания окончательного результата всей операции. Внутренние запросы могут быть помещены непосредственно после оператора сравнения (=, <, >, <=, >=, <>) в предложения WHERE и HAVING внешнего оператора SELECT – они получают название подзапросов или вложенных запросов. Кроме того, внутренние операторы SELECT могут применяться в операторах INSERT, UPDATE и DELETE.
Подзапрос – это инструмент создания временной таблицы, содержимое которой извлекается и обрабатывается внешним оператором. Текст подзапроса должен быть заключен в скобки. К подзапросам применяются следующие правила и ограничения:
· фраза ORDER BY не используется, хотя и может присутствовать во внешнем подзапросе;
· список в предложении SELECT состоит из имен отдельных столбцов или составленных из них выражений – за исключением случая, когда в подзапросе присутствует ключевое слово EXISTS;
· по умолчанию имена столбцов в подзапросе относятся к таблице, имя которой указано в предложении FROM. Однако допускается ссылка и на столбцы таблицы, указанной во фразе FROM внешнего запроса, для чего применяются квалифицированные имена столбцов (т.е. с указанием таблицы);
· если подзапрос является одним из двух операндов, участвующих в операции сравнения, то запрос должен указываться в правой части этой операции.
Существует два типа подзапросов:
Скалярный подзапрос возвращает единственное значение. В принципе, он может использоваться везде, где требуется указать единственное значение.
Табличный подзапрос возвращает множество значений, т.е. значения одного или нескольких столбцов таблицы, размещенные в более чем одной строке. Он возможен везде, где допускается наличие таблицы.
Использование подзапросов, возвращающих единичное значение.
Пример. Определить дату продажи максимальной партии товара.
SELECT Дата, Количество
FROM Сделка
WHERE Количество=(SELECT Max(Количество) FROM Сделка)
Во вложенном подзапросе определяется максимальное количество товара. Во внешнем подзапросе – дата, для которой количество товара оказалось равным максимальному. Необходимо отметить, что нельзя прямо использовать предложение WHERE Количество=Max(Количество), поскольку применять обобщающие функции в предложениях WHERE запрещено. Для достижения желаемого результата следует создать подзапрос, вычисляющий максимальное значение количества, а затем использовать его во внешнем операторе SELECT, предназначенном для выборки дат сделок, где количество товара совпало с максимальным значением.
Пример. Определить даты сделок, превысивших по количеству товара среднее значение и указать для этих сделок превышение над средним уровнем.
SELECT Дата, Количество,
Количество-(SELECT Avg(Количество)
FROM Сделка) AS Превышение
FROM Сделка
WHERE Количество>
(SELECT Avg(Количество)
FROM Сделка)
В приведенном примере результат подзапроса, представляющий собой среднее значение количества товара по всем сделкам вообще, используется во внешнем операторе SELECT как для вычисления отклонения количества от среднего уровня, так и для отбора сведений о датах.
Пример. Определить клиентов, совершивших сделки с максимальным количеством товара.
SELECT Клиент.Фамилия
FROM Клиент INNER JOIN Сделка
ON Клиент.КодКлиента=Сделка.КодКлиента
WHERE Сделка.Количество=
(SELECT Max(Сделка.Количество)
FROM Сделка)
Здесь показан пример использования подзапроса при выборке данных из разных таблиц.
Пример. Определить клиентов, в сделках которых количество товара отличается от максимального не более чем на 10%.
SELECT Клиент.Фамилия,
Сделка.Количество
FROM Клиент INNER JOIN Сделка
ON Клиент.КодКлиента=
Сделка.КодКлиента
WHERE Сделка.Количество>=0.9*
(SELECT Max(Сделка.Количество)
FROM Сделка)
Покажем, как применяются подзапросы в предложении HAVING.
Пример. Определить даты, когда среднее количество проданного за день товара оказалось больше 20 единиц.
SELECT Сделка.Дата, Avg(Сделка.Количество) AS
Среднее_за_день
FROM Сделка
GROUP BY Сделка.Дата
HAVING Avg(Сделка.Количество)>20
За каждый день определяется среднее количество товара, которое сравнивается с числом 20. Добавим в запрос подзапрос.
Пример. Определить даты, когда среднее количество проданного за день товара оказалось больше среднего показателя по всем сделкам вообще.
SELECT Сделка.Дата,
Avg(Сделка.Количество)
AS Среднее_за_день
FROM Сделка
GROUP BY Сделка.Дата
HAVING Avg(Сделка.Количество)>
(SELECT Avg(Сделка.Количество)
FROM Сделка)
Внутренний подзапрос определяет средний по всем сделкам показатель, с которым во внешнем запросе сравнивается среднее за каждый день количество товара.
Использование подзапросов, возвращающих множество значений.
Во многих случаях значение, подлежащее сравнению в предложениях WHERE или HAVING, представляет собой не одно, а несколько значений. Вложенные подзапросы генерируют непоименованное промежуточное отношение, временную таблицу. Оно может использоваться только в том месте, где появляется в подзапросе. К такому отношению невозможно обратиться по имени из какого-либо другого места запроса. Применяемые к подзапросу операции основаны на тех операциях, которые, в свою очередь, применяются к множеству, а именно:
{ WHERE | HAVING } выражение [ NOT ] IN (подзапрос);
{ WHERE | HAVING } выражение оператор_сравнения { ALL | SOME | ANY }(подзапрос);
{WHERE | HAVING } [ NOT ] EXISTS (подзапрос);
Использование операций IN и NOT IN.
Оператор IN используется для сравнения некоторого значения со списком значений, при этом проверяется, входит ли значение в предоставленный список или сравниваемое значение не является элементом представленного списка.
Пример. Определить список товаров, которые имеются на складе.
SELECT Название
FROM Товар
WHERE КодТовара In
(SELECT КодТовара FROM Склад)
Пример. Определить список отсутствующих на складе товаров.
SELECT Название
FROM Товар
WHERE КодТовара Not In (SELECT КодТовара
FROM Склад)
Пример. Определить товары, которые покупают клиенты из Москвы.
SELECT DISTINCT Товар.Название,
Клиент.ГородКлиента
FROM Товар INNER JOIN
(Клиент INNER JOIN Сделка
ON Клиент.КодКлиента=Сделка.КодКлиента)
ON Товар.КодТовара=Сделка.КодТовара
WHERE Клиент.ГородКлиента='Москва'
В результат включаются товары, приобретенные клиентами из Москвы, однако не исключено, что покупателями таких товаров были и клиенты из других городов.
Введение в запрос фразы «только» требует использования операции NOT IN.
Пример. Определить товары, покупку которых осуществляют только клиенты из Москвы, и никто другой.
SELECT DISTINCT Товар.Название,
Клиент.ГородКлиента
FROM Товар INNER JOIN
(Клиент INNER JOIN Сделка
ON Клиент.КодКлиента=Сделка.КодКлиента)
ON Товар.КодТовара=Сделка.КодТовара
WHERE Товар.Название NOT IN
(SELECT Товар.Название
FROM Товар INNER JOIN
(Клиент INNER JOIN Сделка
ON Клиент.КодКлиента=Сделка.КодКлиента)
ON Товар.КодТовара=Сделка.КодТовара
WHERE Клиент.ГородКлиента<>'Москва')
Пример. Какие товары ни разу не купили московские клиенты?
SELECT DISTINCT Товар.Название,
Клиент.ГородКлиента
FROM Товар INNER JOIN
(Клиент INNER JOIN Сделка
ON Клиент.КодКлиента=Сделка.КодКлиента)
ON Товар.КодТовара=Сделка.КодТовара
WHERE Товар.Название NOT IN
(SELECT Товар.Название
FROM Товар INNER JOIN
(Клиент INNER JOIN Сделка
ON Клиент.КодКлиента=Сделка.КодКлиента)
ON Товар.КодТовара=Сделка.КодТовара
WHERE Клиент.ГородКлиента='Москва')
Во вложенном запросе определяется список товаров, приобретаемых клиентами из Москвы. Во внешнем запросе выбираются только те товары, которые не входят в этот список.
Пример. Определить фирмы, покупающие товары местного производства.
SELECT DISTINCT Клиент.Фирма, Клиент.ГородКлиента,
Товар.ГородТовара
FROM Товар INNER JOIN
(Клиент INNER JOIN Сделка
ON Клиент.КодКлиента=Сделка.КодКлиента)
ON Товар.КодТовара=Сделка.КодТовара
WHERE Клиент.ГородКлиента=Товар.ГородТовара
В результате выполнения запроса перечисляются сделки, когда клиенту был продан товар, изготовленный в его городе, что совсем не исключает наличие сделок этих же клиентов, связанных с приобретением товара из другого города.
Введем в запрос фразу «только» – сразу потребуется привлечение операции NOT IN.
Пример. Определить фирмы, которые покупают только товары, произведенные в своем городе, и никакие другие.
SELECT DISTINCT Клиент.Фирма,
Клиент.ГородКлиента,
Товар.ГородТовара
FROM Товар INNER JOIN
(Клиент INNER JOIN Сделка
ON Клиент.КодКлиента=Сделка.КодКлиента)
ON Товар.КодТовара=Сделка.КодТовара
WHERE Клиент.ГородКлиента NOT IN
(SELECT DISTINCT Клиент.ГородКлиента
FROM Товар INNER JOIN
(Клиент INNER JOIN Сделка
ON Клиент.КодКлиента=Сделка.КодКлиента)
ON Товар.КодТовара=Сделка.КодТовара
WHERE Клиент.ГородКлиента<>
Товар.ГородТовара)
Во вложенном запросе определяется множество фирм, совершивших хотя бы одну покупку товара из чужого города. Затем определяются фирмы, не входящие в это множество.
Использование ключевых слов ANY и ALL.
Ключевые слова ANY и ALL могут использоваться с подзапросами, возвращающими один столбец чисел.
Если подзапросу будет предшествовать ключевое слово ALL, условие сравнения считается выполненным, только когда оно выполняется для всех значений в результирующем столбце подзапроса.
Если записи подзапроса предшествует ключевое слово ANY, то условие сравнения считается выполненным, когда оно выполняется хотя бы для одного из значений в результирующем столбце подзапроса.
Если в результате выполнения подзапроса получено пустое значение, то для ключевого слова ALL условие сравнения будет считаться выполненным, а для ключевого слова ANY – невыполненным. Ключевое слово SOME является синонимом слова ANY.
Пример. Определить клиентов, совершивших сделки с максимальным количеством товара.