Синтаксис:
{столбец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 – точность округления.