Books and articles about SQL   Сменить язык на: Русский 20 November 2019 08:05:26   Print version

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 Print version


 Usage of any materials of this site is possibleonly under condition of mandatory allocation of the direct link to a sitehttp://www.sqlbooks.ruon each page where used materials are placed. Main Articles Books     Copyright c 2002-2006. All rights reserved. contact