Задания для самостоятельной работы. 1. Напишите инструкцию, результатом выполнения которой была бы вставка нескольких строк в таблицу Сотрудники

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

2. Напишите инструкцию, результатом выполнения которой было бы удаление всех неоплаченных на текущую дату заказов из таблицы Заказы.

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

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

5. Напишите инструкцию, результатом выполнения которой было бы удаление из таблицы Заказчики тех заказчиков, которые сделали последний заказ более 1 года тому назад. Удалите соответствующие заказы из таблицы Заказы.

6. В таблицу Заказы добавьте столбец Номер_договора типа uuid, введите в каждой строке в этом столбце значение, равное количеству секунд, прошедших от ‘01.01.1900 00:00’ до того момента, когда очередное такое значение вносится в таблицу, и сделайте Номер_договора первичным ключом, предварительно удалив созданный ранее первичный ключ.

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

8. Создайте представления для ролей «Директор», «Закупщик», «Продавец», исходя из того, что им необходимо создавать отчеты:

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

Контрольное задание.

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

2. В одну из базовых таблиц, на которую не ссылаются внешние ключи других таблиц, добавить столбец типа uuid и сделать его первичным ключом, отменив существующее в этой таблице ограничение первичного ключа. В этот столбец в каждой строке ввести значение, равное количеству секунд, прошедших от ‘01.01.1900 00:00’ до того момента, когда очередное такое значение вносится в таблицу. Для столбцов, входивших в состав первичного ключа, создать уникальный индекс.

3. Создать модифицируемое представление:

№ варианта	Содержание представления
	Данные прайс-листа, упорядоченные по типу изделия и номеру модели.
	Данные об оплате коммунальных услуг за текущий месяц, упорядоченные по личному номеру клиента и типу услуг.
	Данные о выполненных заказах за прошедший месяц, упорядоченные по номеру заказа и типу выполненных работ.
	Данные о выплате зарплаты за текущий месяц, упорядоченные по ФИО сотрудника.
	Данные и поставках с начала текущего года, упорядоченные по номеру договора и типу продукции.
	Данные о билетах, проданных за предыдущий квартал, упорядоченные по номеру рейса и категории.
	Данные о сотрудниках, проработавших более 3 дет, упорядоченные по ФИО сотрудника и должности.
	Данные о клиентах, проживающих в данный момент времени в отеле, упорядоченные по номеру комнаты и ФИО клиента.
	Данные о продукции, экспортированной с начала текущего года, упорядоченные по типу продукции и стране импортера.
	Данные о кредитах, которыми пользуются клиенты банка в данный момент времени, упорядоченные по юридическому наименованию клиента и номеру договора о кредитовании.
	Данные о финансовых показателях предприятий за последние 5 лет, упорядоченные по юридическому наименованию предприятия, балансовому году и рейтингу.
	Данные о ходе текущего выполнения работ, упорядоченные по наименованию проекта и типу выполняемой работы.

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

4. Написать инструкции для внесения данных во все вычисляемые столбцы базовых таблиц (если таких столбцов нет, создать 2-3 таких столбца по указанию преподавателя).

5. Для базовой таблицы, являющейся связующей для двух таблиц, связь между которыми в логической модели имеет степень N:M, создать представление для вывода тех строк, в которых в столбцах внешних ключей содержатся NULL-значения. Написать инструкции для: а) модификации таких строк путем замены NULL-значений допустимыми значениями из родительских столбцов; б) удаления таких строк.

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

7. Оформить отчет о выполнении контрольного задания. Отчет по лабораторной работе должен содержать:

1. отчеты DDL для всех базовых таблиц разработанной БД;
2. скрипты и результаты выполнения запросов контрольного задания.

Контрольные вопросы

1. Дайте характеристику основным типам ограничений.

2. Как можно обеспечить уникальность значений для данного столбца или группы столбцов?

3. Что такое индекс? Какие типы индексов поддерживает PostgreSQL?

4. Что такое кластеризация таблиц? Почему для таблицы может быть создан только один кластерный индекс?

5. Что будет введено в столбец, если в инструкции INSERT этот столбец не указан?

6. Чем отличается TRUNCATE от DELETE?

7. Как в SQL решаются аномалии удаления и обновления значений родительских ключей?

8. Что такое представление?

9. В чем заключается преимущество использования в запросах представлений по сравнению с базовыми таблицами БД?

10. Какие представления называются модифицируемыми?