Одна задачка на SQL

В феврале 2013 года мне посчастливилось участвовать в жюри региональных финалов олимпиады Oracle ИТ-Планета

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

Итак, одно из заданий было сформулировано так:

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

Сведения о сотрудниках, для которых неизвестно к какому подразделению они приписаны, выводить не нужно.

В результат вывести 5 (пять) столбцов:
1. Идентификатор сотрудника
2. Фамилию сотрудника
3. Имя сотрудника
4. Оклад, установленный сотруднику
5. Идентификатор подразделения, к которому приписан сотрудник

Результат отсортировать:
1. По окладу, установленный сотруднику (по убыванию)
2. По фамилии сотрудника (по возрастанию)
3. По имени сотрудника (по возрастанию)
4. По идентификатору сотрудника (по возрастанию)

Решением задачи является запрос:


SELECT employee_id, last_name, first_name, salary, department_id
FROM employees E
WHERE salary > (SELECT AVG(salary) FROM employees X
WHERE E.department_id = x.department_id)
ORDER BY salary DESC, last_name, first_name, employee_id;

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

Continue reading “Одна задачка на SQL”

Advertisements
Одна задачка на SQL