Общие правила записи директив языка SQL

к РегисТРУ.
Оператор языка SQL можно разместить в нескольких строках.
Важно только

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

словарь (справочник) данных, а также при выводе результатов запроса автоматически

из одной или нескольких таблиц, а также (в случае необходимости)

можно выделить несколько основных разделов:
SELECT 〈 Список_выбора 〉
[ INTO 〈

наиболее распространенные варианты применения команды SELECT.
Задание 1. Выбрать из таблицы

Здесь символ * означает «все столбцы».

2) Отображение конкретных столбцов таблицы

SELECT 〈 список_столбцов 〉 FROM 〈 имя_исх_таб 〉

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

В этом случае для элемента списка выбора применяется следующая конструкция:

〈 элемент_списка 〉 [ AS ] 〈 псевдоним 〉

Задание 2. Сделать выборку столбцов из таблицы EMPLOYEES с применением псевдонимов.

Если псевдоним содержит пробелы или специальные символы, то такой псевдоним нужно поместить в двойные кавычки.

и операторов.
Для числовых типов используются арифметические операции: + , -

Задание 3. Используя столбец SALARY в таблице EMPLOYEES (месячная зарплата сотрудника), вычислить годовой доход для каждого из них.

т.е. выполнять операцию конкатенации ( || ).
Если в начале списка

Задание 5. По данным из таблицы EMPLOYEES выдать неповторяющиеся значения JOB_ID.

Задание 4. При выборке данных из таблицы EMPLOYEES значения полей FIRST_NAME и LAST_NAME для каждого сотрудника поместить в один столбец с названием FULL_NAME.

всему списку выбора, т.е. его указывают только один раз.
SELECT 〈

В условие отбора могут входить простые операции сравнения: = , < , > , <= , >=, <> .
Задание 7. По таблице EMPLOYEES получить список сотрудников с низкой зарплатой (не более 2500$).

Задание 6. По данным из таблицы EMPLOYEES неповторяющиеся значения JOB_ID выдать только в пределах отдельного департамента.

в текстовом поле наличие подстроки, задаваемой в виде шаблона.
Шаблон (маска)

зарплата находится в замкнутом диапазоне [3000$, 7000$].
Принадлежность к множеству (списку)

Задание 10. По таблице EMPLOYEES получить список сотрудников в департаментах 20, 60 и 90.
Важно, что элементы списка сами могут быть списками (множествами) значений. Например:

SELECT department_id, last_name, salary
FROM employees
WHERE (department_id, salary)
IN ( (20, salary), (60, 4800), (90, 17000) );

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

SELECT last_name FROM employees
WHERE commission_pct=NULL

(составных) условий применяют логические операции AND, OR и NOT.
SELECT last_name

Задание 11. По таблице EMPLOYEES получить списки сотрудников:
для категории клерков (поле job_id содержит подстроку CLERK), у которых зарплата находится в диапазоне [2500$, 3000$];
которые приняты на работу с начала 1999 г. и не относятся к департаментам 50, 80, 100.

неизвестное значение ( NULL ).
Как следствие, в языке SQL действуют

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

Результаты выполнения операции AND

входящих в эти выражения.
В принципе все это запоминать особой нужды

〉 ORDER BY 〈 Ключи_сортировки 〉
В ключе сортировки обычно присутствует

зарплата не ниже 10000$, с указанием кода подразделения (department_id) и

Язык SQL в СУБД Oracle располагает большим количеством встроенных (стандартных) функций.
Встроенная функция обычно имеет некоторое количество входных аргументов, с учетом которых она выдает результирующее значение.

5) Встроенные функции языка SQL

– манипулируют со строками символов;
числовые функции – выполняют расчеты с

〈str〉 с использованием заглавных букв.
Задание 13. По таблице EMPLOYEES сформировать

Функция SUBSTR ( 〈str〉, m [, n ]) – из исходной строки 〈str〉 выделяет подстроку длиной n символов, которая начинается с позиции m.

строки 〈str〉.
Функция MONTHS_BETWEEN ( 〈d1〉, 〈d2〉 ) – получает число

Задание 14. По таблице EMPLOYEES получить список сотрудников с указанием фамилии (last_name) и инициала – первая буквы имени (first_name) с точкой. При выводе сортировать список по убыванию длины фамилии, а фамилии одинаковой длины – по алфавиту.

числовое значение, полученное с помощью выражения 〈expr〉, преобразует в символьную

Задание 15. По таблице EMPLOYEES получить список сотрудников, где указать фамилию и имя (last_name, first_name), текущий оклад (salary) и дату приема на работу (hire_date). Одновременно для каждого сотрудника определить стаж работы (в месяцах) и начислить бонус в размере 1% от оклада за каждый месяц работы. При выводе сортировать список по убыванию стажа работы.

〈expr1〉 имеет неопределенное значение (NULL), то вместо него использует выражение

Задание 16. С помощью числового формата (например, ‘999.9’) и аналогичного денежного формата (‘$999.99’) убрать лишние знаки при выводе стажа работы и бонуса в запросе из предыдущего задания.

Задание 17. По данным из таблицы EMPLOYEES посчитать зарплату сотрудников за текущий месяц с учетом commission_pct (комиссионная надбавка). Результат упорядочить по убыванию зарплаты.

директивы языка SQL реализуется логика IF-THEN-ELSE.
Синтаксис обращения к функции:
DECODE (

Функция последовательно сравнивает значение 〈expr〉 со значениями 〈s_val1〉, 〈s_val2〉 и т.д.
При первом же совпадении дальнейшие сравнения прекращаются и функция возвратит значение 〈res〉 с соответствующим номером.

а если этот элемент не указан, то функция возвратит NULL.
Существует

Задание 18. По таблице EMPLOYEES получить список сотрудников, где указать фамилию и имя, а также код должности (job_id). При этом в столбце job_id значения SA_REP и SA_MAN нужно заменить строками ‘Торговый представитель’ или ‘Менеджер по продажам’, соответственно. Для остальных значений job_id нужно выводить строку ‘Другое’.

с группой записей.
Очень часто это требуется при статистической обработке, а

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

значения.
Особый случай: COUNT(*) возвратит общее число записей в группе.
Задание 19.

выполнить их группировку, т.е. сделать так, чтобы в одну группу

SELECT 〈 список_столбцов 〉 FROM 〈 имя_исх_таб 〉 GROUP BY 〈 Ключи_группировки 〉

Важно: логика работы запросов с группировкой требует наличия жесткой связи между разделами SELECT и GROUP BY.

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

Задание 20. По таблице EMPLOYEES найти минимальный, максимальный и средний оклад (SALARY) для каждого департамента.

происходит по аналогии с разделом WHERE, когда идет отбор определенных

Группировка по нескольким ключам позволяет детализировать итоговые результаты.

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

(оклад меньше 3000$) превышает 3.
Пример. По таблице EMPLOYEES получить список

SELECT DEPARTMENT_ID, COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(*) > 5

этого внутреннего запроса (подзапроса) передается внешнему запросу.
Рассмотрим сначала скалярный подзапрос,

7) Вложенные запросы (подзапросы)

SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE SALARY >

( SELECT AVG (SALARY)
FROM EMPLOYEES )

из списка вывода (раздел SELECT).
Пример. В списке сотрудников с окладами

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

SELECT FIRST_NAME, LAST_NAME ,

FROM EMPLOYEES
WHERE SALARY >
( SELECT AVG (SALARY)
FROM EMPLOYEES )

SALARY / ( SELECT AVG(SALARY)
FROM EMPLOYEES ) Koeff

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

SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE department_id IN

( SELECT department_id
FROM DEPARTMENTS
WHERE department_name
IN(‘Marketing’, ‘Sales’, ‘IT’) )

(например, пары значений).
Задание 23. Получить список, в котором из каждого

( SELECT department_id, MAX(salary) FROM EMPLOYEES
GROUP BY department_id )

SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE ( department_id, salary ) IN

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

SELECT department_id, AVG(salary) avg_sal
FROM EMPLOYEES
GROUP BY department_id

HAVING AVG(salary) <
( SELECT AVG(salary)
FROM EMPLOYEES )

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

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

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

размещаются на территории США (country_id ='US').
Наряду с этим, возможны и

SELECT department_id, department_name
FROM DEPARTMENTS dps
WHERE 'US' =

( SELECT country_id
FROM LOCATIONS
WHERE location_id = dps.location_id )

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

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

Подзапрос, обладающий такой особенностью, называют связанным (или коррелированным).

FROM EMPLOYEES
WHERE department_id = dps.department_id )
Логический оператор EXISTS позволяет

Пример. Сформировать перечень департаментов, в которых отсутствуют сотрудники.

Легко заметить, что здесь использован коррелированный подзапрос.

если в разделе FROM через запятую перечислить несколько источников данных.
Такой

8) Многотабличные запросы с операцией JOIN

– E и J.
Наряду с условием сцепления строк (записей) из

SELECT FIRST_NAME, LAST_NAME, JOB_TITLE
FROM EMPLOYEES E, JOBS J
WHERE E.JOB_ID = J.JOB_ID

Пример. По таблицам EMPLOYEES и JOBS получить общий список сотрудников с указанием полного названия должности сотрудника.

которые размещены на территории США (country_id ='US').
Соединения, при которых записи

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

SELECT department_name d_name, last_name, first_name
FROM departments D, employees E
WHERE D.department_id = E.department_id (+)
ORDER BY d_name;

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

соединить таблицу саму с собой.
Задание 28. Выдать список департаментов, указав

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

S.first_name Slave, B.last_name || ' ' || B.first_name Boss
Задание 29. По

WHERE S.manager_id = B.employee_id
ORDER BY Boss;

условие 〈join_cond〉 для соединения строк переносится внутрь раздела FROM.
Дополнительные условия

Начиная с СУБД Oracle 9i и стандарта SQL:1992, стало возможным в разделе FROM оператора SELECT указывать операцию соединения явным образом в виде следующей конструкции:

〈left_tab〉 〈join_type〉 〈right_tab〉 ON 〈join_cond〉

[ OUTER ] JOIN — левое внешнее соединение;
RIGHT [ OUTER

Синтаксический элемент 〈join_type〉 может принимать следующие значения:

department_id, department_name
FROM departments D JOIN locations L ON (D.location_id

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

FROM departments JOIN locations USING (location_id)

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

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

Задание 31. Получить список сотрудников, которые относятся к службам Marketing, Sales и IT.

Задание 32. Получить список сотрудников департамента, для которого POSTAL_CODE=’26192’.

уровня оклада (salary). Эти категории задаются в виде специальной таблицы

В конструкции ON 〈join_cond〉 можно указать соединение строк по произвольному условию с применением любых операций сравнения (<=, <>, LIKE, BETWEEN и др.).

Это позволяет реализовать так называемое Θ-соединение.

Low_limit AND High_limit)
GROUP BY Low_limit, High_limit
ORDER BY Low_limit;
Можно

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

Самый простой вариант – это естественное соединение двух таблиц:

〈left_tab〉 〈 NATURAL JOIN 〉 〈right_tab〉

строк из рассматриваемых таблиц:
Реальный пример такой опасности:
SELECT department_name d_name,

E.department_id = D.department _id AND E.manager_id = D.manager_id

Но при построении списка сотрудников по каждому департаменту нужна только левая часть этого условия.
Вторая часть условия вообще не отражает логику связей между таблицами departments и employees.

начальник — менеджер подразделения.

в языке SQL имеются реляционные операции, очень похожие на операции

9) Работа с результатами нескольких запросов

query_1 〉
{ UNION | UNION ALL | INTERSECT | MINUS }
〈 query_2 〉

одинаковую структуру (с точностью до совместимости типов столбцов);
за исключением UNION

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

first_name, last_name
FROM employees
WHERE employee_id IN
( SELECT DISTINCT manager_id

UNION

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