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