Запрос, содержащий соотнесенный запрос, выполняется следующим образом.
Шаг 1. Выбирается строка из таблицы, именованной во внешнем запросе. Эта строка называется текущей строкой-кандидатом.
Шаг 2. Значение из этой строки-кандидата запоминается.
Шаг 3. Выполняется подзапрос, причем во всех местах появления имени таблицы из внешнего запроса используется значение из текущей строки-кандидата.
Шаг 4. Оценивается предикат внешнего запроса и в случае его истинности выводится строка-кандидат.
Процедура повторяется для всех строк таблицы.
Для того чтобы ссылаться к одной и той же таблице во внутреннем и внешнем запросах, используют разные псевдонимы этой таблицы.
Подзапросы с ANY (любой), SOME (некоторый), ALL (все)
В случае использования оператора ALL предикат является истинным, если каждое из значений, выбранных подзапросом, удовлетворяет условию в предикате запроса.
Пример 2. выведем данные на тех детей, фамилии которых предшествуют по алфавиту фамилиям детей с 1995 годом рождения:
SELECT * FROM РЕБЕНОК
WHERE ФИО_Р < ALL (SELECT ФИО_Р FROM РЕБЕНОК
WHERE Год_рождения = 1995);
Отличие между ALL и SOME (ANY) – действие в ситуации, когда подзапрос не возвращает никаких значений: если множество результатов подзапроса пусто, ALL истинен, а SOME (ANY) ложен.
Слово ALL, которое должно следовать за операцией сравнения, означает "возвратить TRUE, если сравнение дает TRUE для всех строк, возвращаемых подзапросом".
Использование оператора EXISTS
EXISTS - это оператор, который производит верное или неверное значение. Оператор EXISTS используется, чтобы оценить факт истинности вывода для соотнесенного подзапроса. Подзапрос рассматривается как аргумент и оценивается как истинный, если тот производит любой вывод или как неистинный – в противном случае. Оператор EXISTS отличается от других операторов тем, что он не может быть неизвестным.
Пример 4.
SELECT *
FROM Студенты
WHERE EXISTS
(SELECT *
FROM Группа
WHERE специальность = ‘230115');
Пример 5. Выведем данные на студентов, живущих по одному адресу:
SELECT * FROM СТУДЕНТ первый
WHERE EXISTS (SELECT * FROM СТУДЕНТ второй
WHERE первый.Адрес = второй.Адрес
AND первый.ФИО_НТ < > второй.ФИО_НТ);
4. Практические указания:
Использование подзапросов, возвращающих одно значение
4.1.1. Выведите информацию об авторах книги под названием «Ваше название книги ».
4.1.2. Подзапрос с DISTINCT: Произведите вывод списка книг, которые были взяты пользователями библиотеки.
4.1.3. Агрегатные функции в подзапросах: Выведите на экран все книги, которые имеют стоимость, большую средней стоимости всех книг.
4.1.4. Найдите все книги библиотеки, которые имеет стоимость такую же, как книга «Война и мир».
4.1.5. HAVING: Выведите список количеств книг каждого автора, год рождения которых от 1900 до 1920.
4.2. Подзапросы с IN
4.2.1. Выведите список авторов книг, которые написали книги жанра «фантастика».
4.2.2. Найти имена авторов, которые принимали участие в написании, по крайней мере, одной книги, выданной пользователям библиотеки категории студент.
4.2.3. Напишите запрос на изменение записей, который удвоит цену всех книг, изданных издательством Дрофа.
4.2.4. Напишите запрос на удаление всех данных о выдачи книг автора Чуковский К.И.
Подзапросы с ANY, SOME, ALL
4.3.1. ANY: Напишите запрос для определения списка книг, которые взяли хотя бы один пользователь.
4.3.2. SOME: Напишите запрос для вывода списка пользователей библиотеки, год рождения которых по алфавиту больше года рождения читателей категории студент.
4.3.3. ALL. найти книги, которые стоят больше чем самая дорогая книга жанра Роман.
4.4. Подзапросы с EXISTS, NOT EXISTS
4.4.1. Напишите подзапрос для вывода фамилий авторов, которые пишут книги одного или более жанров.
4.4.2. Напишите запрос, для нахождения списка книг, которые на взяли ни один пользователь.
4.4.3. Напишите запрос для вывода списка книг, которые были взяты хотя бы одним пользователем.
4.4.4. Напишите запрос для вывода фамилий авторов без повторений, которые написали книги каждого жанра.
5. Содержание отчёта по практической работе
5.1. Цель работы.
5.2. Результаты выполнения работы;
5.3. Выполненные работы в электронном виде представляются на сетевом диске H (X)
6. Контрольные вопросы
6.1. Функции работы с датой и временем.
6.2. Функции работы со строками.
7. Список рекомендуемой литературы:
7.1. Агальцов В.П. Базы данных. Распределённые и удалённые базы данных. Учебник М.: ИД «Форум»: ИНФРА-М, 2011. - 272с.
7.2. Дунаев В.В. Базы данных. Язык SQL. СПб.: БХВ-Петербург-2006, 288с.
7.3. Дюбуа П. MySQL.: учеб.пособие.- М.-:Вильямс,2007.- 1168с.
7.4. Кузнецов М.В., MySQL 5, СПб.: БХВ-Петербург, 2010, 1024с.
7.5. Петров В.Н. Избачков Ю.С., Информационные системы. СПб.: Питер, 2006, 656с.
7.6. Шелдон Р. MySQL.: учеб.пособие.- М.-:Вильямс,2007.- 880с.