Typical mistakes at the solution of SELECT statement exercises

Tips and solutions

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
                 );

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                  );

    Scheme

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
                 );

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                  );

    Scheme

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  Cèñòåìà óïðàâëåíèÿ ðåëÿöèîííûìè áàçàìè äàííûõ (ÑÓÁÄ), ðàçðàáîòàííàÿ êîðïîðàöèåé Microsoft. 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.

To return to discussion of exercise #(-2)

To solve a problem on SQL-EX.RU