Common mistakes in solving SELECT statement exercises

Database «Ships»

Solution 3.10.2

Console

Execute

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'
 UNION ALL
 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;

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' UNION ALL 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;

    Scheme

Pay attention to conversion of data type to a number with fixed point, which carries out a demanded rounding off of result.

In the subquery, 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 hand, we can't write UNION instead of UNION ALL according to the reason stated above.

T&S