Московский государственный университет имени М.В.Ломоносова
Опубликован: 10.10.2005 | Доступ: свободный | Студентов: 8464 / 634 | Оценка: 3.85 / 3.50 | Длительность: 22:03:00
Лекция 4:

Предикаты раздела WHERE оператора SELECT

Предикат exists

Предикат exists определяется следующим синтаксическим правилом:

exists_predicate ::= EXISTS (query_expression)

Значением условия EXISTS (query_expression) является true в том и только в том случае, когда мощность таблицы-результата выражения запросов больше нуля, иначе значением условия является false.

Примеры запросов с использованием предиката exists
SELECT DEPT.DEPT_NO
FROM DEPT
WHERE EXISTS
    (SELECT EMP.EMP_NO
    FROM EMP
    WHERE EMP.DEPT_NO = DEPT.DEPT_NO 
        AND EXISTS
            (SELECT PRO.PRO_MNG
            FROM PRO
            WHERE PRO.PRO_MNG = EMP.EMP_NO));
14.16. Найти номера отделов, среди служащих которых имеются менеджеры проектов.

Эту формулировку можно упростить, избавившись от самого вложенного запроса ( пример 14.16.1):

SELECT DEPT.DEPT_NO
FROM DEPT
WHERE EXISTS
    (SELECT EMP.EMP_NO
      FROM EMP, PRO
      WHERE EMP.DEPT_NO = DEPT.DEPT_NO
        AND PRO.PRO_MNG = EMP.EMP_NO);
14.16.1.

Далее заметим, что по смыслу предиката EXISTS список выборки во вложенном подзапросе является несущественным, и формулировку запроса можно изменить, например, следующим образом ( пример 14.16.2):

SELECT DEPT.DEPT_NO
FROM DEPT
WHERE EXISTS
    (SELECT *
      FROM EMP, DEPT
      WHERE EMP.DEPT_NO = DEPT.DEPT_NO
        AND PRO.PRO_MNG = EMP.EMP_NO);
14.16.2.

Запросы с предикатом EXISTS можно также переформулировать в виде запросов с предикатом сравнения ( пример 14.16.3):

SELECT DEPT.DEPT_NO
FROM DEPT
WHERE (SELECT COUNT(*)
    FROM EMP, DEPT
    WHERE EMP.DEPT_NO = DEPT.DEPT_NO
     AND PRO.PRO_MNG = EMP.EMP_NO ) >= 1;
14.16.3.
SELECT DEPT.DEPT_NO
FROM DEPT
WHERE NOT EXISTS
    (SELECT *
      FROM EMP EMP1, EMP EMP2
      WHERE EMP1.EMP_NO = DEPT.DEPT_MNG AND
        EMP2.DEPT_NO = DEPT.DEPT_NO AND
        EMP2.EMP_SAL > EMP1.EMP_SAL);
14.17. Найти номера отделов, размер заработной платы служащих которых не превышает размер заработной платы руководителя отдела.

Предикат unique

Этот предикат позволяет сформулировать условие отсутствия дубликатов в результате запроса:

unique_predicate ::= UNIQUE (query_expression)

Результатом вычисления условия UNIQUE (query_expression) является true в том и только в том случае, когда в таблице-результате выражения запросов отсутствуют какие-либо две строки, одна из которых является дубликатом другой. В противном случае значение условия есть false.

Примеры запросов с использованием предиката unique
SELECT DEPT_NO
FROM DEPT
WHERE UNIQUE
    (SELECT EMP_NAME, EMP_BDATE
      FROM EMP
      WHERE EMP.DEPT_NO = DEPT.DEPT_NO);
14.18. Найти номера отделов, служащих которых можно различить по имени и дате рождения.

Возможна альтернативная, но более сложная формулировка этого запроса с использованием предиката NOT EXISTS ( пример 14.18.1):

SELECT DEPT_NO
FROM DEPT
WHERE NOT EXISTS
    (SELECT *
      FROM EMP, EMP EMP1
      WHERE EMP1.EMP_NO <> EMP.EMP_NO
        AND EMP.DEPT_NO = DEPT.DEPT_NO
        AND EMP1.DEPT_NO = DEPT.DEPT_NO
        AND EMP1.EMP_NAME = EMP.EMP_NAME
        AND(EMP1.EMP_BDATE = EMP.EMP_BDATE
          OR (EMP.EMP_BDATE IS NULL
          AND EMP1.EMP_BDATE IS NULL)));
14.18.1.

Если же ограничиться требованием уникальности имен служащих, то возможна следующая формулировка ( пример 14.18.2):

SELECT DEPT_NO
FROM DEPT
WHERE (SELECT COUNT (EMP_NAME)
      FROM EMP
      WHERE EMP.DEPT_NO = DEPT.DEPT_NO) =
    (SELECT COUNT (DISTINCT EMP_NAME)
      FROM EMP
      WHERE EMP.DEPT_NO = DEPT.DEPT_NO);
14.18.2.

Предикат overlaps

Этот предикат служит для проверки перекрытия во времени двух событий. Условие определяется следующим синтаксисом:

overlaps_predicate ::= row_value_constructor OVERLAPS
    row_value_constructor

Степень каждой из строк-операндов должна быть равна 2. Тип данных первого столбца каждого из операндов должен быть типом даты-времени, и типы данных первых столбцов должны быть совместимы. Тип данных второго столбца каждого из операндов должен быть типом даты-времени или интервала. При этом:

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

Пусть D1 и D2 - значения первого столбца первого и второго операндов соответственно. Если второй столбец первого операнда имеет тип дата-время, то пусть E1 обозначает его значение. Если второй столбец первого операнда имеет тип INTERVAL, то пусть I1 -его значение, а E1 = D1 + I1. Если D1 является неопределенным значением или если E1 < D1, то пусть S1 = E1 и T1 = D1. В противном случае, пусть S1 = D1 и T1 = E1. Аналогично определяются S2 и T2 применительно ко второму операнду. Результат условия совпадает с результатом вычисления следующего булевского выражения:

(S1 > S2 AND NOT (S1 >= T2 AND T1 >= T2)) 
OR 
(S2 > S1 AND NOT (S2 >= T1 AND T2 >= T1)) 
OR 
(S1 = S2 AND (T1 <> T2 OR T1 = T2))
Примеры запросов с использованием предиката overlaps
SELECT PRO_NO
FROM PRO
WHERE (PRO_SDATE, PRO_DURAT) OVERLAPS
    (DATE '2000-01-15', DATE '2002-12-31');
14.19. Найти номера проектов, которые выполнялись в период с 15 января 2000 г. по 31 декабря 2002 г.
SELECT PRO_TITLE
FROM PRO
WHERE (PRO_SDATE, PRO_DURAT) OVERLAPS
   (CURRENT_DATE, INTERVAL '1' YEAR);
14.20. Найти названия проектов, которые будут выполняться в течение следующего года.
Алексей Ковтун
Алексей Ковтун

При попытке исполнения запроса:

CREATE DOMAIN EMP_NO AS INTEGER

    CHECK (VALUE BETWEEN 1 AND 10000);

Выдается ошибка: Неизвестный тип объекта "DOMAIN" в интсрукции CREATE, DROP или ALTER. 

Используется SQL Server MS SQL 2008R2

Александра Каева
Александра Каева