Common mistakes in solving SELECT statement exercises

Database «Ships»

For each class, define the year in which the first ship of this class was launched. If year of launch of the head ship is unknown, define the minimum launch year for ships of this class.Result set: class, year.

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 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 is absents in Ships table, we can`t 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

Console

Execute

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;

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;

    Scheme

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 matches 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 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.