Skip navigation.
Home

Задания для магистров первого года (2012)

ER-диаграмма схемы HR, над которой выполняются запросы.

1) Выбрать название городов и суммарные затраты на зарплату в 10 городах с наибольшими затратами, упорядочить по убыванию. Если городе нет отделов или в отделе нет сотрудников, выдать 0.
2) В каком городе больше всего затраты на зарплату руководящего звена?
3) Выбрать фамилии и имена сотрудников, которые работали в разных городах.
-- Аналитические функции
4) Выбрать сотрудников IT и Executive, их зарплату , количество сотрудников в отделе с такой же зарплатой, упорядочить по имени отдела, по зарплате (в обратном порядке)
-- Рекурсивные запросы
5) Выбрать всех начальников сотрудника c заданными фамилией и именем
-- PL/pgSQL
6) Написать функцию, которая для всех таблиц, имя которых содержит слово, передаваемое в качестве параметра, в зависимости от второго параметра преобразует поля character varying в text или наоборот, text в character varying(255). Проверить, что происходит, когда text не умещается в character varying(255). Для выполнения задания можно использовать информацию из information_schema.tables, information_schema.columns.
7) Написать функцию, которая будет удалять все представления в cхеме public и, в зависимости от значения параметра, возвращать таблицу имен представлений, которые удалось (или не удалось) удалить в выходном параметре. В случае, если мы сообщаем, что представление не удалось удалить, подобного рода ошибку игнорируем. Если не сообщаем, то подобная ошибка фатальна. Для выполнения задания можно использовать информацию из pg_catalog.pg_views или information_schema.views, но стоит учитывать, что information_schema.views не предоставляет информации о представлениях, на которые у пользователя нет прав.
-- Написание хранимых функций на Си
8) Написать функцию, которая выдаст содержимое pg_hba.conf в виде таблицы (необязательное задание).
-- Агрегаты и пользовательские типы данных
9) Написать агрегаты для мат. ожидания и дисперсии
10) Написать функцию, которая возвращает все уникальные с точностью до регистра значения столбца в нижнем регистре через заданный разделитель.
-- Интерфейсы взаимодействия с СУБД
11) С использованием любого интерфейса взаимодействия с PostgreSQL создать редактор таблицы, поддерживающий транзакционную работу. Например, редактируем таблицу Employees из схемы HR. Программа должна получать у пользователя учетные данные, подсоединяться к СУБД. После этого выводить меню, содержащее примерно следующий набор команд: "вставить запись", "изменить запись", "показать содержимое", "показать запись по id", "найти сотрудников по фамилии", "откатить изменения", "применить изменения", "выйти". После этого в цикле позволять выполнять выбранные операции. Делать commit только при явном запросе пользователя. При выходе - выполнять откат активной транзакции.
-- Оптимизация запросов (на данной схеме)
12) select avgmark from students where lower(name)=:name and lower(surname)=:surname; -- где :name, например, равно 'user_repcolumn_group_1', a surname='crramuvvjimuhe' не меняя запроса
13) select surname,lection_name from lections,students,lect_stud where lect_id=lections.id and stud_id=students.id and ( upper(surname)=:surname or upper(lection_name)=:lname); -- например, select surname,lection_name from lections,students,lect_stud where lect_id=lections.id and stud_id=students.id and ( upper(surname)='CTGXZBULLJREKV' or upper(lection_name)='DUAL');
-- MVCC
14) Спровоцировать ошибку could not serialize access due to concurrent update.
15) Спровоцировать ошибку could not serialize access due to read/write dependencies among transactions.
-- Триггеры и секционирование
16) Написать и запланировать с помощью cron задание для периодического создания новых секций и обновления триггера секционирования по диапазону.
-- Система правил в PostgreSQL
17) На основе HR-схемы создать с помощью правил обновляемое представление: сотрудники отделов, расположенных в Сиэтле. Представление должно содержать имя и фамилию сотрудника, дату найма, зарплату, название отдела (считаем, что department_name - уникальное поле) и допускать операции переименования сотрудников, изменения зарплаты сотрудников, перевод сотрудника в другой отдел, если он имеет отделение в Сиэтле. Должно быть возможно добавление новых сотрудников в отделы, расположенные в Сиэтле посредством данного представления, а также удаление сотрудников из отделов в Сиэтле, если их выслуга лет меньше 15. Запретить изменение первичного ключа записи (необязательное задание).
-- Материализованные представления
18) Дописать триггеры, необходимые для работы материализованного представления из материалов к лекциям.
-- Создание пользовательских бэкапов
19) Создание задания для ежедневного создания дампа БД
-- Обновление СУБД
20) Обновление СУБД до 9.2.x
-- Point-in-time recovery
21) Настроить бэкап PostgreSQL на основе архивирования журналов ( в том числе, удаление старых журналов и базовых бэкапов). Провести восстановление из бэкапа.
-- Replication
22) Настроить асинхронную Streaming replication между двумя серверами, задублировать ее архивом журналов, проверить работу.  Переклчюить в синхронный режим. Провести тестирование производительности в разных режимах работы с помощью pgbench. (Необязательное задание).