Лабораторная работа № 5. Написание запросов на языке SQL (ч. 2)

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

Результат, возвращаемый запросом, может содержать вычисляемые поля или константы. Название такого поля можно указать в списке выводимых столбцов после соответствующего значения через ключевое слово AS (стандарт SQL допускает указание и через пробел, без AS, но Access в подобных случаях выдает ошибку). Если название такому столбцу явно не задать, Access автоматически сгенерирует название, но для пользователя такой вариант менее удобен. Аналогичным образом альтернативное имя (псевдоним) можно задать и обычному столбцу.

Например, следующий запрос выводит без повторений список авторов, переименовав столбец в "Авторы", и сегодняшнюю дату в столбце TODAY (функция Date() возвращает текущую дату):

SELECT DISTINCT Author AS [Авторы],

Date () AS TODAY

FROM Book;

Обратите внимание, что псевдоним, как и название столбца или таблицы, в Access надо писать в одно слово или же заключить в квадратные скобки (название без пробелов тоже можно брать в квадратные скобки).

Задание. Напишите запрос, выводящий два столбца: первый должен называться "Подпись" и содержать во всех строках слово "Книга", второй столбец называется "Название книги" и содержит названия книг, взятых без повторения из таблицы Book.

Задание. Измените предыдущий запрос таким образом, чтобы он выводил единственный столбец, в котором была бы подпись "Книга", а за ней в двойных кавычках шло название книги, например: "Книга “Экспертные системы”". Для сцепления строк используйте операцию "+".

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

с тем, что поле BookYear имеет числовой тип, а сложение числа со строкой невозможно:

SELECT DISTINCT 'Книга ' + BookYear

FROM Book;

В подобных случаях, используется явная конвертация типов, для чего Access предоставляет ряд функций: для конвертации в логический тип – СВоо1( ), в строковый – CStr(-), в тип "Целое" – Clnt(-) и т.д.

Задание. Измените представленный выше запрос так, чтобы он корректно сцеплял подпись и год издания.

Псевдонимы можно задавать не только столбцам, но и таблицам. Часто это делается, чтобы получить более компактный код. Ниже приведен пример внутреннего соединения в "не-ANSI" синтаксисе, из результатов которого выбираются данные только одной таблицы. Чтобы не записывать полные имена таблиц в секции WHERE и в перечне выводимых столбцов, им даны более короткие псевдонимы:

SELECT DISTINCT В.*

FROM Book as В, BooklnLib as BL

WHERE В.BookID=BL.BookID;

Задание. По аналогии с рассмотренным примером напишите свой запрос, использующий псевдонимы таблиц. Проверьте его работу.

Другой характерный пример использования псевдонимов таблиц – так называемое "самосоединение" (self-join). Ниже приведен пример, выводящий все сочетания разных статусов книг. Чтобы отличать один экземпляр таблицы BookStatus от другого, потребовалось ввести псевдонимы:

SELECT Т.StatusName, T1. StatusName

FROM BookStatus AS T, BookStatus AS T1

WHERE T.StatusIDOTl .StatusID;

Задание. По анаюгии с рассмотренным примером напишите свой запрос, выводящий без повторения все сочетания разных издательств.

Следующая тема – использование агрегатных функций и группировки. Агрегатные функции обрабатывают набор записей и возвращают одно значение. К ним относятся функция подсчета количества записей или элементов – COUNT(), а также функции нахождения среднего, минимума, максимума, суммы (AVG, MIN, MAX, SUM соответственно). В большинстве случаев в качестве аргумента агрегатной функции выступает название столбца или выражение. У функции Count() есть также вариант Count(*), подсчитывающий количество записей.

Следующий запрос выводит самый ранний год издания книги из таблицы Book:

SELECT Min(BookYear) as (Год первой книги]

FROM Book;

Задание. Напишите запрос, подсчитывающий количество статусов книг в таблице BookStatus.

Агрегатные функции могут применяться не только к таблице целиком, но и к группам, полученным после использования инструкции GROUP BY. Например, надо подсчитать, сколько каждое издательство выпустило книг. Для этого группируем книги по названию издательства и считаем, сколько книг в каждой группе (определяем число идентификаторов):

SELECT Book.Publisher,

Count(Book.BookID) AS [К-во книг]

FROM Book

GROUP BY Book.Publisher;

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

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

Если необходимо выполнить группировку по нескольким столбцам, то в секции GROUP BY они перечисляются через запятую.

Задание. Для книг, экземпляры которых присутствуют в библиотеке (с любым статусом), выведите идентификатор издания, название, автора и число экземпляров книги.

На группу можно наложить условие, которое записывается в секции HAVING. Если секции GROUP BY в запросе не было, вся таблица рассматривается как единая группа. Например, снова выведем издательство и количество изданных им книг, но только для издательств, где было выпущено две или более книг:

SELECT Book.Publisher,

Count(Book.BookID) AS [К-во книг]

FROM Book

GROUP BY Book.Publisher

HAVING COUNT(Book.BookID)>=2;

Задание. Напишите запрос, выводящий список названий статусов книг и количество экземпляров книг с эти статусом, по только если с этим статусом у нас две или более книги.

При написании запросов, в которых есть секции WHERE и HAVING, нужно учитывать что:

• • условие отбора, применяемое к отдельной записи, прописывается в секции WHERE, применяемое к группе – в HAVING;
• • в соответствии с порядком выполнения оператора SELECT секция WHERE выполняется до группировки и секции HAVING.

Рассмотрим пример, где выведем издательство и количество изданных им книг, для издательств, где было выпущено две или более книг, не считая книг, в названии которых присутствует сочетание "баз":

SELECT Book.Publisher,

Count(Book.BookID) AS [К-во книг]

FROM Book

WHERE Title NOT LIKE ' *баз*'

GROUP BY Book.Publisher

HAVING COUNT(Book.BookID)>=2;

Задание. Модифицируйте запрос из предыдущего задания, учитывая при подсчете только книги, изданные после 1998 г.