Московский государственный университет имени М.В.Ломоносова
Опубликован: 10.10.2005 | Доступ: свободный | Студентов: 7916 / 387 | Оценка: 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. Найти названия проектов, которые будут выполняться в течение следующего года.
Александра Каева
Александра Каева
Антон Честиков
Антон Честиков

Не вижу список тем из плана занятий. Проблема известна?

Светлана Кудинова
Светлана Кудинова
Россия
Евгений Снегиренко
Евгений Снегиренко
Россия, г. Кемерово