Лабораторная работа № 6. Вложенные запросы, объединение результатов запросов, перекрестные запросы
Один запрос на SQL может включать в себя другой. Вложенный запрос может располагаться в разных секциях. Например, он может определять источник данных и размещаться в секции FROM:
SELECT StatusID
FROM (SELECT BookStatus.StatusID FROM BookStatus
WHERE StatusName Like 'в *')
WHERE StatusID>=2 ;
Здесь надо отметить, что хотя приведенный пример не противоречит синтаксису SQL и будет выполнен СУБД, с точки зрения эффективности скорее более предпочтительно реализовать ту же логику в виде
SELECT BookStatus.StatusID
FROM BookStatus
WHERE StatusName Like 'в *' AND StatusID>=2;
Задание. Используя базу из второй лабораторной работы, напишите запрос, который будет содержать выражение (подзапрос) SELECT в секции FROM. Проверьте его работу. Можно ли аналогичный запрос записать по-другому?
Если результат выполнения подзапроса является условием для другого запроса, такой подзапрос размещается в секции WHERE или HAVING. Например, нужно выбрать авторов книг, которых нет в нашей библиотеке (т.е. мы знаем о существовании подобных изданий, но ни одного экземпляра в таблице BooklnLib не описано):
SELECT DISTINCT Author
FROM Book
WHERE BookID NOT IN
(SELECT BookID FROM BooklnLib);
Задание. Напишите запрос, который выведет названия книг, ни одного экземпляра которых нет в учебном классе (т.е. ни у одного экземпляра текущий статус не равен "в учебном классе"). Обратите внимание, что в библиотеке может быть несколько экземпляров одного издания с разными статусами и ваш запрос должен корректно обрабатывать эту ситуацию.
Задание. Измените предыдущий запрос. Пусть теперь он выводит без повторений авторов книг, таких, что ни одной книги этого автора нет в учебном классе. При написании запроса надо учитывать, что у автора может быть несколько книг, каждая из которых может быть в нескольких экземплярах с разными статусами, поэтому запрос должен проверять, что ни у одного экземпляра ни одной книги данного автора нет указанного статуса.
Выражение EXISTS возвращает истину, если следующее после него множество содержит хотя бы один элемент. EXISTS часто используется с подзапросами. Например, так может выглядеть запрос, возвращающий список "используемых" статусов (т.е. таких, что этот статус есть хотя бы у одной книги):
SELECT BookStatus.StatusName
FROM BookStatus
WHERE EXISTS
(SELECT * FROM BooklnLib
WHERE BooklnLib.StatusID=BookStatus.StatusID) ;
Задание. Напишите аналогичный no смыслу запрос, используя выражение IN. Какой запрос и почему в подобных случаях будет эффективнее с точки зрения времени выполнения? (Если ответ на этот вопрос вызывает затруднение, прочитайте в параграфе 7.7 о коррелированной и некоррелированной обработке подзапросов.)
В некоторых случаях приходится иметь дело с запросами или таблицами с одинаковыми заголовками, данные из которых нужно объединить либо необходимо получить пересечение этих наборов данных или результат вычитания. Для этого в SQL служат следующие операторы:
- • UNION (объединение);
- • INTERSECT (пересечение) – не поддерживается Access;
- • EXCEPT (вычитание) – не поддерживается Access.
Например, следующий запрос даст объединение множеств строк таблиц Book и Book! (считаем, что заголовки у них совпадают):
SELECT * FROM Book UNION
SELECT * FROM Bookl;
Задание. В базе данных скопируйте таблицу Book и сохраните ее под именем Bookl. Измените набор записей таблицы Bookl, удалив из нее часть старых записей и добавив новые. Напишите запрос, возвращающий результат объединения множеств строк из этих таблиц. Замените UNION на UNION ALL, объясните разницу между этими операторами.
Перейдем к следующему классу запросов – перекрестным. Часто бывает, что необходимо представить не сами данные, а некоторую обобщенную аналитическую информацию. Пусть, например, нам надо подсчитать, сколько в каждом издательстве в каждом году было выпущено книг. Это можно сделать с помощью запроса с группировкой. Однако если в результирующем наборе названия издательств надо разместить в заголовках столбцов, потребуется перекрестный запрос. Чтобы создать его с помощью мастера запросов, нужно в первом окне мастера выбрать соответствующий тип (рис. П.6.1).
Рис. П.6.1. Создание перекрестного запроса
Далее в качестве источника данных выбираем таблицу Book, для формирования заголовков строк укажем поле BookYcar, столбцов – поле Publisher, а используемая функция – Число(ВоокID). Полученный результат представлен на рис. П.6.2.
Рис. П.6.2. Перекрестный запрос
Задание. С помощью мастера постройте запрос, выводящий для каждого автора и издательства первый год, когда в этом издательстве вышла книга данного автора. Издательства выводите в заголовках столбцов, итоговое значение по строкам не отображайте. После проверки работы запроса откройте его в режиме SQL. По справке ознакомьтесь с синтаксическими конструкциями, используемыми для создания перекрестного запроса.
