SELECTSUM(PD.VOLUME) AS SM,MAX(PD.VOLUME) AS MX,MIN(PD.VOLUME) AS MN,AVG(PD.VOLUME) AS AVFROM PD;
В результате получим следующую таблицу с одной строкой:
| SM | MX | MN | AV |
| 2000 | 1000 | 100 | 333.33333333 |
Пример 23. Для каждой детали получить суммарное поставляемое количество (ключевое слово GROUP BY…):
SELECTPD.DNUM,SUM(PD.VOLUME) AS SMGROUPBYPD.DNUM;Этот запрос будет выполняться следующим образом. Сначала строки исходной таблицы будут сгруппированы так, чтобы в каждую группу попали строки с одинаковыми значениями DNUM. Потом внутри каждой группы будет просуммировано поле VOLUME. От каждой группы в результатирующую таблицу будет включена одна строка:
| DNUM | SM |
| 1 | 1250 |
| 2 | 450 |
| 3 | 300 |
Замечание. В списке отбираемых полей оператора SELECT, содержащего раздел GROUP BY можно включать только агрегатные функции и поля, которые входят в условие группировки. Следующий запрос выдаст синтаксическую ошибку:
SELECTPD.PNUM,PD.DNUM,SUM(PD.VOLUME) AS SMGROUP BY PD.DNUM;Причина ошибки в том, что в список отбираемых полей включено поле PNUM, которое не входит в раздел GROUP BY. И действительно, в каждую полученную группу строк может входить несколько строк с различными значениями поля PNUM. Из каждой группы строк будет сформировано по одной итоговой строке. При этом нет однозначного ответа на вопрос, какое значение выбрать для поля PNUM в итоговой строке.
Замечание. Некоторые диалекты SQL не считают это за ошибку. Запрос будет выполнен, но предсказать, какие значения будут внесены в поле PNUM в результатирующей таблице, невозможно.
Пример 24. Получить номера деталей, суммарное поставляемое количество которых превосходит 400 (ключевое слово HAVING…):
Замечание. Условие, что суммарное поставляемое количество должно быть больше 400 не может быть сформулировано в разделе WHERE, т.к. в этом разделе нельзя использовать агрегатные функции. Условия, использующие агрегатные функции должны быть размещены в специальном разделе HAVING:
SELECTPD.DNUM,SUM(PD.VOLUME) AS SMGROUP BY PD.DNUMHAVING SUM(PD.VOLUME) > 400;В результате получим следующую таблицу:
| DNUM | SM |
| 1 | 1250 |
| 2 | 450 |
Замечание. В одном запросе могут встретиться как условия отбора строк в разделе WHERE, так и условия отбора групп в разделе HAVING. Условия отбора групп нельзя перенести из раздела HAVING в раздел WHERE. Аналогично и условия отбора строк нельзя перенести из раздела WHERE в раздел HAVING, за исключением условий, включающих поля из списка группировки GROUP BY.
Очень удобным средством, позволяющим формулировать запросы более понятным образом, является возможность использования подзапросов, вложенных в основной запрос.
Пример 25. Получить список поставщиков, статус которых меньше максимального статуса в таблице поставщиков (сравнение с подзапросом):
SELECT *FROM PWHERE P.STATYS <(SELECT MAX(P.STATUS) FROM P);Замечание. Т.к. поле P.STATUS сравнивается с результатом подзапроса, то подзапрос должен быть сформулирован так, чтобы возвращать таблицу, состоящую ровно из одной строки и одной колонки.
Замечание. Результат выполнения запроса будет эквивалентен результату следующей последовательности действий:
1. Выполнить один раз вложенный подзапрос и получить максимальное значение статуса.
2. Просканировать таблицу поставщиков P, каждый раз сравнивая значение статуса поставщика с результатом подзапроса, и отобрать только те строки, в которых статус меньше максимального.
Пример 26. Использование предиката IN. Получить список поставщиков, поставляющих деталь номер 2:
SELECT *FROM PWHERE P.PNUM IN(SELECT DISTINCT PD.PNUM FROM PDWHEREPD.DNUM = 2);Замечание. В данном случае вложенный подзапрос может возвращать таблицу, содержащую несколько строк.
Замечание. Результат выполнения запроса будет эквивалентен результату следующей последовательности действий:
1. Выполнить один раз вложенный подзапрос и получить список номеров поставщиков, поставляющих деталь номер 2.
2. Просканировать таблицу поставщиков P, каждый раз проверяя, содержится ли номер поставщика в результате подзапроса.
Пример 27. Использование предиката EXIST. Получить список поставщиков, поставляющих деталь номер 2:
SELECT *FROM PWHERE EXIST(SELECT *FROM PDWHEREPD.PNUM = P.PNUM ANDPD.DNUM = 2);Замечание. Результат выполнения запроса будет эквивалентен результату следующей последовательности действий:
1. Просканировать таблицу поставщиков P, каждый раз выполняя подзапрос с новым значением номера поставщика, взятым из таблицы P.
2. В результат запроса включить только те строки из таблицы поставщиков, для которых вложенный подзапрос вернул непустое множество строк.
Замечание. В отличие от двух предыдущих примеров, вложенный подзапрос содержит параметр (внешнюю ссылку), передаваемый из основного запроса - номер поставщика P.PNUM. Такие подзапросы называются коррелируемыми (correlated). Внешняя ссылка может принимать различные значения для каждой строки-кандидата, оцениваемого с помощью подзапроса, поэтому подзапрос должен выполняться заново для каждой строки, отбираемой в основном запросе. Такие подзапросы характерны для предиката EXIST, но могут быть использованы и в других подзапросах.
Замечание. Может показаться, что запросы, содержащие коррелируемые подзапросы будут выполняться медленнее, чем запросы с некоррелируемыми подзапросами. На самом деле это не так, т.к. то, как пользователь, сформулировал запрос, не определяет, как этот запрос будет выполняться. Язык SQL является непроцедурным, а декларативным. Это значит, что пользователь, формулирующий запрос, просто описывает, каким должен быть результат запроса, а как этот результат будет получен - за это отвечает сама СУБД.
Пример 28. Использование предиката NOT EXIST. Получить список поставщиков, не поставляющих деталь номер 2:
SELECT *FROM PWHERE NOT EXIST(SELECT *FROM PDWHEREPD.PNUM = P.PNUM ANDPD.DNUM = 2);Замечание. Также как и в предыдущем примере, здесь используется коррелируемый подзапрос. Отличие в том, что в основном запросе будут отобраны те строки из таблицы поставщиков, для которых вложенный подзапрос не выдаст ни одной строки.
Пример 29. Получить имена поставщиков, поставляющих все детали:
SELECT DISTINCT PNAMEFROM PWHERE NOT EXIST(SELECT *FROM DWHERE NOT EXIST(SELECT *FROM PDWHEREPD.DNUM = D.DNUM ANDPD.PNUM = P.PNUM));Замечание. Данный запрос содержит два вложенных подзапроса и реализует реляционную операцию деления отношений.
Самый внутренний подзапрос параметризован двумя параметрами (D.DNUM, P.PNUM) и имеет следующий смысл: отобрать все строки, содержащие данные о поставках поставщика с номером PNUM детали с номером DNUM. Отрицание NOT EXIST говорит о том, что данный поставщик не поставляет данную деталь. Внешний к нему подзапрос, сам являющийся вложенным и параметризованным параметром P.PNUM, имеет смысл: отобрать список деталей, которые не поставляются поставщиком PNUM. Отрицание NOT EXIST говорит о том, что для поставщика с номером PNUM не должно быть деталей, которые не поставлялись бы этим поставщиком. Это в точности означает, что во внешнем запросе отбираются только поставщики, поставляющие все детали.
Пример 30. Получить имена поставщиков, имеющих статус, больший 3 или поставляющих хотя бы одну деталь номер 2 (объединение двух подзапросов - ключевое слово UNION):
SELECT P.PNAMEFROM PWHERE P.STATUS > 3UNIONSELECT P.PNAMEFROM P, PDWHERE P.PNUM = PD.PNUM ANDPD.DNUM = 2;Замечание. Результатирующие таблицы объединяемых запросов должны быть совместимы, т.е. иметь одинаковое количество столбцов и одинаковые типы столбцов в порядке их перечисления. Не требуется, чтобы объединяемые таблицы имели бы одинаковые имена колонок. Это отличает операцию объединения запросов в SQL от операции объединения в реляционной алгебре. Наименования колонок в результатирующем запросе будут автоматически взяты из результата первого запроса в объединении.
Пример 31. Получить имена поставщиков, имеющих статус, больший 3 и одновременно поставляющих хотя бы одну деталь номер 2 (пересечение двух подзапросов - ключевое слово INTERSECT):
SELECT P.PNAMEFROM PWHERE P.STATUS > 3INTERSECTSELECT P.PNAMEFROM P, PDWHERE P.PNUM = PD.PNUM ANDPD.DNUM = 2;Пример 32. Получить имена поставщиков, имеющих статус, больший 3, за исключением тех, кто поставляет хотя бы одну деталь номер 2 (разность двух подзапросов - ключевое слово EXCEPT):
SELECT P.PNAMEFROM PWHERE P.STATUS > 3EXCEPTSELECT P.PNAMEFROM P, PDWHERE P.PNUM = PD.PNUM ANDPD.DNUM = 2;Опишем синтаксис оператора выборки данных (оператора SELECT) более точно. При описании синтаксиса операторов обычно используются условные обозначения, известные как стандартные формы Бэкуса-Наура (BNF).
В BNF обозначениях используются следующие элементы:
· Символ "::=" означает равенство по определению. Слева от знака стоит определяемое понятие, справа - собственно определение понятия.
· Ключевые слова записываются прописными буквами. Они зарезервированы и составляют часть оператора.
· Метки-заполнители конкретных значений элементов и переменных записываются курсивом.
· Необязательные элементы оператора заключены в квадратные скобки [].
· Вертикальная черта | указывает на то, что все предшествующие ей элементы списка являются необязательными и могут быть заменены любым другим элементом списка после этой черты.
· Фигурные скобки {} указывают на то, что все находящееся внутри них является единым целым.
· Троеточие "…" означает, что предшествующая часть оператора может быть повторена любое количество раз.