Main page
Typical errors at solution of exercises.
Exercise 26
S. Moiseenko
Define the average price of the PCs and laptops produced by maker A. Result set:
single total price.
The example of the decision is described in the FAQ №6 which can be written down
schematically as:
SELECT ( SUM( price_PC) + SUM(price_Laptop))/COUNT(PC) +
COUNT(Laptop))
WHERE maker='A'
This query will work incorrectly if the producer A in a database does not have
production of one of the kinds. Let, for example, there is no PC then COUNT(
PC) will return 0, and the denominator will be correctly counted. However SUM
(price_PC) will give NULL. As is known, arithmetic operations with NULL-value
also give NULL. In the result we receive not SUM (price_Laptop)/COUNT (Laptop),
but NULL.
Below there is the pair of incorrect decisions which still contain other mistake
is given.
(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
(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
The first decision gives the result 772.5, the second - 773.0 at correct value
734.5454545454545.
In the query (1) the prices for all models of producer А are selected from table
PC. Then they are united with the prices for all models of producer А from
table Laptop. At last, average value is calculated. What's wrong here? The
mistake will persist, as the prices are united. Operator UNION excludes
duplicates, therefore from the several identical prices (if those are
available) the only one will stay. As result, the average value will be counted
by incorrect amount.
In the query (2) not only the price is selected, but so the number of the model
as well. I.e. the union is performed by the pair of the attributes. This
decision would be correct if there aren't identical models with identical
prices in the corresponding tables. The last would be guaranteed if the pair
{price, model} is the primary key. However according to our scheme this is not
so. At the same time such situation is not unreal. Let's imagine that more than
one model is replenished by a larger disk that another model but it contain
less memory. Then they quite will have different prices.
As a result of the union duplicates of pairs {price, model} will be excluded and
as consequence, the incorrect result will be given.
I hope, now it is quite obvious, how one should solve this problem.
» Given examples here can be done directly on the website by selecting the check
box “Without checking” on the page with
SELECT exercises.
Main page