Exercise #55

It seems, that mistakes are caused by traps in previous tasks. When users solve tasks with 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 isn’t 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 it is absent from the Ships table, we can’t find out its year of launch though. In case when DB hasn’t other ships of this class the existence of lead ship draw a blank at all, because the result must be the such:

as the task says “for each class”, it means that launch year is unknown for this ship class. This result can be obtained by using the outer join for tables Classes and Ships.

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 doesn’t match any of classes from Ships table. The NULL value is using as the year of launch, that’s 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 ships classes having lead ships in Outcomes table.

The mistake occurs 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 completely excessive keyword DISTINCT. The first subquery is using GROUP BY, that is 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 hasn’t 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 isn’t 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 meantime, 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