Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Практическая работа №3
Часто в запросах требуется формировать промежуточные итоги, что обычно отображается появлением в запросе фразы "для каждого...". Для этой цели в операторе SELECT используется предложение GROUP BY. Запрос, в котором присутствует GROUP BY, называется группирующим запросом, поскольку в нем группируются данные, полученные в результате выполнения операции SELECT, после чего для каждой отдельной группы создается единственная суммарная строка. Стандарт SQL требует, чтобы предложение SELECT и фраза GROUP BY были тесно связаны между собой. При наличии в операторе SELECT фразы GROUP BY каждый элемент списка в предложении SELECT должен иметь единственное значение для всей группы. Более того, предложение SELECT может включать только следующие типы элементов: имена полей, итоговые функции, константы и выражения, включающие комбинации перечисленных выше элементов.
Все имена полей, приведенные в списке предложения SELECT, должны присутствовать и во фразе GROUP BY - за исключением случаев, когда имя столбца используется в итоговой функции. Обратное правило не является справедливым - во фразе GROUP BY могут быть имена столбцов, отсутствующие в списке предложения SELECT.
Если совместно с GROUP BY используется предложение WHERE, то оно обрабатывается первым, а группированию подвергаются только те строки, которые удовлетворяют условию поиска.
Стандартом SQL определено, что при проведении группирования все отсутствующие значения рассматриваются как равные. Если две строки таблицы в одном и том же группируемом столбце содержат значение NULL и идентичные значения во всех остальных непустых группируемых столбцах, они помещаются в одну и ту же группу.
Пример 1.1. Вычислить средний объем покупок, совершенных каждым покупателем.
SELECT Клиент.Фамилия, Avg(Сделка.Количество)
AS Среднее_количество
FROM Клиент INNER JOIN Сделка
ON Клиент.КодКлиента=Сделка.КодКлиента
GROUP BY Клиент.Фамилия
Фраза "каждым покупателем" нашла свое отражение в SQL-запросе в виде предложения GROUP BY Клиент.Фамилия.
Пример 1.2. Определить, на какую сумму был продан товар каждого наименования.
SELECT Товар.Название,
Sum(Товар.Цена*Сделка.Количество)
AS Стоимость
FROM Товар INNER JOIN Сделка
ON Товар.КодТовара=Сделка.КодТовара
GROUP BY Товар.Название
Пример 1.3. Подсчитать количество сделок, осуществленных каждой фирмой.
SELECT Клиент.Фирма, Count(Сделка.КодСделки)
AS Количество_сделок
FROM Клиент INNER JOIN Сделка
ON Клиент.КодКлиента=Сделка.КодКлиента
GROUP BY Клиент.Фирма
Пример 1.4. Подсчитать общее количество купленного для каждой фирмы товара и его стоимость.
SELECT Клиент.Фирма, Sum(Сделка.Количество)
AS Общее_Количество,
Sum(Товар.Цена*Сделка.Количество)
AS Стоимость
FROM Товар INNER JOIN
(Клиент INNER JOIN Сделка
ON Клиент.КодКлиента=Сделка.КодКлиента)
ON Товар.КодТовара=Сделка.КодТовара
GROUP BY Клиент.Фирма
Пример 1.5. Определить суммарную стоимость каждого товара за каждый месяц.
SELECT Товар.Название, Month(Сделка.Дата)
AS Месяц,
Sum(Товар.Цена*Сделка.Количество)
AS Стоимость
FROM Товар INNER JOIN Сделка
ON Товар.КодТовара=Сделка.КодТовара
GROUP BY Товар.Название, Month(Сделка.Дата)
Пример 1.6. Определить суммарную стоимость каждого товара первого сорта за каждый месяц.
SELECT Товар.Название, Month(Сделка.Дата)
AS Месяц,
Sum(Товар.Цена*Сделка.Количество)
AS Стоимость
FROM Товар INNER JOIN Сделка
ON Товар.КодТовара=Сделка.КодТовара
WHERE Товар.Сорт="Первый"
GROUP BY Товар.Название, Month(Сделка.Дата)
При помощи HAVING отражаются все предварительно сгруппированные посредством GROUP BY блоки данных, удовлетворяющие заданным в HAVING условиям. Это дополнительная возможность "профильтровать" выходной набор.
Условия в HAVING отличаются от условий в WHERE:
Пример 2.1. Определить фирмы, у которых общее количество сделок превысило три.
SELECT Клиент.Фирма, Count(Сделка.Количество)
AS Количество_сделок
FROM Клиент INNER JOIN Сделка
ON Клиент.КодКлиента=Сделка.КодКлиента
GROUP BY Клиент.Фирма
HAVING Count(Сделка.Количество)>3
Пример 2.2. Вывести список товаров, проданных на сумму более 10000 руб.
SELECT Товар.Название,
Sum(Товар.Цена*Сделка.Количество)
AS Стоимость
FROM Товар INNER JOIN Сделка
ON Товар.КодТовара=Сделка.КодТовара
GROUP BY Товар.Название
HAVING Sum(Товар.Цена*Сделка.Количество)>10000
Пример 2.3. Вывести список товаров, проданных на сумму более 10000 без указания суммы.
SELECT Товар.Название
FROM Товар INNER JOIN Сделка
ON Товар.КодТовара=Сделка.КодТовара
GROUP BY Товар.Название
HAVING Sum(Товар.Цена*Сделка.Количество)>10000
Практическая часть
Выполнить все запросы из теоретической части в Access.
Напишите запросы для выполнения след. операций:
а) Определите какое количество товара каждого типа было продано.
б) Отберите количество товара, которое было продано в город «Москва» за октябрь 2010 года.
в) Отберите фамилии, имена и отчества клиентов, которые приобрели товары в количестве более 3 штук.
г) Для каждой фирмы посчитать количество сделок за каждый месяц.
д) Определите клиентов и суммы, на которые были куплены ими товары, за каждый месяц.