У нас, как и во многих других организациях, проводится тестирование соискателей при поступлении их на работу. Основу тестирования составляет устное собеседование, но в некоторых случаях, даются также практические задания. Несколько дней назад, Руководство попросило меня подготовить набор задач на знание SQL.

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

Также, не имело смысла давать задания на знание каких-либо особенностей тех или иных СУБД. Мы в работе используем Oracle, но это не должно создавать трудностей для соискателей знающих, например, только MS SQL или PostgreSQL. Таким-образом, использование платформо-зависимых решений не возбраняется, но и не является ожидаемым при решении задач.

Для проведения тестирования, в Oracle 11g была развернута схема, содержащая следующие таблицы:



Требовалось составить SQL-запросы, для решения следующих пяти заданий:

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

Вариант ответа
select a.*
from   employee a, employee b
where  b.id = a.chief_id
and    a.salary > b.salary



Задание 2
Вывести список сотрудников, получающих максимальную заработную плату в своем отделе

Вариант ответа
select a.*
from   employee a
where  a.salary = ( select max(salary) from employee b
                    where  b.department_id = a.department_id )



Задание 3
Вывести список ID отделов, количество сотрудников в которых не превышает 3 человек

Вариант ответа
select department_id
from   employee
group  by department_id
having count(*) <= 3



Задание 4
Вывести список сотрудников, не имеющих назначенного руководителя, работающего в том-же отделе

Вариант ответа
select a.*
from   employee a
left   join employee b on (b.id = a.chief_id and b.department_id = a.department_id)
where  b.id is null



Задание 5
Найти список ID отделов с максимальной суммарной зарплатой сотрудников

Вариант ответа
with sum_salary as
  ( select department_id, sum(salary) salary
    from   employee
    group  by department_id )
select department_id
from   sum_salary a       
where  a.salary = ( select max(salary) from sum_salary )



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

Дополнительное задание
Составить SQL-запрос, вычисляющий произведение вещественных значений, содержащихся в некотором столбце таблицы

Вариант ответа
select
  exp(sum(ln(decode(sign(salary),0,1,-1,-salary,salary))))
 *decode(mod(sum(decode(sign(salary),-1,1,0)),2),1,-1,1)
 *sign(min(abs(salary)))
from employee



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