У нас, как и во многих других организациях, проводится тестирование соискателей при поступлении их на работу. Основу тестирования составляет устное собеседование, но в некоторых случаях, даются также практические задания. Несколько дней назад, Руководство попросило меня подготовить набор задач на знание 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
Разумеется, опубликованные здесь ответы не являются единственно верными. В случае, если запрос соискателя не содержит явных ошибок, результаты его выполнения (для различных наборов исходных данных) сравниваются с результатами выполнения соответствующего эталонного запроса.