18:57

# Exercise #(-2) page 3

Solution 3.6.3. Using join instead of correlated subquery.

Console
Execute
`SELECT a.country, a.numShips AS Qty, MIN(launched) AS Year FROM (SELECT country, COUNT(*) AS numShips, launched FROM Classes INNER JOIN  Ships ON Classes.class = Ships.class GROUP BY country, launched ) AS a INNER JOIN (SELECT a.country, MAX(a.numShips) AS Qty  FROM (SELECT country, COUNT(*) AS numShips FROM Classes INNER JOIN  Ships ON Classes.class = Ships.class GROUP BY country, launched ) AS a GROUP BY country) AS b ON a.country = b.country AND a.numShips = b.QtyGROUP 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