Exercise #(-2) page 2

Removing the "launched" column from grouping isn't enough to correct this mistake.

  1. SELECT MIN(launched)
  2. FROM Ships aa, Classes bb
  3. WHERE bb.class = aa.class
  4. GROUP BY country;
as a result we will get minimal years by every country:


Then rows that satisfies predicate would look like this:

country x launched
Gt.Britain 6 1916
Japan 1 1913
Japan 1 1916
USA 1 1920

Thereby we have already lost correct rows for Japan and USA. We should take notice of this row for Japan:

Japan 1 1916
only by the reason that 1916th year concured with minimal year for USA. The following code doesn't have any sense. But it has mistake though. In the main query:

  1. SELECT country, MAX(x), MIN(launched)
  2. GROUP BY country
we have grouping by country with determining of two aggregated factors maximum of ships quatities and minimum of years. Using of grouping in this case leads to mistake. Indeed, rows that we need could already be found in table which in the FROM clause (for instance {Gt.Britain, 6, 1916}). We do not need any grouping here, we only need to filter out wrong rows. As a result of grouping only one row will be remained. It have statistic rates for whole group. In this case the maximum and the minimum gain in different rows of the group. This is clearly visible at USAs ships table. In this one minimal year does not correspond maximum value (x=1). The maximum value (x=3) is reached at 1941th year. Thats why such grouping may return right result only if all x values for country are equal.

Everything in this solution is reversed. Nevertheless lets try to correct it by finding out the reasons of mistakes and delusions.

Correlated subquery is needed for binding of year and country. It can be used in WHERE clause (AND aa.country = s.country):

  1. WHERE launched = ANY(SELECT MIN(launched)
  2. FROM Ships bb, Classes aa
  3. WHERE aa.class = bb.class AND
  4. aa.country = s.country
  5. GROUP BY country
  6. )

That is correct, but dont take any effect for now, except maybe it excludes wrong row:

Japan 1 1916

For advance we need to calculate minimal year among ones with maximum quantity of ships for each contry. Maximum quantity of ships is primary here. If we would filter only by minimal year we can lose right rows. Thats why we need to count quantity of ships, not a minimal year:

  1. WHERE x >= ALL(SELECT COUNT(launched)
  2. FROM Ships bb, Classes aa
  3. WHERE bb.class = aa.class AND
  4. s.country=aa.country
  5. GROUP BY country, launched
  6. )

Take a note at predicate >= ALL which gives us maximal value of x. Lets rewrite whole query with taking into account all we have said about grouping in the main query.

Solution 3.6.2

  1. SELECT country, x, 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 x >= ALL(SELECT COUNT(launched)
  8. FROM Ships bb, Classes aa
  9. WHERE bb.class = aa.class AND
  10. s.country=aa.country
  11. GROUP BY country, launched
  12. );

Is this all? Not at all. If maximum value would be gained in several different years the row for every such year will occur in the result. But we need to output the row with minimal year by the data. As it was noted above, this is the certain case when grouping is valid: all x values for country are equal and maximal.

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.