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 {список значений}
Список значений в операторе 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 используется для определения строк с неопределенным значением заданного столбца.
Синтаксис:
{имя столбца} 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, кроме списка столбцов таблиц участвующих в запросе, могут присутствовать вычисляемые столбцы, которые представляют собой выражения, состоящие из имен столбцов, констант, функций и арифметических операций. Значению вычисляемого поля можно присвоить имя. Для этого используется следующая конструкция:
{Выражение} As {псевдоним}
При вычислении выражения, содержащего несколько арифметических операций, 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.
Оператор конкатенации
Оператор конкатенации (слияния) записывается двумя вертикальными чертами (||) и используются для того, чтобы объединить при выводе данных два или несколько столбцов или литералов в один столбец.
Синтаксис:
{столбец1/литерал1} || {столбец2/литерал2} …As {псевдоним}
Оператор конкатенации можно применять для строк, чисел и дат. Даты и числа при слиянии конвертируются в строковые значения. При слиянии строки значения со значением типа 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, которое имеет следующий синтаксис:
ORDER BY {имя столбца | номер столбца [ASC|DESC]}
Пример 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. Но данный оператор должен обязательно содержать предложение:
FROM {источник данных}
В качестве источника данных мы будем использовать служебную таблицу DUAL, которая доступна для всех пользователей. Таблица DUAL имеет один столбец с именем DUMMY, тип данных которого VARCHAR2 (), и содержит одну строку со значением X.
Пример 3.1. Вывод содержимого таблицы DUAL
SELECT *
FROM Dual;
Числовые типы
Числовые типы используются для работы с числовыми данными. Можно использовать следующие форматы чисел:
– целые числа;
– вещественные числа в формате с фиксированной точкой;
– вещественные числа в формате с плавающей точкой.
При определении столбцов, содержащих числовые данные, можно использовать следующие спецификации:
NUMBER (n) – целое число не более n цифр;
NUMBER (n,m) – вещественное число с фиксированной точкой,
n – максимальное число цифр в записи числа а m – число цифр справа от десятичной точки;
NUMBER – вещественное число с плавающей точкой с точностью до 38 цифр.
В таблице 3.1 приведены основные функции, которые можно использовать при обработке данных числового типа.
Таблица 3.1. Основные функции для обработки чисел
Функция ROUND
Выполняет округление до ближайшего числа с заданной точностью (результат может быть не целым). Синтаксис:
ROUND (n,m)
n – численное значение;
m – точность округления.