06:22

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Typical mistakes at the solution of SELECT statement exercises

Database «Ships»

Exercise #(-2) page 4

Solution 3.6.4.

Using HAVING clause.

Console

Execute

SELECT country, QTY, MIN(launched)
FROM (SELECT country, launched, COUNT(name) QTY
 FROM Classes c JOIN 
 Ships s ON c.class = s.class
 GROUP BY country,launched
 HAVING COUNT(name) = (SELECT MAX(qty) 
 FROM (SELECT country,launched,COUNT(name) qty
 FROM Classes c1 JOIN 
 Ships s1 ON c1.class = s1.class
 WHERE country = c.country
 GROUP BY country,launched
 )e
 )
 )T
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.

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

HAVING MAX ships exercise (-2)

Previos [Exercise #19 (rating)]

[Exercise #46] Next

Last added:

Tags

aggregate functions ALL ASCII AVG Battles Bezhaev Bismarck C.J.Date Cartesian product CASE cast CHAR CHARINDEX classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CTE data type conversion database schema DATEDIFF DATEPART DATETIME DELETE DISTINCT duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 exercise 37 exercise 39 exercise 46 exercise 54 exercise 55 exercise 56 exercise 57 exercise 7 exercise 70 exercise 8 exercises EXISTS FROM FULL JOIN GROUP BY More tags

The book was updated
several days ago

SQL Problems and solutions

S. I. Moiseenko

Typical mistakes at the solution of SELECT statement exercises

Database «Ships»

Exercise #(-2) page 4