Лабораторная работа № 4. Использование агрегатных функций и специальных операторов в условиях отбора

 

Цель работы: освоение операторов специального вида и возможностей подведения итогов по данным запроса.

 

Операторы специального вида

 

Под операторами специального вида мы будем понимать использование различных ключевых слов в операторах выборки:

IN определяет список значений, в который должно входить значение поля. Набор значений для оператора IN заключается в круглые скобки, значения разделяются запятыми. Например, предложение WHERE ADDRESS IN (‘Samal’, ‘Tastak’) выберет из списка сотрудников, проживающих в микрорайонах Самал и Тастак;

BETWEEN в отличие от списка допустимых значений BETWEEN, определяет диапазон значений. В запросе необходимо указать слово BETWEEN, затем начальное значение, ключевое слово AND и конечное значение;

LIKE применим только к символьным полям, с которыми он используется, чтобы находить подстроки. В качестве условия он использует специальные символы: символ подчеркивания _ - замещает любой одиночный символ; знак процента % - замещает последовательность любого числа символов. LIKE удобен при поиске значений - можно использовать ту часть значения, которую помните;

IS NULL - используется для проверки равенства данного значения значению NULL. Например, WHERE PHONE IS NULL - поиск сотрудников, не имеющих телефонов.

Для всех этих операций можно построить их отрицания (ключевое слово NOT), чтобы рассмотреть противоположные условия. Ключевое слово NOT используется с операциями следующим образом: NOT BETWEEN, IS NOT NULL, NOT IN, NOT LIKE.

 

Сортировка вывода

 

Обычно требуется, чтобы выводимые данные были как-то упорядочены. Выводимые данные можно упорядочить с помощью выражения, связанного с ключевым словом ORDER BY. Упорядочение, задаваемое с помощью ключевого слова ORDER BY, по умолчанию будет упорядочением по возрастанию, обозначается A-Z (А-Я) в случае сортировки имен. Алфавитное упорядочение по убыванию соответствует порядку Z-А (Я-А). Для числовых значений между 1 и 9 упорядочение по возрастанию обозначается 1-9, а по убыванию - 9-1. Синтаксис оператора SELECT, использующего выражение ORDER BY, следующий:

 

SELECT [ALL | * | DISTINCT СТОЛБЕЦ1, СТОЛБЕЦ2]

FROM ТАБЛИЦА1 [, ТАБЛИЦА2] [УСЛОВИЕ1 | ВЫРАЖЕНИЕ1]

[AND УСЛОВИЕ2 | ВЫРАЖЕНИЕ2] BY СТОЛБЕЦ1|ЦЕЛОЕ_ЗНАЧЕНИЕ [ASC|DESC];

 

Ввиду того, что порядок по возрастанию является порядком, принимаемым по умолчанию, нет необходимости указывать ASC вообще.

В SQL предлагаются и некоторые сокращения. Столбец, указанный в списке ключевого слова ORDER BY, можно заменить числом. ЦЕЛОЕ_ЗНАЧЕНИЕ является значением, замещающим действительное имя столбца и соответствующим порядку столбца в списке после ключевого слова SELECT.

 

Использование агрегатных функций

 

Агрегатные функции используются для обобщения данных. SQL Server предоставляет несколько агрегатных функций:

COUNT производит подсчет строк, удовлетворяющих условию запроса. Синтаксис оператора:

 

COUNT [(*) │(DISTINCT│ALL)] (имя_столбца)

 

SUM вычисляет арифметическую сумму всех значений колонки:

([DISTINCT] имя_столбца)

 

AVG вычисляет среднее арифметическое всех значений:

([DISTINCT] имя_столбца)

 

MAX определяет наибольшее из всех выбранных значений:

([DISTINCT] имя_столбца)

 

MIN определяет наименьшее из всех выбранных значений:

([DISTINCT] имя_столбца)

 

Функции SUM и AVG применимы только к числовым полям. С функциями COUNT, MAX, MIN могут использоваться числовые или символьные поля. При применении к символьным полям MAX, MIN сравнивают значения в алфавитном порядке. Агрегатные функции при своей работе игнорируют значения NULL. Функция COUNT несколько отличается от остальных. Она подсчитывает число значений в данной колонке или число строк в таблице.

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

 

Задание на лабораторную работу

 

Прежде, чем приступить к выполнению задания, следует ознакомиться с соответствующими разделами виртуальной обучающей системы SQL_Education.

Выберите для работы базу данных TradeCompany.

Получите список клиентов, адреса которых начинаются на определенную букву.

Подсчитайте количество клиентов фирмы.

Подсчитайте количество наименований товаров.

Получите список счетов, выданных в течение последних трех месяцев.

Получите список клиентов, общая сумма счетов которых превышает указанную сумму.

Получите список клиентов и наименование товаров, приобретенных ими в течение двух последних месяцев.

Подсчитайте общую сумму товара, приобретенного определенными клиентами.

Подсчитайте количество всех счетов, выписанных определенному клиенту.

Предположим, что не все продавцы имеют телефоны (приведите данные в соответствие с этим предположением). Подсчитайте число продавцов, имеющих телефоны.

Подсчитайте сумму зарплат всех продавцов (пусть зарплата каждого продавца вычисляется как некоторый % от сделки).

Подсчитайте среднюю зарплату продавцов.

Найдите зачение максимальной и минимальной зарплаты.

 

Требования к отчету

 

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

 

4.6 Контрольные вопросы

 

Какие операторы специального вида вы знаете? Назначение каждой операции?

Какие операции отношения используются в условиях отбора данных?

Какие логические операции используются в операциях отбора данных?

Что такое агрегатная функция?

Играет ли роль тип данных при использовании функции COUNT.

Чтобы группировать данные по столбцу, должен ли этот столбец быть указан в списке ключевого слова SELECT?

Для чего используются псевдонимы таблиц?