Русский    English

Exercise #(-2) (tips and solutions) page 1

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.

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


In  SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server, names of columns can be bounded by square brackets ([]) to avoid ambiguity. For example, ambiguity arises at use of blanks in names when the first word could be interpreted as a name of a column, and the second - as its alias. Good practice is to avoid usage of blanks in names, however, their use for formation of report's headings is quite justified.

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:

  1. cnt >= ALL(SELECT COUNT(*) cnt
  2. FROM Ships
  3. GROUP BY launched
  4. )

Finally we get.

Solution 4.19.1

  1. SELECT *
  2. FROM (SELECT launched [year], COUNT(*) cnt
  3. FROM Ships
  4. GROUP BY launched
  5. ) x
  6. WHERE cnt >= ALL(SELECT COUNT(*) cnt
  7. FROM Ships
  8. GROUP BY launched
  9. );

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:

Bookmark and Share
Pages 1 2
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100