11:03

# Exercise #32 page 3

Solution 3.14.3

Console
Execute
`SELECT country, AVG(bore*bore*bore/2) FROM Ships s FULL JOIN  Outcomes o ON s.name = o.ship LEFT JOIN  Classes c ON c.class = ISNULL(s.class, o.ship) WHERE c.class IS NOT NULL GROUP BY country;`

The mistake lies in the join.

`Ships s FULL JOIN Outcomes o ON s.name = o.ship`

It it visible only in case when some ship has participated in multiple battles, as then it will be counted multiple times in a result set. But we do not claim that we can’t use a full outer join here. However, you need to care about excluding the duplicates.

Solution 3.14.4

Console
Execute
`SELECT country, AVG(bore*bore*bore/2) FROM (SELECT country, bore, name FROM Classes LEFT JOIN  Ships ON Ships.class = Classes.class UNION SELECT DISTINCT country, bore, ship FROM Classes C LEFT JOIN  Outcomes O ON O.ship = C.class  WHERE NOT EXISTS(SELECT name  FROM SHips  WHERE name = O.ship) AND  NOT (ship IS NULL) ) ABCGROUP BY country;`

Let’s consider a case when there is class (e.g. name – class_1, gun caliber – 12) without ships of this class in the database and another class (e.g. name – class_2, gun caliber – 14) which has only one main ship in the database referred to in the Outcomes table. In this case both classes belongs to one country, let’s say, country_1. For more clarity, add a class column to the table, then the first query within union will produce the following results:

country bore Name class
country_1 12 NULL class_1
country_1 14 NULL class_2
while the second query will produce the correct results:

country bore Name class
country_1 14 class_2 class_2

As you can see, we will consider two odd rows in our result set.

 Pages 1 2 3 4