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

Подсказки и решения

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

Решение 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;

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;

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