loading..
Русский    English
09:51
go

Exercise #(-2) page 3

Solution 3.6.3. Using join instead of correlated subquery.

Console
Execute
  1. SELECT a.country, a.numShips AS Qty, MIN(launched) AS Year 
  2. FROM (SELECT country, COUNT(*) AS numShips, launched
  3.  FROM Classes INNER JOIN 
  4.  Ships ON Classes.class = Ships.class
  5.  GROUP BY country, launched
  6.  ) AS a INNER JOIN
  7.  (SELECT a.country, MAX(a.numShips) AS Qty 
  8.  FROM (SELECT country, COUNT(*) AS numShips
  9.  FROM Classes INNER JOIN 
  10.  Ships ON Classes.class = Ships.class
  11.  GROUP BY country, launched
  12.  ) AS a
  13.  GROUP BY country) AS b
  14.  ON a.country = b.country AND a.numShips = b.Qty
  15. GROUP BY a.country, a.numShips;

Inner join by country and quantity of ships from two subqueries is in the FROM clause. The first subquery determines country and quantity of ships that has been launched in each year for it. The second one contains similar query in the FROM clause but filters only pair {country, quantity of ships} that has maximum quantity of ships that has been launched for one year.

As a result of this join the tuple {country, maximum quantity of ships} is appended by year of launch of that quantity. Finally, data get grouped to determine minimal year if maximum occured more then one for single country similar to as it done in solution 3.6.2.
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.