Упражнение 23

Нижеприведенный запрос содержит характерную ошибку, допускаемую при решении этого упражнения.

Решение 1.15.1

SELECT DISTINCT maker
FROM product
WHERE model IN (SELECT model
                FROM PC
                WHERE speed >= 750
                ) 
    OR model IN (SELECT model
                FROM Laptop
                WHERE speed >= 750
                );

Ошибка состоит в том, что в результирующий набор попадет также и производитель, выпускающий что-нибудь одно: либо ПК, либо ноутбуки, так как предикат в предложении WHERE будет истинен при выполнении хотя бы одного из условий, соединяемых оператором OR. Такой подход не удовлетворяет условиям задачи и совершенно справедливо отвергается системой.

Вот попытка «изменить» ситуацию в лучшую сторону:

Решение 1.15.2

SELECT DISTINCT maker
FROM Product a, PC b, Laptop c
WHERE b.speed >= 750
    AND c.speed >= 750
    AND (a.model = b.model
        OR a.model = c.model
        );

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

x AND (y OR z) = (x AND y) OR (x AND z),

выполним синтаксические преобразования рассматриваемого запроса:

SELECT DISTINCT maker
FROM Product a, PC b, Laptop c
WHERE ((b.speed >= 750
        AND c.speed >= 750
        )
        AND a.model = b.model
    )
    OR ((b.speed >= 750
        AND c.speed >= 750
        )
        AND a.model = c.model
    );

В результирующий набор попадут строки, удовлетворяющие хотя бы одному из предикатов, соединяемых оператором OR. Рассмотрим, например, запрос с первым предикатом:

SELECT DISTINCT maker
FROM Product a, PC b, Laptop c
WHERE ((b.speed >= 750
        AND c.speed >= 750
        )
        AND a.model = b.model
    );

Перепишем его в синтаксически более удобной форме:

SELECT DISTINCT maker
FROM Product a
    JOIN PC b ON a.model = b.model,
    Laptop c
WHERE (b.speed >= 750
        AND c.speed >= 750
    );

SELECT DISTINCT maker
FROM (SELECT maker
    FROM Product a 
        JOIN PC b ON a.model = b.model
    WHERE b.speed >= 750
    ) x,
(SELECT *
FROM Laptop c
WHERE c.speed >= 750
) y;

Теперь, пожалуй, уже можно проанализировать. Первый подзапрос, который мы обозначили как х соединяет по внешнему ключу таблицу PC с таблицей Product, отбирая производителей ПК со скоростью больше или равной 750. Второй подзапрос (y) фильтрует модели ноутбуков со скоростью больше или равной 750.

То, как соединяются x и y, называется декартовым произведением. То есть производитель требуемых ПК будет в результирующем наборе сочетаться с каждой моделью ноутбука, даже если она произведена другим производителем.

В результате мы опять получим производителей, которые могут производить только что-то одно. Некоторая разница по сравнению с первым решением заключается в том, что если ни один производитель не выпускает ноутбуки с требуемой скоростью, то мы получим пустой набор записей. Этот частично правильный результат не дает первый пример 1.15.1.

Совпадение результатов на основной базе является совершенно случайным. Так уж оказалось, что те производители, которые выпускают требуемые по условию задачи ПК, выпускаю также и нужные ноутбуки. Таким образом, несмотря на совпадение результатов на «видимой» базе, запрос не является правильным при любом совместимом со схемой состоянием базы данных.

Чтобы не быть голословным, покажем результаты оригинального запроса 1.15.2 с расширением списка выводимых столбцов:

SELECT maker, a.model a_m, b.model b_m, c.model c_m
FROM Product a, PC b, Laptop c
WHERE ((b.speed >= 750
        AND c.speed >= 750
        )
        AND a.model = b.model
       )
       OR ((b.speed >= 750
         AND c.speed >= 750
        )
         AND a.model = c.model
       );

Рассмотрим пару строк из результирующего набора:

Как видно, модель 1121 (ПК) принадлежит производителю В, а модель 1752 (ноутбук) — производителю А. Так что у нас нет никаких оснований считать, что оба эти производителя удовлетворяют условиям задачи.

Объединение требуемых моделей ПК и ноутбуков в один набор дает лишь иллюзию, что мы получаем и то, и другое:

Решение 1.15.3

SELECT maker
FROM (SELECT maker
      FROM Product
           INNER JOIN PC ON Product.model = PC.model
      WHERE type='PC'
            AND speed >= 750
      UNION ALL
      SELECT maker
      FROM Product
            INNER JOIN Laptop ON Product.model = Laptop.model
      WHERE type='laptop'
            AND speed >= 750
      ) S
GROUP BY maker;

В результате будет получен список производителей, для которых имеется хотя бы одна строка в наборе из предложения FROM. Ниже более короткий вариант той же ошибки.

Решение 1.15.4

SELECT maker
FROM Product
WHERE model IN (SELECT model
                FROM PC
                WHERE speed >= 750
                UNION ALL
                SELECT model
                FROM Laptop
                WHERE speed >= 750
                )
GROUP BY maker;

Следующее решение использует соединение.

Решение 1.15.5

SELECT maker
FROM Product
     INNER JOIN PC ON Product.model = PC.model
     INNER JOIN Laptop ON Laptop.model = Product.model
WHERE PC.speed >= 750 AND
     Laptop.speed >= 750
GROUP BY maker;

Идея состоит в следующем: получить в результирующей таблице столбец с именем производителя, столбец со скоростью ПК этого производителя, а также столбец со скоростью ноутбука того же производителя. Поэтому, если задать нужные ограничения на скорости, то строки, удовлетворяющие этим ограничениям, должны дать то, что нужно (группировка по производителю устраняет дубликаты). Идея правильная, однако, соединяется не то и не по тем предикатам.

Действительно, первое внутреннее соединение даст нам список производителей и номера моделей ПК. Только ПК, так как model — первичный ключ в таблице Product. Поэтому второе внутреннее соединение с таблицей Laptop (по столбцу model!) даст нам пустой результирующий набор, так как в таблице Laptop нет (и не может быть!) моделей ПК.

Чтобы реализовать эту идею, нужно соединять производителей нужных ПК с аналогичными производителями ноутбуков, но не по номеру модели, а по имени производителя, или же использовать внешние соединения, а не внутренние.

ПиР

Решить задачу на SQL-EX.RU