Файл: Практика - Элементы языка SQL.pdf

например, так: 

SELECT 
    P.NAME AS PNAME, 
    C.NAME AS CNAME, 
    DETAILS.DNAME, 
    CD.VOLUME 
  FROM 
    CONTRAGENTS P, 
    CONTRAGENTS C, 
    DETAILS NATURAL JOIN CD 
  WHERE 
    P.NUM = CD.PNUM AND 
    C.NUM = CD.CNUM; 

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

Пример 21. Получить общее количество поставщиков (ключевое слово 

COUNT

): 

SELECT COUNT(*) AS N 
  FROM P; 

В  результате  получим  таблицу  с  одним  столбцом  и  одной  строкой,  содержащей 
количество строк из таблицы P: 

N 

3 

Пример  22

.  Получить  общее,  максимальное,  минимальное  и  среднее  количества 

поставляемых деталей (ключевые слова 

SUM

, 

MAX

, 

MIN

, 

AVG

): 

SELECT 
    SUM(PD.VOLUME) AS SM, 
    MAX(PD.VOLUME) AS MX, 
    MIN(PD.VOLUME) AS MN, 
    AVG(PD.VOLUME) AS AV 
  FROM PD; 

В результате получим следующую таблицу с одной строкой: 

SM  M

X 

M

N 

AV 

200

0 

100

0 

10

0 

333.33333333 

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

Пример 23. Для каждой детали получить суммарное поставляемое количество (ключевое 
слово 

GROUP BY

…): 

SELECT 
    PD.DNUM, 

    SUM(PD.VOLUME) AS SM 
  GROUP BY PD.DNUM; 

Этот запрос будет выполняться следующим образом. Сначала строки исходной таблицы 
будут  сгруппированы  так,  чтобы  в  каждую  группу  попали  строки  с  одинаковыми 
значениями  DNUM.  Потом  внутри  каждой  группы  будет  просуммировано  поле 
VOLUME.  От  каждой  группы  в  результатирующую  таблицу  будет  включена  одна 
строка: 

DNU

M 

SM 

1 

125

0 

2 

450 

3 

300 

Замечание. В списке отбираемых полей оператора SELECT, содержащего раздел GROUP 
BY  можно  включать 

только 

агрегатные  функции  и  поля, 

которые  входят  в  условие 

группировки

. Следующий запрос выдаст синтаксическую ошибку: 

SELECT 
    PD.PNUM, 
    PD.DNUM, 
    SUM(PD.VOLUME) AS SM 
  GROUP BY PD.DNUM; 

Причина ошибки в том, что в список отбираемых полей включено поле PNUM, которое 

не  входит 

в раздел  GROUP  BY.  И  действительно,  в  каждую  полученную  группу  строк 

может  входить  несколько  строк  с 

различными 

значениями  поля  PNUM.  Из  каждой 

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

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

Пример  24

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

превосходит 400 (ключевое слово 

HAVING

…): 

Замечание. Условие, что суммарное поставляемое количество должно быть больше 400 
не  может  быть  сформулировано  в  разделе  WHERE,  т.к.  в  этом  разделе  нельзя 
использовать агрегатные функции. Условия, использующие агрегатные функции должны 
быть размещены в специальном разделе HAVING: 

SELECT 
    PD.DNUM, 
    SUM(PD.VOLUME) AS SM 
  GROUP BY PD.DNUM 
  HAVING SUM(PD.VOLUME) > 400; 

В результате получим следующую таблицу: 

DNU

M 

SM 

1 

125

0 

2 

450 

Замечание.  В  одном  запросе  могут  встретиться  как  условия  отбора  строк  в  разделе 
WHERE, так и условия  отбора групп в разделе HAVING. Условия отбора групп нельзя 
перенести  из  раздела  HAVING  в  раздел  WHERE.  Аналогично  и  условия  отбора  строк 
нельзя  перенести  из  раздела  WHERE  в  раздел  HAVING,  за  исключением  условий, 
включающих поля из списка группировки GROUP BY. 

Использование подзапросов 

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

Пример  25

.  Получить  список  поставщиков,  статус  которых  меньше  максимального 

статуса в таблице поставщиков (сравнение с подзапросом): 

SELECT * 
  FROM P 
  WHERE P.STATYS < 
    (SELECT MAX(P.STATUS) 
        FROM P); 

Замечание.  Т.к.  поле  P.STATUS  сравнивается  с  результатом  подзапроса,  то  подзапрос 
должен быть сформулирован так, чтобы возвращать таблицу, состоящую 

ровно из одной 

строки и одной колонки

. 

Замечание.  Результат  выполнения  запроса  будет  эквивалентен  результату  следующей 
последовательности действий: 

1.

Выполнить 

один раз 

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

2.

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

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

Пример 26

. Использование предиката 

IN

. Получить список поставщиков, поставляющих 

деталь номер 2: 

SELECT * 
  FROM P 
  WHERE P.PNUM IN 
    (SELECT DISTINCT PD.PNUM 
        FROM PD 
        WHERE PD.DNUM = 2); 

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

Замечание.  Результат  выполнения  запроса  будет  эквивалентен  результату  следующей 
последовательности действий: 

1.

Выполнить 

один раз 

вложенный  подзапрос и  получить  список  номеров  поставщиков, 

поставляющих деталь номер 2. 

2.

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

поставщика в результате подзапроса. 

Пример  27

.  Использование  предиката 

EXIST

.  Получить  список  поставщиков, 

поставляющих деталь номер 2: 

SELECT * 
  FROM P 
  WHERE EXIST 
    (SELECT * 
        FROM PD 
        WHERE 
            PD.PNUM = P.PNUM AND 
            PD.DNUM = 2); 

Замечание.  Результат  выполнения  запроса  будет  эквивалентен  результату  следующей 
последовательности действий: 

1.

Просканировать  таблицу  поставщиков  P, 

каждый  раз  выполняя  подзапрос 

с  новым 

значением номера поставщика, взятым из таблицы P. 

2.

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

вложенный подзапрос вернул непустое множество строк. 

Замечание.  В  отличие  от  двух  предыдущих  примеров,  вложенный  подзапрос  содержит 
параметр  (внешнюю  ссылку),  передаваемый  из  основного  запроса  -  номер  поставщика 
P.PNUM. Такие подзапросы называются 

коррелируемыми 

(

correlated

). Внешняя ссылка 

может  принимать  различные  значения  для  каждой  строки-кандидата,  оцениваемого  с 
помощью  подзапроса,  поэтому  подзапрос  должен  выполняться  заново  для  каждой 
строки,  отбираемой  в  основном  запросе.  Такие  подзапросы  характерны  для  предиката 
EXIST, но могут быть использованы и в других подзапросах. 

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

не определяет

, 

как  этот  запрос  будет  выполняться.  Язык  SQL  является  непроцедурным,  а 
декларативным.  Это  значит,  что  пользователь,  формулирующий  запрос,  просто 
описывает, 

каким должен быть результат запроса

, а как этот результат будет получен - 

за это отвечает сама СУБД. 

Пример  28

.  Использование  предиката 

NOT  EXIST

.  Получить  список  поставщиков,  не 

поставляющих деталь номер 2: 

SELECT * 
  FROM P 
  WHERE NOT EXIST 
    (SELECT * 
        FROM PD 

        WHERE 
            PD.PNUM = P.PNUM AND 
            PD.DNUM = 2); 

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

Пример 29

. Получить имена поставщиков, поставляющих все детали: 

SELECT DISTINCT PNAME 
  FROM P 
  WHERE NOT EXIST 
    (SELECT * 
        FROM D 
        WHERE NOT EXIST 
          (SELECT * 
              FROM PD 
              WHERE 
                  PD.DNUM = D.DNUM AND 
                  PD.PNUM = P.PNUM)); 

Замечание.  Данный  запрос  содержит  два  вложенных  подзапроса  и  реализует 
реляционную операцию 

деления отношений

. 

Самый внутренний подзапрос параметризован двумя параметрами (D.DNUM, P.PNUM) 
и  имеет  следующий  смысл:  отобрать  все  строки,  содержащие  данные  о  поставках 
поставщика с номером PNUM детали с номером DNUM. Отрицание NOT EXIST говорит 
о том, что данный поставщик не поставляет данную деталь. Внешний к нему подзапрос, 
сам являющийся вложенным и параметризованным параметром P.PNUM, имеет смысл: 
отобрать  список  деталей,  которые  не  поставляются  поставщиком  PNUM.  Отрицание 
NOT  EXIST  говорит  о  том,  что  для  поставщика  с  номером  PNUM  не  должно  быть 
деталей, которые не поставлялись бы этим поставщиком. Это в точности означает, что во 
внешнем запросе отбираются только поставщики, поставляющие все детали. 

Использование объединения, пересечения и разности 

Пример  30.  Получить  имена  поставщиков,  имеющих  статус,  больший  3  или 
поставляющих хотя бы одну деталь номер 2 (объединение двух подзапросов - ключевое 
слово 

UNION

): 

SELECT P.PNAME 
  FROM P 
  WHERE P.STATUS > 3 
  UNION 
  SELECT P.PNAME 
    FROM P, PD 
    WHERE P.PNUM = PD.PNUM AND 
        PD.DNUM = 2; 

Замечание.  Результатирующие  таблицы  объединяемых  запросов  должны  быть