Что является результатами выполнения операторов языка sql

(Квадратными скобками отмечены необязательные элементы.)

Дадим предварительное описание элементов данного оператора.

Оператор SELECT начинается со списка столбцов или выражений, значения которых будет отображаться в результате выполнения запроса. По умолчанию SELECT не исключает дублирование строк в результате выполнения запроса. Для исключения дублирования следует использовать ключевое слово DISTINCT.

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

Предложение WHERE содержит условия выбора строк, а также может содержать условия соединения таблиц в многотабличных запросах.

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

В предложении ORDER BY указываются имена столбцов, по значению которых следует упорядочить результат выполнения запроса. По умолчанию строки упорядочиваются в порядке возрастания значений столбца. Для сортировки в порядке убывания после имени столбца следует указать параметр DESC. Если указать несколько столбцов, то результат будет упорядочиваться сначала по значению первого столбца; строки, имеющие одинаковые значения первого столбца, упорядочиваются по значению второго столбца, и так далее.

В предложении GROUP BY можно указать столбцы, по которым следует осуществить группировку. Группировка состоит в том, что несколько строк, имеющих совпадающие значение столбцов, по которым осуществляется группировка, объединяются в одну строку. Обычно группировка используется в запросах, использующих агрегатные функции, например: Sum (), Max ().

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

При изучении SQL следует обратить внимание на то, что для формирования запроса необходимо:

— определить структуру запроса, соответствующую заданной задаче обработки данных;

— синтаксически правильно записать запрос.

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

В своей простейшей форме оператор SELECT должен включать в себя следующее:

— предложение SELECT, где указываются имена столбцов, значение которых будет отображаться в результате выполнения запроса;

— предложение FROM, в котором указывается имя таблицы, содержащей данные.

Пример 2.1. Вывод содержимого одного столбца

SELECT employee_id

FROM Employees;

Пример 2.2. Вывод содержимого нескольких столбцов

SELECT employee_id, first_name, last_name, department_id

FROM Employees;

Если в качестве результата выполнения запроса нужно вывести значения всех столбцов, то вместо списка столбцов указывается символ *.

Пример 2.3. Вывод значений всех столбцов

FROM Employees;

Исключение дублирования данных

Рассмотрим запрос, который выводит коды должностей сотрудников.

Пример 2.4. Вывод значений столбца job_id

SELECT job_id

FROM Employees;

Так как одну должность могут занимать несколько сотрудников, то коды должностей будут повторяться. Для того чтобы исключить повторения значений, следует добавить ключевое слово DISTINCT.

Пример 2.5 Вывод значений столбца job_id без дублирования

SELECT DISTINCT job_id

FROM Employees;

Для того чтобы выводить только те данные, которые удовлетворяют определенным условиям, оператор SELECT должен содержать предложение WHERE, которое содержит условное выражение.

Условное выражение для каждой строки таблицы может принимать значения: ИСТИНА (TRUE), ЛОЖЬ (FALSE), НЕ ОПРЕДЕЛЕНО (UNKNOWN). Результат выполнения запроса будет содержать только те строки, для которых условное выражение будет иметь значение ИСТИНА (TRUE).

Пример 2.6. Вывод данных о сотрудниках, зарплата которых больше 5000

SELECT employee_id, first_name, last_name, salary, department_id

FROM Employees

WHERE salary> 5000;

Пример 2.7. Вывод данных о сотрудниках, принятых на работу 20.08.1997

SELECT employee_id, first_name, last_name, salary, department_id

FROM Employees

WHERE hire_date = ′ 20.08.1997 ′;

В процессе выполнения этого оператора осуществляется неявное преобразование строки ′ 20.08.1997 ′ в формат Date. Для того чтобы это преобразование произошло без ошибок, содержимое строки, содержащую дату, должно быть совместимо с настройками Oracle SQL Developer (рисунок 1.21).

Для указаний условий выбора могут быть использованы операторы сравнения: =,>, <и логические операторы: NOT, AND, OR. Логические операторы используются для формирования сложных условий выбора и имеют разный приоритет. Сначала выполняются все операторы NOT, потом операторы AND; операторы OR выполняются в последнюю очередь. Для исключения возможных ошибок при формировании сложных запросов следует использовать скобки. Выражения внутри скобок выполняются первыми, слева направо.

Рассмотрим примеры запросов, использующих логические операторы при формировании условий выбора.

Пример 2.8. Вывод данных о сотрудниках, которые работают в отделе 50 и занимают должность ST_MAN

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE (department_id = 50) AND (job_id= ′ ST_MAN ′);

Пример 2.9. Вывод данных о договорах, заключенных сотрудником 150 с клиентом 49, совершенных в определенную дату (27.09.2017)

SELECT * FROM Orders

WHERE (salesman_id = 150) AND (customer_id=49)

AND (order_date = ′ 27.09.2017 ′);

Использование скобок при формировании условий выбора может существенным образом изменять логику выполнения запроса.

Пример 2.10. Вывод данных о договорах сотрудника 155, заключенных 15.03.2018 или 02.11.2019

SELECT * FROM Orders

WHERE (salesman_id = 155) AND (order_date = ′ 15.03.2018 ′

OR order_date = ′ 02.11.2019 ′);

Если в предложении WHERE скобки поставить так, как это показано в примере 2.11, то запрос будет иметь совсем другой смысл.

Пример 2.11. Вывод данных о договорах сотрудника 155, заключенных 15.03.2018, или обо всех договорах, заключенных 02.11.219

SELECT * FROM Orders

WHERE (salesman_id = 155) AND (order_date = ′ 15.03.2018 ′)

OR (order_date = ′ 02.11.2019 ′);

Специальные операторы

Для формирования условий выбора можно использовать специальные операторы, представленные в таблице 2.1.

Таблица 2.1. Специальные операторы

Рассмотрим примеры запросов, использующих специальные операторы.

Оператор LIKE

Оператор LIKE используется для работы со строками. Он проверяет, совпадает ли часть строки с заданным шаблоном. Для создания шаблонов в операторе LIKE используются следующие символы:

— символ подчеркивания _ обозначает один символ;

— символ процента % обозначает несколько символов.

Пример 2.12. Вывод данных о сотрудниках, имена которых начинаются на букву L

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE first_name LIKE ′ L% ′;

Пример 2.13. Вывод имен сотрудников, вторым символом которых является буква а

SELECT DISTINCT first_name

FROM Employees

WHERE first_name LIKE ′ _a% ′;

Пример 2.14. Вывод имен сотрудников, которые состоят из четырех символов, начинаются на букву J и заканчиваются буквой n

SELECT DISTINCT first_name

FROM Employees

WHERE first_name LIKE ′ J__n ′;

Для поиска в строке символов _ и % при построении шаблона используется опция ESCAPE /. Символ, который в шаблоне будет располагаться после /, будет рассматриваться как символ поиска. Вместо символа / можно использовать и другие символы, например!.

Пример 2.15. Вывести имя и адрес клиентов, столбец address которых содержит символ /

SELECT c_name, address

FROM Customers

WHERE address LIKE ′ %//% ′ ESCAPE ′ / ′;

Оператор BETWEEN

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

<имя столбца>BETWEEN V_MIN AND V_MAX

V_MIN — нижняя граница диапазона;

V_MAX — верхняя граница диапазона

Оператор BETWEEN осуществляет поиск среди всех значений диапазона, включая границы. Оператор BETWEEN эквивалентен двум операциям сравнения, объединенным логическим оператором AND.

Пример 2.16. Вывести данные о сотрудниках, зарплата которых находится в определенном диапазоне

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE salary BETWEEN 6000 AND 8000;

Для определения границ диапазона можно использовать вещественные числа, даты и строки.

Пример 2.17. Получить данные о сотрудниках, у которых значение комиссионных находится в определенном диапазоне

SELECT employee_id, first_name, last_name, department_id,

salary, commission_pct

FROM Employees

WHERE commission_pct BETWEEN 0.15 AND 0.2;

Пример 2.18. Получить данные о договорах, дата заключения которых лежит в определенном диапазоне

SELECT * FROM Orders

WHERE order_date BETWEEN ′ 01.09.2019 ′

AND ′ 30.09.2019 ′;

Оператор BETWEEN можно использовать совместно с логическим оператором NOT.

Пример 2.19. Получить данные о договорах, дата заключения которых не лежит в определенном диапазоне

SELECT * FROM Orders

WHERE order_date NOT BETWEEN ′ 01.09.2019 ′ AND ′ 30.09.2019 ′;

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

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

Пример 2.20. Получить данные о сотрудниках, имена которых начинаются с букв в диапазоне с A по B (содержит ошибку)

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE first_name BETWEEN ′ A ′ AND ′ B ′;

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

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

Пример 2.21. Получить данные о сотрудниках, имена которых начинаются с букв в диапазоне с A по B

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE first_name BETWEEN ′ A ′ AND ′ C ′;

Оператор IN

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

Список значений в операторе IN может формироваться в результате выполнения оператора SELECT (подзапроса).

Пример 2.22. Вывести данные о сотрудниках, которые работают в отделах с определенными номерами

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE department_id IN (40, 10, 110);

Пример 2.23. Вывести данные о договорах, заключенных в определенные даты

SELECT * FROM Orders

WHERE order_date IN (′ 07.09.19 ′, ′ 14.09.19 ′, ′ 02.11.19 ′);

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

Пример 2.24. Вывести данные о сотрудниках, которые не работают в отделах с определенными номерами

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE department_id NOT IN (40, 10, 110);

Условия выбора, формируемые оператором IN, можно объединять с другими условиями выбора.

Пример 2.25. Вывести названия городов, которые расположены в США или Канаде и почтовый индекс которых заканчивается цифрой 2

SELECT city FROM Locations

WHERE (country_id IN (′ US ′, ′ CA ′))

AND (postal_code LIKE ′ %2 ′);

Следует иметь в виду, что если список значений в IN будет содержать NULL, то результат выполнения оператора не будет содержать строк, у которых проверяемый столбец имеет значение NULL, так как результат сравнения NULL имеет значение НЕ ОПРЕДЕЛЕНО (UNKNOWN).

Пример 2.26. Вывести данные о сотрудниках, которые работают в отделах с определенными номерами, и о сотрудниках, у которых не задан номер отдела

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE department_id IN (40, 10, 110, NULL);

При этом в таблице Employees есть строки, у которых столбец department имеет значение NULL (см. результаты выполнения запроса из примера 2.29).

Если список значений в NOT IN будет содержать NULL, то результат выполнения оператора SELECT будет пуст. Это происходит, потому что оператор

X NOT IN (A1, A2, AN)

X <> A1 AND X <> A2 AND…X <> AN

Если одно из Ai будет NULL, то результат этого выражения будет иметь значение НЕ ОПРЕДЕЛЕНО (UNKNOWN).

Пример 2.27. Вывести данные о сотрудниках, которые не работают в отделах с определенными номерами

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME,

DEPARTMENT_ID

FROM EMPLOYEES

WHERE DEPARTMENT_ID NOT IN (30,50,60,80,90,100,NULL);

Результат выполнения этого запроса не будет содержать строк.

Оператор IS NULL

Оператор IS NULL используется для определения строк с неопределенным значением заданного столбца.

Данное выражение принимает значение TRUE, если значение проверяемого столба будет NULL.

Пример 2.28. Получить данные о сотрудниках, для которых неизвестен номер руководителя

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE manager_id IS NULL;

Пример 2.29. Вывести данные о сотрудниках, у которых не задан номер отдела

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE department_id IS NULL;

Можно также использовать разновидность данного оператора IS NOT NULL, который возвращает значение FALSE, если значение проверяемого столба будет NULL.

Пример 2.30. Получить данные о сотрудниках, для которых известен номер руководителя

SELECT employee_id, first_name, last_name, department_id

FROM Employees

WHERE manager_id IS NOT NULL;

Использование вычисляемых столбцов

В предложении SELECT, кроме списка столбцов таблиц участвующих в запросе, могут присутствовать вычисляемые столбцы, которые представляют собой выражения, состоящие из имен столбцов, констант, функций и арифметических операций. Значению вычисляемого поля можно присвоить имя. Для этого используется следующая конструкция:

При вычислении выражения, содержащего несколько арифметических операций, Oracle выполняет операции с более высоким приоритетом перед выполнением операций с более низким приоритетом.

Сначала выполняются операции умножения и деления, которые имеют одинаковый приоритет, потом сложения и вычитания, которые также относительно друг друга имеют одинаковый приоритет.

Если операции в выражении имеют одинаковый приоритет, то их выполнение производится слева направо.

Рассмотрим примеры использования вычисляемых столбцов. Если значение столбца commission_pct в таблице Employees обозначает надбавку к зарплате как часть заработной платы, то общая зарплата с учетом комиссионных может быть вычислена с использованием выражения:

SALARY * (1 + COMMISSION_pct) As Total_Salary

Следует иметь в виду то, что у некоторых сотрудников значение столбца commission_pct равно NULL. А если один из элементов выражения равен NULL, то и все выражение будет иметь значение NULL. Данную проблему можно решить, используя специальные функции, которые мы рассмотрим позже.

Пример 2.31. Вывести данные о размере комиссионных для сотрудников, которые получают комиссионные

SELECT employee_id, first_name, last_name, department_id,

commission_pct*salary as commission

FROM Employees

WHERE commission_pct IS NOT NULL;

Вычисляемые столбцы можно использовать в предложении WHERE.

Пример 2.32. Вывести данные о продажах товаров, в которых сумма одной покупки превышала 300 000

SELECT product_id, order_id, item_id, quantity, unit_price,

quantity*unit_price

FROM Order_items

WHERE quantity*unit_price> 300000;

Использование псевдостолбца ROWNUM

Значение псевдостолбца ROWNUM равно номеру записи, возвращаемой запросом. Используя этот столбец, можно ограничить число строк в результате выполнения запроса.

Пример 2.33. Вывести пять строк с данными о продажах товаров, в которых сумма одной покупки превышала 300 000

SELECT product_id, order_id, item_id, quantity, unit_price,

quantity*unit_price

FROM Order_items

WHERE quantity*unit_price> 300000

AND ROWNUM <=5;

Следует иметь в виду, что любой запрос, содержащий условие ROWNUM = N, где N> 1, будет пуст. Это происходит потому, что первая строка, возвращаемая запросом, имеет значение ROWNUM = 1, это значение не удовлетворяет условию ROWNUM = N и поэтому не попадает в результат выполнения запроса. После этого каждая следующая строка будет иметь значение ROWNUM = 1 и также не будет удовлетворять условию ROWNUM = N.

Оператор конкатенации (слияния) записывается двумя вертикальными чертами ( ) и используются для того, чтобы объединить при выводе данных два или несколько столбцов или литералов в один столбец.

Оператор конкатенации можно применять для строк, чисел и дат. Даты и числа при слиянии конвертируются в строковые значения. При слиянии строки значения со значением типа NULL Oracle возвращает строковое значение.

Пример 2.34. Вывести данные о заказах, оформленных сотрудником 165

SELECT ′ Order ′ order_id ′ from ′ order_date

′ is ′ status AS Order_Statys

FROM Orders

WHERE salesman_id =165;

Результат выполнения оператора SELECT может быть упорядочен по значению одного или нескольких столбцов. Для этого служит предложение ORDER BY, которое имеет следующий синтаксис:

Пример 2.35. Вывести данные о сотрудниках, упорядочив их в порядке убывания зарплаты

SELECT employee_id, first_name, last_name, department_id, salary

FROM Employees

ORDER BY salary DESC;

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

Пример 2.36. Вывести данные о сотрудниках, упорядочив их в порядке возрастания номеров отделов, в которых они работают. Данные о сотрудниках, которые работают в одном отделе, упорядочить в порядке убывания зарплаты

SELECT employee_id, first_name, last_name, department_id, salary

FROM employees

ORDER BY department_id, salary DESC;

В предложении ORDER BY можно использовать псевдонимы столбцов.

Пример 2.37. Вывести данные о сотрудниках, которые получают комиссионные, упорядочив их в порядке убывания суммы комиссионных

SELECT employee_id, first_name, last_name, department_id,

commission_pct*salary as commission

FROM Employees

WHERE commission_pct IS NOT NULL

ORDER BY commission DESC;

Можно сортировать строки по столбцам, не указанным в предложении SELECT.

Пример 2.38. Вывести данные о сотрудниках, которые работают в отделе 80, упорядочив их в порядке убывания рейтинга

SELECT employee_id, first_name, last_name, department_id, salary

FROM Employees

WHERE department_id = 80

ORDER BY rating_e;

Следует иметь в виду, что запрос с группировкой, содержащий условие ROWNUM ≤ N, не вернет первые N строк из всего набора записей, удовлетворяющих условиям запроса отсортированные по значениям определенного столбца. Это происходит потому, что сначала проверяется условие ROWNUM ≤ N, затем осуществляется сортировка. Это может стать причиной трудно обнаруживаемых ошибок. Для иллюстрации этого утверждения рассмотрим следующий пример.

Пример 2.39. Вывести пять строк с данными о продажах товаров с максимальными суммами (запрос содержит ошибку)

SELECT product_id, order_id, item_id, quantity, unit_price,

quantity*unit_price

FROM Order_Items

WHERE ROWNUM <=5

ORDER BY quantity*unit_price DESC;

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

Пример 2.40. Вывести пять строк с данными о продажах товаров с максимальными суммами

SELECT product_id, order_id, item_id, quantity, unit_price,

quantity*unit_price

FROM Order_Items

ORDER BY quantity*unit_price DESC

FETCH FIRST 5 ROWS ONLY;

Этот запрос содержит строку FETCH FIRST 5 ROWS ONLY, которая выбирает первые пять строк после сортировки. Этот оператор появился в Oracle 12.

Задачи для самостоятельного решения

1. Вывести данные о товарах, у которых столбец rating_p имеет значение 3 или 4, а price <1000.

2. Вывести first_name, last_name сотрудников, у которых first_name начинается на букву P и в last_name есть буква r.

3. Вывести значения столбцов employee_id, department_id, first_name, last_name, job_id, salary, department_id сотрудников, у которых зарплата salary> 9000 и работают в одном из отделов: 50, 80, 100.

4. Вывести содержимое столбца street_address в таблице Locations тех строк, у которых значение этого столбца начинается не с цифры.

5. Вывести first_name, last_name, job_id и суммарную зарплату за год в следующем виде:

Michael Hartstein занимает должность MK_MAN, и зарплата за год составляет 156 000.

6. Вывести значения столбцов employee_id, department_id, first_name, last_name, department_id, job_id, salary, bonus для сотрудников, у которых зарплата salary <10 000. Вычисляемый столбец bonus содержит размер премии, которая вычисляется по формуле: Salary * (1 +0.1 * rating_e). Выводимые данные упорядочить по размеру премии.

7. Вывести значения столбцов employee_id, department_id, first_name, last_name, job_id сотрудников, которые работают в отделах 50 или 80, но не являются менеджерами. Менеджерами являются те сотрудники, у которых столбец job_id содержит подстроку MAN.

8. Вывести значения столбцов employee_id, department_id, first_name, last_name, job_id, salary сотрудников, у которых код должности (job_id) имеет значение IT_PROG и зарплата имеет одно из значений (4800, 6000, 9000), а также о сотрудниках, у которых код должности (job_id) имеет значение SA_REP, а зарплата находится вне диапазона от 7000 до 9000.

Глава 3. Типы данных и встроенные функции

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

Типы данных можно разбить на три категории:

— типы даты и времени.

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

При изучении каждого типа будут приведены основные встроенные функции, аргументы которых могут иметь рассматриваемый тип.

Для вывода результатов выполнения выражений с использованием рассматриваемых функций мы будем использовать оператор SELECT. Но данный оператор должен обязательно содержать предложение:

В качестве источника данных мы будем использовать служебную таблицу DUAL, которая доступна для всех пользователей. Таблица DUAL имеет один столбец с именем DUMMY, тип данных которого VARCHAR2 (), и содержит одну строку со значением X.

Пример 3.1. Вывод содержимого таблицы DUAL

Числовые типы используются для работы с числовыми данными. Можно использовать следующие форматы чисел:

— вещественные числа в формате с фиксированной точкой;

— вещественные числа в формате с плавающей точкой.

При определении столбцов, содержащих числовые данные, можно использовать следующие спецификации:

NUMBER (n) — целое число не более n цифр;

NUMBER (n,m) — вещественное число с фиксированной точкой,

n — максимальное число цифр в записи числа а m — число цифр справа от десятичной точки;

NUMBER — вещественное число с плавающей точкой с точностью до 38 цифр.

В таблице 3.1 приведены основные функции, которые можно использовать при обработке данных числового типа.

Таблица 3.1. Основные функции для обработки чисел

Функция ROUND

Выполняет округление до ближайшего числа с заданной точностью (результат может быть не целым). Синтаксис:

n — численное значение;

m — точность округления.

Значение m может быть отрицательным, позиция округления отсчитывается влево.

Пример 3.2. Примеры использования функции ROUND

SELECT ROUND (147.76), ROUND (147.76,1), ROUND (147.76, — 1)

Пример 3.3. Вывести значение зарплаты, округленное до 1000

SELECT employee_id, first_name, last_name, department_id, salary, ROUND (salary, — 3)

FROM Employees;

Функция TRUNC

Усекает (отбрасывает) значащие цифры справа без округления. Синтаксис:

Параметры n, m аналогичны параметрам функции ROUND.

Пример 3.4. Пример использования функции TRUNC

SELECT TRUNC (147.76), TRUNC (147.76,1), TRUNC (147.76, — 1)

Функция MOD

Возвращает остаток от деления n на m. Синтаксис:

где n и m — численные значения целого или вещественного типа.

Пример 3.5. Пример использования функции MOD

SELECT mod (5,2), mod (6.5,1),mod (6.5,2.2)

Пример 3.6. Найти сотрудников с нечетным рейтингом

SELECT employee_id, first_name, last_name,

department_id, rating_e

FROM Employees

WHERE MOD (rating_e,2) =1;

Пример 3.7. Вывести ту часть зарплаты сотрудника, которая меньше 1000

SELECT employee_id, first_name, last_name, department_id,

salary, MOD (salary,1000)

FROM Employees;

Функция POWER

Возводит число x в степень n. Синтаксис:

x — численное значение;

n — степень, может иметь вещественный тип и отрицательное значение.

Пример 3.8. Пример использования функции POWER

SELECT POWER (2,2),POWER (9,0.5),POWER (10, — 1)

Функция SQRT

Возвращает квадратный корень от числа. Синтаксис:

x — численное значение;

Пример 3.9. Пример использования функции SQRT

SELECT SQRT (1), SQRT (9),SQRT (7)

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

CHAR (n) — строка символов фиксированной длины n, позволяет хранить символьные данные длиной от 1 до 2000 символов. Если длина (n) явно не указана, то она считается равной 1. Если длина присваиваемого значения будет меньше n, то оно дополняется пробелами справа.

VARCHAR2 (n) — строка символов переменной длины, которая может содержать не более n — 1 символа, предназначена для хранения символьных данных длиной от 1 до 4000 символов. Хранит столько символов, сколько содержит присваиваемое значение. Значение n трактуется как максимально возможная длина строки.

Типы NCHAR (n) и NVARCHAR2 (n) — предназначены для хранения символьных данных фиксированной и переменной длинны в формате Unicode.

Функции для обработки символьных данных

Эти функции принимают на вход строку символов, обрабатывают ее и возвращают результат обработки. Источником данных может быть: строковая константа, столбец таблицы, выражение. Все функции для обработки символьных данных можно разбить на две группы:

— функции преобразования регистра;

— функции обработки строк.

Таблица 3.2. Функции преобразования регистра

Пример 3.10. Вывести название товара, используя различные функции преобразования регистра

UPPER (Product_name) As UPPER,

LOWER (Product_name) As LOWER,

INITCAP (Product_name) As INITCAP

FROM Products

WHERE product_id = 50;

Довольно часто столбец, имеющий символьный тип, содержит значения в различных регистрах. Например, столбец first_name может содержать как значение DAVID, так и значение David. В этом случае запрос, содержащий условие выбора: first_name = ′DAVID′ или first_name = ′David′ выведет только часть необходимых данных. Эту проблему можно решить, используя функции преобразования регистра.

Пример 3.11. Вывести данные о сотрудниках, у которых столбец first_name имеет значение DAVID, или David, или david

SELECT employee_id, first_name, last_name, department_id, salary

FROM Employees

WHERE UPPER (first_name) = ′DAVID′;

Таблица 3.3. Функции обработки строк

Примеры использования функций обработки строк

Функции LPAD () и RPAD () можно использовать для отображения результата выполнения запроса в виде, который более удобен для восприятия.

Пример 3.12. Вывод данных о зарплате сотрудников без использования функций LPAD () и RPAD ()

SELECT first_name ′ ′ last_name ′ has a monthly salary of ′

salary ′ dollars. ′ AS Pay

FROM Employees;

Пример 3.13. Вывод данных о зарплате сотрудников c использованием функций LPAD () и RPAD ()

SELECT RPAD (first_name ′ ′ last_name,25)

′ has a monthly salary of ′

LPAD (salary,6) ′ dollars.» AS Pay

FROM Employees;

Рассмотрим более подробно функцию INSTR, которая часто используется при работе с символьными данными.

Функция INSTR возвращает номер позиции в строке str, начиная с которой строка search_str входит в строку str. Если вхождений не найдено, то функция возвращает значение 0. Синтаксис:

INSTR (str, search_str [, n [, m]])

— str — исходная строка;

— search_str — строка поиска;

— n — начало поиска, определяет начальную позицию, с которой следует начинать поиск;

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

Пример 3.14. Использование функции INSTR для нахождения позиции первого пробела в названии товара

SELECT product_name, INSTR (product_name, ′ ′)

FROM Products;

Пример 3.15. Использование функции INSTR для нахождения позиции второго пробела в названии товара

SELECT product_name, INSTR (product_name, ′ ′,1,2)

FROM Products;

Используя функцию SUBSTR совместно с функцией INSTR, можно вывести часть строчного значения, которая состоит из одного или нескольких слов. Следует иметь в виду, что если название товара состоит из двух слов, то второй пробел найден не будет и этот товар не попадет в результат выполнения запроса.

Пример 3.16. Вывести первые два слова из названия товара

SELECT SUBSTR (product_name,1,

(INSTR (product_name, ′ ′,1,2) — 1))

FROM Products;

Пример 3.17. Вывести названия товаров, первое слово которых состоит из трех символов

SELECT product_id, product_name

FROM Products

WHERE Length (SUBSTR (product_name,1,

(INSTR (product_name, ′ ′) — 1))) =3;

Используя функцию INSTR, можно осуществлять поиск по части строчного значения.

Пример 3.18. Вывести данные о товарах, в названии которых есть слово Core

FROM Products

WHERE INSTR (UPPER (product_name), ′CORE»′> 0;

Типы даты и времени

Эти типы используются для работы с данными, представляющими собой даты с учетом времени. Тип Date является основным при работе с данными, представляющими собой дату и время. При использовании этого типа данные хранятся в формате DD-MM-YY HH: MI: SS, где:

DD — двузначное значение дня;

MM — двузначный номер месяца;

YY — две последние цифры года;

HH, MI, SS — двузначные значения часа, минуты и секунды.

При выводе значений данного типа по умолчанию отображается дата. Для получения текущей даты в формате Date используется функция SYSDATE.

Пример 3.19. Вывод текущей даты в формате по умолчанию

SELECT SYSDATE

Для отображения и обработки полного значения, содержащего время, используются специальные функции.

Пример 3.20. Вывод текущей даты с использованием функции TO_CHAR

SELECT TO_CHAR (SYSDATE, ′DD MONTH YYYY, HH24:MI: SS′)

As Date_Time

К типам данных, используемых для представления значений даты и времени, также относятся:

— TIMESTAMP — аналогичен типу Date, но время хранится с точностью до миллиардной доли секунды. Для получения текущей даты в этом формате используется функция LOCALTIMESTAMP;

— TIMESTAMP WITH TIME ZONE — хранит вместе со значением даты и времени информацию о часовом поясе. Часовым поясом называется смещение от времени по Гринвичу. Для получения текущей даты в этом формате используется функция SYSTIMESTAMP.

Пример 3.21. Использование функция LOCALTIMESTAMP

SELECT LOCALTIMESTAMP

Пример 3.22. Использование функция SYSTIMESTAMP

SELECT SYSTIMESTAMP

Функции для работы с данными, имеющими тип даты и времени

Для обработки данных, имеющих тип Date, можно использовать функции, представленные в таблице 3.4.

Таблица 3.4. Функции для работы с данными, имеющими тип даты и времени

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

Прибавление целого значения n к значению типа Date эквивалентно прибавлению n дней к дате. Прибавление значения n/24 к значению типа Date эквивалентно прибавлению n часов к дате.

Если в выражении участвует строка, содержащая значение даты, то ее рекомендуется преобразовать к значению типа Date, используя функцию TO_DATE ().

Рассмотрим примеры, в которых значения, имеющие тип Date, участвуют в арифметических выражениях.

Пример 3.23. Вывод значения текущей даты, увеличенного на 45 дней

SELECT SYSDATE, SYSDATE +45

Пример 3.24. Вывод значения текущей даты и времени, увеличенного на два часа

SELECT TO_CHAR (SYSDATE, ′DD MONTH YYYY, HH24:MI: SS′)

TO_CHAR (SYSDATE +2/24, ′DD MONTH YYYY, HH24:MI: SS′)

Пример 3.25. Определить количество дней, прошедших между датой приема на работу и сегодняшним днем

SELECT employee_id, ROUND (SYSDATE — hire_date AS DAYS, — 2)

FROM Employees;

Следует обратить внимание на то, что этот запрос возвращает дробное значение, так как значение, возвращаемое функцией SYSDATE, содержит текущее время, которое трактуется как часть суток: например, 12 часов отобразятся в виде значения 0,5.

Пример 3.26. Определить количество недель, прошедших между датой приема на работу и сегодняшним днем

SELECT employee_id, TRUNC ((SYSDATE — hire_date) /7)

FROM Employees;

Для определения интервалов между двумя датами в месяцах следует использовать специальную функцию MONTHS_BETWEEN.

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

SELECT employee_id, first_name, last_name,

department_id, hire_date,

TRUNC (MONTHS_BETWEEN (SYSDATE, hire_date)) AS MONTHS

FROM Employees;

Пример 3.28. Вывести данные о сотрудниках, которые проработали более 30 лет

SELECT employee_id, first_name, last_name, salary,

department_id, hire_date

FROM Employees

WHERE MONTHS_BETWEEN (SYSDATE, hire_date)> 360;

Функция NEXT_DAY (x, день недели) возвращает следующую ближайшую дату, соответствующую определенному дню недели: например, среда.

Пример 3.29. Использование функции NEXT_DAY

SELECT SYSDATE AS «Сегодня», EXT_DAY (SYSDATE, ′Tuesday′)

Функция LAST_DAY (x) возвращает дату, соответствующую последнему дню месяца, которому принадлежит x.

Пример 3.30. Использование функции LAST_DAY

SELECT SYSDATE, LAST_DAY (SYSDATE)

Функция ROUND (x, <параметр>) округляет дату x, если параметр отсутствует, то до начала ближайших суток; если <параметр>= MM/ MON / MONTH — то до начала ближайшего месяца; если параметр = YY / YYYY /YEAR — то до начала ближайшего года.

Пример 3.31. Использование функции ROUND c параметром MM

SELECT ROUND (TO_DATE (′12.05.2018′, ′DD.MM.YYYY′),′MM′)

As ′′ ROUND MONTCH 12.05.2018 ′′,

ROUND (TO_DATE (′20.05.2018′, ′DD.MM.YYYY′),′MM′)

As ′′ ROUND MONTCH 20.05.2018 ′′

Пример 3.32. Использование функции ROUND c параметром YYYY

SELECT ROUND (TO_DATE (′12.05.2018′, ′DD.MM.YYYY′),′YYYY′)

As ′′ ROUND YEAR 12.05.2018 ′′,

ROUND(TO_DATE(′12.07.2018′,′DD.MM.YYYY′),′YYYY′)

As ′′ ROUND YEAR 12.07.2018 ′′

Функция TRUNC (x, <параметр>) отличается от ROUND тем, что возвращает начало текущих суток, начало текущего месяца, начало текущего года соответственно.

Пример 3.33. Использование функции TRUNC c параметром MM.

SELECT TRUNC (TO_DATE (′12.05.2018′, ′D.MM.YYYY′),′MM′)

As ′′ TRUNC MONTCH 12.05.2018 ′′,

TRUNC (TO_DATE (′20.05.2018′, ′D.MM.YYYY′),′MM′)

As ′′ TRUNC MONTCH 20.05.2018 ′′

Функция EXTRACT

Функция EXTRACT возвращает значение заданного поля даты-времени из значения, имеющего тип date. Синтаксис:

Таблица 3.5. Часть даты, возвращаемая функцией EXTRACT

Пример 3.34. Вывести данные о сотрудниках, которые были приняты на работу в 1999 году

SELECT employee_id, first_name, last_name, department_id, hire_date

FROM Employees

WHERE EXTRACT (YEAR FROM hire_date) =1999;

В СУБД Oracle используются три простых типа данных:

— строки CHAR, VARCHAR2;

Сервер Oracle может конвертировать данные, имеющие тип VARCHAR2 и CHAR, в данные типов NUMBER и DATE. Он может преобразовать данные, имеющие тип NUMBER или DATE, в данные типов CHAR и VARCHAR2.

Преобразование может осуществляться явным и неявным образом. Неявное преобразование осуществляется при выполнении следующего оператора:

При выполнении этого оператора значение или выражение в правой части преобразуется к типу, который имеет левая часть.

— WHERE order_date= ′2-04-2017′;

— WHERE order_date= ′26-apr-2017′;

— WHERE order_date= ′26-апр-2017′;

В этих примерах в зависимости от языковых настроек во втором или третьем операторе возникнет ошибка. Если используемый язык — английский, то ошибка возникнет в третьем операторе, Если используемый язык — русский, то ошибка возникнет во втором операторе. Следует иметь в виду, что значение ′26-APR-2017′ имеет тип строки символов.

— WHERE salary = ′4200′;

— WHERE salary = ′4000′+200;

— WHERE salary = ′4.200′;

— WHERE salary = ′$4200′;

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

Для того чтобы неявное преобразование было возможно, необходимо, чтобы присваиваемое значение соответствовало формату столбца, которому это значение присваивается.

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

Четыре типа преобразования:

— число в строку символов;

— строку символов в число;

— дату в строку символов;

— строку символов в дату.

Преобразование чисел в строку символов

Числа, хранящиеся в базе данных, не имеют форматирования. Это означает, что они не имеют символов валюты, запятых, десятичных знаков и других параметров форматирования. Чтобы добавить форматирование, необходимо преобразовать число в строку символов. Для этого используется функция:

Для преобразования численного значения в строку можно использовать элементы формата, представленные в таблице 3.6.

Таблица 3.6. Элементы маски преобразования, используемые в функции TO_CHAR

Пример 3.35. Использование функции TO_CHAR

SELECT TO_CHAR (1475.29, ′9999.9′) As ′′9999.9′′,

TO_CHAR (1475.29, ′9999.99′) As ′′9999.99′′,

TO_CHAR (1475.29, ′099999.90′) As ′′099999.99′′,

TO_CHAR (1475.29, ′9,999.99′) As ′′9,999.99′′,

TO_CHAR (1475.29, ′$9,999.99′) As ′′$9,999.99′′

Преобразование строки символов в число

Для преобразования символьного значения в число используется функция TO_NUMBER. Синтаксис:

Строка x может содержать цифры и символы, которые соответствуют заданному формату. Параметр <маска преобразования>определяет, как нужно интерпретировать символьное представление числа, может содержать те же элементы, которые были определены для функции TO_CHAR.

Если число символов в строке будет больше числа элементов формата, то возникает ошибка. Примеры преобразований, при которых возникает ошибка:

TO_NUMBER (′1475.29′,′999.99′)

TO_NUMBER (′1475.29′, ′9999.9′)

Если число символов в строке будет меньше числа элементов формата, то возникает ошибка.

Пример 3.36. Использование функции TO_NUMBER

SELECT TO_NUMBER (′1475.29′, ′99999.99′),

TO_NUMBER (′1475.29′, ′9999.999′)

Преобразование строки символов в дату

Для преобразования строки символов в значение, имеющее формат даты, используется функция:

Строка x содержит символьное значение даты. Параметр <маска преобразования>определяет, как нужно интерпретировать символьное представление даты.

Маска может содержать элементы формата, представленные в таблице 3.7.

Таблица 3.7. Элементы маски преобразования, используемые в функции TO_DATE

Пример 3.37. Использование функции TO_DATE

SELECT TO_DATE (′01-SEP-2018′, ′DD-MON-YYYY′)

As ′′01-SEP-2018′′,

TO_DATE (′09/01/18′, ′MM/DD/RR′) As ′′ 09/01/18′′,

TO_DATE (′01092018′, ′DDMMYYYY′) As ′′ 01092018′′

Замечание: срока преобразуется в дату, а дата выводится в установленном формате даты. Для ввода и вывода значения времени используется маска HH24:MI: SS, где:

— HH24 — двузначное значение часа в 24-часовом формате;

— MI — двузначное значение минут;

— SS — двузначное значение секунд.

Замечание: введенное значение времени сохраняется, но по умолчанию не отображается. Для отображения времени в значениях, имеющих тип Date, необходимо использовать функцию TO_CHAR.

Пример 3.38. Ввод и вывод значения даты, содержащей время

SELECT TO_CHAR (TO_DATE (′01-SEP-2018, 14:45:51′,

′DD-MON-YYYY HH24:MI: SS′),′DD MONTH YYYY, HH24:MI: SS′)

As Date_Time

Использование формата RR

Этот формат связан с проблемой 2000 года. Определяет год, если в дате заданы две последние цифры года. Если две последние цифры лежат в диапазоне от 0 до 49, то год принадлежит текущему столетию. Если две последние цифры лежат в диапазоне от 50 до 99, то год принадлежит предыдущему столетию.

TO_DATE (′04-JUL-18′, ′DD-MON-RR′) → 04/JUL/2018

TO_DATE (′04-JUL-75′, ′DD-MON-RR′) → 04/JUL/1975

Более полная информация о правилах использования формата RR приведена в таблице 3.8.

Таблица. 3.8. Правила преобразования года в формате RR

При использовании формата YY первые две цифры всегда соответствуют текущему столетию. Совет: при работе с датами всегда указывайте четыре цифры года.

Пример 3.39. Использование формата RR при вводе двузначного значения года

SELECT TO_CHAR (TO_DATE

(′04-07-18′, ′DD-MM-RR′),′DD-MON-YYYY′) As DAT1,

TO_CHAR (TO_DATE (′04-07-75′, ′DD-MM-RR′),′DD-MON-YYYY′)

Пример 3.40. Использование формата YY при вводе двузначного значения года

SELECT TO_CHAR (TO_DATE (′04-07-18′, ′DD-MM-YY′),

′DD-MON-YYYY′) As DAT1,

TO_CHAR (TO_DATE (′04-07-75′, ′DD-MM-YY′),

′DD-MON-YYYY′) As DAT2

Преобразование даты в строку символов

Это преобразование выполняется для того, чтобы отобразить значение, имеющее тип Date в требуемом виде. Для осуществления этого преобразования используется функция:

где: x — значение, имеющее тип Date, а строка <маска преобразования>) — маска, которая определяет, как нужно отобразить значение x; может содержать те же элементы, которые были определены для функции TO_DATE.

Пример 3.41. Использование функции TO_CHAR для преобразования значения, имеющего тип Date, в строку символов

SELECT TO_CHAR (SYSDATE, ′ DD/MM/YYYY′) AS RESULT1,

TO_CHAR (SYSDATE, ′ DD MON, YYYY′) AS RESULT2,

TO_CHAR (SYSDATE, ′ DD DAY MONTH, YYYY′) AS RESULT3,

TO_CHAR (SYSDATE, ′ DD — MONTH — YYYY, HH24:MI: SS′)

Используя функцию TO_CHAR при работе с данными, имеющими тип Date, можно выделить определенную часть даты: день, месяц, год.

Пример 3.42. Вывести данные о сотрудниках, которые были приняты на работу в 2000 году

SELECT employee_id, first_name, last_name, hire_date, salary

FROM Employees

WHERE TO_CHAR (hire_date, ′YYYY′) = ′2000′;

Работа с неопределенными значениями

Если при вводе новой строки в таблицу столбцу не будет присвоено значение, то этот столбец будет иметь значение NULL — не определено. Это может происходить по двум основным причинам. Первая причина: в момент ввода строки значение столбца неизвестно, в этом случае значение будет присвоено позже. Вторая причина: значение не может быть присвоено исходя из правил предметной области. Для рассматриваемой базы данных вторую причину можно пояснить на примере столбца commission_pct таблицы Employees. Некоторым сотрудникам полагаются комиссионные, столбец commission_pct содержит значение комиссионных. Зарплата таких сотрудников рассчитывается по формуле: Salary * (1 + commission_pct). У сотрудников, которым комиссионные не полагаются, значение столбца commission_pct не может быть определено.

При работе с арифметическими и логическими выражениями следует иметь в виду следующее: арифметическое выражение вернет значение NULL, если один или несколько операндов будут иметь значение NULL; результатом операции сравнения будет NULL, если один или оба операнда будут иметь значение NULL.

Результат логических операций AND и OR приведен в таблицах 3.9 и 3.10 соответственно.

Таблица 3.9. Таблица истинности логической функции AND с учетом значений NULL

Таблица 3.10. Таблица истинности логической функции OR с учетом значений NULL

Для корректной обработки данных, которые могут иметь значения NULL, следует использовать специальные функции.

Функция NVL

Позволяет заменить значение NULL фактическим значением. Синтаксис:

Возвращает x, если x не NUUL, и возвращает y, если x имеет значение NUUL, например: NVL (commission_pct,0).

Рассмотрим примеры использования функции NVL при решении конкретных задач.

Пример 3.43. Вывести данные о сотрудниках, включая размер комиссионных, которые работают в отделах 30 и 80

SELECT employee_id, first_name, last_name, department_id,

salary, NVL (commission_pct,0)

FROM Employees

WHERE department_id IN (30,80)

ORDER BY department_id;

Пример 3.44. Вывести данные о сотрудниках, включая зарплату с учетом комиссионных (полная зарплата), которые работают в отделах 30 и 80, упорядочив их в порядке убывания значений зарплаты с учетом комиссионных

SELECT employee_id, first_name, last_name, department_id,

salary* (1+NVL (commission_pct,0)) AS total_salary

FROM Employees

WHERE department_id IN (30,80)

ORDER BY total_salary DESC;

Псевдонимы столбцов можно использовать в предложении ORDER BY, но нельзя использовать в предложении WHERE.

Пример 3.45. Вывести данные о сотрудниках, включая зарплату с учетом комиссионных, полная зарплата которых больше 15 000, упорядочив их в порядке убывания значений полной зарплаты

SELECT employee_id, first_name, last_name, department_id,

salary* (1+NVL (commission_pct,0)) AS total_salary

FROM Employees

WHERE total_salary> 15000

ORDER BY total_salary DESC;

Правильный вариант решения задачи 3.45:

SELECT employee_id, first_name, last_name, department_id,

salary* (1+NVL (commission_pct,0)) AS total_salary

WHERE salary* (1+NVL (commission_pct,0))> 15000

ORDER BY total_salary DESC;

Функция NVL2

Расширяет возможности функции NVL. Синтаксис:

Возвращает y1, если x не NUUL, и возвращает y2, если x имеет значение NUUL.

NVL2 (commission_pct, salary* (1+commission_pct), salary)

Пример 3.46. Вывести данные о сотрудниках, которые работают в отделах 30 и 80, размере премии, которую они должны получить. Размер премии, у сотрудников, которые получают комиссионные, равен зарплате с учетом комиссионных. Размер премии, у сотрудников, которые не получают комиссионные, равен зарплате, увеличенной на 30%

SELECT employee_id, first_name, last_name, department_id,

NVL2 (commission_pct, salary* (1+commission_pct), salary*1.3)

FROM Employees

WHERE department_id IN (30,80)

ORDER BY prize DESC;

Функция COALESCE

Предназначена для обработки значений NULL и предоставляет более широкие возможности, чем функции NVL и NVL2. Позволяет отрабатывать несколько значений NULL. Синтаксис:

COALESCE (y1,y2,…yn)

Возвращает первое не NULL значение.

Для того чтобы продемонстрировать возможности этой функции, рассмотрим следующую задачу. Предположим, что таблица Employees имеет еще один столбец bonus. Значение этого столбца равно некоторой фиксированной сумме, которая должна быть прибавлена к зарплате сотрудника, может иметь значение NULL. С учетом столбца bonus зарплата сотрудников равна:

— bonus + salary * (1 + commission_pct) — если сотруднику положен бонус и он получает комиссионные;

— bonus + salary — если сотруднику положен бонус, но он не получает комиссионные;

— salary * (1 + commission_pct) — если сотруднику не положен бонус, но он получает комиссионные;

— salary — если сотруднику не положен бонус и он не получает комиссионные.

Используя функцию COALESCE, это правило начисления зарплаты можно реализовать следующим образом.

Пример 3.47. Вывести данные о сотрудниках и их полную зарплату, которая включает комиссионные и бонус

SELECT employee_id, first_name, last_name, department_id,

COALESCE (bonus + salary* (1+commission_pct),

bonus + salary, salary* (1+commission_pct), salary)

AS total_salary

FROM Employees

ORDER BY total_salary DESC;

Довольно часто значение столбца, которое должен вернуть SQL-запрос, зависит от условий, которые нужно проверять для каждой строки. Для реализации подобного выбора используются выражение CASE и функция DECODE. Используя CASE и DECODE, можно реализовать условную логику if-then-else в операторе SELECT. Выражение CASE соответствует стандарту ANSI SQL, а функция DECODE специфична для Oracle.

Выражение CASE

Практически во всех современных языках программирования используется выражение CASE. Есть два варианта выражения CASE:

— выражение CASE с параметром;

— выражение CASE с условием.

Выражение CASE с параметром имеет следующий синтаксис:

Выражение CASE выполняется следующим образом: сравниваются значение <параметр>со значениями <значение i>в предложениях WHEN и возвращает результат <результатi>первого предложения, в котором будет выполнено условие <параметр>= <значениеi>.

Следует иметь в виду, что Oracle не оценивает остальные предложения WHEN. Если ни в одном из предложений WHEN не выполняется условие <параметр>= <значениеi>, то возвращается значение <результат_ELSE>. Если предложение ELSE отсутствует, то выражение CASE вернет результат NULL.

Возвращаемый результат может быть значением или выражением. Выражения <параметр>и <значение1>должны иметь один и тот же тип данных. Все возвращаемые значения <результат2>должны иметь одинаковый тип данных.

Примечание. Выражение CASE может содержать другие выражения CASE. Единственным ограничением является то, что одно выражение CASE может иметь максимум 255 условных выражений.

Пример 3.48. Вывести данные о сотрудниках и размере их премии, которая задана в виде фиксированной суммы, размер которой зависит от отдела, где работает сотрудник

Пример 3.49. Вывести данные о сотрудниках и размере их премии, которая задана как часть заработной платы, размер которой зависит от отдела, где работает сотрудник

В этом примере отсутствует предложение ELSE, поэтому размер премии для сотрудников отделов, номеров которых нет в предложениях WHERE, имеет значение NULL.

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

Пример 3.50. Вывести данные о сотрудниках и размере их премии, которая зависит как от отдела, где работает сотрудник, так и от его должности.

Выражение CASE с условием имеет следующий синтаксис:

При использовании этой разновидности оператора CASE последовательно поверяются значения условных выражений в предложениях WHEN и возвращается результат из первого предложения, в котором это выражение будет иметь значение TRUE.

Пример 3.51. Вывести данные о сотрудниках и размере их премии, которая зависит от зарплаты сотрудника

Пример 3.52. Вывести данные о сотрудниках и размере их премии, которая зависит от количества лет, которые проработал сотрудник

Функция DECODE

По своему назначению функция DECODE аналогична условному выражению CASE, но не поддерживается стандартом ANSI/ISO SQL. Синтаксис:

Значение <столбец> <выражение>сравнивается со значениями <значение i>и возвращается результат первого совпадения.

Если совпадения не будет, то возвращается значение <результат default>. Если <результат default>отсутствует, то функция DECODE вернет результат NULL.

Следует обратить внимание на то, что функция DECODE требует точного совпадения значений и не позволяет использовать операции сравнения>, <и сложные условия. Поэтому возможности функции DECODE уступают возможностям условного выражения CASE.

Пример 3.53. Вывести данные о сотрудниках и размер их премии, которая задана в виде фиксированной суммы, размер которой зависит от отдела, в котором работает сотрудник

SELECT department_id, employee_id, first_name, last_name,

job_id, salary,

DECODE (department_id, 10, 1000,30, 1200,60, 1500,500)

FROM Employees

WHERE department_id in (10,30,40,60,100)

ORDER BY department_id;

Результат выполнения этого запроса совпадает с результатом выполнения запроса из примера 3.50.

Рассмотрим еще один пример использования функции DECODE для решения задачи из примера 3.53. Особенностью этой задачи является использование операции сравнения>, которую нельзя использовать в 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),

0.5*salary) As bonus

FROM Employees

WHERE department_id IN (10,30,40,60)

ORDER BY department_id;

Задачи для самостоятельного решения

1. Вывести значения столбцов employee_id, first_name, last_name и значение зарплаты, увеличенное на 25%. Увеличенное значение зарплаты округлить до сотен.

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 <3 и salary <5000, то category = ′Low′;

— у остальных сотрудников category = ′Middle′.

Глава 4. Агрегатные функции и группировка данных

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

Таблица 4.1. Агрегатные функции

Синтаксис агрегатных функций:

где: expr — аргумент агрегатной функции, который может содержать следующие элементы:

Следует обратить внимание на то, что аргументом групповой функции может быть однострочная функция. Хотя стандарт языка SQL запрещает использование агрегатных функций в качестве аргумента агрегатных функций, СУБД Oracle допускает это, но только на один уровень в глубину и только в предложении SELECT. Рассмотрим примеры использования агрегатных функций.

Пример 4.1. Вывод обобщенных данных о зарплате сотрудников

SELECT MIN (salary) AS minimum, MAX (salary) AS maximum, ROUND (AVG (salary)) AS medium, SUM (salary) As summa, COUNT (salary), COUNT (*)

FROM Employees;

В полученном результате следует обратить внимание на то, что:

— COUNT (salary) возвращает число сотрудников, получающих зарплату, у которых значение столбца salary не NULL;

— COUNT (*) возвращает число всех сотрудников.

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

salary * (1 + NVL (commission_pct,0))

Используя это выражение в предыдущем запросе, вместо столбца salary получим:

Пример 4.2. Вывод обобщенных данных о зарплате сотрудников с учетом комиссионных

SELECT MIN (salary* (1+NVL (commission_pct,0))) AS minimum,

MAX (salary* (1+NVL (commission_pct,0))) AS maximum,

ROUND (AVG (salary* (1+NVL (commission_pct,0)))) AS medium,

SUM (salary* (1+NVL (commission_pct,0))) As summa,

COUNT (salary* (1+NVL (commission_pct,0))) AS ′′COUNT (expr) ′′,

FROM Employees;

Пример 4.3. Использование функции COUNT

SELECT COUNT (*), COUNT (salary),COUNT (DISTINCT salary),

COUNT (commission_pct)

FROM Employees

WHERE department_id =80;

Анализ результатов этого запроса:

— COUNT (*) — вернула число сотрудников в отделе 80;

— COUNT (salary) — вернула число сотрудников в отделе 80,

у которых значение столбца salary не NULL;

— COUNT (DISTINCT salary) — вернула число различных значений в столбце salary;

— COUNT (commission_pct) — вернула число сотрудников в отделе 80, у которых значение столбца commission_pct не NULL.

Оператор DISTINCT используется для исключения повторяющихся значений. Например, необходимо определить количество должностей. Запрос без оператора DISTINCT вернет количество сотрудников, у которых значение столбца job_id не NULL.

Пример 4.4. Количество сотрудников, у которых значение столбца job_id не NULL

SELECT COUNT (job_id)

FROM Employees;

Если в аргумент функции COUNT добавить оператор DISTINCT, то будут исключены повторяющиеся значения столбца job_id и запрос вернет количество должностей (количество уникальных значений столбца job_id).

Пример 4.5. Количество уникальных значений столбца job_id

SELECT COUNT (DISTINCT job_id)

FROM Employees;

Задача: требуется определить средний размер комиссионных. Рассмотрим два варианта решения этой задачи.

SELECT AVG (commission_pct)

FROM Employees;

SELECT AVG (NVL (commission_pct, 0))

FROM Employees;

Здесь правильный вариант решения не очевиден. У значительной части сотрудников значение столбца commission_pct имеет значение NULL. Если этим сотрудникам комиссионные не положены и они не должны учитываться, то правильным будет первый вариант. Если значение NULL следует считать равным нулю, то следует использовать второй вариант запроса.

Агрегатные функции нельзя использовать в предложении WHERE. Например, нельзя найти сотрудника с максимальной зарплатой, используя следующий запрос:

Пример 4.6a. Найти сотрудника, получающего максимальную зарплату

Внимание: ЭТОТ ЗАПРОС НЕ БУДЕТ ВЫПОЛНЕН!

SELECT employee_id, salary

FROM Employees

WHERE salary = MAX (salary);

Данную задачу можно решить следующим образом:

Пример 4.6б. Найти сотрудника, получающего максимальную зарплату

SELECT employee_id, salary AS maximum

FROM Employees

WHERE salary = (SELECT MAX (salary) FROM Employees);

Данный запрос содержит в предложении WHERE подзапрос. Использование подзапросов будет рассмотрено позже.

Чаще всего агрегатные функции используются в запросах с группировкой. В общем виде запрос с группировкой может быть представлен в следующем виде:

Списки столбцов в предложениях SELECT и GROUP BY должны совпадать.

Предложение GROUP BY разбивает данные на группы, и запрос выводит обобщенные данные о каждой группе.

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

Пример 4.7. Для каждого отдела определить суммарную зарплату

SELECT department_id, SUM (salary) AS SUM_salary

FROM Employees

GROUP BY department_id

ORDER BY department_id;

Пример 4.8. Для каждого отдела определить суммарную зарплату с учетом комиссионных

SELECT department_id, SUM (salary* (1+NVL (commission_pct,0)))

FROM Employees

GROUP BY department_id

ORDER BY department_id;

Пример 4.9. Для каждого отдела определить суммарную длину имен (столбца first_name)

SELECT department_id, SUM (LENGTH (first_name)) As sum_f_nam

FROM Employees

GROUP BY department_id

ORDER BY department_id;

Группировка по нескольким столбцам

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

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

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

SELECT department_id, job_id, count (*)

FROM employees

GROUP BY department_id;

Причина ошибки: при наличии группировки предложение SELECT может содержать только столбцы, по которым осуществляется группировка и агрегатные функции.

Пример 4.10б. Для отделов 30 и 50 определить должности и количество сотрудников, занимающих каждую должность

SELECT department_id, job_id, count (*)

FROM Employees

WHERE department_id IN (30,50)

GROUP BY department_id, job_id

ORDER BY department_id;

Пример 4.11. Для отделов, номер которых меньше 50, вывести рейтинги, которые имеют сотрудники этого отдела, их количество и суммарную зарплату

SELECT department_id, rating_e, count (*),sum (salary)

FROM Employees

WHERE department_id <= 50

GROUP BY department_id, rating_e

ORDER BY department_id;

Использование условий на группу

Условия на группу указываются в предложении HAVING.

Пример 4.12. Вывести суммарную зарплату для тех отделов, у которых суммарная зарплата превышает 50 000

SELECT department_id, SUM (salary)

FROM Employees

GROUP BY department_id

HAVING SUM (salary)> 50000;

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

Пример 4.13. Вывести должности и количество сотрудников, которые получают зарплату более 10 000

SELECT department_id, SUM (salary)

FROM Employees

GROUP BY department_id

HAVING SUM (salary)> 50000;

К условиям предыдущей задачи можно добавить условие: вывести только те должности, которые занимают более одного сотрудника.

Пример 4.14. Вывести должности и количество сотрудников, которые получают зарплату более 10 000, которые занимают более одного сотрудника, упорядочив их в порядке убывания количества сотрудников

SELECT job_id, COUNT (*) As num_job

FROM Employees

WHERE salary> 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;

Использование вложенных агрегатных функций

Хотя Oracle и допускает использование вложенных агрегатных функций, но только на один уровень и только в предложении SELECT. При этом предложение SELECT не должно содержать других элементов.

Пример 4.16. Определить количество сотрудников в каждом отделе

SELECT department_id, count (*)

FROM Employees

GROUP BY department_id

ORDER BY department_id;

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

Пример 4.17. Найти максимальное число сотрудников работающих в одном отделе

SELECT Max (COUNT (*))

FROM Employees

GROUP BY department_id;

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

Пример 4.18. Найти отдел, в котором работает максимальное число сотрудников.

SELECT department_id, COUNT (*)

FROM Employees

GROUP BY department_id

HAVING COUNT (*) =

(SELECT MAX (COUNT (*))

FROM Employees

GROUP BY department_id);

Использование специальных операторов группировки

Рассмотрим специальные операторы группировки и функции, которые позволяют существенно расширить возможности запросов с группировкой данных.

Оператор GROUP BY ROLLUP

Расширяет возможности GROUP BY, возвращая для каждой группы строку, содержащую итоги по группе, а также строку, содержащую общий итог для всех групп, и имеет следующий вид:

GROUP BY ROLLUP