12:56

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Common mistakes in solving SELECT statement exercises

Database «Ships»

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)
 ) ABC
GROUP 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: