Учебные задачи по PL/SQL — различия между версиями
Материал из CustisWiki
BenderBot (обсуждение | вклад) м (реплицировано из внутренней CustisWiki) |
|||
(не показано 6 промежуточных версий 2 участников) | |||
Строка 6: | Строка 6: | ||
Особенности отдельных сущностей. | Особенности отдельных сущностей. | ||
− | * В интервалах цен Дата конца | + | * В интервалах цен Дата конца - всегда не входит. |
* Подразделения образуют иерархию. Для отчетов (запросов) будет использоваться выборка по иерархии. Однако название однозначно идентифицирует подразделение и его достаточно при показе информации. | * Подразделения образуют иерархию. Для отчетов (запросов) будет использоваться выборка по иерархии. Однако название однозначно идентифицирует подразделение и его достаточно при показе информации. | ||
* Для именования клиентов и поставщиков применяется два атрибута: Название (moniker), которое должно быть уникальным и Наименование (name), которое соотвествует официальным документам и используется в печатных формах. | * Для именования клиентов и поставщиков применяется два атрибута: Название (moniker), которое должно быть уникальным и Наименование (name), которое соотвествует официальным документам и используется в печатных формах. | ||
* Каталог товаров также иерархический. В отличие от подразделений, на каждом уровне ветки именуются коротким кодом, а для отражения в плоских отчетах и интерфейсах применяется иерархический код. | * Каталог товаров также иерархический. В отличие от подразделений, на каждом уровне ветки именуются коротким кодом, а для отражения в плоских отчетах и интерфейсах применяется иерархический код. | ||
− | * На строку продажи действуют две скидки: указанная в продаже | + | * На строку продажи действуют две скидки: указанная в продаже и дополнительная, назначенная индивидуально для строки. |
− | * Стоимость товара в каждой строке, естественно, получается умножением цены на количество, а для продаж | + | * Стоимость товара в каждой строке, естественно, получается умножением цены на количество, а для продаж - дополнительно с применением скидки. Теоретически его можно не хранить, либо можно не хранить цену, однако и то и другое часто нужно в разных отчетах, и поэтому оба атрибута обычно хранимые. Кроме того, необходима воспроизводимость при печати документов, не смотря на возможные проблемы окрушгления, что тоже является аргументом за хранение всех атрибутов, а также НДС. |
* Общая сумма продажи и НДС продажи, естественно, равна сумме по всем позициям. Однако из тех же соображений удобства отчетов она обычно является хранимой. | * Общая сумма продажи и НДС продажи, естественно, равна сумме по всем позициям. Однако из тех же соображений удобства отчетов она обычно является хранимой. | ||
− | * Поставки и Продажи имеют два состояния: Черновик | + | * Поставки и Продажи имеют два состояния: Черновик и Исполнена. При исполнении документа изменяются остатки на складе. Пока документ - черновик, его можно свободно изменять. А исполненные документы править нельзя. Естественно, в реальных системах состояний больше, но для модельных целей этого достаточно. |
− | + | ||
− | + | ||
== Задачи для запроса данных == | == Задачи для запроса данных == | ||
− | + | * Найти товары, цены на которые отличаются от цены на модель. | |
− | + | * Найти продаже, цены без скидки в которых отличаются от цены на товар. | |
− | + | * Проверить гипотезу, что там, где цены продажи отличаются от цены каталога цены в каталоге просто были испорчены. Для этого надо для каждой модели, для которых есть продажи с ценами, отличающимися от цен на товар, посмотреть продажи с совпадающими и несовпадающими ценами. | |
− | + | * Вывод каталога с отступами в соответствии с уровнями каталога. Для каждого узла - число нижележащих узлов. | |
− | + | * Вывод таблицы подразделений с отступами в соответствии с уровнями каталога | |
− | + | * Вывод каталога с отступами, для листа с товарами - число моделей и товаров в ним. | |
− | + | * Вывод каталога с отступами, для каждого узла - число моделей и товаров в нем и нижележащих узлах | |
− | + | * Вычислить число продаж, их общую сумму, средневзвешенную, максимальную и минимальную скидки по каждому клиенту, у которых были продажи со скидкой более, чем 25%. Все - без учета скидок на строки | |
− | + | * Вычислить число строк продаж, количество, сумму средневзвешенную, максимальную и минимальную скидку по торговой марке (t_model.label). | |
− | + | * Выдать отчет о продажах по подразделениям, для каждого подразделения в дереве в отчет включать подчиненные.В отчете - число продаж, число строк, количество, общая сумма. Указание. Для начала сделать запрос, который бы для каждого подразделения выводил все подчиненные. | |
− | + | * Выявить наиболее характерные значения скидок. Для этого построить распределение по предоставляемым скидкам, для каждой определить количество позиций, товаров и моделей, колдичество и сумму продаж. | |
+ | * Найти модели, на которые предоставлены нехарактерно большие скидки, с учетом предыдущего анализа. Сделать предположение о с составе этих моделей | ||
− | ; | + | ; Пока не готовы данные |
− | + | * Вывод таблицы подразделений, для каждого узла - стоимость товаров на складе. | |
− | + | * Стандратная оборотка по товару в штуках за период: остаток на начало, приход, уход, остаток на конец. Различные группировки, выбор наилучших. | |
+ | * Отчет по прибылям от продажи - в разрезе товаров и подразделений. | ||
+ | * Описанные выше отчеты построить по узлам каталога и по подразделениям с иерархическим включением по принадлежности. | ||
− | |||
== Задачи для программирования == | == Задачи для программирования == | ||
− | + | * Триггер, блокирующий продажу товара себе в убыток, то есть по цене, меньшей чем имеющаяся сейчас у товара на складе. | |
+ | * Триггер, блокирующий установку скидки более 20% для не vip клиентов. | ||
+ | * Триггер, автоматически устанавливающий цену продажи товара по значению цены на дату продажи с учетом скидки. | ||
+ | * Триггер для строки поставки, автоматически вычисляющий сумму по строкам при изменении цены или количества. | ||
+ | * Триггер для строки продажи и для самой продажи, автоматически вычисляющий сумму по строкам при изменении цены или количества, а также скидки в заголовке, и изменяющие общую сумму продажи в заголовке. Задача сложная из-за mutating по простой схеме, когда изменение скидки меняет строки, а изменение суммы строки - меняет сумму в заголовке. Как альтернатива: для изменения скидки используется процедура, но тогда нужно придумать механизм блокировки прямого изменения этого поля в таблице. | ||
+ | * Процедура назначения цены товару на заданном интервале, соответственно изменяющая таблицу цен. Должна изменять также цены в продажах-черновиках, естественно, с учетом скидки. | ||
+ | * Набор процедур для элементарных операций над узлами каталога, обеспечивающих вычисление иерархического кода: вставка, перемещение в иерархии, переименование. | ||
+ | * Альтернатива предыдущему: триггера, обеспечивающие вычисление иерархического кода для узлов каталога. Это сложнее, чем набор процедур. | ||
+ | * Набор процедур элементарных действий или триггера, контролирующие отсутствие циклов в иерархии подразделений. | ||
+ | * Процедура или триггер исполнения поставки, изменяющая количество и стоимость товара на складе. | ||
+ | * Процедура или триггер исполнения продажи, изменяющая количество и стоимость товара на складе. Необходимо блокировать продаже отсутствующего товара, но при этом обязательно выдавать сообщение по всем товарам, которых не хватает, а не останавливаться на первом. | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | + | {{replicate-from-custiswiki-to-lib}} | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + |
Версия 15:18, 23 ноября 2007
Логическая схема
База данных эмулирует географически распределенную торговую компанию, торгующую с центрального склада. Каждое низовое подразделение отвечает за определенный регион (город), где набирает клиентов и организует продажи. Ассортимент товаров, цены и закупки осуществляются централизованно. Часть схемы, относящаяся к закупкам (и остаткам) пока не наполнена данными.
Особенности отдельных сущностей.
- В интервалах цен Дата конца - всегда не входит.
- Подразделения образуют иерархию. Для отчетов (запросов) будет использоваться выборка по иерархии. Однако название однозначно идентифицирует подразделение и его достаточно при показе информации.
- Для именования клиентов и поставщиков применяется два атрибута: Название (moniker), которое должно быть уникальным и Наименование (name), которое соотвествует официальным документам и используется в печатных формах.
- Каталог товаров также иерархический. В отличие от подразделений, на каждом уровне ветки именуются коротким кодом, а для отражения в плоских отчетах и интерфейсах применяется иерархический код.
- На строку продажи действуют две скидки: указанная в продаже и дополнительная, назначенная индивидуально для строки.
- Стоимость товара в каждой строке, естественно, получается умножением цены на количество, а для продаж - дополнительно с применением скидки. Теоретически его можно не хранить, либо можно не хранить цену, однако и то и другое часто нужно в разных отчетах, и поэтому оба атрибута обычно хранимые. Кроме того, необходима воспроизводимость при печати документов, не смотря на возможные проблемы окрушгления, что тоже является аргументом за хранение всех атрибутов, а также НДС.
- Общая сумма продажи и НДС продажи, естественно, равна сумме по всем позициям. Однако из тех же соображений удобства отчетов она обычно является хранимой.
- Поставки и Продажи имеют два состояния: Черновик и Исполнена. При исполнении документа изменяются остатки на складе. Пока документ - черновик, его можно свободно изменять. А исполненные документы править нельзя. Естественно, в реальных системах состояний больше, но для модельных целей этого достаточно.
Задачи для запроса данных
- Найти товары, цены на которые отличаются от цены на модель.
- Найти продаже, цены без скидки в которых отличаются от цены на товар.
- Проверить гипотезу, что там, где цены продажи отличаются от цены каталога цены в каталоге просто были испорчены. Для этого надо для каждой модели, для которых есть продажи с ценами, отличающимися от цен на товар, посмотреть продажи с совпадающими и несовпадающими ценами.
- Вывод каталога с отступами в соответствии с уровнями каталога. Для каждого узла - число нижележащих узлов.
- Вывод таблицы подразделений с отступами в соответствии с уровнями каталога
- Вывод каталога с отступами, для листа с товарами - число моделей и товаров в ним.
- Вывод каталога с отступами, для каждого узла - число моделей и товаров в нем и нижележащих узлах
- Вычислить число продаж, их общую сумму, средневзвешенную, максимальную и минимальную скидки по каждому клиенту, у которых были продажи со скидкой более, чем 25%. Все - без учета скидок на строки
- Вычислить число строк продаж, количество, сумму средневзвешенную, максимальную и минимальную скидку по торговой марке (t_model.label).
- Выдать отчет о продажах по подразделениям, для каждого подразделения в дереве в отчет включать подчиненные.В отчете - число продаж, число строк, количество, общая сумма. Указание. Для начала сделать запрос, который бы для каждого подразделения выводил все подчиненные.
- Выявить наиболее характерные значения скидок. Для этого построить распределение по предоставляемым скидкам, для каждой определить количество позиций, товаров и моделей, колдичество и сумму продаж.
- Найти модели, на которые предоставлены нехарактерно большие скидки, с учетом предыдущего анализа. Сделать предположение о с составе этих моделей
- Пока не готовы данные
- Вывод таблицы подразделений, для каждого узла - стоимость товаров на складе.
- Стандратная оборотка по товару в штуках за период: остаток на начало, приход, уход, остаток на конец. Различные группировки, выбор наилучших.
- Отчет по прибылям от продажи - в разрезе товаров и подразделений.
- Описанные выше отчеты построить по узлам каталога и по подразделениям с иерархическим включением по принадлежности.
Задачи для программирования
- Триггер, блокирующий продажу товара себе в убыток, то есть по цене, меньшей чем имеющаяся сейчас у товара на складе.
- Триггер, блокирующий установку скидки более 20% для не vip клиентов.
- Триггер, автоматически устанавливающий цену продажи товара по значению цены на дату продажи с учетом скидки.
- Триггер для строки поставки, автоматически вычисляющий сумму по строкам при изменении цены или количества.
- Триггер для строки продажи и для самой продажи, автоматически вычисляющий сумму по строкам при изменении цены или количества, а также скидки в заголовке, и изменяющие общую сумму продажи в заголовке. Задача сложная из-за mutating по простой схеме, когда изменение скидки меняет строки, а изменение суммы строки - меняет сумму в заголовке. Как альтернатива: для изменения скидки используется процедура, но тогда нужно придумать механизм блокировки прямого изменения этого поля в таблице.
- Процедура назначения цены товару на заданном интервале, соответственно изменяющая таблицу цен. Должна изменять также цены в продажах-черновиках, естественно, с учетом скидки.
- Набор процедур для элементарных операций над узлами каталога, обеспечивающих вычисление иерархического кода: вставка, перемещение в иерархии, переименование.
- Альтернатива предыдущему: триггера, обеспечивающие вычисление иерархического кода для узлов каталога. Это сложнее, чем набор процедур.
- Набор процедур элементарных действий или триггера, контролирующие отсутствие циклов в иерархии подразделений.
- Процедура или триггер исполнения поставки, изменяющая количество и стоимость товара на складе.
- Процедура или триггер исполнения продажи, изменяющая количество и стоимость товара на складе. Необходимо блокировать продаже отсутствующего товара, но при этом обязательно выдавать сообщение по всем товарам, которых не хватает, а не останавливаться на первом.
Любые правки этой статьи будут перезаписаны при следующем сеансе репликации. Если у вас есть серьезное замечание по тексту статьи, запишите его в раздел «discussion».
Репликация: База Знаний «Заказных Информ Систем» → «Учебные задачи по PL/SQL»