Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 29 March 2024 01:24:25


www.sql-ex.ru
Skip Navigation Links  

 

Print  Print version

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

Print  Print version


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.