Books and articles about SQL Сменить язык на: Русский 23 October 2017 07:12:44

Main page

## Typical errors at solution of exercises. Exercise 43

S. Moiseenko

For each country, find the year, in which the maximal number of ships had been launched.
In the case of more than one year in question, take a minimal year. Result set : country , number of ships , year.

I decided to explain to all subscribers the same mistake in solving this exercise, because I've tired answering the questions for it. 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.

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

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

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

Finally we get

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

However, there's a mistake. This mistake is not connected with a formal scheme of solution. It's unquestioned. It usually happens when you solve exercises on the website, the mistake lies in the incorrect accounting of enterprise model's features, namely its constraints. Incidentally it is a permissibility of a fact that there may be ships with an unknown year of launching in the database. I want to note here, that if in an enterprise description is not bespoken contrary, non-key fields allow NULL-values. By default it means when we create a table with CREATE TABLE operator.

Building ships is not a rabbit breeding :-). Ships are being built for ages. That's why if for a number of ships 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:

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

Given examples here can be done directly on the website by selecting the check box “Without checking” on the page with SELECT exercises.

Main page


 Usage of any materials of this site is possibleonly under condition of mandatory allocation of the direct link to a sitehttp://www.sqlbooks.ruon each page where used materials are placed. Main Articles Books