Тест Глобал: Практика 1.1 Упражнения по языку sql. | Югорский государственный университет

Дать ответ на задание

Ответ на задание необходимо предоставить в виде текстоыфх файлов. Скриншоты интерфейса приложения для заданий выполняемых в интерфейсе и для демонстрации работы кода в системе Global ERP и других инструментах разработчика следует отправить в формате PDF, PNG или JPG. Для удобства рекомендуется прислать все файлы в одном архиве.

В ответе на задание должны содержаться элементы, обеспечивающие работоспособность описанного в задании функционала.

Просмотр учебной схемы

В навигаторе базы данных (см. пункт меню Windows > Database Navigator):

Перейдите к учебной схеме exercises \ Databases \ exercises \ Schemas \ cd
В контекстном меню выберите View schema
В открывшимся окне перейдите на закладку ER diagram

Выполнение запроса

Создайте новый запрос
Для этого выберите пункт меню Sql Editor > New Sql Script. При этом откроется окно для ввода sql
Установите активное соединение exercises
Для этого выберите пункт меню Sql Editor > Set Active Connection
В окне ввода sql введите
```
select * from cd.facilities;  
```
Выполните sql
Для этого выберите пункт меню Sql Editor > Execute Sql Statement
Посмотрите план запроса
Для этого выберите пункт меню Sql Editor > Explain Execution Plan

Совет:
Понимание плана запроса позволяет принять решение о необходимости дальнейшей оптимизации. Для более подробной информации смотрите оптимизация производительности в документации PostgreSql

Результат:

|facid|name           |membercost|guestcost|initialoutlay|monthlymaintenance|
|-----|---------------|----------|---------|-------------|------------------|
|0    |Tennis Court 1 |5         |25       |10,000       |200               |
|1    |Tennis Court 2 |5         |25       |8,000        |200               |
|2    |Badminton Court|0         |15.5     |4,000        |50                |
|3    |Table Tennis   |0         |5        |320          |10                |
|4    |Massage Room 1 |35        |80       |4,000        |3,000             |
|5    |Massage Room 2 |35        |80       |4,000        |3,000             |
|6    |Squash Court   |3.5       |17.5     |5,000        |80                |
|7    |Snooker Table  |0         |5        |450          |15                |
|8    |Pool Table     |0         |5        |400          |15                |

Упражнения по языку sql

Простое соединение таблиц

Напишите запрос получения списка времени начала использования объекта для участников с именем и фамилией „David Farrell“

Совет
Смотрите соединённые таблицы в документации PostgreSql

Результат:

|starttime              |
|-----------------------|
|2012-09-18 09:00:00.000|
|2012-09-18 13:30:00.000|
|2012-09-18 17:30:00.000|
|2012-09-18 20:00:00.000|
|2012-09-19 09:30:00.000|
|2012-09-19 12:00:00.000|
|2012-09-19 15:00:00.000|
|2012-09-20 11:30:00.000|
|2012-09-20 14:00:00.000|
|2012-09-20 15:30:00.000|
|2012-09-21 10:30:00.000|
|2012-09-21 14:00:00.000|
|2012-09-22 08:30:00.000|
|2012-09-22 17:00:00.000|
|2012-09-23 08:30:00.000|
|2012-09-23 17:30:00.000|
|2012-09-23 19:00:00.000|
|2012-09-24 08:00:00.000|
|2012-09-24 12:30:00.000|
|2012-09-24 16:30:00.000|
|2012-09-25 15:30:00.000|
|2012-09-25 17:00:00.000|
|2012-09-26 13:00:00.000|
|2012-09-26 17:00:00.000|
|2012-09-27 08:00:00.000|
|2012-09-28 09:30:00.000|
|2012-09-28 11:30:00.000|
|2012-09-28 13:00:00.000|
|2012-09-29 10:30:00.000|
|2012-09-29 13:30:00.000|
|2012-09-29 14:30:00.000|
|2012-09-29 16:00:00.000|
|2012-09-29 17:30:00.000|
|2012-09-30 14:30:00.000|

Подзапросы

Создайте запрос, возвращающий список заказов на день 14.09.2012, которые будут стоить участнику (или гостю) более 30 у.е. Добавьте в вывод:

Имя объекта
Имя, фамилия участника
Информация должна быть выведена одной колонкой
Стоимость

Отсортируйте вывод по убыванию. Используйте подзапрос для выбора данных и запрос для сортировки, это позволит уменьшить текстовое дублирование.

Примечание
Гость имеет отличную от участника стоимость (Стоимость учитывается на 1 слот, который равняется получасу). Идентификатор гостевого пользователя всегда 0.

Совет
Смотрите:
- Подзапросы
- Функции в документации PostgreSql.
Для расчета стоимости используйте атрибуты:
- bookings.slots
- facilities.guestcost
- facilities.membercost

Результат:

|member         |facility      |cost|
|---------------|--------------|----|
|GUEST GUEST    |Massage Room 2|320 |
|GUEST GUEST    |Massage Room 1|160 |
|GUEST GUEST    |Massage Room 1|160 |
|GUEST GUEST    |Massage Room 1|160 |
|GUEST GUEST    |Tennis Court 2|150 |
|Jemima Farrell |Massage Room 1|140 |
|GUEST GUEST    |Tennis Court 1|75  |
|GUEST GUEST    |Tennis Court 2|75  |
|GUEST GUEST    |Tennis Court 1|75  |
|Matthew Genting|Massage Room 1|70  |
|Florence Bader |Massage Room 2|70  |
|GUEST GUEST    |Squash Court  |70  |
|Jemima Farrell |Massage Room 1|70  |
|Ponder Stibbons|Massage Room 1|70  |
|Burton Tracy   |Massage Room 1|70  |
|Jack Smith     |Massage Room 1|70  |
|GUEST GUEST    |Squash Court  |35  |
|GUEST GUEST    |Squash Court  |35  |

Агрегация

Создайте запрос, возвращающий список количества слотов, заказанных каждый месяц в 2012. Колонки списка:

Идентификатор объекта
Номер месяца
Количество слотов

Отсортируйте результат по идентификатору и номеру месяца.

Совет
Смотрите:
- Группировку строк
- Функции для работы с датами в документации PostgreSql.

Результат:

|facid|month|Total Slots|
|-----|-----|-----------|
|0    |7    |270        |
|0    |8    |459        |
|0    |9    |591        |
|1    |7    |207        |
|1    |8    |483        |
|1    |9    |588        |
|2    |7    |180        |
|2    |8    |459        |
|2    |9    |570        |
|3    |7    |104        |
|3    |8    |304        |
|3    |9    |422        |
|4    |7    |264        |
|4    |8    |492        |
|4    |9    |648        |
|5    |7    |24         |
|5    |8    |82         |
|5    |9    |122        |
|6    |7    |164        |
|6    |8    |400        |
|6    |9    |540        |
|7    |7    |156        |
|7    |8    |326        |
|7    |9    |426        |
|8    |7    |117        |
|8    |8    |322        |
|8    |9    |471        |

Оконные функции

Найдите три лучших объекта, приносящих доход

Совет
Смотрите оконные функции в документации PostgreSql

Результат:

|name          |rank|
|--------------|----|
|Massage Room 1|1   |
|Massage Room 2|2   |
|Tennis Court 2|3   |

Рекурсия

Найдите восходящую цепочку рекомендаций для участника(A) с идентификатором 27 : то есть участника(B), который рекомендовал участника A и участника(C) который рекомендовал участника (B) и так далее.

Верните идентификатор участника, имя и фамилию. Порядок по убыванию идентификатора участника.

Совет
Смотрите рекурсивные запросы в документации PostgreSql

Результат:

|recommender|firstname|surname|
|-----------|---------|-------|
|20         |Matthew  |Genting|
|5          |Gerald   |Butters|
|1          |Darren   |Smith  |

Json

Напишите запрос, возвращающий одну строку (используя агрегацию), содержащую json массив для первых 5 записей по членам клуба. Записи в массиве должны быть отсортированы по идентификатору члена клуба. Указывайте формат даты явным образом(to_char(joindate, 'DD.MM.YYYY HH24:MI:SS')), чтобы избежать зависимостей от настроек базы данных.

Совет
Смотрите:

Функции по работе с json в документации PostgreSql.

{"members":[
   {"memid":0,"surname":"GUEST","firstname":"GUEST","address":"GUEST","zipcode":0,"telephone":"(000) 000-0000","recommendedby":null,"joindate":"01.07.2012 00:00:00"}, 
   {"memid":1,"surname":"Smith","firstname":"Darren","address":"8 Bloomsbury Close, Boston","zipcode":4321,"telephone":"555-555-5555","recommendedby":null,"joindate":"02.07.2012 12:02:05"}, 
   {"memid":2,"surname":"Smith","firstname":"Tracy","address":"8 Bloomsbury Close, New York","zipcode":4321,"telephone":"555-555-5555","recommendedby":null,"joindate":"02.07.2012 12:08:23"}, 
   {"memid":3,"surname":"Rownam","firstname":"Tim","address":"23 Highway Way, Boston","zipcode":23423,"telephone":"(844) 693-0723","recommendedby":null,"joindate":"03.07.2012 09:32:15"}, 
   {"memid":4,"surname":"Joplette","firstname":"Janice","address":"20 Crossing Road, New York","zipcode":234,"telephone":"(833) 942-4710","recommendedby":1,"joindate":"03.07.2012 10:25:05"}
]}