16:56

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Common mistakes in solving SELECT statement exercises

Tips and solutions

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
               ) x
WHERE 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
               ) x
WHERE cnt >= ALL(SELECT COUNT(launched) cnt 
                 FROM Ships 
                 GROUP BY launched
                 );