Русский    English

Exercise #56 page 2

Here is one more solution to this task, which does not use UNION, but contains another mistake:

Solution 3.12.2

  1. SELECT classes.class, COUNT(ship) sunked
  2. FROM Classes FULL JOIN
  3. Ships ON classes.class = ships.class LEFT JOIN
  4. (SELECT ship
  5. FROM Outcomes
  6. WHERE result = 'sunk'
  7. ) s ON s.ship = ships.name OR
  8. s.ship = classes.class
  9. GROUP BY classes.class;

The first (full) join

  1. Classes FULL JOIN
  2. Ships ON classes.class = ships.class
will include all possible classes of ships. Note that in this case LEFT JOIN would be enough in view of constraints on the Ships table, i.e. this table can't have a ship of a class which is absent from Classes table.

Then the left join with sunken ships from Outcomes table is taken on the following predicate (the set s includes all the sunken ships):

  1. ON s.ship = ships.name OR s.ship = classes.class

Thus, result set includes a ship if her name coincides with a name of sunken ship or if the class coincides with a name of sunken ship. For the examples of data that we considered before, the above query will work correctly, unlike of solution 3.12.1. Indeed, if two sunken ships belong to the Bismarck class and one of these is a head ship absent from Shipa table, the both will be accounted up due to above predicate. On the other hand, if the head ship is presented in Ships table, it does not matter as the predicate will be evaluated as true also.

But where is mistake in the solution? The mistake consists just in the predicate of latter join. Let Ships table include unsunken ships of any class (for example, ships "A" and "B" of Class_1 class). In addition, let Outcomes table have sunken head ship of the same class. Then the following tables will be joined (only columns meaningful for analysis are retained):

Class Name
Class_1 A
Class_1 B


Ship (derived table s)
on predicate

  1. s.ship = classes.class

As a result the table will be obtained that includes ships which did not be sunken but be considered by this solution:

Class Name Ship
Class_1 A Class_1
Class_1 B Class_1

In other words, sunken head ship is counted up not once but with each ship of the same class in the Ships table (both the sunken and unsunken). Anyhow, but COUNT (ship) = 2, that is incorrect, as only one ship has been sunken.

By the way, it is obviously from what have been said how to correct this solution; this is very simply in so doing. You can add 8 characters. :-) Do you ready to try?

Bookmark and Share
Pages 1 2 3 4 5
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 CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.