06:24

# Exercise #32 page 1

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 weightFROM ClassesWHERE 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 s1WHERE 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.

 Pages 1 2 3 4
Tags
The book was updated
month ago
•  в Москве заменить тормозные колодки на Porsche недорого porsche-centr.ru
•  www.ros-znak.com Знаки пожарной безопасности, светоотражающие знаки, плакаты. Большой выбор ros-znak.com
•  торговые сигналы от профессионала в режиме онлайн strade24.com