20:39

# Exercise #55 page 3

Solution 3.11.3 (comments belong to author of solution)

Console
Execute
`/* the launch year of lead ships*/SELECT class, launched AS yearFROM ShipsWHERE name = classUNION/*  minimal year of launch by classes which haven`t information about lead ships in Ships table */SELECT class, MIN(launched)FROM ShipsWHERE class NOT IN (SELECT class  FROM Ships  WHERE name = class )GROUP BY classUNION/*  using NULL value as launch year for absent in Ships table classes */SELECT class, NULLFROM classesWHERE 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 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

Console
Execute
`SELECT class,(SELECT launched FROM (SELECT launched FROM Ships sh WHERE cl.class = sh.nameUNIONSELECT launched FROM Ships sh WHERE launched = (SELECT MIN(launched) FROM ships sh2WHERE class = cl.class AND NOT EXISTS(SELECT launched FROM ships sh WHERE cl.class = sh.name))) tab) yearFROM classes cl`

T&S

 Pages 1 2 3