Exercise #26

Solution 1.18.1

SELECT AVG(av.p) AS avg_price
FROM (SELECT AVG(price) p
FROM Product m, PC
WHERE m.model = PC.model AND
maker = 'A'
UNION
SELECT AVG(price) p
FROM Product m, Laptop l
WHERE m.model = l.model AND
maker = 'A'
) AS av;

In the subquery of FROM clause, the average prices for the PCs and laptops for the manufacturer A are united, then the average of these average values is calculated in the main query. A mistake cleanly arithmetic, which consists that the general average value (which and it is necessary to get) is not equal generally to an average from average values.

Solution 1.18.2

SELECT ((SELECT SUM(price)
FROM Product INNER JOIN
PC ON Product.model = PC.model
WHERE maker='A'
) +
(SELECT SUM(price)
FROM Product INNER JOIN
Laptop ON Product.model = Laptop.model
WHERE maker='A')
) / ((SELECT COUNT(price)
FROM Product INNER JOIN
PC ON Product.model = PC.model
WHERE maker='A')
+
(SELECT COUNT(price)
FROM Product INNER JOIN
Laptop ON Product.model = Laptop.model
WHERE maker='A')
) as AVG_price;

The query 1.18.2 takes the sum price of both the PCs and laptops produced by maker A. Then that sum price is divided by the overall quantity of these PCs and laptops. It is all right from the mahtematical point of view, but is not from the point of view of SQL. If the reference database does not include any PC (or laptop) by the maker A, the COUNT function returns 0 (which is the expected result), but the SUM function returns NULL value. As a result, the sum price will be NULL, but will not be the sum price of other products, which is required to be.

T&S

To solve the problem on SQL-EX.RU