Ульяновский государственный университет
Опубликован: 10.09.2004 | Доступ: свободный | Студентов: 34481 / 4444 | Оценка: 4.06 / 3.65 | Длительность: 16:15:00
ISBN: 978-5-9556-0101-4
Лекция 7:

Построение нетривиальных запросов

< Лекция 6 || Лекция 7: 123 || Лекция 8 >

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

Ключевые слова ANY и ALL могут использоваться с подзапросами, возвращающими один столбец чисел.

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

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

Если в результате выполнения подзапроса получено пустое значение, то для ключевого слова ALL условие сравнения будет считаться выполненным, а для ключевого слова ANY – невыполненным. Ключевое слово SOME является синонимом слова ANY.

Пример 7.14. Определить клиентов, совершивших сделки с максимальным количеством товара (эквивалентно запросу 7.3.)

SELECT Клиент.Фамилия, Сделка.Количество
FROM Клиент INNER JOIN Сделка 
    ON Клиент.КодКлиента=Сделка.КодКлиента
WHERE Сделка.Количество>=ALL(SELECT Количество
    FROM Сделка)
Пример 7.14. Определение клиентов, совершивших сделки с максимальным количеством товара.

В примере определены клиенты, в сделках которых количество товара больше или равно количеству товара в каждой из всех сделок.

Пример 7.15. Найти фирму, купившую товаров на сумму, превышающую 10000 руб.

SELECT Клиент.Фирма, 
    Sum(Товар.Цена*Сделка.Количество) 
    AS Общ_стоимость
FROM Товар INNER JOIN 
    (Клиент INNER JOIN Сделка 
    ON Клиент.КодКлиента=Сделка.КодКлиента) 
    ON Товар.КодТовара=Сделка.КодТовара
GROUP BY Клиент.Фирма
HAVING Sum(Товар.Цена*Сделка.Количество)>10000
Пример 7.15. Определение фирмы, купившей товаров на сумму, превышающую 10000 руб.

Добавим в запрос подзапрос.

Пример 7.16. Найти фирму, которая приобрела товаров на самую большую сумму.

SELECT Клиент.Фирма, 
    Sum(Товар.Цена*Сделка.Количество) 
    AS Общ_стоимость
FROM Товар INNER JOIN 
    (Клиент INNER JOIN Сделка
    ON Клиент.КодКлиента=Сделка.КодКлиента) 
    ON Товар.КодТовара=Сделка.КодТовара
GROUP BY Клиент.Фирма
HAVING Sum(Товар.Цена*Сделка.Количество)>=
ALL(SELECT Sum(Товар.Цена*Сделка.Количество)
    FROM Товар INNER JOIN Сделка 
    ON Товар.КодТовара=Сделка.КодТовара
GROUP BY Сделка.КодКлиента)
Пример 7.16. Определение фирмы, которая приобрела товаров на самую большую сумму.

Вложенный подзапрос подсчитывает общую стоимость покупок каждого клиента. Внешний подзапрос также подсчитывает общую стоимость покупок каждого клиента и определяет тех, для кого эта сумма, по сравнению с другими покупателями, оказалась больше или точно такой же.

Пример 7.17. Найти фирмы, в сделках которых количество товара превышает такой же показатель хотя бы в одной сделке клиентов из Самары.

SELECT Клиент.Фирма, Сделка.Количество
FROM Клиент INNER JOIN Сделка 
    ON Клиент.КодКлиента=Сделка.КодКлиента
WHERE Сделка.Количество>
ANY(SELECT Сделка.Количество
    FROM Клиент INNER JOIN Сделка 
    ON Клиент.КодКлиента=Сделка.КодКлиента
WHERE Клиент.ГородКлиента='Самара')
Пример 7.17. Определение фирм, в сделках которых количество товара превышает такой же показатель хотя бы в одной сделке клиентов из Самары.

Использование операций EXISTS и NOT EXISTS

Ключевые слова EXISTS и NOT EXISTS предназначены для использования только совместно с подзапросами. Результат их обработки представляет собой логическое значение TRUE или FALSE. Для ключевого слова EXISTS результат равен TRUE в том и только в том случае, если в возвращаемой подзапросом результирующей таблице присутствует хотя бы одна строка. Если результирующая таблица подзапроса пуста, результатом обработки операции EXISTS будет значение FALSE. Для ключевого слова NOT EXISTS используются правила обработки, обратные по отношению к ключевому слову EXISTS . Поскольку по ключевым словам EXISTS и NOT EXISTS проверяется лишь наличие строк в результирующей таблице подзапроса, то эта таблица может содержать произвольное количество столбцов.

Пример 7.18. Определить список имеющихся на складе товаров (запрос эквивалентен примеру 7.7).

SELECT Название
FROM Товар
WHERE EXISTS (SELECT КодТовара
    FROM Склад
WHERE Товар.КодТовара=Склад.КодТовара)
Пример 7.18. Определение списка имеющихся на складе товаров.

Пример 7.19. Определить список отсутствующих на складе товаров (запрос эквивалентен примеру 7.8).

SELECT Название
FROM Товар
WHERE NOT EXISTS (SELECT КодТовара
    FROM Склад
WHERE Товар.КодТовара=Склад.КодТовара)
Пример 7.19. Определение списка отсутствующих на складе товаров.
< Лекция 6 || Лекция 7: 123 || Лекция 8 >
Федор Антонов
Федор Антонов

Здравствуйте!

Записался на ваш курс, но не понимаю как произвести оплату.

Надо ли писать заявление и, если да, то куда отправлять?

как я получу диплом о профессиональной переподготовке?

Ирина Мельник
Ирина Мельник

Здравствуйте, записалась на курс основы SQL, подскажите, стоимость курса.