Разделы презентаций


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

Содержание

Правила именования объектов в СУБД OracleОбычное имя необходимо начинать с буквы, а дальше, кроме букв, можно использовать только цифры и символы _ , $ , #.Обычное имя не должно совпадать с

Слайды и текст этой презентации

Слайд 1Общие правила записи директив языка SQL
Операторы языка SQL нЕ чуВствиТельнЫ

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

сохранять целостность отдельных лексем, т.е. нельзя разрывать ключевые слова, имена объектов, значения констант и т.п.
Общие правила записи директив языка SQLОператоры языка SQL нЕ чуВствиТельнЫ к РегисТРУ.Оператор языка SQL можно разместить в

Слайд 2Правила именования объектов в СУБД Oracle
Обычное имя необходимо начинать с

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

символы _ , $ , #.
Обычное имя не должно совпадать с ключевыми (зарезервированными) словами.
Если имя заключить в двойные кавычки, то можно использовать абсолютно любую последовательность символов.
В этом случае допускается даже совпадение имени с ключевыми словами!
Пример:
CREATE TABLE
“(.)[/]” (“NUMBER” NUMBER, “SELECT” CHAR);
Правила именования объектов в СУБД OracleОбычное имя необходимо начинать с буквы, а дальше, кроме букв, можно использовать

Слайд 3Правила именования объектов в СУБД Oracle (продолжение)
Обычное имя при записи в

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

приводится к верхнему регистру.
Двойные кавычки исключают такое преобразование.
Кириллица допускается без ограничений, если только для БД указана одна из "русских" кодировок.
Правила именования объектов в СУБД Oracle (продолжение)Обычное имя при записи в словарь (справочник) данных, а также при

Слайд 4Выборка данных с помощью языка SQL
Команда SELECT позволяет извлечь данные

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

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

Слайд 5В структуре этой мощной команды, которая имеет достаточно сложный синтаксис,

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

Новая_таблица 〉 ]
FROM 〈 Набор_источников_данных 〉
[ WHERE 〈 Условия_отбора_записей 〉 ]
[ GROUP BY 〈 Ключи_группировки 〉 ]
[ HAVING 〈 Условия_отбора_групп 〉 ]
[ ORDER BY 〈 Ключи_сортировки 〉 ]
Обязательными являются только два раздела:
SELECT, где указываются столбцы, которые должны присутствовать в выходной таблице;
FROM, где задается перечень таблиц и других источников данных запроса
В структуре этой мощной команды, которая имеет достаточно сложный синтаксис, можно выделить несколько основных разделов:SELECT 〈 Список_выбора

Слайд 61) Полное отображение таблицы
SELECT * FROM 〈 имя_исх_таб 〉
Рассмотрим

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

EMPLOYEES столбцы FIRST_NAME, LAST_NAME, HIRE_DATE.

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

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

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

1) Полное отображение таблицыSELECT * FROM  〈 имя_исх_таб 〉Рассмотрим наиболее распространенные варианты применения команды SELECT.Задание 1.

Слайд 7Выводимое имя столбца можно поменять при помощи псевдонима (alias).
В списке

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

полей в выходную таблицу.

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

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

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

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

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

Слайд 8Обычно выражение — это некоторая комбинация полей в таблицах, констант

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

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

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

Обычно выражение — это некоторая комбинация полей в таблицах, констант и операторов.Для числовых типов используются арифметические операции:

Слайд 9При построении вычисляемых полей можно сцеплять элементы типа «строка символов»,

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

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

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

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

При построении вычисляемых полей можно сцеплять элементы типа «строка символов», т.е. выполнять операцию конкатенации ( || ).Если

Слайд 103) Выборка записей по заданному условию
Важно: слово DISTINCT относится ко

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

список_столбцов 〉 FROM 〈 имя_исх_таб 〉 WHERE 〈 условие_отбора 〉

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

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

3) Выборка записей по заданному условиюВажно: слово DISTINCT относится ко всему списку выбора, т.е. его указывают только

Слайд 11В условии отбора может присутствовать оператор LIKE ‘шаблон’, который выявляет

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

поиска может включать в себя следующие специальные символы:
_ – один произвольный символ;
% – последовательность любых символов (в том числе ни одного).
Задание 8. По таблице EMPLOYEES получить список сотрудников, у которых в фамилии (LAST_NAME) два предпоследних символа – ‘EN’.
Попадание в замкнутый числовой интервал [a, b] проверяет условный оператор BETWEEN a AND b
В условии отбора может присутствовать оператор LIKE ‘шаблон’, который выявляет в текстовом поле наличие подстроки, задаваемой в

Слайд 12Задание 9. По таблице EMPLOYEES получить список сотрудников, у которых

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

значений, которые заданы в явном виде, можно проверить с помощью условного оператора IN ( 〈 список 〉 )

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

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

Задание 9. По таблице EMPLOYEES получить список сотрудников, у которых зарплата находится в замкнутом диапазоне [3000$, 7000$].Принадлежность

Слайд 13Отсутствующие (пустые) значения (NULL) имеют ряд особенностей при выполнении обычных

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

операция с NULL всегда дает NULL.
В частности, если значение Х неизвестно (NULL), то сравнение Х=Х не дает значения «истина».
Логика здесь простая: при сравнении «неизвестно чего» и результат «неизвестно какой».
По этой причине мы не получим желаемого результата с помощью следующей директивы:

SELECT last_name FROM employees
WHERE commission_pct=NULL

Отсутствующие (пустые) значения (NULL) имеют ряд особенностей при выполнении обычных операций с такими значениями.В упрощенном виде можно

Слайд 14Проверку на отсутствие значения выполняет специальный оператор IS:
При построении сложных

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

FROM employees
WHERE commission_pct IS NULL

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

Проверку на отсутствие значения выполняет специальный оператор IS:При построении сложных (составных) условий применяют логические операции AND, OR

Слайд 15Это связано с тем, что любой из операндов может иметь

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

правила трехзначной логики.

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

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


Это связано с тем, что любой из операндов может иметь неизвестное значение ( NULL ).Как следствие, в

Слайд 16При построении сложных условных выражений нужно учитывать приоритетность отдельных операций,

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

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

Слайд 174) Сортировка результатов запроса
SELECT 〈 список_выбора 〉 FROM 〈 имя_исх_таб

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

имя столбца (или псевдоним) из списка выбора.
Вместо этого можно указать порядковый номер элемента из списка SELECT.
Порядок сортировки определяют указатели:
ASC – возрастающий порядок (по умолчанию);
DESC – убывающий порядок.
Если имеется несколько ключей сортировки, то каждый следующий ключ вступает в действие при одинаковых значениях всех предыдущих ключей.
4) Сортировка результатов запросаSELECT 〈 список_выбора 〉  FROM 〈 имя_исх_таб 〉 ORDER BY 〈 Ключи_сортировки 〉

Слайд 18Задание 12. По таблице EMPLOYEES получить список сотрудников, у которых

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

фамилии (last_name). Список отсортировать сначала по коду подразделения, а в пределах одного подразделения – по фамилии.

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

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

Задание 12. По таблице EMPLOYEES получить список сотрудников, у которых зарплата не ниже 10000$, с указанием кода

Слайд 19Встроенные функции в СУБД Oracle разделяются на несколько категорий:
символьные функции

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

числовыми данными;
функции для работы с датой и временем;
функции преобразования типов данных и др.
Некоторые из перечисленных функций рассмотрим с помощью упражнений.
Встроенные функции в СУБД Oracle разделяются на несколько категорий:символьные функции – манипулируют со строками символов;числовые функции –

Слайд 20Функция UPPER ( 〈str〉 ) – возвращает представление исходной строки

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

список сотрудников, у которых первая буква фамилии (last_name) находится в интервале от ‘F’ до ‘K’. Одновременно для каждого сотрудника получить идентификатор, который объединяет 3 первых символа имени (first_name) и 2 первых символа фамилии (все в виде заглавных букв).

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

Функция UPPER ( 〈str〉 ) – возвращает представление исходной строки 〈str〉 с использованием заглавных букв.Задание 13. По

Слайд 21Функция LENGTH( 〈str〉 ) – возвращает длину (число знаков) исходной

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

месяцев между датами 〈d1〉 и 〈d2〉.
Функция SYSDATE – возвращает текущую дату (аргументы отсутствуют).

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

Функция LENGTH( 〈str〉 ) – возвращает длину (число знаков) исходной строки 〈str〉.Функция MONTHS_BETWEEN ( 〈d1〉, 〈d2〉 )

Слайд 22Функция TO_CHAR ( 〈expr〉 [, 〈fmt〉] ) – дату/время или

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

строку с учетом формата 〈fmt〉.

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

Функция TO_CHAR ( 〈expr〉 [, 〈fmt〉] ) – дату/время или числовое значение, полученное с помощью выражения 〈expr〉,

Слайд 23Функция NVL ( 〈expr1〉 , 〈expr2〉 ) – если выражение

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

〈expr2〉.

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

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

Функция NVL ( 〈expr1〉 , 〈expr2〉 ) – если выражение 〈expr1〉 имеет неопределенное значение (NULL), то вместо

Слайд 24Функция DECODE – производит условную замену конкретных величин.
При этом рамках

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

〈expr〉, 〈s_val1〉, 〈res1〉
[ , 〈s_val2〉, 〈res2〉
[ , … ] ]
[ , 〈res_def〉 ] )

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

Функция DECODE – производит условную замену конкретных величин.При этом рамках директивы языка SQL реализуется логика IF-THEN-ELSE.Синтаксис обращения

Слайд 25Если ни одного совпадения не произошло, то результатом будет 〈res_def〉,

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

также аналогичная функция CASE.

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

Если ни одного совпадения не произошло, то результатом будет 〈res_def〉, а если этот элемент не указан, то

Слайд 265) Групповые (агрегатные) функции
При получении результата функции этого типа работают

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

также для подсчета итогов.

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

5) Групповые (агрегатные) функцииПри получении результата функции этого типа работают с группой записей.Очень часто это требуется при

Слайд 27При записи аргумента можно указать DISTINCT, чтобы учесть только различающиеся

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

Для сотрудников, у которых стаж работы в компании не превышает 15 лет, с помощью запроса по таблице EMPLOYEES найти:
их число;
минимальное, максимальное и среднее значение по окладу (SALARY);
суммарную месячную зарплату с учетом комиссионной надбавки.
При записи аргумента можно указать DISTINCT, чтобы учесть только различающиеся значения.Особый случай: COUNT(*) возвратит общее число записей

Слайд 286) Запросы с группировкой
Часто при работе с табличными данными нужно

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

попадали записи с одинаковыми значениями для заданных атрибутов (ключи группировки).
В этом случае применяется следующая команда:

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

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

6) Запросы с группировкойЧасто при работе с табличными данными нужно выполнить их группировку, т.е. сделать так, чтобы

Слайд 29В частности, любой элемент списка выбора в разделе SELECT должен

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

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

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

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

Слайд 30Для отбора определенных групп по некоторому условию служит раздел HAVING.
Это

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

записей.
Раздел HAVING может применяться только вместе с разделом GROUP BY.

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

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

Для отбора определенных групп по некоторому условию служит раздел HAVING.Это происходит по аналогии с разделом WHERE, когда

Слайд 31Задание 22. Получить список департаментов, в которых число низкооплачиваемых сотрудников

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

департаментов, в которых число сотрудников больше 5:

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

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

Слайд 32В разделе WHERE оператора SELECT может присутствовать вложенный запрос.
Результат выполнения

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

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

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

SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE SALARY >

( SELECT AVG (SALARY)
FROM EMPLOYEES )

В разделе WHERE оператора SELECT может присутствовать вложенный запрос.Результат выполнения этого внутреннего запроса (подзапроса) передается внешнему запросу.Рассмотрим

Слайд 33Следующий пример показывает, как скалярный подзапрос используется в арифметическом выражении

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

выше среднего указать также коэффициент превышения.

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

SELECT FIRST_NAME, LAST_NAME ,

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

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

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

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

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

Sales и IT:

SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE department_id IN

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

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

Слайд 35В списке, который формирует вложенный подзапрос, могут быть составные элементы

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

департамента должны быть только сотрудники с самым высоким окладом (элита).

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

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

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

Слайд 36Еще один возможный вариант использования вложенного подзапроса демонстрируется следующим примером.
Задание

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

по всей компании.

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

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

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

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

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

штате менеджера отдела продаж (department_name='Sales').

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

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

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

Слайд 38Пример. Получить список департаментов (с указанием department_id и department_name), которые

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

другие варианты.

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

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

Пример. Получить список департаментов (с указанием department_id и department_name), которые размещаются на территории США (country_id ='US').Наряду с

Слайд 39Для последнего примера важно отметить одну принципиальную особенность:
внешний (основной) запрос

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

«сверху вниз».
В частности, при работе подзапроса нужно иметь конкретное значение location_id, которое передается из основного запроса.

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

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

Для последнего примера важно отметить одну принципиальную особенность:внешний (основной) запрос управляет работой вложенного подзапроса, т.е. здесь происходит

Слайд 40SELECT department_id, department_name
FROM DEPARTMENTS dps
WHERE not EXISTS
( SELECT *

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

проверить, дает ли подзапрос требуемый результат.

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

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

SELECT department_id, department_nameFROM  DEPARTMENTS dpsWHERE not EXISTS( SELECT * FROM EMPLOYEES WHERE department_id = dps.department_id )Логический

Слайд 41Запрос SELECT будет реализован с помощью операции соединения таблиц (JOIN),

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

синтаксис, относящийся к «старому стилю», определяет в неявном виде каждую операцию соединения двух таблиц.
Это связано с тем, что дополнительно требуется указать логическое выражение, которое должно содержаться в разделе WHERE.
Здесь задаются условия для соединения отдельных строк (записей) из исходных таблиц.
Тем самым конкретно определяется вид соединения.

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

Запрос SELECT будет реализован с помощью операции соединения таблиц (JOIN), если в разделе FROM через запятую перечислить

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

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

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

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

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

В этом запросе для сокращенного обозначения таблиц используются их псевдонимы – E и J.Наряду с условием сцепления

Слайд 43Задание 27. По таблицам DEPARTMENTS и LOCATIONS получить список департаментов,

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

из исходных таблиц связываются по условию совпадения значений в заданных полях, относятся к классу внутренних соединений.
Их называют также эквисоединениями (equijoin), т.е. соединениями по условию равенства.
Чтобы получить внешнее соединение, нужно к имени поля, в котором ожидается отсутствие совпадающих значений, добавить (+).
Задание 27. По таблицам DEPARTMENTS и LOCATIONS получить список департаментов, которые размещены на территории США (country_id ='US').Соединения,

Слайд 44Пример. По таблицам DEPARTMENTS и EMPLOYEES получить список сотрудников для

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

на данный момент сотрудники отсутствуют.

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 можно произвести группировку результатов соединения таблиц.

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

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

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

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

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

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

Слайд 46FROM employees S, employees B
SELECT S.last_name || ' ' ||

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

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

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

FROM employees S, employees BSELECT S.last_name || ' ' || S.first_name Slave, B.last_name || ' ' ||

Слайд 47Эта конструкция применяется для соединения таблиц 〈left_tab〉 и 〈right_tab〉.
При этом

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

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

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

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

Эта конструкция применяется для соединения таблиц 〈left_tab〉 и 〈right_tab〉.При этом условие 〈join_cond〉 для соединения строк переносится внутрь

Слайд 48[ INNER ] JOIN — внутреннее соединение (применяется по умолчанию);
LEFT

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

] JOIN — правое внешнее соединение;
FULL [ OUTER ] JOIN — полное внешнее соединение.

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

[ INNER ] JOIN — внутреннее соединение (применяется по умолчанию);LEFT [ OUTER ] JOIN — левое внешнее

Слайд 49Пример. Запрос из задания 27 можно записать в следующем виде:
SELECT

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

= L.location_id)
WHERE country_id='US';

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

FROM departments JOIN locations USING (location_id)

Пример. Запрос из задания 27 можно записать в следующем виде:SELECT  department_id, department_nameFROM  departments D JOIN

Слайд 50Задание 30. По таблицам DEPARTMENTS и EMPLOYEES с помощью внешнего

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

который в данном случае будет наиболее подходящим.

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

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

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

Задание 30. По таблицам DEPARTMENTS и EMPLOYEES с помощью внешнего соединения получить список сотрудников для каждого департамента.

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

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

Sal_Grade:

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

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

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

Слайд 52SELECT Low_limit, High_limit, count(salary) Freq
FROM employees JOIN sal_grade ON (salary BETWEEN

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

предложить и другие варианты решения рассмотренной задачи (в том числе без операции JOIN), но этот вариант имеет некоторые важные достоинства.
Самое главное преимущество заключается в том, что пользователь может самостоятельно заходить в таблицу Sal_Grade и менять ее содержимое.
SELECT Low_limit, High_limit, count(salary) FreqFROM employees JOIN sal_grade ON (salary BETWEEN Low_limit AND High_limit)GROUP BY  Low_limit,

Слайд 53Такая конструкция подразумевает, что при соединении таблиц равенство значений будет

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

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

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

〈left_tab〉 〈 NATURAL JOIN 〉 〈right_tab〉

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

Слайд 54По правилам NATURAL JOIN здесь формально применяется следующее условие соединения

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

last_name, first_name
FROM departments NATURAL JOIN employees
ORDER BY d_name;

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

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

По правилам NATURAL JOIN здесь формально применяется следующее условие соединения строк из рассматриваемых таблиц:Реальный пример такой опасности:SELECT

Слайд 55В результате получаем список только тех сотрудников, у которых непосредственный

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

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

Слайд 56Чтобы комбинировать результаты (наборы данных), которые получены от нескольких запросов,

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

над множествами:
объединять (union), пересекать (intersect), исключать (except или minus).
В упрощенном виде обращение к этим операциям (для случая двух запросов) выглядит так:

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

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

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

Слайд 57Основные правила для этих операций:
все исходные наборы данных должны иметь

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

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

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

Основные правила для этих операций:все исходные наборы данных должны иметь одинаковую структуру (с точностью до совместимости типов

Слайд 58SELECT first_name, last_name
FROM employees E JOIN departments D ON (E.employee_id=D.manager_id)
SELECT

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

FROM employees );

UNION

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

SELECT first_name, last_nameFROM employees E JOIN departments D ON (E.employee_id=D.manager_id)SELECT  first_name, last_nameFROM  employeesWHERE  employee_id

Обратная связь

Если не удалось найти и скачать доклад-презентацию, Вы можете заказать его на нашем сайте. Мы постараемся найти нужный Вам материал и отправим по электронной почте. Не стесняйтесь обращаться к нам, если у вас возникли вопросы или пожелания:

Email: Нажмите что бы посмотреть 

Что такое TheSlide.ru?

Это сайт презентации, докладов, проектов в PowerPoint. Здесь удобно  хранить и делиться своими презентациями с другими пользователями.


Для правообладателей

Яндекс.Метрика