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 beginners solution:

  1. SELECT country, MAX(x), MIN(launched)
  2. FROM (SELECT country, COUNT(*) x , launched
  3. FROM Ships b, Classes a
  4. WHERE a.class = b.class
  5. GROUP BY country, launched
  6. ) s
  7. WHERE launched = ANY(SELECT MIN(launched)
  8. FROM Ships bb, Classes aa
  9. WHERE bb.class = aa.class
  10. GROUP BY country, launched
  11. )
  12. 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:

  1. SELECT MIN(launched)
  2. FROM Ships bb, Classes aa
  3. WHERE bb.class = aa.class
  4. 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.

Bookmark and Share
Pages 1 2 3 4
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.