Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 27 May 2024 15:55:28
Skip Navigation Links  


Print  Print version

Main page

Typical errors at solution of exercises. Exercise 54

S. Moiseenko

To within two decimal digits, define the average amount of guns for all the battleships (taking into account Outcomes table).

As a rule, there are no more problems with rounding. But same mistake is regularly admitted. Here is the query that contains it:

SELECT CAST( AVG( numGuns *1.0) AS NUMERIC(10,2)) FROM
  SELECT numguns
     FROM [Classes] c JOIN Ships s ON c.class = s.class
     WHERE type='bb' 
  SELECT numguns
     FROM [Classes] c JOIN Outcomes o ON c.class = o.ship
     WHERE type='bb' and o.ship NOT IN(SELECT name FROM Ships)
  ) t

In the request two queries are combined (UNION ALL). The first one defines the number of guns for the ships from the Ships table which belong to linear ships' classes (‘bb' type). The second one takes into account the head ships of corresponding classes if they are not in the Ships table.

So, it's an attempt to take into account each ship in the database only once. As UNION ALL is used for combining data, the duplicates won't be removed. This is important because many ships will have same number of guns and only this column is printed out in the SELECT clause of this sub query.

But however the mistake is raised by UNION ALL. Let's take a formal turn, that is, we won't think of the subject area but will refer to the scheme. In the Ships table the primary key is the ship's name, that's why the first query in combination will give us one string for each ship of a known class. But in the Outcomes table the key is a pair of {ship, battle}, that means, its unique value is granted for a combination of a ship's name and a battle in which it took part. What results from this is that the same vessel can be mentioned in the Outcomes table for several times if it participated in several battles.

As a result, the second query will return duplicated ships if the leading ship took part in several fights. That's what makes this request wrong.

On the other side, we can't write UNION instead of UNION ALL according to the reason stated above.

Conclusion. It's not enough to perform the conjunction by only the numguns column. It seems to me I've said everything J .

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

Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.