Учебные задачи по PL/SQL

Логическая схема

База данных эмулирует географически распределенную торговую компанию, торгующую с центрального склада. Каждое низовое подразделение отвечает за определенный регион (город), где набирает клиентов и организует продажи. Ассортимент товаров, цены и закупки осуществляются централизованно. Часть схемы, относящаяся к закупкам (и остаткам) пока не наполнена данными.

Особенности отдельных сущностей.

В интервалах цен Дата конца - всегда не входит.
Подразделения образуют иерархию. Для отчетов (запросов) будет использоваться выборка по иерархии. Однако название однозначно идентифицирует подразделение и его достаточно при показе информации.
Для именования клиентов и поставщиков применяется два атрибута: Название (moniker), которое должно быть уникальным и Наименование (name), которое соотвествует официальным документам и используется в печатных формах.
Каталог товаров также иерархический. В отличие от подразделений, на каждом уровне ветки именуются коротким кодом, а для отражения в плоских отчетах и интерфейсах применяется иерархический код.
На строку продажи действуют две скидки: указанная в продаже и дополнительная, назначенная индивидуально для строки.
Стоимость товара в каждой строке, естественно, получается умножением цены на количество, а для продаж - дополнительно с применением скидки. Теоретически его можно не хранить, либо можно не хранить цену, однако и то и другое часто нужно в разных отчетах, и поэтому оба атрибута обычно хранимые. Кроме того, необходима воспроизводимость при печати документов, не смотря на возможные проблемы окрушгления, что тоже является аргументом за хранение всех атрибутов, а также НДС.
Общая сумма продажи и НДС продажи, естественно, равна сумме по всем позициям. Однако из тех же соображений удобства отчетов она обычно является хранимой.
Поставки и Продажи имеют два состояния: Черновик и Исполнена. При исполнении документа изменяются остатки на складе. Пока документ - черновик, его можно свободно изменять. А исполненные документы править нельзя. Естественно, в реальных системах состояний больше, но для модельных целей этого достаточно.

Задачи для запроса данных

Найти товары, цены на которые отличаются от цены на модель.
Найти продаже, цены без скидки в которых отличаются от цены на товар.
Проверить гипотезу, что там, где цены продажи отличаются от цены каталога цены в каталоге просто были испорчены. Для этого надо для каждой модели, для которых есть продажи с ценами, отличающимися от цен на товар, посмотреть продажи с совпадающими и несовпадающими ценами.
Вывод каталога с отступами в соответствии с уровнями каталога. Для каждого узла - число нижележащих узлов.
Вывод таблицы подразделений с отступами в соответствии с уровнями каталога
Вывод каталога с отступами, для листа с товарами - число моделей и товаров в ним.
Вывод каталога с отступами, для каждого узла - число моделей и товаров в нем и нижележащих узлах
Вычислить число продаж, их общую сумму, средневзвешенную, максимальную и минимальную скидки по каждому клиенту, у которых были продажи со скидкой более, чем 25%. Все - без учета скидок на строки
Вычислить число строк продаж, количество, сумму средневзвешенную, максимальную и минимальную скидку по торговой марке (t_model.label).
Выдать отчет о продажах по подразделениям, для каждого подразделения в дереве в отчет включать подчиненные.В отчете - число продаж, число строк, количество, общая сумма. Указание. Для начала сделать запрос, который бы для каждого подразделения выводил все подчиненные.
Выявить наиболее характерные значения скидок. Для этого построить распределение по предоставляемым скидкам, для каждой определить количество позиций, товаров и моделей, колдичество и сумму продаж.
Найти модели, на которые предоставлены нехарактерно большие скидки, с учетом предыдущего анализа. Сделать предположение о с составе этих моделей

Пока не готовы данные

Вывод таблицы подразделений, для каждого узла - стоимость товаров на складе.
Стандратная оборотка по товару в штуках за период: остаток на начало, приход, уход, остаток на конец. Различные группировки, выбор наилучших.
Отчет по прибылям от продажи - в разрезе товаров и подразделений.
Описанные выше отчеты построить по узлам каталога и по подразделениям с иерархическим включением по принадлежности.

Задачи для программирования

Триггер, блокирующий продажу товара себе в убыток, то есть по цене, меньшей чем имеющаяся сейчас у товара на складе.
Триггер, блокирующий установку скидки более 20% для не vip клиентов.
Триггер, автоматически устанавливающий цену продажи товара по значению цены на дату продажи с учетом скидки.
Триггер для строки поставки, автоматически вычисляющий сумму по строкам при изменении цены или количества.
Триггер для строки продажи и для самой продажи, автоматически вычисляющий сумму по строкам при изменении цены или количества, а также скидки в заголовке, и изменяющие общую сумму продажи в заголовке. Задача сложная из-за mutating по простой схеме, когда изменение скидки меняет строки, а изменение суммы строки - меняет сумму в заголовке. Как альтернатива: для изменения скидки используется процедура, но тогда нужно придумать механизм блокировки прямого изменения этого поля в таблице.
Процедура назначения цены товару на заданном интервале, соответственно изменяющая таблицу цен. Должна изменять также цены в продажах-черновиках, естественно, с учетом скидки.
Набор процедур для элементарных операций над узлами каталога, обеспечивающих вычисление иерархического кода: вставка, перемещение в иерархии, переименование.
Альтернатива предыдущему: триггера, обеспечивающие вычисление иерархического кода для узлов каталога. Это сложнее, чем набор процедур.
Набор процедур элементарных действий или триггера, контролирующие отсутствие циклов в иерархии подразделений.
Процедура или триггер исполнения поставки, изменяющая количество и стоимость товара на складе.
Процедура или триггер исполнения продажи, изменяющая количество и стоимость товара на складе. Необходимо блокировать продаже отсутствующего товара, но при этом обязательно выдавать сообщение по всем товарам, которых не хватает, а не останавливаться на первом.

Любые правки этой статьи будут перезаписаны при следующем сеансе репликации. Если у вас есть серьезное замечание по тексту статьи, запишите его в раздел «discussion».

Репликация: База Знаний «Заказных Информ Систем» → «Учебные задачи по PL/SQL»