Использование подзапросов

Использование подзапросов
Урок 2B / Слайд из 32
СРС 4_4
Запишите команду SQL для извлечения имен всех работников из таблицы Employee записанныые строчными буквами.
Какая функция возвращает начальную позицию данного образца в строке?
Какая функция удаляет начальные пробелы из символьного выражения?
Записать функцию, которая добавит 10 дней к текущей дате.
Какие бывают различные типы соединений?

Использование подзапросов
Урок 2B / Слайд из 32
Подзапросы
Подзапрос может быть определен как запрос SELECT возвращающий одно значение.
Подзапросы вкладываются в команду SELECT, INSERT, UPDATE или DELETE.
Подзапросы могут применятся для извлечения данных из нескольких таблиц и использоваться как альтернатива соединению.
Они также могут использоваться внутри выражения WHERE или HAVING команд SELECT, INSERT, UPDATE и DELETE.

Использование подзапросов
Урок 2B / Слайд из 32
Пример применения одного запроса в
другом запросе
Формулировка задачи
Вывести список вербовщиков, проживающих в том же городе, что и соискатель Барбара Джонсон.

Использование подзапросов
Урок 2B / Слайд из 32
Решение
1. Создать формат выхода запроса.
2. Составить запрос.
3. Выполнить запрос.
4. Проверить, что выход запроса соответствует требуемым результатам.

Использование подзапросов
Урок 2B / Слайд из 32
Создание формата выхода запроса
Результат
Требуемым выходом запроса является имена вербовщиков, которые проживают в том же городе, что и ‘Барбара Джонсон’
Требуемые данные представлены в таблицах ContractRecruiter (вербовщики) и ExternalCandidate (соискатель).

Использование подзапросов
Урок 2B / Слайд из 32
Составление запроса
Результат
Требуемая информация доступна в таблицах ExternalCandidate и ContractRecruiter.
Т.е. запрос, использующий команду SELECT, должен быть следующим:
SELECT cName
FROM ContractRecruiter
WHERE cCity = (SELECT cCity
FROM ExternalCandidate
WHERE vFirstName = 'Barbara'
AND vLastName = 'Johnson')

Использование подзапросов
Урок 2B / Слайд из 32
Выполнение запроса
Действие
В окне Query Analyzer набрать запрос
SELECT cName
FROM ContractRecruiter
WHERE cCity = (SELECT cCity FROM ExternalCandidate
WHERE vFirstName = 'Barbara'
AND vLastName = 'Johnson')
Выполнение запроса

Использование подзапросов
Урок 2B / Слайд из 32
Проверка выхода запроса на соответствие требуемым результатам
Действие
Проверить, что:
Отображены требуемые столбцы

Использование подзапросов
Урок 2B / Слайд из 32
Дополнительные сведения о подзапросах
Подзапросы с IN
Подзапрос, введенный с IN возвращает (не)нулевые значения.
Пример
SELECT Au_Id
FROM TitleAuthor
WHERE Title_Id IN
(SELECT Title_Id FROM Sales)

Использование подзапросов
Урок 2B / Слайд из 32
Доп. сведения о подзапросах (продолжение)
Подзапросы с EXISTS
Подзапрос, используемый с выражением EXISTS, всегда возвращает данные в виде значения TRUE или FALSE.
Пример
SELECT Pub_Name
FROM Publishers
WHERE EXISTS (SELECT * FROM Titles
WHERE Type = 'business')

Использование подзапросов
Урок 2B / Слайд из 32
Доп. сведения о подзапросах (продолжение)
Подзапросы с агрегированными (обобщенными) функциями
Агрегированные (обобщенные) функции также могут использоваться в подзапросах.
Пример
SELECT Title
FROM Titles
WHERE Advance > (SELECT AVG(Advance)
FROM Titles
WHERE Type = 'business')

Использование подзапросов
Урок 2B / Слайд из 32
Доп. сведения о подзапросах (продолжение)
Ограничения подзапросов
SQL Server ограничивает использование некоторых методов и способов, и задает реализацию некоторых стандартов при использовании подзапросов. Налагаемыми ограничениями являются:
Список столбца команды SELECT в запросе, введенном с оператором сравнения, может включать только один столбец.
Столбец, используемый в выражении WHERE внешнего запроса должен быть совместимым со столбцом, используемым в списке SELECT внутреннего запроса.
Выражение ORDER BY и выражение GROUP BY не могут использоваться во внутреннем запросе, когда в основном запросе применяются =, !=, <, <=, > или >=, поскольку внутренний запрос может вернуть более чем одно значение, которое не сможет быть перехвачено внешним запросом.

Использование подзапросов
Урок 2B / Слайд из 32
Доп. сведения о подзапросах (продолжение)
Вложенные подзапросы
Подзапрос может сам содержать один или более подзапросов.
Пример
SELECT 'Author Name' = SUBSTRING
(Au_Fname, 1, 1) + '. '+ Au_Lname
FROM Authors
WHERE Au_Id IN (SELECT Au_Id
FROM TitleAuthor
WHERE Title_Id =(SELECT
Title_Id FROM Titles
WHERE Title = 'Net Etiquette'))

Использование подзапросов
Урок 2B / Слайд из 32
Доп. сведения о подзапросах (продолжение)
Связанные подзапросы
Могут быть определены как запросы, зависящие от внешнего запроса для своего вычисления
Пример
SELECT Title, Type, Advance
FROM Titles t1
WHERE t1.Advance > (SELECT AVG(t2.Advance)
FROM Titles t2 WHERE
t1.Type = t2.Type)
Запросы с модифицированными операторами сравнения
В SQL Server есть ключевые слова ALL и ANY, которые могут применяться для модификации существующих операторов сравнения.

Использование подзапросов
Урок 2B / Слайд из 32
Пример выделения данных в
другую таблицу
Постановка задачи
Для проведения анализа биографий кандидатов, претендовавших на прием в мае 2010 года, необходимо скопировать их данные в новую таблицу.

Использование подзапросов
Урок 2B / Слайд из 32
Решение
1. Установить требования выхода запроса.
2. Составить запрос.
3. Выполнить запрос.
4. Проверить, что выход запроса соответствует требуемым результатам.

Использование подзапросов
Урок 2B / Слайд из 32
Установление требований запроса
Результат
Требуемым выходом запроса является передача данных из таблицы ExternalCandidate во временную таблицу, названную tempExternalCandidate.

Использование подзапросов
Урок 2B / Слайд из 32
Составление запроса
Команда SELECT INTO
Команда SELECT с выражением INTO применяется для сохранения результирующего множества в новую таблицу без процесса описания данных. Команда SELECT INTO создает новую тааблицу.
Синтаксис
SELECT список_столбцов
INTO имя_новой_таблицы
FROM имя_таблицы1, имя_таблицы2,………,
имя_таблицы n
WHERE условие1, условие2,……….,
условие n
Пример
SELECT Title_Id, Title
INTO NewTitles
FROM Titles
WHERE Price > $15

Использование подзапросов
Урок 2B / Слайд из 32
Составление запроса (продолжение)
Результат
Требуемая информация доступна в таблице ExternalCandidate
Т.е. запрос, использующий команду SELECT, должен быть следующим:
sp_dboption recruitment, 'select into/bulkcopy', true
SELECT * INTO tempExternalCandidate
FROM ExternalCandidate
WHERE DATEPART(mm,dDateOfApplication)= 5
AND DATEPART(yyyy,dDateOfApplication)= 2001

Использование подзапросов
Урок 2B / Слайд из 32
Выполнение запроса
Действие
В окне Query Analyzer набрать запрос
sp_dboption recruitment, 'select into/bulkcopy', true
SELECT * INTO tempExternalCandidate
FROM ExternalCandidate
WHERE DATEPART(mm,dDateOfApplication)= 5
AND DATEPART(yyyy,dDateOfApplication)= 2001
Выполнить запрос

Использование подзапросов
Урок 2B / Слайд из 32
Проверка выхода запроса соответствует требуемым результатам
Действие
Проверить, что:
В конечной таблице имеются все данные из исходной таблицы.

Использование подзапросов
Урок 2B / Слайд из 32
Пример объединения данных из двух
таблиц
задачи
Требуется вывести список вербовщиков и кадровых агентств вместе с контактными телефонами.

Использование подзапросов
Урок 2B / Слайд из 32
Решение
1. Создать формат выхода запроса.
2. Составить запрос.
3. Выполнить запрос.
4. Проверить, что выход запроса соответствует требуемым результатам.

Использование подзапросов
Урок 2B / Слайд из 32
Создание формата выхода запроса
Результат
Требуемым выходом запроса является единый список имен и номеров телефонов вербовщиков и кадровых агентств.

Использование подзапросов
Урок 2B / Слайд из 32
Составление запроса
Команда UNION
используется для комбинации результирующего множества двух или более запросов.
Синтаксис
SELECT column_list [INTO new_table_name]
[FROM clause] [WHERE clause]
[GROUP BY clause][HAVING clause]
[UNION [ALL]
SELECT column_list
[FROM clause] [WHERE clause]
[GROUP BY clause][HAVING clause]...]
[ORDER BY clause]
[COMPUTE clause]

Использование подзапросов
Урок 2B / Слайд из 32
Составление запроса (продолжение)
Результат
Требуемая информация доступна в таблицах ContractRecruiter и RecruitmentAgencies.
Т.е. запрос, использующий команду SELECT, должен быть следующим:
SELECT cName,cPhone
FROM ContractRecruiter
UNION
SELECT cName, cPhone
FROM RecruitmentAgencies

Использование подзапросов
Урок 2B / Слайд из 32
Выполнение запроса
Действие
В окне Query Analyzer набрать запрос
SELECT cName,cPhone
FROM ContractRecruiter
UNION
SELECT cName, cPhone
FROM RecruitmentAgencies
Выполнить запрос

Использование подзапросов
Урок 2B / Слайд из 32
Проверка выхода запроса на соответствие требуемым результатам
Действие
Проверить, что:
Отображены все требуемые столбцы.
Все строки из обоих таблиц выведены в виде одного списка.

Использование подзапросов
Урок 2B / Слайд из 32
Выводы
Из этого урока Вы узнали, что:
Подзапросы вкладываются в оператор SELECT, INSERT, UPDATE или DELETE.
Они также могут использоваться внутри выражений WHERE или HAVING внешних команд SELECT, INSERT, UPDATE и DELETE.
Подзапрос, введенный с IN или NOT IN возвращает (не)нулевые значения.
Подзапрос, применяемый с выражением EXISTS возвращает данные в виде значения TRUE или FALSE.
Подзапрос может сам содержать один или более подзапросов.
На количество подзапросов, которые можно включить с командами SELECT, INSERT, UPDATE или DELETE, ограничений нет.
Связанный подзапрос может быть определен как запрос, зависящий от внешнего запроса для своего вычисления.