19:58

	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:

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

duplicates ships classes exercise 32

Previous [Exercise #57]

[Exercise 78] Next

Last added:

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 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
several days ago

SQL Problems and solutions

S. I. Moiseenko

Common mistakes in solving SELECT statement exercises

Database «Ships»

Exercise #32 page 3