Упражнение 24

Вот ошибочное решение, которое какое-то время назад принималось проверочной системой сайта:

Решение 1.16.1

SELECT model
FROM (SELECT model, price
      FROM PC
      WHERE price = (SELECT MAX(price)
                     FROM PC
                    )
      UNION
      SELECT model, price
      FROM Laptop
      WHERE price = (SELECT MAX(price)
                     FROM Laptop
                    )
      UNION
      SELECT model, price
      FROM Printer
      WHERE price = (SELECT MAX(price)
                     FROM Printer
                    )
     ) T
WHERE price = (SELECT MAX(price)
               FROM Laptop
              );

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

Но сначала разберем, что же делает этот запрос. В каждом из трех аналогичных подзапросов разыскиваются максимальные по цене модели по каждому из трех видов продукции — ПК, ноутбукам и принтерам. Далее используется оператор UNION для объединения найденных моделей, что, помимо этого, устраняет дубликаты строк модель, цена. Наконец, отбираются только те модели, цена которых совпадает с максимальной ценой на ноутбуки.

Поэтому, если максимальной окажется цена на принтеры, то данное решение не будет приниматься системой. Но тогда будет приниматься решение, в котором условие отбора будет следующим:

WHERE price = (SELECT MAX(price)
               FROM Printer  
              )

Более того, если максимальная цена будет у моделей только одного типа продукции (скажем, принтеров), то будет приниматься еще более неправильно решение:

Решение 1.16.2

SELECT DISTINCT model
FROM Printer
WHERE price = (SELECT MAX(price)
               FROM Printer
              );

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

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

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

Рассмотрим еще один, хотя и неправильный, подход без использования 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
                    );

В предложении 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.

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