01:40

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Common mistakes in solving SELECT statement exercises

Database «Ships»

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.Qty
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

Previous [Exercise #151]

[Exercise #46] Next

Last added:

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
month ago

SQL Problems and solutions

S. I. Moiseenko

Common mistakes in solving SELECT statement exercises

Database «Ships»

Exercise #(-2) page 3