Exercise #32

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

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;

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.

Here’s another query posted on the forum.

Solution 3.14.2

SELECT Country, AVG(Wght) Wght, ISNULL(AVG(Wght),0)
FROM (SELECT DISTINCT Country, name, ship,
CASE WHEN (Outcomes.Ship IS NULL AND
Ships.Class IS NULL)
THEN NULL
ELSE POWER(Bore,3)/2
END Wght
FROM Outcomes FULL OUTER JOIN
Ships ON Outcomes.Ship = Ships.Name RIGHT OUTER JOIN
Classes ON Outcomes.Ship = Classes.Class OR
Ships.Class = Classes.Class
) s
GROUP BY Country;

You can see another solution. Here we have grouping by countries, full outer join and application of
non-standard functions (SQL Server):

ISNULL(var, sub) — – returns var unless it’s NULL, otherwise returns sub;

POWER(var, N) — returns var raised to the power of N.

Despite the obvious difference, this query contains the same mistake as the previous one. One ship could be counted multiple times if it was involved in several battles. Try to fix this mistake without changing the solution logic.

Below you can find three solutions containing different errors. Namely, there is some data for each solution that produce a non-recurrent result in other solutions. So, let’s take a closer look only on “unique” mistakes.

Solution 3.14.3

SELECT country, AVG(bore*bore*bore/2)
FROM Ships s FULL JOIN
Outcomes o ON s.name = o.ship LEFT JOIN
Classes c ON c.class = ISNULL(s.class, o.ship)
WHERE c.class IS NOT NULL
GROUP BY country;

The mistake lies in the join.

Ships s FULL JOIN Outcomes o ON s.name = o.ship

It it visible only in case when some ship has participated in multiple battles, as then it will be counted multiple times in a result set. But we do not claim that we can’t use a full outer join here. However, you need to care about excluding the duplicates.

Solution 3.14.4

SELECT country, AVG(bore*bore*bore/2)
FROM (SELECT country, bore, name
FROM Classes LEFT JOIN
Ships ON Ships.class = Classes.class
UNION
SELECT DISTINCT country, bore, ship
FROM Classes C LEFT JOIN
Outcomes O ON O.ship = C.class
WHERE NOT EXISTS(SELECT name
FROM SHips
WHERE name = O.ship) AND
NOT (ship IS NULL)
) ABC
GROUP BY country;

Let’s consider a case when there is class (e.g. name – class_1, gun caliber – 12) without ships of this class in the database and another class (e.g. name – class_2, gun caliber – 14) which has only one main ship in the database referred to in the Outcomes table. In this case both classes belongs to one country, let’s say, country_1. For more clarity, add a class column to the table, then the first query within union will produce the following results:

As you can see, we will consider two odd rows in our result set.

Solution 3.14.5

SELECT Country, AVG(bore*bore*bore)/2
From (SELECT c.country, bore
FROM Classes C,
Ships S
WHERE S.class = C.Class AND
NOT bore IS NULL
UNION ALL
SELECT country, bore
FROM Classes C,
OutComes O
WHERE O.Ship = C.Class AND
NOT EXISTS (SELECT 1
FROM Ships S
Where s.Name = O.Ship
) AND
Not bore IS NULL
GROUP BY country, bore
) AS Q1
GROUP BY country;

Two-attribute relations {country, caliber} are joined in the sub query. The second query predicate is the following:

NOT EXISTS (SELECT 1
FROM Ships S
Where s.Name = O.Ship
)

It eliminates possibility of multi-counting the ship if this ship is present in both Ships and Outcomes tables. This justifies the use of UNION ALL operator. Duplicates which can appear in case of ship participating in multiple battles are excluded by grouping by country and caliber.

However, consider a case when a country has several classes of ships with the same caliber and these ships are present in the Outcomes table. As the result, only one ship would be counted, instead of several times what makes the given solution a mistaken.

To solve the problem on SQL-EX.RU