Кудравцев Создание баз данных 2010
.pdfскольких строках. Настоятельно рекомендуется структурировать запрос, располагая вложенные подзапросы с некоторым отступом и размещая ключевые слова FROM, WHERE и т.д. под тем ключевым словом SELECT, к которому они относятся. В этом случае его легче понимать и анализировать.
SELECT fio FROM student
WHERE EXISTS (SELECT travel.st_id FROM travel, country
WHERE travel.c_id=country.c_id AND travel.st_id=student.st_id AND country.name='GreatBritain'
)
Рассматривая данный запрос нетрудно заметить, что он состоит из главного запроса
SELECT fio FROM student
и подзапроса
SELECT travel.st_id FROM travel, country
WHERE travel.c_id=country.c_id AND travel.st_id=student.st_id AND country.name='GreatBritain'.
Подзапрос формирует таблицу, состоящую из одного столбца travel.st_id (идентификатор студента). Строками данной таблицы будут идентификаторы студентов побывавших в Великобритании.
В предложении WHERE содержится условие travel.st_id=student.st_id,
в котором фигурирует значение st_id из таблицы student внешнего запроса (таблица student указана в предложении FROM внешнего запроса). Имеет место так называемая корреляция основного запроса и подзапроса. Порядок выполнения коррелированных запросов следующий:
1.Из внешнего запроса выбирается первая строка (т.е. информация о первом студенте),
2.Выполняется подзапрос для значения student.st_id из первой строки. Подзапрос вернет непустую таблицу, если этот студент ездил в Великобританию и, следовательно, квантор существо-
141
вания (предикат) EXISTS будет иметь значение true (истина). Если студент не ездил в Великобританию, то подзапрос вернет пустую таблицу и квантор существования EXISTS будет иметь значение false (ложь).
3.Фамилия студента из первой строки будет помещена в результирующую таблицу, если значение квантора существования EXISTS окажется истинным.
4.Из внешнего запроса выбирается вторая строка, и весь процесс
повторяется заново. Так продолжается для всех строк из таб-
лицы student.
Следует отметить, что указание таблицы student в подзапросе приведет к неверной выборке информации.
Запрос 2
Перечислить студентов, которые были во Франции и Великобритании
Данный запрос очень похож на предыдущий, но здесь добавляется еще один подзапрос с помощью которого выбирается информация о посещении студентом Франции. Два предиката EXISTS объединяются в одно логическое условие с помощью оператора
AND:
SELECT fio FROM student
WHERE EXISTS (SELECT travel.st_id FROM travel, country
WHERE travel.c_id=country.c_id AND travel.st_id=student.st_id AND country.name='GreatBritain'
)
AND
EXISTS (SELECT travel.st_id FROM travel, country
WHERE travel.c_id=country.c_id AND travel.st_id=student.st_id AND country.name='France'
)
142
Если СУБД поддерживает операцию пересечения INTERSECT (например, PostgreSQL – поддерживает, а MS Access 2003 – нет), то данный запрос можно представить как
SELECT DISTINCT fio FROM student, travel, country
WHERE student.st_id=travel.st_id AND travel.c_id=country.c_id AND country.name='GreatBritain'
INTERSECT
SELECT DISTINCT fio FROM student, travel, country
WHERE student.st_id=travel.st_id AND travel.c_id=country.c_id AND country.name='France'
Здесь каждый из запросов выбирает фамилии студентов ездивших или в Великобританию или во Францию. Пересечение этих таблиц (множеств) будет давать ответ на поставленный запрос.
И, наконец, рассмотрим способ ответа на данный запрос с помощью предиката IN.
SELECT fio FROM student
WHERE student.st_id IN (SELECT travel.st_id FROM travel, country
WHERE travel.c_id=country.c_id AND country.name='GreatBritain'
)
AND
student.st_id IN (SELECT travel.st_id FROM travel, country
WHERE travel.c_id=country.c_id AND country.name='France'
)
Подзапросы обязательно должны возвращать таблицу из одного столбца, причем тип поля этой таблицы должен быть аналогичен типу левого операнда в предикате IN. Следует отметить отсутствие
143
корреляции между внешним и внутренними подзапросами, так как внутренние подзапросы могут быть выполнены автономно вне зависимости от значения st_id из таблицы student. Отсутствие корреляции приводит к уменьшению времени выполнения запроса.
Запрос 3
Перечислить студентов, которые были только один раз во Франции и только один раз в Великобритании
Реализуем данный запрос с помощью агрегатной функции
COUNT():
SELECT fio FROM student
WHERE (SELECT COUNT(travel.st_id) FROM travel, country
WHERE travel.c_id=country.c_id AND travel.st_id=student.st_id AND country.name='GreatBritain'
)=1 AND
(SELECT COUNT(travel.st_id) FROM travel, country
WHERE travel.c_id=country.c_id AND travel.st_id=student.st_id AND country.name='France'
)=1
Подзапросы коррелированны с основным запросом, поскольку в условиях отбора WHERE фигурирует поле st_id из таблицы student внешнего запроса.
В подзапросах с помощью функции COUNT(travel.st_id) производится подсчет количества строк в результирующей таблице, т.е. количества поездок для каждого студента в Великобританию или во Францию. Так как подзапросы возвращают таблицу, состоящую из одной строки и одного столбца, можно выполнять непосредственной сравнение с заданным значением, в данном случае — с единицей.
144
Запрос 4
Перечислить количество туристических поездок каждого студента. Для тех, кто ни разу никуда не ездил, указать ноль
Для решения данной задачи объединим с помощью UNION два запрос. В первом запросе подсчитаем с помощью функции COUNT количество поездок каждого студента, а во втором выберем студентов, которые никуда не ездили:
SELECT fio, COUNT(travel.c_id) FROM student, travel
WHERE student.st_id=travel.st_id GROUP BY student.fio
UNION SELECT fio, 0 FROM student
WHERE NOT EXISTS (SELECT * FROM travel
WHERE student.st_id=travel.st_id
)
ORDER BY fio
Во втором запросе имеются коррелированный подзапрос и отрицание квантора существования EXISTS. В этом случае комбинация NOT EXISTS будет возвращать истину, если множество
SELECT * FROM travel
WHERE student.st_id=travel.st_id
будет пустым, т.е. выбранный студент не совершил ни одного путешествия.
Конструкция ORDER BY fio приведет к сортировке результирующей таблицы по фамилиям по алфавиту.
Запрос 5
Перечислить количество стран, которые посетил каждый студент. Для тех, кто ни разу никуда не ездил, указать ноль
SELECT fio, COUNT(DISTINCT travel.c_id)
FROM student, travel
145
WHERE student.st_id=travel.st_id GROUP BY student.fio
UNION SELECT fio, 0 FROM student
WHERE NOT EXISTS (SELECT * FROM travel
WHERE student.st_id=travel.st_id
)
ORDER BY fio
Данный запрос отличается от запроса 4 только тем, что при аргументе функции COUNT(DISTINCT travel.c_id) указано ключевое слово DISTINCT, которое исключает из списка одинаковые значения.
Запрос 6
Перечислить студентов, которые посетили максимальное количество стран (таких студентов может быть несколько)
SELECT fio FROM student
WHERE student.st_id IN (SELECT travel.st_id FROM travel
GROUP BY travel.st_id
HAVING COUNT(DISTINCT travel.c_id)= (SELECT MAX(cn)
FROM (SELECT COUNT(DISTINCT travel.c_id) AS cn
FROM travel
GROUP BY travel.st_id ) AS sel_cn
)
)
В данном запросе имеется несколько подзапросов. Прежде всего, рассмотрим самый внутренний подзапрос 1:
146
SELECT COUNT(DISTINCT travel.c_id) AS cn
FROM travel
GROUP BY travel.st_id
в котором используется только одна таблица travel. Группи-
ровка строк по полю st_id и использование агрегатной функции COUNT(DISTINCT travel.c_id) позволяет подсчитать количество стран, которые посетил каждый студент. Результатом данного запроса будет таблица, состоящая из одного столбца. Количество строк будет равно количеству студентов совершивших хотя бы одно путешествие. Значениями таблицы будут числа равные числу стран, которые посетил тот или иной студент. В данной таблице нет информации о студентах (их идентификаторах), но они и не нужны, так как результаты этого подзапроса используются только для определения максимального значения с помощью агрегатной функции MAX в подзапросе 2:
SELECT MAX(cn)
FROM (SELECT COUNT(DISTINCT travel.c_id) AS cn FROM travel
GROUP BY travel.st_id ) AS sel_cn
В подзапросе 3
SELECT travel.st_id FROM travel
GROUP BY travel.st_id
HAVING COUNT(DISTINCT travel.c_id) = max_travel
снова выполняется группировка строк таблицы travel по полю st_id, но для отбора групп, в предложении HAVING записано условие:
COUNT(DISTINCT travel.c_id) = max_travel
где max_travel вычисляется с помощью подзапроса 2. Результатом выполнения подзапроса 3 является таблица, состоящая из одного
147
столбца и содержащая идентификаторы студентов, побывавших в максимальном количестве стран.
Внешний запрос выбирает из таблицы student фамилии студентов с помощью предиката IN (множество для этого предиката формирует подзапрос 3).
Запрос 7
Подсчитать среднее количество поездок, приходящееся на каждого студента группы и перечислить студентов, которые совершили количество поездок выше среднего
Данный запрос можно реализовать следующим образом:
SELECT fio, COUNT(travel.c_id) FROM student, travel
WHERE student.st_id=travel.st_id GROUP BY student.fio
HAVING COUNT(travel.c_id) >= (SELECT AVG(Cn_Tr)
FROM (SELECT fio, COUNT(travel.c_id) AS Cn_Tr FROM student, travel
WHERE student.st_id=travel.st_id GROUP BY student.fio
UNION
SELECT fio, 0 AS Cn_Tr FROM student
WHERE NOT EXISTS (SELECT * FROM travel
WHERE student.st_id=travel.st_id
)
) AS Sel_Cn
)
Для вычисления среднего количества поездок, приходящегося на каждого студента, используется агрегатная функция AVG(). Для того чтобы учесть студентов, которые вообще никуда не ездили, используется объединение двух подзапросов с помощью оператора UNION. Отбор подходящих студентов выполняется с помощью условия, записанного в предложении HAVING.
148
Запрос 8
Перечислить студентов, которые совершили поездки во все страны, представленные в таблице country
Запросы подобного вида могут быть реализованы путем использования двойного отрицания квантора существования EXISTS. Для этого запрос 8 можно записать так:
Перечислить студентов, для которых НЕ существует стран (из таблицы country), куда бы они НЕ совершили поездки.
Двойное отрицание существования можно представить в следующем виде:
SELECT fio FROM student
WHERE NOT EXISTS (SELECT * FROM country
WHERE NOT EXISTS (SELECT * FROM travel
WHERE travel.c_id=country.c_id AND travel.st_id=student.st_id
)
)
Здесь имеется внешний запрос, содержащий коррелированный подзапрос 1:
SELECT * FROM country
WHERE NOT EXISTS (SELECT * FROM travel
WHERE travel.c_id=country.c_id AND travel.st_id=student.st_id
)
в котором содержится еще один коррелированный подзапрос 2:
149
SELECT * FROM travel
WHERE travel.c_id=country.c_id AND travel.st_id=student.st_id
Подзапрос 1 возвращает множество (таблицу) стран, в которые студент не ездил, а подзапрос 2 — множество (таблицу) поездок конкретного студента в конкретную страну.
Порядок выполнения данного запроса следующий:
1.Из внешнего запроса выбирается первая строка (т.е. информация о первом студенте),
2.Выполняется подзапрос 1 для значения student.st_id из первой строки таблицы student. Данный подзапрос возвращает множество стран (строки из таблицы country) в которые этот студент не ездил.
3.Для выполнения подзапроса 1 необходимо для каждой строки таблицы country выполнить подзапрос 2. Данный подзапрос возвращает множество строк из таблицы travel, если студент ездил в указанную страну. Если студент не ездил в указанную страну, то множество будет пустым. Страна попадет в результирующее множество подзапроса 1, если множество из подзапроса 2 будет пустым и наоборот.
4.Если подзапрос 1 вернет пустое множество, то отрицание квантора существования NOT EXISTS будет иметь значение true (истина) и фамилия студента из первой строки будет помещена в результирующую таблицу, в противном случае фамилия студента будет пропушена.
5.Из внешнего запроса выбирается вторая строка, и весь процесс повторяется заново. Так продолжается для всех строк из таблицы student.
Ив заключение приведем без комментариев довольно большой и сложный запрос.
Запрос 9
Перечислить студентов с максимальным приростом числа поездок в 2009 г. по сравнению с 2008-м
150