Наличие пустых (NULL) значений в полях таблицы определяет особенности выполнения агрегирующих операций над данными, которые следует учитывать в SQL-запросах.
Влияние NULL-значений в функции COUNT
Если аргумент функции COUNT является константой или столбцом без пустых значений, то функция возвращает количество строк, к которым применимо определенное условие или группирование.
Если аргументом функции является столбец, содержащий пустое значение, то COUNT вернет число строк, которые не содержат пустые значения и к которым применимо определенное в COUNT условие для группировки.
Поведение функции COUNT (*) не зависит от пустых значений. Она возвратит общее количество строк в таблице.
Влияние NULL-значений в функции AVG
Среднее значение множества чисел равно сумме чисел, деленной на число элементов множества. Однако если некоторые элементы пусты, то деление на количество всех элементов множества приведет к неправильному результату.
Функция AVG вычисляет среднее значение всех известных значений множества элементов, то есть эта функция подсчитывает сумму известных значений и делит ее на количество этих значений, а не на общее количество значений, среди которых могут быть NULL-значения. Если столбец состоит только из пустых значений, то функция AVG также возвратит NULL.
Оператор ORDER BY
Оператор ORDER BY (сортировать по) применяется для упорядочивания (сортировки) записей. Если он используется в запросе, то в самом конце запроса. Этот оператор сортирует строки всей таблицы или отдельных ее групп (в случае применения оператора GROUP BY). Если в выражении запроса оператора GROUP BY нет, то оператор ORDER BY рассматривает все записи таблицы как одну группу.
Вслед за ключевым словом ORDER BY указывается столбец, по значениям которого следует произвести сортировку. После имени столбца можно указать ключевое слово, задающее порядок (режим) сортировки:
p ASC – по возрастанию (ascending). Это значение принято по умолчанию, поэтому если необходима сортировка, например, в алфавитном порядке, то специально указывать порядок не требуется;
p DESC – по убыванию (descending).
Если в выражении ORDER BY указаны несколько столбцов сортировки, то сначала записи упорядочиваются по значениям первого столбца, затем для каждого значения первого столбца записи упорядочиваются по значениям второго столбца и т.д. Столбцы в списке разделяются, как обычно, запятыми. Таким образом, создается иерархическая система сортировки записей результирующей таблицы.
Пример:
SELECT *
FROM SUBJECTS
ORDER BY SUBJECT_NAME;
Выбрать все данные из таблицы SUBJECRS, упорядочив их по возрастанию предмета. По умолчанию ASC
SELECT *
FROM SUBJECTS
ORDER BY SUBJECT_NAME DESC;
Упорядочить выводимый список предметов обучения по семестру, а внутри семестра по наименованию предмета:
SELECT *
FROM SUBJECTS
ORDER BY SEMESTER, SUBJECT_NAME;
SELECT *
FROM SUBJECTS
GROUP BY SEMESTER, SUBJECT_NAME
ORDER BY SEMESTER;
Вложенные подзапросы
SQL позволяет использовать одни запросы внутри других запросов, то есть вкладывать запросы друг в друга.
Пример:
Предположим, известна фамилия студента, но не известно значение поля STUDENT_ID для него. Чтобы извлечь все данные обо всех оценках этого студента, можно записать следующий запрос:
SELECT *
FROM EXAM_MARKS
WHERE STUDENT_ID =
(SELECT STUDENT_ID
FROM STUDENTS
WHERE SURNAME ='Петров');
Алгоритм работы запроса SQL со связанным подзапросом:
1 шаг: выбирается строка из таблицы, имя которой указано во внешнем запросе;
2 шаг: выполняется подзапрос и полученное значение применяется для анализа этой строки в условии предложения WHERE внешнего запроса;
3 шаг: по результату оценки этого условия принимается решение о включении или не включении строки в состав выходных данных;
4 шаг: процедура повторяется для следующей строки таблицы внешнего запроса.
В некоторых случаях для гарантии получения единственного значения в результате выполнения подзапроса используется DISTINCT. Одним из видов функций, которые автоматически всегда выдают в результате единственное значение для любого количества строк, являются агрегирующие функции.
Пример:
Данные обо всех оценках студентов из Воронежа можно выбрать с помощью следующего запроса:
SELECT *
FROM EXAM_MARKS
WHERE STUDENT_ID IN
(SELECT STUDENT_ID
FROM STUDENTS
WHERE CITY ='Воронеж');
Подзапросы можно применять внутри предложения HAVING.
Пример:
Пусть требуется определить количество предметов обучения с оценкой, превышающей среднее значение оценки студента с идентификатором 301:
SELECT COUNT (DISTINCT SUBJ_ID), MARK
FROM EXAM_MARKS
GROUP BY MARK
HAVING MARK >
(SELECT AVG (MARK)
FROM EXAM_MARKS
WHERE STUDENT_ID = 301);