Запросы к базе данных Оптовая база

База данных Оптовая база
Минимальный список характеристик:

• Код товара, название товара, количество на складе, единица измерения, стоимость единицы товара, примечания - описание товара;
• Номер, адрес, телефон и ФИО поставщика товара, срок поставки и количество товаров в поставке, номер счета.

Примечание: Один и тот же товар может доставляться несколькими поставщиками и один и тот же поставщик может доставлять несколько видов товаров.

Выборки:

• Выбрать поставщиков, которые поставляют все товары.
• Определить поставщика, который поставляет товар 'коврик для мыши' по самой низкой в среднем цене.
• Вывести названия товаров, цены на которые никогда не повышались.
• Определить, на сколько единиц возросли поставки товара 'инструмент' в 2001г. по сравнению с предыдущим годом. 

Создадим три таблицы. Все данные, относящиеся к товарам, будем заносить в таблицу «Товары», данные поставщиков – в таблицу «Поставщики», а в таблицу «Поставки» будем заносить данные конкретных поставок – номер счета, дата, срок поставки, количество товара, цена и, конечно, коды товара и поставщика.
Получается такая схема:

1. Запрос на выборку поставщиков, которые поставляют все товары.
Самое простое – сделать два запроса. В одном группируем данные по поставщикам и наименованиям поставляемых товаров и суммируем количество, во втором еще раз группируем по поставщикам, но считаем число поставляемых товаров, которое сравниваем с числом товаров в таблице «Товары». Если числа совпадут, то поставщики продают все товары из таблицы.

Первый запрос

SELECT Поставщики.ФИО, Товары.Название, Sum(Поставки.Количество_товара) AS [Sum-Количество_товара]
FROM Поставщики INNER JOIN (Товары INNER JOIN Поставки ON Товары.[Код товара] = Поставки.Товар) ON Поставщики.Номер = Поставки.Поставщик
GROUP BY Поставщики.ФИО, Товары.Название;

Второй запрос (итоговый)

SELECT Все_товары1.ФИО, Count(Все_товары1.Название) AS [Count-Название]
FROM Все_товары1
GROUP BY Все_товары1.ФИО
HAVING (((Count(Все_товары1.Название))=(SELECT Count(Название) FROM Товары)));

2. Определим поставщика с самыми низкими в среднем ценами на коврик для мыши
Сначала найдем среднюю цену на коврики у каждого поставщика. Делаем запрос с группировкой, группируем по поставщикам, для нахождения средней цены используем функцию Avg

SELECT Avg(Поставки.Цена) AS [Avg-Цена], Поставщики.ФИО
FROM Поставщики INNER JOIN (Товары INNER JOIN Поставки ON Товары.[Код товара] = Поставки.Товар) ON Поставщики.Номер = Поставки.Поставщик
WHERE (((Товары.Название)="коврик для мыши"))
GROUP BY Поставщики.ФИО;

Найдем минимальную цену:

SELECT Min(Коврик1.[Avg-Цена]) AS [Min-Avg-Цена]
FROM Коврик1;

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

SELECT Коврик1.ФИО
FROM Коврик1, Коврик2
WHERE (((Коврик1.[Avg-Цена])=[Коврик2]![Min-Avg-Цена]));

3. Найдем товары, цена на которые никогда не повышались. Самый простой запрос, пожалуй. Группируем по товарам. От максимальной цены отнимаем минимальную и выбираем записи, у которых последняя сумма меньше или равна нулю (то есть цена уменьшилась или осталась прежней)

SELECT Товары.Название
FROM Товары INNER JOIN Поставки ON Товары.[Код товара] = Поставки.Товар
GROUP BY Товары.Название
HAVING (((Max([Поставки].[Цена])-Min([Поставки].[Цена]))<=0));

4. Последний запрос – находим увеличение поставок товара «инструмент». Пойдем длинным, но легким и понятным путем. Создаем запрос с группировкой. Суммируем количество товара «инструмент» (ставим как условие), группируем по году, который определяем с помощью функции DatePart:

SELECT Sum(Поставки.Количество_товара) AS [Sum-Количество_товара], DatePart('yyyy',[Поставки]![Срок_поставки]) AS Год
FROM Товары INNER JOIN Поставки ON Товары.[Код товара] = Поставки.Товар
WHERE (((Товары.Название)="Инструмент"))
GROUP BY DatePart('yyyy',[Поставки]![Срок_поставки]);

Из этого запроса делаем два других – по 2000 и 2001 году

SELECT Инструмент1.[Sum-Количество_товара], Инструмент1.Год
FROM Инструмент1
WHERE (((Инструмент1.Год)=2000));

И аналогичный по 2001 году (не буду тут приводить).
И из двух последних делаем еще один, минусуя  из количества 2001 года количество товара в 2000 году

SELECT [Инструмент2001]![Sum-Количество_товара]-[Инструмент2000]![Sum-Количество_товара] AS Разность
FROM Инструмент2000, Инструмент2001;