Базы данных и информационные системы

информационные системы
Лекции 11,12

подзапросы:
{ WHERE | HAVING } выражение [ NOT ] IN

(подзапрос);
{WHERE | HAVING } выражение оператор_сравнения { ALL | SOME | ANY }(подзапрос);
{WHERE | HAVING } [ NOT ] EXISTS (подзапрос);
Подзапрос в предложении FROM.

2. Коррелирующий подзапрос;
3. Выражение операций РА (объединение, пересечение, разность) средствами SQL;
4. Примеры;
Заключение

и обрабатывается внешним оператором.
К подзапросам применяются следующие правила:
подзапросы могут

быть помещены непосредственно после операторов:
сравнения (=, <, >, <=, >=, <>), IN, ANY, SOME, ALL в предложениях WHERE, HAVING,
в строке SELECT внешнего оператора SELECT;
в строке FROM;
текст подзапроса должен быть заключен в скобки;
по умолчанию имена столбцов в подзапросе относятся к таблице, имя которой указано в предложении FROM подзапроса. Для ссылки на столбцы таблицы, указанной во фразе FROM внешнего запроса используется точечная нотация;

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

где требуется указать единственное значение.

Табличный подзапрос возвращает множество значений,

т.е. значения одного или нескольких столбцов таблицы, размещенные в более чем одной строке.

Он возможен везде, где допускается наличие таблицы.

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

(сделку с максимальным количеством товара).


Запрос 1б. Определите дату продажи максимальной

партии товара.
SELECT Дата, Кол_во FROM Сделка
WHERE Кол_во = (SELECT Max(Кол_во)
FROM Сделка);

Запрещено!
WHERE Количество=Max(Количество)

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

Определить даты сделок, превысивших по количеству товара среднее значение и

указать для этих сделок превышение над средним уровнем.
SELECT Дата, Кол_во, Кол_во - (SELECT Avg(Кол_во) FROM Сделка) AS Превышение
FROM Сделка
WHERE Кол_во > (SELECT Avg(Кол_во) FROM Сделка);

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

клиентов, совершивших сделки с максимальным количеством товара. Выводить фамилию.





Запрос 4

Определить клиентов, в сделках которых количество товара отличается от максимального не более чем на 10%.

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

среднее количество проданного за день товара оказалось больше 20 единиц.





Запрос

5б Определить даты, когда среднее количество проданного за день товара оказалось больше среднего показателя по всем сделкам вообще.
SELECT Дата, Avg(Кол_во) AS Среднее_за_день
FROM Сделка
GROUP BY Дата
HAVING Avg(Сделка.Кол_во) > (SELECT Avg(Кол_во) FROM Сделка);

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

| HAVING } выражение [ NOT ] IN (подзапрос);
{WHERE

| HAVING } выражение оператор_сравнения { ALL | SOME | ANY }(подзапрос);
{WHERE | HAVING } [ NOT ] EXISTS (подзапрос).






а
Рисунок 1.1 – Схема данных (Access), отношение-экземпляр Склад б

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

IN (подзапрос);

Оператор IN используется для сравнения некоторого значения со

списком значений
и может использоваться с подзапросами, возвращающими один столбец.
Запрос 6 Вывести название товаров, которых на складе > 10
Вариант1
SELECT Название
FROM Товар_New INNER JOIN Склад
ON Товар_New.КодТовара = Склад.КодТовара
WHERE Остаток>10;

Вариант2
SELECT Название
FROM Товар_New
WHERE КодТовара IN
(SELECT КодТовара From Склад WHERE Остаток>10);

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

IN (подзапрос);

Запрос 7а Вывести без повторов название товаров, которые

уже покупались (присутствуют в таблице Сделка)
Вариант1





Вариант2

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

IN (подзапрос);

Запрос 7б Вывести название товаров, которые еще ни

разу не покупались (отсутствуют в таблице Сделка)
Вариант1
SELECT Название
FROM Товар_New LEFT JOIN Сделка
ON Товар_New.КодТовара = Сделка.КодТовара
WHERE Сделка.КодТовара IS NULL;

Вариант2
SELECT Название
FROM Товар_New
WHERE КодТовара NOT IN
(SELECT КодТовара From Сделка);

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

| SOME | ANY }(подзапрос)

Использование ключевых слов ANY, SOME

и ALL:
Ключевые слова ANY, SOME и ALL могут использоваться с подзапросами, возвращающими один столбец значений;
Ключевое слово ALL- условие сравнения считается выполненным, только когда оно выполняется для всех значений в результирующем столбце подзапроса.
Ключевое слово ANY, SOME - условие сравнения считается выполненным, когда оно выполняется хотя бы для одного из значений в результирующем столбце подзапроса.
Если результат выполнения подзапроса не содержит строк, то для ключевого слова ALL условие сравнения будет считаться выполненным (TRUE), а для ключевого слова ANY – невыполненным (FASLE).
Если результат выполнения подзапроса содержит NULL, то результатом сравнения и для ALL, и для ANY будет UNKNOWN.

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

Запрос 8 Определить фамилии клиентов, совершивших сделки с максимальным количеством

товара (экв-но запросу 3)
Вариант1 (max)
SELECT Фамилия
FROM Клиент INNER JOIN Сделка ON
Клиент.КодКлиента=Сделка.КодКлиента
WHERE Кол_во=(SELECT max(Кол_во) FROM Сделка);
Вариант2 (ALL)
SELECT Фамилия
FROM Клиент INNER JOIN Сделка ON
Клиент.КодКлиента=Сделка.КодКлиента
WHERE Кол_во>=ALL(SELECT Кол_во FROM Сделка);
Поиск минимального значения
WHERE Кол_во <= ALL (SELECT Кол_во FROM Сделка);

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

Запрос 9 Определить фамилии клиентов, в сделках которых количество товаров

превышает количество товаров хотя бы в одной сделке (больше минимального значения)
Вариант1(ANY)
SELECT Фамилия, Кол_во
FROM Клиент INNER JOIN Сделка ON Клиент.КодКлиента=Сделка.КодКлиента
WHERE Кол_во > ANY (SELECT Кол_во FROM Сделка);

Вариант2(ALL)
Поиск не минимального значения
WHERE NOT Кол_во <= ALL (SELECT Кол_во FROM Сделка);
Вариант3(min)
WHERE Кол_во > (SELECT min(Кол_во) FROM Сделка);

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

Запрос 10 Определить клиентов, для которых среднее количество товаров в

сделке (операции) равно максимальному среднему количеству.
Вариант1(ALL, HAVING)
SELECT КодКлиента, AVG(Кол_во)
FROM Сделка
GROUP BY КодКлиента
HAVING AVG(Кол_во)>=ALL (SELECT AVG(Кол_во)
FROM Сделка GROUP BY КодКлиента);
Подзапрос в предложении FROM
Вариант2(ALL, HAVING, FROM)
SELECT КодКлиента, AVG(Кол_во)
FROM Сделка GROUP BY КодКлиента
HAVING AVG(Кол_во) = (SELECT MAX(T.AVG_for_Клиент)
FROM (SELECT AVG(Кол_во) AS AVG_for_Клиент
FROM Сделка
GROUP BY КодКлиента) AS T);

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

товаров на сумму,
превышающую 10000грн






Запрос 11б Найти фирму, которая приобрела

товаров на самую большую сумму (ALL, HAVING)

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

ключевых слов (предикатов) EXISTS и NOT EXISTS :
предназначены для использования

только совместно с подзапросами;
EXISTS принимает значение TRUE, если подзапрос содержит любое количество строк, иначе FALSE; Для NOT EXISTS – наоборот;
Никогда не возвращает значение UNKOWN;
Подзапрос может содержать любое количество столбцов;
Обычно EXISTS используется в зависимых (коррелирующих подзапросах);

КОРРЕЛИРУЮЩИЙ ПОДЗАПРОС
Подзапрос, имеющих внешнюю ссылку, связанную со значением в основном запросе.
Результат подзапроса зависит от значение во внешнем запросе и оценивается отдельно для каждой строки внешнего запроса. Следовательно, предикат EXISTS может иметь разные значения для разных строк основного запроса.

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

12 Определить название товаров, которые уже покупались
(Экв-но запр. 7а)

Вариант3

SELECT DISTINCT Название
FROM Товар_New
WHERE EXISTS (SELECT КодТовара From Сделка
WHERE Сделка.КодТовара= Товар_New.КодТовара);


Запрос 13 Определить название товаров, которые еще ни разу не покупались
(Экв-но запр. 7б) Вариант3

SELECT DISTINCT Название
FROM Товар_New
WHERE NOT EXISTS (SELECT КодТовара From Сделка
WHERE Сделка.КодТовара= Товар_New.КодТовара);

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

купил товара. Вывести клиентов (код и фамилию), подсчитанное количество. Если

клиент не покупал еще товаров, он тоже должен быть в списке.
Вариант 1
SELECT Клиент.КодКлиента, Клиент.Фамилия, Sum(Кол_во) AS SumКол_во
FROM Клиент LEFT JOIN Сделка ON Клиент.КодКлиента = Сделка.КодКлиента
GROUP BY Клиент.КодКлиента, Клиент.Фамилия;




Вариант 2
SELECT Клиент.КодКлиента,
Клиент.Фамилия, (SELECT SUM(Кол_во) AS SumКол_во
FROM Сделка
WHERE Сделка.КодКлиента=Клиент.КодКлиента)
FROM Клиент;

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

должны быть совместимы по типам;
В результирующем запросе используются имена столбцов,

заданные в первом запросе;
Предложение ORDER BY применяется к результату соединения, поэтому оно может быть указано только в конце всего составного запроса.

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

или откуда поставляются товары.

SELECT ГородКлиента AS Город
FROM Клиент
UNION
SELECT ГородТовара FROM

Товар
ORDER BY 1;

Запрос 16 Вывести отсортированный список городов,
в которых находятся клиенты или откуда поставляются товары
с указанием статуса города (ГородКлиента или ГородПоставщика)

SELECT 'ГородКлиента' AS СтатусГорода, ГородКлиента
FROM Клиент
UNION ALL
SELECT 'ГородПоставщика', ГородТовара
FROM Товар
ORDER BY 2 DESC;
Замечание! MS SQL Server использует ‘’ для литералов

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

клиенты, и откуда поставляются товары.
Вариант 1
SELECT ГородКлиента
FROM Клиент
INTERSECT
SELECT ГородТовара FROM Товар
ORDER

BY 1;

Вариант 2 (INNER JOIN)



Вариант 3 (EXISTS)



Вариант 4 (IN)

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

Вариант 5 (ANY)






Вариант 6 (×)

не проживают клиенты.
Вариант 1
SELECT ГородТовара FROM Товар
EXCEPT
SELECT ГородКлиента
FROM Клиент;

Вариант 2 (LEFT

JOIN)




Вариант 3 (EXISTS)




Вариант 4 (IN)

ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

Вариант 5 (ALL)

результата к каждом соревновании
Задание:
Найти в каждом соревновании призеров (три лучших

результата), занявших 1,2,3 места. Вывести ID_race, Competition, Sportsman, Time, Место

(подготовка в самостоятельной работе):

Необходимо выполнить в СУБД Access для БД

«Торговля» (файл Trade_SubQuery_2013_11_16) все запросы из презентации «Подзапросы. Выражение операций РА (объединение, пересечение, разность) средствами языка SQL». Особое внимание на выполнение запросов:
запрос11б;
запрос17 (варианты 2-6);
запрос18 (варианты 2-5);
а также запросы со слайда 27 (подготовка к сам. работе по темам «Соединение таблиц» и «Подзапросы»).

(подготовка в самостоятельной работе):
Подсчитать общее количество купленного каждой фирмой товара

и его стоимость (на какую сумму) до 2009 года. Выводить название фирмы, количество товара, сумму, если количество сделок для фирмы равно 10, 20, 30, 40. Отсортировать по количеству сделок по возрастанию, затем по названию фирм по убыванию.
Подсчитать на какую сумму каждая фирма купила каждого товара. Вывести название фирмы, код товара, название товара.
Определить на какую сумму продано товара типа «мебель».
Определить на какую сумму каждый год продавалось товара типа «мебель». Выводить год и сумму. Результат интересует для тех лет, в которых сумма находится в диапазоне [1 000, 10 000]. Отсортировать результат по годам.
Вывести дату сделки, в которой было куплено максимальное количество товара.
Вывести ФИО клиентов, которые покупали товар более одного раза (inner, in, exists).
Вывести города, где находится либо Товар, либо Клиент.