Sql-ex.ru selecting an extract from exercise 51
I am doing the exercises at http://www.sql-ex.ru/learn_exercises.php .
In Exercise 51, they ask "Find the names of the ships with the most guns among all ships with the same displacement (including ships in the Results table).
This is my code, it passes the 1st kind of db, but doesn't skip the second, invisible, db (error - "less by 3"):
with t1 as
(select displacement, max(numguns) 'guns' from classes group by displacement),
t2 as
(select class from classes, t1 where classes.displacement = t1.displacement and classes.numguns = t1.guns)
select name from ships, t2 where ships.class = t2.class
union
select ship from outcomes, t2 where outcomes.ship = t2.class
The logic I am using is that the only table in the db hold offset and multiple guns is the class, and so t1 and t2 retrieve the class names, and then I just get the names belonging to those classes from the ships and the results table.
There is no way to understand why there should be any error.
EDIT: Added checking for null values in classes and I get a new error in the second invisible db - "data mismatch (11)":
with t1 as
(select displacement, max(numguns) 'guns' from classes group by displacement),
t2 as
(select class from classes, t1 where (classes.displacement = t1.displacement or classes.displacement is null) and (classes.numguns = t1.guns or classes.numguns is null))
select name from ships, t2 where ships.class = t2.class
union
select ship from outcomes, t2 where outcomes.ship = t2.class
source to share
If you look in the database description of the corresponding exercise, you will see the following:
The ships in the classes have the same overall design. Usually the class is assigned either the name of the first ship built , according to the corresponding design, or a name other than any ship name in the database. The ship whose name is assigned to the class is called the lead ship .
This means that the table outcomes
can contain a lead ship whose name is used as a class in the table classes
. This is why you need a union
name and a class from ship
a name and a name again because outcomes
of lead ship
having the same class as the name.
so you can use this query or a similar one with the same logic:
WITH cte
AS ( SELECT A.name ,
C.numGuns ,
C.displacement
FROM ( SELECT S.name ,
S.class
FROM dbo.Ships AS S
UNION
SELECT O.ship ,
O.ship
FROM dbo.Outcomes AS O
) AS A
JOIN dbo.Classes AS C ON A.class = C.class
)
SELECT cte.name
FROM cte
JOIN ( SELECT cte.displacement ,
MAX(cte.numGuns) AS MaxNumGun
FROM cte
GROUP BY cte.displacement
) AS M ON cte.displacement = M.displacement
AND cte.numguns = M.MaxNumGun
Output
source to share