Упражнение 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