23:56

# Exercise #(-2) page 1

It is necessary to determine year when maximum quantity of ships had been launched for each country. If there were several diffirent years with maximum value, the minimal one must be returned. On the output: country, quantity of ships, year.

Solution 3.6.1. Here is a typical beginner’s solution:

Console
Execute
`SELECT country, MAX(x), MIN(launched) FROM (SELECT country, COUNT(*) x , launched  FROM Ships b, Classes a WHERE a.class = b.class  GROUP BY country, launched ) sWHERE launched = ANY(SELECT MIN(launched)  FROM Ships bb, Classes aa  WHERE bb.class = aa.class  GROUP BY country, launched ) GROUP BY country;`

The subquery in FROM clause determines quantity of rows for each unique pair {country, year of launch}. In the terms of the data domain this means that it is determined quantity of ships that had been launched by every single country in every year. Let the resultant set of the subquery “s” will be the following:

country x launched
Gt.Britain 6 1916
Gt.Britain 1 1917
Japan 1 1913
Japan 2 1914
Japan 2 1915
Japan 1 1916
Japan 1 1941
Japan 1 1942
USA 1 1920
USA 1 1921
USA 3 1941
USA 2 1943
USA 2 1944

Next, in the WHERE clause it will be selected rows with year of launch that matches any year from this subquery:

Console
Execute
`SELECT MIN(launched) FROM Ships bb, Classes aa WHERE bb.class = aa.class GROUP BY country, launched;`

What this subquery gives us? It gives us all years from the table above, because the data is grouped by country and year. As result this subquery doesn't take any effect to the data set, therefore it is unnecessary. It seemes that author want to express the minimal year condition in this expression. But the minimal year must be considered as minumum of those years in which maximum ships’ quantity for this country had been launched.

 Pages 1 2 3 4