loading..
Русский    English
04:08
go

Exercise #(-2) page 4

Solution 3.6.4.

Using HAVING clause.

Console
Execute
  1. SELECT country, QTY, MIN(launched)
  2. FROM (SELECT country, launched, COUNT(name) QTY
  3.  FROM Classes c JOIN 
  4.  Ships s ON c.class = s.class
  5.  GROUP BY country,launched
  6.  HAVING COUNT(name) = (SELECT MAX(qty) 
  7.  FROM (SELECT country,launched,COUNT(name) qty
  8.  FROM Classes c1 JOIN 
  9.  Ships s1 ON c1.class = s1.class
  10.  WHERE country = c.country
  11.  GROUP BY country,launched
  12.  )e
  13.  )
  14.  )T
  15. GROUP BY t.qty, t.country;

If the subquery of the FROM clause rows {country, launch year, quantity of ships} are determined first. Then HAVING clause filters only rows with quantity of ships that is equal to maximum quantity of ships for this country. Take note that subquery in this predicate is correlated.

  1. WHERE country = c.country

That is why MAX(qty) relates to certain country from the main query and is not a global maximum. At last, it determines minimal year for each combination {country, maximum quantity of ships}.

Can we move to the next task? No we can’t. All of considered variants of solutions contain one type of mistake. We offer you to find it by your own.

If you can’t make it you are free to look at Q&A.

To solve a problem on SQL-EX.RU
Pages 1 2 3 4
Tags
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
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.