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

Solution 4.19.2

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

Checking for NULL values in the subquery of WHERE clause is not necessary if you'll use function COUNT (launched) instead of function COUNT (*) because only the ships with known launch year in this case will be counted up:

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

For all ships with unknown launch year the row with value 0 will be received so, if in a set there is no records, COUNT function returns this value. The last should not confuse us as the quantity of the ships in the main query is more than zero if there is at least one ship with known launch year. It is possible to act similarly in the main query also to allow us to receive more brief form of the solution:

Solution 4.19.3

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

For the sake of justice it is necessary to note that the cost of the solution 4.19.3 in accordance with its execution plan in  A database management system (DBMS) by Microsoft Corporation. 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 2000 slightly concedes (in the third significant digit) against the cost of the solution 4.19.2.


It is possible to see cost of any query to educational databases and also the procedural plan of its execution on this page of the site.

To return to discussion of exercise #(-2)

To solve a problem on SQL-EX.RU

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 CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.