Common mistakes in solving SELECT statement exercises

Tips and solutions

When solving this exercise one typical mistake, which has place in the solution 3.6.2 also, is admitted by participants. But I'll just simplify the formulation for you not to loose pleasure to do this exercise on your own. So,

Find the year, in which the maximal number of ships had been launched. Result set: number of ships, year.

We can determine the distribution of ships' quantity by the years in such a way.

Console

Execute

SELECT launched [year], COUNT(*) cnt 
FROM Ships 
GROUP BY launched;

SELECT launched [year], COUNT(*) cnt FROM Ships GROUP BY launched;

    Scheme

In our case ([year]) square brackets are applied in order to prevent messing with year() function which returns year from the argument of datetime data type.

Now we should leave from all the rows, returned by this query, only those that have maximal quantity (cnt), so that is:

cnt >= ALL(SELECT COUNT(*) cnt 
 FROM Ships 
 GROUP BY launched
 )

Finally we get.

Solution 4.19.1

Console

Execute

SELECT * 
FROM (SELECT launched [year], COUNT(*) cnt 
 FROM Ships 
 GROUP BY launched
 ) x
WHERE cnt >= ALL(SELECT COUNT(*) cnt 
 FROM Ships 
 GROUP BY launched
 );

SELECT * FROM (SELECT launched [year], COUNT(*) cnt FROM Ships GROUP BY launched ) x WHERE cnt >= ALL(SELECT COUNT(*) cnt FROM Ships GROUP BY launched );

    Scheme

However, there's a mistake. This mistake is not associated with a formal scheme of solution. It's unquestioned. It usually happens when you solve exercises on the website, origin of the mistake is in the incorrect accounting of domain model's features, namely its constraints. Database schema of the exercise we are considering adopts a fact that there may be ships with an unknown year of launching in the database. This is firstly due to nullable of the launched column and, second, for a head ship, which is present only in Outcomes table, launch year is unknown also.

Building ships is not a rabbit breeding :-). Ships are being built for years. That's why if for a number of ships the launching year is unknown (NULL), then there's a high probability that the amount of such ships will be higher than the amount of ships launched in any real year. The feature of grouping lies in the fact (mentioned in Standard) that NULL values treat as equal. Hence, all ships with unknown launching year will be summarized with a year NULL. I suppose that the result shouldn't include this string, because the ‘unknown year' doesn't mean the ‘same year'. Certainly, it's disputable. However, all disputes will come to permissibility of using of a specific NULL value in relational model. Discussions about this are being carried on from the times of creation of this model by Codd E.F., whom the idea of NULL value belongs to. However, as far as I know, no deserving alternative was offered.

Returning to our task, in token of measureless respect to Codd I will change the solution in this way: