Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Тестовое задание на PL/SQL  [new]
Kamino
Member

Откуда:
Сообщений: 1
Здравствуйте. Дали тестовое задание на PL/SQl. Есть вопросы и моменты, которые я не могу понять. Проверить свой код, мне негде.

Дана База данных(приложенный файл)
https://ibb.co/xqr6QYp
Задания такие

Начальный уровень
1. Даты регистрации
Вывести количество контрактов, зарегистрированных в системе за каждый день с 01.01.18 по 01.09.18.

Тут у меня можеть быть проблема с датами в PL/SQL
SELECT DT_REG, COUNT(DT_REG)
FROM  CONTRACTS
WHERE DT_REG >= TO_DATE(‘ 01.01.18, ‘DD.MM.YY’) AND DT_REG <= TO_DATE(‘ 01.09.18, ‘DD.MM.YY’)
GROUP BY DT_REG

2. Суммарный счёт
Вывести сумму всех счетов по контракту с кодом 478192, выставленных в мае 2018 года.

Тоже проблема с датами может быть
SELECT SUM(F_SUM)
FROM  BILLS
WHERE ID_CONTRACT_INST = 478192 AND DT_EVENT = TO_DATE(‘ 05.18, ‘MM.YY’)



3. Совпадение?
Вывести те контракты, для которых номер контракта начинается с кода этого же контракта. Учесть, что V_EXT_IDENT является текстовым полем.

Тут я не знаю, как грамотно переводить один формат в другой, и как написать запрос на сравнение
SELECT ID_CONTRACT_INST
FROM CONTRACTS
WHERE V_EXIT_IDENT LIKE


4. Отчёт по статусам
Вывести количество контрактов для каждого значения статуса контракта из списка: A - активен, B - заблокирован, C - расторгнут.
Результат: статус, «словесное» наименование статуса, количество контрактов.

Я правильно понимаю, что бы вывести словесное наименование статуса, нам нужно создать новую таблицу?
SELECT V_STATUS, COUNT(V_STATUS)
FROM CONTRACTS
GROUP BY V_STATUS



Продвинутый уровень

1. «Пустые» филиалы
Вывести наименования филиалов, в которых нет ни одного активного контракта.

Тут я просто вычитаю из всех контрактов активные контракты, возможно все можно сделать гораздо проще.
SELECT DISTINCT V_NAME
FROM DEPARTMENTS, CONTRACTS
WHERE ID_DEPARTMENT.DEPARTMENTS = ID.DEPARTMENT.CONTRACTS
EXCEPT ALL
SELECT DISTINCT V_NAME
FROM DEPARTMENTS, CONTRACTS
WHERE ID_DEPARTMENT.DEPARTMENTS = ID.DEPARTMENT.CONTRACTS
WHERE  V_STATUS = ‘A’



2. Наиболее популярные ТП
Вывести наименования тарифных планов для топ-5 самых популярных услуг.
Результат: наименование тарифного плана, количество услуг с таким тарифным планом.
SELECT V_NAME, C
FROM( SELECT ID_SERVICE, V_NAME,COUNT(ID_SERVICE) AS C
FROM SERVICES, TARIFF_PLAN
WHERE ID_TARIFF_PLAN.SERVICES = ID_TARIFF_PLAN.TARIFF_PLAN
GROUP BY ID_SERVICE
ORDER BY C DESC)
WHERE ROWNUM <=5
3. Уникальные услуги в рамках филиалов
Вывести наименования услуг, которые являются уникальными в рамках филиалов, т.е. таких услуг, которые есть только в конкретном филиале и ни в каком другом.
Результат: наименование услуги, код филиала.

Тут я смог соеденить только код филиала, и код услуги. Дальше не могу сообразить.
SELECT ID_SERVICE, ID_CONTRACT_INST.SERVICES, ID_DEPARTMENT
FROM SERVICES, CONTRACTS
WHERE ID_CONTRACT_INST.CONTRACTS = ID_CONTRACT_INST.SERVICES

4. Ошибка при выставлении счетов
Оператор Андрей поторопился и выставил счета по контрактам, у которых сумма всех ранее выставленных счетов > 30 000 рублей, на несколько дней раньше положенного срока. Напишите запрос, который выведет коды счетов, ошибочно выставленных в текущем месяце Андреем, зная, что код пользователя Андрея = 2021. Следует учесть, что в текущем месяце все счета, выставленные Андреем, ошибочные.

Тут у меня вообще нет идей, как что то сделать.
5. Рекомендательная система
Дмитрий занимается разработкой рекомендательных систем, и ему нужно знать контракты, у которых количество услуг на начало каждого месяца только уменьшалось в течение последних 6 месяцев. Помогите написать запрос, который выводит номера таких контрактов.

Тут тоже нет идей.

Сообщение было отредактировано: 15 мар 20, 14:14
15 мар 20, 14:11    [22099350]     Ответить | Цитировать Сообщить модератору
 Re: Тестовое задание на PL/SQL  [new]
-2-
Member

Откуда:
Сообщений: 15330
Kamino
Проверить свой код, мне негде.
livesql.oracle.com
15 мар 20, 16:28    [22099424]     Ответить | Цитировать Сообщить модератору
 Re: Тестовое задание на PL/SQL  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18065
Тестовое задание по сути своей должно позволить определить степень владения предметом.
Потому ответов не будет.
Тем не менее:
Kamino
Тут у меня можеть быть проблема с датами в PL/SQL
SELECT DT_REG, COUNT(DT_REG)
FROM  CONTRACTS
WHERE DT_REG >= TO_DATE(‘ 01.01.18, ‘DD.MM.YY’) AND DT_REG <= TO_DATE(‘ 01.09.18, ‘DD.MM.YY’)
GROUP BY DT_REG


1. По возможности стоит избегать "двузначных" значений года.
Если это по каким-то (непонятным пока) причинам невозможно - ознакомьтесь внимательно с масками 'YY' и 'RR'

+
SQL> select TO_DATE('01.01.50', 'DD.MM.YY')
  2       , TO_DATE('01.01.50', 'DD.MM.RR')
  3  from dual
  4  ;
TO_DATE('01.01.50','DD.MM.YY') TO_DATE('01.01.50','DD.MM.RR')
------------------------------ ------------------------------
01.01.2050                     01.01.1950

SQL> 


2. GROUP BY DT_REG - тут следует иметь ввиду, что тип date в oracle включает компоненту времени.
Т.е. фактически группировать Вы будете по секундам, хотя и не увидите проблемы, если даты сохранены до суток.
Честным решением данной задачи будет группировка по TRUNC(DT_REG,'DD')


Kamino

Тоже проблема с датами может быть
AND DT_EVENT = TO_DATE(‘ 05.18, ‘MM.YY’)


Точно есть.
Прочитайте описание на to_date.
Прочитайте про оператор between.

Kamino

Тут я не знаю, как грамотно переводить один формат в другой, и как написать запрос на сравнение
SELECT ID_CONTRACT_INST
FROM CONTRACTS
WHERE V_EXIT_IDENT LIKE


Ознакомьтесь с to_char и форматами для чисел.
Ознакомьтесь с операцией конкатенации "||"
Ознакомьтесь с синтаксисом оператора like и спец. символами '_', '%'.

Kamino

4. Отчёт по статусам
Вывести количество контрактов для каждого значения статуса контракта из списка: A - активен, B - заблокирован, C - расторгнут.
Результат: статус, «словесное» наименование статуса, количество контрактов.

Я правильно понимаю, что бы вывести словесное наименование статуса, нам нужно создать новую таблицу?

Не обязательно. Ознакомьтесь с CASE expression и DECODE - задача решается обоими способами.

Kamino


Продвинутый уровень

1. «Пустые» филиалы
Вывести наименования филиалов, в которых нет ни одного активного контракта.



Тут поможет конструкция NOT EXISTS

Kamino

2. Наиболее популярные ТП
Вывести наименования тарифных планов для топ-5 самых популярных услуг.

Метод с динамическим представлением и rownum на современных версиях может быть заменен конструкцией
ORDER BY Fetch First <N> Rows Only

Кроме того, убедитесь, что services - это именно использование услуг клиентами, а не справочник услуг тарифного плана.

Kamino

3. Уникальные услуги в рамках филиалов
Тут я смог соеденить только код филиала, и код услуги. Дальше не могу сообразить.

Предполагается конструкция Group by.. HAVING

Kamino
4. Ошибка при выставлении счетов
Тут у меня вообще нет идей, как что то сделать.
5. Рекомендательная система
Тут тоже нет идей.
На нет и суда нет.
Это оценка умений, а не просто "головоломка".
16 мар 20, 13:52    [22099879]     Ответить | Цитировать Сообщить модератору
 Re: Тестовое задание на PL/SQL  [new]
Elic
Member [заблокирован]

Откуда: 1984. Выбраковка финно-угром продолжается. КЯЗ
Сообщений: 29861
andrey_anonymous
Kamino
TO_DATE(‘ 01.01.18, ‘DD.MM.YY’)
1. По возможности стоит избегать "двузначных" значений года.
Если это по каким-то (непонятным пока) причинам невозможно - ознакомьтесь внимательно с масками 'YY' и 'RR'
А более правильный совет: 5916562
16 мар 20, 13:58    [22099892]     Ответить | Цитировать Сообщить модератору
 Re: Тестовое задание на PL/SQL  [new]
sygo88
Member

Откуда:
Сообщений: 2
Интересно, эти задания какому уровню pl/sql разработчика соответствуют? Jinor или ближе к middle?
16 июн 20, 18:20    [22151878]     Ответить | Цитировать Сообщить модератору
 Re: Тестовое задание на PL/SQL  [new]
legg
Member

Откуда: Москва
Сообщений: 6598
sygo88
Интересно, эти задания какому уровню pl/sql разработчика соответствуют? Jinor или ближе к middle?

Юниор из юниоров имхо. Тут вообще pl/sql нет. Только sql причем базовый. Никаких хитростей и тонкостей.
16 июн 20, 23:47    [22152068]     Ответить | Цитировать Сообщить модератору
 Re: Тестовое задание на PL/SQL  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18065
legg
Никаких хитростей и тонкостей.

№5?
16 июн 20, 23:56    [22152073]     Ответить | Цитировать Сообщить модератору
 Re: Тестовое задание на PL/SQL  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2468
andrey_anonymous
legg
Никаких хитростей и тонкостей.

№5?

можно решать формально, а можно "рекомендательно"

ноюнсы точно появятся

напр включать текущий месяц в "последние 6 месяцев", или нет.
учитывать влияние карантина, к-во дней в месяце и тд


.....
stax
17 июн 20, 09:09    [22152141]     Ответить | Цитировать Сообщить модератору
 Re: Тестовое задание на PL/SQL  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18065
Stax
напр включать текущий месяц в "последние 6 месяцев", или нет.

Подходы (особенно их разнообразие) к решению этой задачи позволяют соискателю продемонстрировать достаточно высокий уровень владения sql
17 июн 20, 10:51    [22152191]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить