Всего за 239.9 руб. Купить полную версию
Размер премии может зависеть как от отдела, в котором работает сотрудник, так и от его должности. Для решения этой задачи необходимо использовать вложенные выражения CASE.
Пример 3.50. Вывести данные о сотрудниках и размере их премии, которая зависит как от отдела, где работает сотрудник, так и от его должности.
Выражение CASE с условием имеет следующий синтаксис:
CASE
WHEN {условие1} THEN {результат1}
[WHEN {условие2} THEN {результат2}
WHEN {условиеN} THEN {результатN}]
[ELSE {результат_ELSE}]
END
При использовании этой разновидности оператора CASE последовательно поверяются значения условных выражений в предложениях WHEN и возвращается результат из первого предложения, в котором это выражение будет иметь значение TRUE.
Пример 3.51. Вывести данные о сотрудниках и размере их премии, которая зависит от зарплаты сотрудника
Пример 3.52. Вывести данные о сотрудниках и размере их премии, которая зависит от количества лет, которые проработал сотрудник
Функция DECODE
По своему назначению функция DECODE аналогична условному выражению CASE, но не поддерживается стандартом ANSI/ISO SQL. Синтаксис:
DECODE ({столбец} | {выражение}
{, {значение 1}, {результат 1}
[, {значение 2}, {результат2}
[, {значение N}, {результат N}]
[, {результат default}]);
Значение {столбец} | {выражение} сравнивается со значениями {значение i} и возвращается результат первого совпадения.
Если совпадения не будет, то возвращается значение {результат default}. Если {результат default} отсутствует, то функция DECODE вернет результат NULL.
Следует обратить внимание на то, что функция DECODE требует точного совпадения значений и не позволяет использовать операции сравнения>, , которую нельзя использовать в DECODE. Но при решении этой задачи данное ограничение удается обойти. Обратите внимание на то, что число месяцев, которые проработал сотрудник, делится на 60, что соответствует пяти годам работы. Если целая часть результата равна пяти, то это означает что сотрудник проработал не менее 25, но не более 30. Последнее замечание означает, что запросы из примеров 3.53 и 3.55 не эквивалентны и запрос с использованием функции DECODE требует расширения списка значений.
Пример 3.54. Вывести данные о сотрудниках и размере их премии, которая зависит от количества лет, которые проработал сотрудник, используя функцию DECODE
SELECT department_id, employee_id, first_name, last_name, job_id,
hire_date, salary,
DECODE (TRUNC (MONTHS_BETWEEN (SYSDATE, hire_date) /60),
6,3*salary,
5,3*salary,
4, 2*salary,
3, salary,
0.5*salary) As bonus
FROM Employees
WHERE department_id IN (10,30,40,60)
ORDER BY department_id;
Задачи для самостоятельного решения
2. Вывести значения столбцов employee_id, first_name, last_name, salary и ту часть зарплаты сотрудника, которая меньше 1000.
3. Создать запрос, который вернет столбец name_and_salaries. Столбец должен содержать полное имя сотрудника, зарплату и несколько звездочек (*) по одной звездочке на каждые $1000 зарплаты.
4. Вывести данные о товарах, название которых содержит слово AMD и не содержит слова RYZEN.
5. Вывести названия товаров, второе слово которых состоит из шести букв.
6. Вывести данные о товарах, второе слово в названии которых iPhone.
7. Вывести данные о сотрудниках, которые были приняты на работу в понедельник.
8. Вывести данные о сотрудниках, которые были приняты на работу 21 апреля.
9. Для сотрудников, работающих в отделе 50, вывести разницу между текущей датой и датой приема на работу в формате: УУ лет ММ месяцев ДД дней.
10. Вывести значения столбцов employee_id, first_name,
last_name, salary и премию, которую они должны получить. Размер премии у сотрудников, которые получают комиссионные, равен зарплате с учетом комиссионных. Размер премии у сотрудников, которые не получают комиссионные, равен зарплате, увеличенной на 30%.
11. Вывести значения столбцов employee_id, first_name, last_name, salary и bonus премию, которую они должны получить. Размер премии зависит от рейтинга и вычисляется по следующему правилу:
если рейтинг сотрудника равен 5, то bonus = salary * 1.5;
если рейтинг сотрудника равен 4, то bonus = salary * 1.3;
если рейтинг сотрудника равен 3, то bonus = salary * 1.1;
сотрудникам, рейтинг которых меньше 3, премия не полагается.
12. Вывести значения столбцов employee_id, first_name, last_name, salary и category. Значение категории (category) определяется по следующему правилу:
если rating_e 4 и salary 10 000, то category = High;
если rating_e 10000
GROUP BY job_id
HAVING COUNT (*)> 1
ORDER BY num_job DESC;
Пример 4.15. Вывести номера отделов, у которых число сотрудников, имеющих рейтинг 5, больше одного, количество сотрудников, имеющих рейтинг 5, и их суммарную зарплату
SELECT department_id, rating_e, count (*),sum (salary)
FROM Employees
WHERE rating_e = 5
GROUP BY department_id, rating_e
HAVING count (*)> 1
ORDER BY department_id;
Использование вложенных агрегатных функций
Пример 4.16. Определить количество сотрудников в каждом отделе
SELECT department_id, count (*)
FROM Employees
GROUP BY department_id
ORDER BY department_id;
Используя