02:49

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

Solution 4.19.2

Console
Execute
`SELECT * FROM (SELECT launched [year], COUNT(*) cnt                FROM  Ships                WHERE launched IS NOT NULL               GROUP BY launched               ) xWHERE cnt >= ALL(SELECT COUNT(*) cnt                  FROM  Ships                  WHERE launched IS NOT NULL                 GROUP BY launched                 );`

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:

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

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

Console
Execute
`SELECT * FROM (SELECT launched [year], COUNT(launched) cnt                FROM Ships                GROUP BY launched               ) xWHERE cnt >= ALL(SELECT COUNT(launched) cnt                  FROM Ships                  GROUP BY launched                 );`

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.

Notes:

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 solve a problem on SQL-EX.RU

 Pages 1 2
Tags
The book was updated
several days ago
https://exchangesumo.com/obmen/SBRFRUB-HCBRUB/