Упражнение 26 (подсказки и решения)

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

Решение 4.12.1

SELECT AVG(price)
FROM (SELECT price
    FROM PC
    WHERE model IN (SELECT model
                    FROM product
                    WHERE maker='A' 
                        AND type='PC'
                    )
    UNION
    SELECT price
    FROM Laptop
    WHERE model IN (SELECT model
                    FROM product
                    WHERE maker='A' 
                        AND type='Laptop'
                    )
    ) AS prod;

Решение 4.12.2

SELECT AVG(price)
FROM (SELECT price, model
    FROM pc
    WHERE model IN (SELECT model
                    FROM product
                    WHERE maker='A' 
                        AND type='PC'
                    )
    UNION
    SELECT price, model
    FROM Laptop
    WHERE model IN (SELECT model
                    FROM product
                    WHERE maker='A' 
                        AND type='Laptop'
                    )
    ) AS prod;

Первое из этих решений дает на основной базе результат 772.5, а второе — 773.0 при правильном значении 734.5454545454545.

В запросе 4.12.1 выбираются цены на все модели производителя А из таблицы PC. Затем они объединяются с ценами на все модели производителя А из таблицы Laptop. Наконец, вычисляется среднее значение. Что же тут неправильного? Ошибка состоит в том, как объединяются цены. Оператор UNION исключает дубликаты, поэтому из нескольких одинаковых цен (если таковые имеются) будет оставаться только одна. Как результат, среднее будет посчитано по неверному количеству.

В запросе 4.12.2 выбирается не только цена, но и номер модели. То есть объединение выполняется по паре атрибутов. Это решение было бы правильным, если бы в соответствующей таблице не было одинаковых моделей с одинаковыми ценами. Последнее было бы гарантировано, если бы пара атрибутов {price, model} являлась первичным ключом. Однако согласно нашей схеме это не так. Сама по себе такая ситуация не является нереальной. Представим себе, что одна модель комплектуется большим диском, чем другая модель с тем же номером, а оперативной памяти, наоборот, имеет меньше. Тогда цены у них вполне могут быть одинаковы. Естественно, может быть и несколько идентичных моделей.

В результате объединения будут исключены дубликаты пар {price, model} и, как следствие, получен неверный результат.

Надеемся, теперь вполне очевидно, как следует решать эту задачу.

Вернуться к обсуждению упражнения 26

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