Main page
Typical errors at solution of exercises.
Exercise 50
S. Moiseenko
The weight of a shell fired by the gun, in pounds, is nearly equal to half the
cube of its caliber. Define the average weight of shells for ships of each
country. Take into account the ships from the Outcomes table, too.
The guns' caliber, as well as the country, is an attribute of the Classes table.
That means we need to find all ships in the database for which the class is
known. The hint to remember the ships from the Outcomes table, as usual,
signifies that the leading ship's class is known even if it's not included in
the Ships table.
Then add a calculations column to define the weight of a shell and count the
average value of that weight, arranging the ships by countries.
Let's take a look at the query the system declines:
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
This query is interesting because it doesn't use grouping and the average value
for a country is defined with the help of the correlating subquery executed for
every country from the Classes table. Besides, it is made fully according to
the standard. We can immediately mark the inefficiency of this query's
execution because if a county has several classes of ships, which is not
surprising for us, the subquery will be performed for each class, and that's
obviously odd. The duplicates we get as a result are removed by DISTINCT and
that will influence the velocity. But there's another question that concerns
us, why is this request wrong. To understand it let's examine it by parts.
Let's begin form the subquery where two queries are combined (UNION ALL):
(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)
In query (1) the weight of ships' shells from the Ships table is counted for a
country that is forwarded from the outer request (correlating subquery). The
clause c1.bore IS NOT NULL seems absolutely unnecessary to me, for even if
there are classes with unknown caliber, these values will be automatically
excluded while calculating the average value by the AVG function. But that's
not the mistake in the solution of this task.
In query (2) the equivalent calculations are performed for the leading ships
from the Outcomes table, which are absent in the Ships table.
Then the conjunction by UNION ALL allows keeping all the weight duplicates,
which is necessary as at least ships of the same class have shells of one
caliber (weight).
In the outer query the average value for the country is counted, filtering the
case when the caliber is unknown for all ships of a country (WHERE pen.p IS NOT
NULL). This is explained by the fact that if AVG is applied to an empty set the
result of the calculation will be NULL.
At last, in the main request we print out the data we need for this task.
Have you found the mistake yet? If not, the knowledge of the subject area will
help us. What is the Outcomes table? The data on ships that took place in
battles is stored here. And if the ship was not sunk it can take part in
several battles. So we potentially count the leading ship several times. If
considering formally, the primary key on this table {ship, battle} allows same
ship to appear more than once.
At the same time we can't use UNION instead of UNION ALL according to the
reasons discussed above but anyway it won't be very difficult for you to
correct this query now.
Pointing out the mistakes of our visitors, I indicate those data variants on
which the queries we discuss return incorrect data. I advice you to fill your
database with similar data so the testing of your queries will be more
efficient even on other tasks.
» 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