Exercise #55

It seems, that mistakes are caused by traps in previous tasks. When users solve tasks whith level one difficulty, they try to take into account all possible conditions. The taking all variants in solution into account doesn`t make it wrong, but it increases cost of execution. Besides, some such solutions contain typical mistakes, that it was decided to examine it.

First of all, users are try to take into account the lead ships from table Outcomes. The Outcomes table isnt necessary for solution, because we need to find out the year, which is an attribute of table Ships. So even if the ship exists in the Outcomes table, but is absents in Ships table, we cant find out its year of launch though. In case when DB hasn`t  other ships of this class the existance of lead ship draw a blank at all, because the result must be the such:

Let`s move to the analysis of solutions having mistakes with accounting the Outcomes table.

Solution 3.11.1

SELECT C.class , launched
FROM Classes C ,
(SELECT name , class , launched
FROM Ships
UNION
SELECT ship , ship , NULL
FROM Outcomes O
WHERE NOT EXISTS (SELECT *
FROM Ships S
WHERE S.class = O.ship
)
UNION
SELECT ship , ship , MIN(launched)
FROM Ships S ,
Outcomes O
WHERE S.class = O.ship
GROUP BY ship
) S
WHERE C.class = S.name;

Take a look at subquery S. It has union of three queries with {ship name, class, year of launch} as resulting set of attributes. The first query selects all ships from Ships table. The second one selects ships from Outcomes table which have name doesnt matches any of classes from Ships table. The NULL value is using as the year of launch, thats correct. The ship name is associates with class name (SELECT ship, ship, NULL). It`s correct because this query selects lead ships:

WHERE C.class = S.name

Finally, third query determines minimal launch year for shipses classes having lead ships in Outcomes table.

The mistake occures when lead ship from Outcomes table exists in Ships table as well and its year of launch is unknown. Then the first query returns row with year is NULL for this class and the third one with minimal launch year. Totally we have two non-duplicate rows, therefore they haven`t been excluded by using UNION.

Also, the select condition by lead ships automatically excludes ships which doesn`t have lead ships at all.

Solution 3.11.2

SELECT DISTINCT class, MIN(launched)
FROM Ships GROUP BY Class
UNION
SELECT DISTINCT Ship AS class, NULL
FROM Outcomes
WHERE ship IN (Select class
FROM Classes
) AND
ship NOT IN (SELECT name
FROM Ships
);

The first query in union counts minimal year of launch from Ships table by classes. The second query selects lead ships from Outcomes table (predicate IN) which is absent in Ships table (predicate NOT IN). Such ship takes into account with NULL year of launch, as it was done in solution 3.11.1. But current solution has other mistake. Outcomes table consists ship which is absent in Ships table (e.g. Bismarck ship) but Ships table has other ship of Bismark class with known year of launch. In this case we have two rows on the one class with different year of launch (known year and NULL value).

Solution 3.11.2 has completly excessive keyword DISTINCT. The first subquery is using GROUP BY, thats why it doesn`t have duplicates. The second subquery removes duplicates by using UNION.

Next, let`s consider solutions without using Outcomes table, but having logical mistakes yet.

Solution 3.11.3 (comments belong to author of solution)

/*
the launch year of lead ships
*/
SELECT class, launched AS year
FROM Ships
WHERE name = class
UNION
/*
 minimal year of launch by classes  
 which haven`t information about lead ships in Ships table   
*/
SELECT class, MIN(launched)
FROM Ships
WHERE class NOT IN (SELECT class
FROM Ships
WHERE name = class
)
GROUP BY class
UNION
/*
  using NULL value as launch year for absent in Ships table classes   
*/
SELECT class, NULL
FROM classes
WHERE class NOT IN(SELECT class
FROM ships
);

This solution takes into account all classes, include that which hasnt ships in DB (the last query in union). Seemingly, mistake of this solution results from attempting to account lead ships (which is the first ships in classes, therefore they have minimal year of launch) and classes which isnt have lead ships in DB.

Let`s imagine situation when launch year of ship is unknown (NULL value), but ship is presents in Ships table. In the mean time, Ships table contains ship with the same class with known launch year. Then just this year must be in resulted set.

But this solution returns NULL, meantime the ship with known year will be ignored due to the following filtration:

WHERE class NOT IN (SELECT class
FROM Ships
WHERE name = class
)

Such mistake is contains in the next solution. It suggested to analyze it by your own.

Solution 3.11.4

SELECT class,  
 (SELECT launched   
 FROM (SELECT launched   
 FROM Ships sh   
 WHERE cl.class = sh.name  
 UNION  
 SELECT launched   
 FROM Ships sh   
 WHERE launched = (SELECT MIN(launched)   
 FROM ships sh2  
 WHERE class = cl.class AND   
 NOT EXISTS(SELECT launched   
 FROM ships sh   
 WHERE cl.class = sh.name  
 )  
 )  
 ) tab  
 ) year  
FROM classes cl

T&S

To solve the problem on SQL-EX.RU