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