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

Минимальный список характеристик:

• Код изделия, название изделия, является ли типовым, примечание - для каких целей предназначено, годовой объем выпуска;
• код, название, адрес и телефон предприятий, выпускающих изделия;
• название, тип, единица измерения материала, цена за единицу, отметка об использовании материала в данном изделии;
• количество материала в спецификации изделия, дата установления спецификации, дата отмены;
• год выпуска и объем выпуска данного изделия предприятием.

Одно изделие может содержать много типов материалов и один и тот же материал может входить в состав разных изделий.

Выборки:

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

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

По идее, цена на материалы тоже должна меняться, но тогда нужно добавить еще одну таблицу. И еще я добавил поле «Месяц» в таблицу «Выпуск», хотя это и необязательно. Хотел учесть спецификацию, но тогда нужно делать выпуск по конкретным датам, а у меня сделан по месяцам. Ну я просто, как пример, сделаю. А кому надо, сделают по-другому.
 

1. Определим изделие с самым большим содержанием цветных металлов.
Делаем первый запрос:

SELECT Изделия.Название, Sum(Спецификация.Количество) AS [Sum-Количество], Материалы.Тип
FROM Спец INNER JOIN (Материалы INNER JOIN (Изделия INNER JOIN Спецификация ON Изделия.Код_изделия = Спецификация.Изделие) ON Материалы.Код_материала = Спецификация.Материал) ON Спец.Код = Спецификация.Спецификация
WHERE (((IIf((IsNull([Спец]![Дата_отмены]) Or [Спец]![Дата_отмены]>Date()) And [Спец]![Дата_установки]<Date(),1,0))=1))
GROUP BY Изделия.Название, Материалы.Тип
HAVING (((Материалы.Тип)="Цветной металл"));

Группируем по названию изделия и по типу материала, как условие ставим «цветной металл». И, раз уж я связался с датами спецификации, придется проверять все спецификации на даты действия. Количество материалов берется из действующей специализации, то есть дата отмены отсутствует или больше текущей, а дата установки меньше или равна текущей. Точнее, я даже не даты сравниваю, а года. И, если спецификация менялась в течение года, то результат будет неправильный. Повторюсь, это просто пример. Переделать на дату или год с месячного учета несложно. Я просто показываю, как можно учесть некоторые нюансы.
 Получаем список изделий, в состав которых входят материалы из цветных металлов. В следующем запросе выбираем из этого списка изделием с наибольшим количеством

SELECT Max(ЦветМет1.[Sum-Количество]) AS [Max-Sum-Количество]
FROM ЦветМет1;

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

SELECT ЦветМет1.Название, ЦветМет1.[Sum-Количество]
FROM ЦветМет1, ЦветМет2
WHERE (((ЦветМет1.[Sum-Количество])=[ЦветМет2]![Max-Sum-Количество]));

2. Выведем список изделий, не выпускавшихся в 2000 году
Первый способ (два запроса)
Сначала выберем те изделия, которые выпускались в 2000 г

SELECT Изделия.Код_изделия, Выпуск.Год_выпуска, Sum(Выпуск.Объем_выпуска) AS [Sum-Объем_выпуска]
FROM Изделия INNER JOIN Выпуск ON Изделия.Код_изделия = Выпуск.Изделие
GROUP BY Изделия.Код_изделия, Выпуск.Год_выпуска
HAVING (((Выпуск.Год_выпуска)=2000));

Из-за того, что сделал выпуск помесячно, пришлось группировать по годам.
Второй запрос делаем на основе первого и таблицы «Изделия», причем используем правое объединение – выбираем все записи из таблицы и только те записи из запроса, в которых связанные поля совпадают. В этом случае, если изделие не выпускалось в 2000 году, поле «Год выпуска» будет пустым. 

SELECT Изделия.Название
FROM Нет_2000 RIGHT JOIN Изделия ON Нет_2000.Код_изделия = Изделия.Код_изделия
WHERE (((IsNull([Год_выпуска]))=Yes));

Второй способ. Делаем то же самое, но на языке SQL и одним запросом:

SELECT Изделия.Название
FROM Изделия
WHERE (((Изделия.Код_изделия) Not In (SELECT Изделия.Код_изделия
FROM Изделия INNER JOIN Выпуск ON Изделия.Код_изделия = Выпуск.Изделие
GROUP BY Изделия.Код_изделия, Выпуск.Год_выпуска
HAVING ((Выпуск.Год_выпуска)=2000))));

3. Найти среднемесячный расход лапши в 2000 году
 Делаем запрос с группировкой, группируем по названию и году, отбираем нужный год, умножаем количество материала на объем выпуска и находим среднее функцией Avg. В этом случае, если изделие выпускалось два месяца в году, посчитается среднее за два месяца. Если нужно среднее за год, то следует делить на 12.

Подробно условие проверки действующей спецификации (берем спецификацию, действовавшую во время выпуска)

И вот такой огромный текст запроса, чтобы получить одно число)))

SELECT Выпуск.Год_выпуска, Материалы.Название, Sum(Выпуск.Объем_выпуска) AS [Sum-Объем_выпуска], Avg([Спецификация]![Количество]*[Выпуск]![Объем_выпуска]) AS Расход
FROM Спец INNER JOIN (Материалы INNER JOIN ((Изделия INNER JOIN Выпуск ON Изделия.Код_изделия = Выпуск.Изделие) INNER JOIN Спецификация ON Изделия.Код_изделия = Спецификация.Изделие) ON Материалы.Код_материала = Спецификация.Материал) ON Спец.Код = Спецификация.Спецификация
WHERE (((IIf([Выпуск]![Год_выпуска] Between DatePart('yyyy',[Спец]![Дата_установки]) And IIf(IsNull([Спец]![Дата_отмены]),DatePart('yyyy',Date()),DatePart('yyyy',[Спец]![Дата_отмены])),1,0))=1))
GROUP BY Выпуск.Год_выпуска, Материалы.Название
HAVING (((Выпуск.Год_выпуска)=2000) AND ((Материалы.Название)="Лапша"));

4. Нужно найти изделия, затраты на которые снизились в 2000 году.
Сначала посчитаем затраты за все годы с учетом действовавших на тот момент спецификаций. Я посчитал затраты, как произведение выпуска, количества материала в единице изделия и цены материала. Цена у нас постоянная, поэтому затраты зависят от спецификации и объема выпуска. Если объем выпуска не важен, то его можно убрать.

Условие проверки спецификация, как в предыдущем запросе.

SELECT Изделия.Код_изделия, Выпуск.Год_выпуска, Sum([Выпуск]![Объем_выпуска]*[Спецификация]![Количество]*[Материалы]![Цена]) AS Затраты
FROM Спец INNER JOIN (Материалы INNER JOIN ((Изделия INNER JOIN Выпуск ON Изделия.Код_изделия = Выпуск.Изделие) INNER JOIN Спецификация ON Изделия.Код_изделия = Спецификация.Изделие) ON Материалы.Код_материала = Спецификация.Материал) ON Спец.Код = Спецификация.Спецификация
WHERE (((IIf([Выпуск]![Год_выпуска] Between DatePart('yyyy',[Спец]![Дата_установки]) And IIf(IsNull([Спец]![Дата_отмены]),DatePart('yyyy',Date()),DatePart('yyyy',[Спец]![Дата_отмены])),1,0))=1))
GROUP BY Изделия.Код_изделия, Выпуск.Год_выпуска;

Затем отбираем выпуск 1999 года

SELECT Затраты2000.Код_изделия, Затраты2000.Год_выпуска, Затраты2000.Затраты
FROM Затраты2000
WHERE (((Затраты2000.Год_выпуска)=1999));

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

SELECT Изделия.Название, Затраты_1999.Затраты, Затраты_2000.Затраты, [Затраты_1999.Затраты]-[Затраты_2000.Затраты] AS [Снижение затрат]
FROM (Затраты_1999 INNER JOIN Изделия ON Затраты_1999.Код_изделия = Изделия.Код_изделия) INNER JOIN Затраты_2000 ON Изделия.Код_изделия = Затраты_2000.Код_изделия
WHERE (((Затраты_1999.Затраты) Is Not Null) AND ((Затраты_2000.Затраты) Is Not Null) AND (([Затраты_1999.Затраты]-[Затраты_2000.Затраты])>0));

Вот такой вот вариант. Хотя, может, я там что-то неправильно понял в условии, и на самом деле все гораздо проще. Удачи в решении!