Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 23 October 2017 07:14:19


www.sql-ex.ru
Skip Navigation Links  

 

Print  Print version

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

Print  Print version


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.