tasks.md

1 лабораторная

1. Список домов по улице Дзержинского.

2. Список домов по улице Дзержинского и Елизаровых в формате: улица, дом.

3. Cписок всех острых вегетарианских пицц.

4. Список всех острых пицц стоимостью до 500.

5. Список всех не острых и не вегетарианских пицц стоимостью до 490.

6. Список домов по улице Белинского, исключая проезд Белинского, в формате: улица, дом.

7. Список полных имен, в которых есть две “е” и нет “c”.

8. Список все улиц, на которых есть дома номер 1, 15, 16.

9. Список все улиц, на которых есть дома номер с 1 по 17.

10. Список все улиц, на которых нет домов номер с 10 по 30, и название которых начинается с “М” или “C”.

11. Список всех улиц, на которых есть дома не принадлежащие ни одному району.

12. Список заказов, которые были доставлены в сентябре 2017-го. Список должен быть отсортирован.

13. Список заказов, которые были доставлены за последние 3 месяца. Список должен быть отсортирован.

14. Список заказов, которые были доставлены с 1 по 10 любого месяца. Список должен быть отсортирован.

15. Список всех продуктов с их типами.

16. Все дома в Кировском районе.

17. Все дома в Кировском районе или не принадлежащие ни одному району.

18. Все дома в Кировском районе или не принадлежащие ни одному району. Для домов, не принадлежащих ни одному району, в советующем столбце должно стоять ‘нет’.

19. Список имён все страдников и с указанием имени начальника. Для начальников в соотв. Столбце выводить – ‘шеф’

20. Список всех заказов доставленных в советский район.

21. Список всех пицц, которые были доставлены в этом месяце.

22. Список всех заказчиков, делавших заказ в октябрьском районе по улице Алтайской.

23. Список всех пицц, которые были доставлены под руководствам Козлова (или им самим). В списке также должны отображаться имя курьера и район (‘нет’ – если район не известен).

24. Список продуктов с типом, которые заказывали вмести с острыми или вегетарианскими пиццами в этом месяце.

25. Найти среднюю стоимость пиццы с точность до второго знака.

26. Для каждого заказа посчитать общее количество товаров в заказе, и количество позиций в заказе. Столбцы: номер заказа, общее количество, количество позиций.

27. Для каждого заказа посчитать сумму заказа.

28. Для каждой пиццы найти общую сумму заказов.

29. Составьте отчёт по суммам заказов за последние три месяца

30. Найти всех заказчиков, которые сделали заказ одного товара на сумму не менее 3000. Отчёт должен содержать имя заказчика, номер заказа и стоимость.

31. Найти всех заказчиков, которые делали заказы во всех районах.

32. Вывести все “чеки” (номер заказа, курьер, заказчик, стоимость заказа) для всех заказов, сделанных в кировском районе и содержащих хотя бы 1 острую пиццу.

33. Для каждого заказа, в котором есть хотя бы 1 острая пицца посчитать стоимость напитков.

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

35. Вывести номера и имена сотрудников ни разу не задержавших доставку более чем на полтора часа. Использовать вариант решения без групповых операций и DISTINCT

36. Найти курьера выполнившего наибольшее число заказов.

37. Найти курьера с наименьшим процентов заказов выполненных с задержкой.

38. Для каждого курьера найти число заказов, доставленных с задержкой, как процент от числа выполненных им заказов и процент от общего числа заказов. Отчёт должен содержать имя курьера, количество заказов, количество и процент выполненных без задержки заказов

39. Для клиента найти дату и номер самого дорогого заказа.

40. Для каждого старшего группы найти стоимость всех заказов, выполненных им самим или его подчинёнными.

2 лабораторная

1. Создать представления по описанию. 1. Данные о сотрудниках: имя, должность, адрес (одной строкой), телефон, срок работы в месяцах. 2. Данные о заказах: номер заказа, номер заказчика, номер курьера, срок доставки общая стоимость заказа. 3. Расширенные данные о заказе: номер заказа, имя курьера, имя заказчика, обща стоимость заказа, строк доставки, отметка о том был ли заказа доставлен вовремя. 4. Представление, позволяющее получить маршрут курьера.
2. Создать ограничения по требованиям. 1. Ни один заказ не может включать не известные продукты, доставляться не известным сотрудником, по не известному адресу. 2. Начальником может быть только реально существующий сотрудник. 3. Цена товара не может быть отражательной или нулевой. 4. Наименования категории, наименования продуктов, имена сотрудников, имена заказчиков, названия районов, названия улиц, номера домов не могут быть пустыми. 5. Поля “острая” и “вегетарианская” могут принимать только значения 1 или 0. 6. Количество любого продукта в заказе не может быть отрицательным или превышать 100. 7. Срок, к которому надо доставить заказ, не может превышать дату и время заказа, заказ не может быть доставлен до того как его сделали. 8. Принимаются заказы только на 10 дней вперёд.
3. Модифицировать схему базы данных согласно схеме stud_2. Для каждого сотрудника может быть указано несколько адресов.
4. Добавить информацию о составе продуктов. Один продукт может содержать несколько компонентов, но в составе отдельно продукта каждый компонент может быть указан только один раз. Требуется описать только состав, точные рецепт с весом не требуется.

3 лабораторная

1. Написать функцию, возвращающую общую стоимость заказов сделанных заданным заказчиком за выбранный период. Если заказчик не указан или не заданы, граница периода выводить сообщение об ошибке. Параметры функции: промежуток времени и номер заказчика.

2. Написать процедуру выводящую маршрут курьера в указанный день. Формат вывода: ФИО курьера и список адресов доставки в формате: “hh:MM - адрес “ через точку с запятой.

3. Написать процедуру формирующую список скидок по итогам заданного месяца (месяц считает от введенной даты). Условия: скидка 10% на самую часто заказываемую пиццу , скидка 5% на пиццу, которую заказали на самые большую сумму, 15% на пиццу, которые заказывали с наибольшим числом напитков. Формат вывода: наименование – новая цена, процент скидки.

4 лабораторная

1. Написать триггер, активизирующийся при изменении содержимого таблицы Orders и проверяющий, чтобы срок доставки был больше текущего времени не менее чем на 30 минут. Если время заказа не указано автоматически должно проставляться текущее время, если срок доставки не указан, то он автоматически должен ставиться на час позже времени заказа.

2. Написать триггер, сохраняющий статистику изменений таблицы EMPLOYEES в таблице (таблицу создать), в которой хранятся номер сотрудника дата изменения, тип изменения (insert, update, delete). Триггер также выводит на экран сообщение с указанием количества дней прошедших со дня последнего изменения.

3. Добавить к таблице Orders не обязательное поле cipher, которое должно заполняться автоматически согласно шаблону: <YYYYMMDD> - <номер район> - <номер заказа в рамках месяца>. Номера не обязательно должны соответствовать дате заказа, если район не известен, то “ номер района” равен 0.

5 лабораторная (Вариант 2, Зарплата)

1. Написать команды создания таблиц заданной схемы с указанием необходимых ключей и ограничений. Все ограничения должны быть именованными (для первичных ключей имена должны начинаться с префикса PK_, для вторичного ключа – FK_, проверки - CH_). Ограничения:

• продолжительность болезни не может быть менее 1 для и более 3 месяцев;
• оклад и надбавка не могут быть отрицательными;
• значение null допустимо только в поле адрес.

2. Заполнить созданные таблицы данными, 5-10 записей для каждой таблицы.

3. Запросы

   1. Вывести список сотрудников проживающих в Томске, оклад которых больше 35 000 рублей, упорядочив их по размеру оклада.

   2. Вывести список сотрудников получающих надбавку за вредность.

   3. Сформировать статистику сотрудников по семейному положению.

   4. Вывести список сотрудников, у которых оклад меньше среднего по должности.

   5. Вывести список болезней которыми болели суммарно не более 2-х сотрудников

4. Изменений данных.

   1. Провести увеличение оклада на 10% всем сотрудникам, которые не болели в течение всего прошлого года.

   2. Удалить не используемые надбавки.

5. Представления

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

   2. Сформировать зарплатную ведомость, просуммировав оклад и все надбавки + 30% районный коэффициент, минус подоходный налог 13% и профсоюзный взнос 1%. Ведомость оформить в виде представления, содержащего табельный номер, ФИО, зарплата без вычетов, зарплата с вычетами.

6. Создать индекс для таблицы Сотрудники_надбавки содержащий 2 поля.

7. Создать пакет, состоящий из процедуры и функций, включить обработчики исключительных ситуаций.

   1. Функция возвращает число больничных листов, выписанных сотруднику за заданный период (Табельный номер и промежуток времени – параметры функции).

   2. Функция формирует список сотрудников болевших в течение года (Табельный номер и год – аргументы функции). Формат вывода: Табельный номер и ФИО сотрудника: список болезней.

   3. Процедура выдает информацию по всем болезням, которыми болели сотрудники более 2. Формат вывода: ФИО сотрудника и список болезней.

8. Создать пакет, состоящий из триггеров, включить обработчики исключительных ситуаций.

   1. Триггер, активизирующийся при изменении содержимого таблицы Сотрудники и проверяющий, чтобы должность была из допустимого списка должностей и поле оклад заполнялось автоматически в зависимости от должности, в соответствии с таблицей:

      1. Инженер - 5000
      2. Бухгалтер - 5000
      3. Начальник отдела - 10000
      4. Разнорабочий - 2000
      5. Специалист - 4000

   2. Триггер, сохраняющий статистику изменений таблицы Сотрудники в таблице Сотрудники_Статистика, в которой хранится дата изменения, тип изменения (insert, update, delete). Триггер также выводит на экран сообщение с указанием количества дней прошедших со дня последнего изменения.