Common mistakes in solving SELECT statement exercises

Database «Ships»

One of characteristics of a ship is one-half cube of calibre of its main guns (mw). Within 2 decimal places, define the average value of mw for the ships of each country which has ships in database.

Guns caliber as well as a country are Classes table attributes. Therefore, here you have to find in the database all ships with known class. Note about taking into account ships from the Outcomes table, as usual, means that main-ship class could be known even if it’s not present in the Ships table.

Then you have to add a row for defining the guns weight (mw) and calculate the average mw value by grouping ships by country.

Let's take a look on the query rejected by the system:

Solution 3.14.1

Console

Execute

SELECT DISTINCT Classes.country, 
 (SELECT AVG( pen.p ) 
 FROM (SELECT (c1.bore*c1.bore*c1.bore)/2 AS p 
 FROM Classes AS c1, Ships AS s1
 WHERE c1.class = s1.class AND 
 c1.country = Classes.country AND 
 c1.bore IS NOT NULL
 UNION ALL
 SELECT (c2.bore*c2.bore*c2.bore)/2 
 FROM Classes AS c2, Outcomes 
 WHERE c2.country = Classes.country AND 
 c2.class = Outcomes.ship AND 
 c2.bore IS NOT NULL AND 
 Outcomes.ship NOT IN (SELECT ss.name 
 FROM Ships AS ss
 )
 ) AS pen 
 WHERE pen.p IS NOT NULL
 ) AS weight
FROM Classes
WHERE Classes.country IS NOT NULL;

SELECT DISTINCT Classes.country, (SELECT AVG( pen.p ) FROM (SELECT (c1.bore*c1.bore*c1.bore)/2 AS p FROM Classes AS c1, Ships AS s1 WHERE c1.class = s1.class AND c1.country = Classes.country AND c1.bore IS NOT NULL UNION ALL SELECT (c2.bore*c2.bore*c2.bore)/2 FROM Classes AS c2, Outcomes WHERE c2.country = Classes.country AND c2.class = Outcomes.ship AND c2.bore IS NOT NULL AND Outcomes.ship NOT IN (SELECT ss.name FROM Ships AS ss ) ) AS pen WHERE pen.p IS NOT NULL ) AS weight FROM Classes WHERE Classes.country IS NOT NULL;

    Scheme

The given query is interesting, because here we have omitted the grouping and average value of mw for a country is calculated with correlating subquery executed for each country fetched from the Classes table. Note that if a country has several classes (which is highly possible), the same subquery will be executed for each class which leads to degrading performance. Removing the received duplicates with DISTINCT will also negative influence on the performance. But we have another question: why this query is incorrect? To clarify this, let's analyze it by parts:

Let's starts with subquery with uniting (UNION ALL) of two queries:

(1)

SELECT (c1.bore*c1.bore*c1.bore)/2 AS p 
FROM Classes AS c1, Ships AS s1
WHERE c1.class = s1.class AND 
 c1.country = Classes.country AND 
 c1.bore IS NOT NULL

and

(2)

SELECT (c2.bore*c2.bore*c2.bore)/2 
FROM Classes AS c2, Outcomes 
WHERE c2.country = Classes.country AND 
 c2.class=Outcomes.ship AND 
 c2.bore IS NOT NULL AND 
 Outcomes.ship NOT IN (SELECT ss.name 
 FROM Ships AS ss 
 )

Query (1) calculates gun weight for ships from the Ships table for the country fetched from the correlating subquery. Condition where c1.bore IS NOT NULL, in our opinion, is absolutely useless, because even classes with unknown caliber do exist this values will be automatically rejected by AVG function. But this is not a mistake.

Query (2) makes the same calculations for main ships from Outcomes, which are absent from the Ships table.

Next, merging with UNION ALL let us save all weight duplicates, that is necessary, as, at least, one-class ships have the same caliber guns.

Average value by country is calculated in outer query, filtering the case where caliber is unknown for all ships of some country (WHERE pen.p IS NOT NULL). This is because if the AVG function is applied for the empty set, the result will be NULL.

Finally, we are deriving the required data in the main query.

Have you already found a mistake? If no, then we need some knowledge of subject area. What is the Outcomes table? It stores data about battles in which ships have taken part. The ship can take part in multiple battles, unless it was sunk. Thus, potentially, we are counting the main ship multiple times. Thinking formally, the primary key {ship, battle} allows ship appearance more than once.

At the same time, we can't use UNION instead of UNION ALL, because of the above-described reasons.
But it won't be too difficult to correct this query.

Analyzing users errors, usually, author shows data which produce the wrong answer on users queries. Our advice: fill your test databases with similar data, this will raise your testing efficiency on other tasks too.