Наличие пустых (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);