loading..
Русский    English
03:01

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
  1. SELECT DISTINCT Classes.country,
  2. (SELECT AVG( pen.p )
  3. FROM (SELECT (c1.bore*c1.bore*c1.bore)/2 AS p
  4. FROM Classes AS c1, Ships AS s1
  5. WHERE c1.class = s1.class AND
  6. c1.country = Classes.country AND
  7. c1.bore IS NOT NULL
  8. UNION ALL
  9. SELECT (c2.bore*c2.bore*c2.bore)/2
  10. FROM Classes AS c2, Outcomes
  11. WHERE c2.country = Classes.country AND
  12. c2.class = Outcomes.ship AND
  13. c2.bore IS NOT NULL AND
  14. Outcomes.ship NOT IN (SELECT ss.name
  15. FROM Ships AS ss
  16. )
  17. ) AS pen
  18. WHERE pen.p IS NOT NULL
  19. ) AS weight
  20. FROM Classes
  21. 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)

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

and

(2)

  1. SELECT (c2.bore*c2.bore*c2.bore)/2
  2. FROM Classes AS c2, Outcomes
  3. WHERE c2.country = Classes.country AND
  4. c2.class=Outcomes.ship AND
  5. c2.bore IS NOT NULL AND
  6. Outcomes.ship NOT IN (SELECT ss.name
  7. FROM Ships AS ss
  8. )

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.

Bookmark and Share
Pages 1 2 3 4
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100