Характерные ошибки при решении задач на написание запросов на выборку (SELECT)

База данных «Компьютерная фирма»

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

Решение 1.16.3

Консоль

Выполнить

SELECT DISTINCT CASE
                    WHEN PC.price > = l.price AND 
                         PC.price > = prn.price 
                    THEN pc.model
                    WHEN l.price > = PC.price AND 
                         l.price > = prn.price 
                    THEN l.model
                    WHEN prn.price > = l.price AND 
                         prn.price > = pc.price
                    THEN prn.model
                END AS model
FROM PC, laptop l, printer prn
WHERE PC.price = (SELECT MAX(price) 
                  FROM PC
                  ) AND 
      l.price = (SELECT MAX(price) 
                 FROM Laptop
                 ) AND 
      prn.price = (SELECT MAX(price) 
                   FROM Printer
                   );

SELECT DISTINCT CASE                     WHEN PC.price > = l.price AND                          PC.price > = prn.price                     THEN pc.model                     WHEN l.price > = PC.price AND                          l.price > = prn.price                     THEN l.model                     WHEN prn.price > = l.price AND                          prn.price > = pc.price                     THEN prn.model                 END AS model FROM PC, laptop l, printer prn WHERE PC.price = (SELECT MAX(price)                   FROM PC                   ) AND       l.price = (SELECT MAX(price)                  FROM Laptop                  ) AND       prn.price = (SELECT MAX(price)                    FROM Printer                    );

    Схема

В предложении FROM используется декартово произведение трех таблиц. С помощью предложения WHERE отбираются только те строки, которые содержат модели каждого типа продукции, имеющие максимальную цену в своей продукционной категории. Возникающая здесь избыточность (если, скажем, по две модели из каждой таблицы имеют максимальную цену, то результирующее число строк будет равно восьми — 2*2*2) не является ошибочной, так как возможные дубликаты моделей будут впоследствии устранены при помощи DISTINCT в предложении SELECT. Главное, что каждая строка будет содержать искомую глобальную максимальную цену.

Затем модели с этой глобальной максимальной ценой отбираются в операторе CASE. Вот здесь и кроется ошибка. Особенность обработки оператора CASE заключается в последовательной проверке предложений WHEN. Поэтому при первом выполнении условия будет возвращаться значение из соответствующего предложения THEN, и проверка последующих предложений WHEN выполняться уже не будет.

Рассмотрим с этой точки зрения следующий вариант данных. Пусть максимальную стоимость имеют модели принтера и ПК. Тогда первое предложение WHEN оператора CASE будет удовлетворено:

WHEN PC.price > = l.price AND 
     PC.price > = prn.price 
THEN pc.model

Действительно, оба предиката сравнения будут истинны, в результате чего запрос вернет только модель ПК, но не принтера. Если быть более точным, то в результате мы получим все модели ПК, которые имеют одинаковую максимальную цену.

Попробуйте исправить это решение, не используя оператор UNION.