Обычно в этой рубрике мы публикуем задачи от компаний, но оказалось, что среди наших читателей есть специалисты, готовые пощекотать мозги задачкорешателей журнала хорошей порцией квестов собственного разлива. Нельзя сказать, что эти задачи dbms_photoshop сварил для личного пользования, — когда-то они пригодились ему для реального отбора специалистов. Впрочем, не буду перехватывать инициативу — передаю слово автору!

Задачи были использованы для отбора специалистов по Oracle, но с минимальными изменениями могут быть приспособлены для MS SQL Server и некоторых других СУБД. Изначально никаких задач не было и все кандидаты, резюме которых прошло предварительную фильтрацию, приглашались в офис. Потом, чтобы уменьшить затраты времени, было решено проводить pre-screening-интервью. Однако затем и этот подход был изменен, так как если человек не имеет базовых знаний, то продолжать беседу бессмысленно, но и заканчивать диалог после двух минут разговора весьма бестактно.

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

Акцент при решении делался прежде всего на знание возможностей языка SQL, поэтому все запросы должны быть написаны на чистом SQL без использования model clause или каких-либо встроенных не SQL-функций (например, вызов функций PL/SQL или использование фич XML).

Ждите ответов в следующем номере!

Правильные ответы dbms_photoshop опубликует через месяц, и именно тогда ты сможешь проверить, прошел бы ты строгий отбор на собеседование в неизвестную, но очень приличную компанию 😉

 

Задача 1

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

Результат для узла 1
Результат для узла 1
SQL> with graph as
  2  (select 1 id, 2 parent_id from dual
  3  union all select 1 id, 3 parent_id from dual
  4  union all select 4 id, 3 parent_id from dual
  5  union all select 5 id, 4 parent_id from dual
  6  union all select 6 id, 5 parent_id from dual
  7  union all select 7 id, 6 parent_id from dual
  8  union all select 8 id, 7 parent_id from dual
  9  union all select 7 id, 6 parent_id from dual
 10  union all select 8 id, 10 parent_id from dual
 11  union all select 9 id, 9 parent_id from dual
 12  union all select 12 id, 11 parent_id from dual)
...
 20  /

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
        10

9 rows selected
 

Задача 2

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

SQL> with log as
  2  (select 'U1' username, date '2013-08-08'+1/24 logon_time,
  3   date '2013-08-08'+10/24 logoff_time from dual
  4  union all select 'U1' username, date '2013-08-08'+6/24 logon_time,
  5   date '2013-08-08'+14/24 logoff_time from dual
  6  union all select 'U1' username, date '2013-08-08'+4/24 logon_time,
  7   date '2013-08-08'+12/24 logoff_time from dual
  8  union all select 'U1' username, date '2013-08-08'+8/24 logon_time,
  9   date '2013-08-08'+17/24 logoff_time from dual
 10  union all select 'U1' username, date '2013-08-08'+16/24 logon_time,
 11   date '2013-08-08'+18/24 logoff_time from dual
 12  union all select 'U1' username, date '2013-08-08'+9/24 logon_time,
 13   date '2013-08-08'+16/24 logoff_time from dual
 14  union all select 'U2' username, date '2013-08-08'+1/24 logon_time,
 15   date '2013-08-08'+3/24 logoff_time from dual
 16  union all select 'U2' username, date '2013-08-08'+2/24 logon_time,
 17   date '2013-08-08'+12/24 logoff_time from dual
 18  union all select 'U2' username, date '2013-08-08'+11/24 logon_time,
 19   date '2013-08-08'+13/24 logoff_time from dual
 20  union all select 'U2' username, date '2013-08-08'+10/24 logon_time,
 21   date '2013-08-08'+14/24 logoff_time from dual)
...
 37  /

US CNT_SESSIONS TIME
-- ------------ -----------------
U1            5 08.08.13 09.00.00
U2            3 08.08.13 11.00.00

Число пользователей может быть любым, интервалы не обязательно с дискретностью «час», как для примера выше.

 

Задача 3

Написать запрос, генерирующий первые n чисел Фибоначчи. Использование любых формул не допускается. Число для генерации должно быть указано в связываемой переменной.

SQL> ...
  2  < :b)
  3  select lvl, result from fib
  4  /

       LVL     RESULT
---------- ----------
         1          0
         2          1
         3          1
         4          2
         5          3
         6          5
         7          8
         8         13
         9         21
        10         34

10 rows selected
 

Задача 4

Для данных таблиц master и detail tables написать запрос, который вернет сумму value с группировкой по grp, а также общую сумму в первой строке. Допускается только одно сканирование таблицы master.

SQL> with
  2  master as
  3  (select 1 as id_m, 111 as value  from dual union all
  4  select 2 as id_m, 222 as value  from dual union all
  5  select 3 as id_m, 333 as value  from dual union all
  6  select 4 as id_m, 444 as value  from dual union all
  7  select 5 as id_m, 555 as value  from dual union all
  8  select 6 as id_m, 666 as value  from dual),
  9  detail as
 10  (select 1 as id_m, 1 as grp from dual union all
 11  select 1 as id_m, 2 as grp from dual union all
 12  select 1 as id_m, 4 as grp from dual union all
 13  select 2 as id_m, 3 as grp from dual union all
 14  select 2 as id_m, 4 as grp from dual union all
 15  select 3 as id_m, 1 as grp from dual union all
 16  select 3 as id_m, 3 as grp from dual union all
 17  select 3 as id_m, 5 as grp from dual)
...
 24  /

       GRP     ALLSUM
---------- ----------
                 2331
         1        444
         2        111
         3        555
         4        333
         5        333

6 rows selected
 

Задача 5

Таблица содержит узлы для нескольких деревьев. Написать запрос, который вернет узлы с признаком signed, а также их родителей на первых трех уровнях. Connect by может быть использовано только однократно.

SQL> with tree as
  2  (select 3 id, 1 parent_id, 0 sign from dual
  3  union all select 4 id, 2 parent_id, 0 sign from dual
  4  union all select 5 id, 2 parent_id, 0 sign from dual
  5  union all select 6 id, 3 parent_id, 0 sign from dual
  6  union all select 7 id, 3 parent_id, 0 sign from dual
  7  union all select 8 id, 3 parent_id, 0 sign from dual
  8  union all select 9 id, 4 parent_id, 0 sign from dual
  9  union all select 10 id, 4 parent_id, 1 sign from dual
 10  union all select 11 id, 7 parent_id, 1 sign from dual
 11  union all select 12 id, 7 parent_id, 0 sign from dual
 12  union all select 13 id, 9 parent_id, 0 sign from dual
 13  union all select 14 id, 9 parent_id, 1 sign from dual
 14  union all select 15 id, 9 parent_id, 1 sign from dual
 15  union all select 2 id, null parent_id, 0 sign from dual
 16  union all select 1 id, null parent_id, 0 sign from dual)
...
 30  /

SIGNED_NODE    ID_LVL1    ID_LVL2    ID_LVL3
----------- ---------- ---------- ----------
         10          2          4         10
         11          1          3          7
         14          2          4          9
         15          2          4          9

4 rows selected.

parent_id может быть не обязательно null для корней, как на примере выше.


IT-компании, шлите нам свои задачки!

Миссия этой мини-рубрики — образовательная, поэтому мы бесплатно публикуем качественные задачки, которые различные компании предлагают соискателям. Вы шлете задачки на lozovsky@glc.ru — мы их публикуем. Никаких актов, договоров, экспертиз и отчетностей. Читателям — задачки, решателям — подарки, вам — респект от нашей многосоттысячной аудитории, пиарщикам — строчки отчетности по публикациям в топовом компьютерном журнале.

5 комментариев

Подпишитесь на ][, чтобы участвовать в обсуждении

Обсуждение этой статьи доступно только нашим подписчикам. Вы можете войти в свой аккаунт или зарегистрироваться и оплатить подписку, чтобы свободно участвовать в обсуждении.

Check Also

Скрытая сила пробела. Эксплуатируем критическую уязвимость в Apache Tomcat

В этой статье мы поговорим о баге в Apache Tomcat, популярнейшем веб-сервере для сайтов на…