sql-ex.ru exercise 51

[ruhestorer]

Hi,

I have a problem with exercise 51 from sql-ex.ru, please help.

Thanks in advance.

Short database description "Ships"

The database of naval ships that took part in World War II is under consideration. The database has the following relations:
Classes(class, type, country, numGuns, bore, displacement)
Ships(name, class, launched)
Battles(name, date)
Outcomes(ship, battle, result)
Ships in classes are arranged to a single project. A class is normally assigned the name of the first ship in the class under consideration (head ship); otherwise, the class name does not coincide with any ship name in the database.
The Classes relation includes the class name, type (bb for a battle ship, or bc for a battle cruiser), country where the ship was built, number of main guns, gun caliber (diameter of the gun barrel, in inches), and displacement (weight in tons). The Ships relation includes the ship name, its class name, and launch year. The Battles relation covers the name and date of a battle the ships participated; while the result of their participation in the battle (sunk, damaged, or unharmed - OK) is in the Outcomes relation.
Notes: 1) Outcomes relation may include the ships not included in the Ships relation. 2) Sunk ship can not participate in battles after that.

Exercise

Find the names of the ships having the largest amount of guns among all the ships with the same displacement (taking into account Outcomes table).

My solution

SELECT A.name FROM (
SELECT s.name as name, c.numguns, c.displacement
FROM classes c JOIN ships s ON s.class = c.class JOIN outcomes o
ON  o.ship=s.name 
WHERE c.numguns= (SELECT MAX(c2.numguns) FROM classes c2 WHERE c.displacement=c2.displacement)
UNION
SELECT s.name as name, c.numguns, c.displacement
FROM classes c JOIN ships s
ON s.class = c.class 
WHERE c.numguns= (SELECT MAX(c2.numguns) FROM classes c2 WHERE c.displacement=c2.displacement)
UNION
SELECT o.ship as name, c.numguns, c.displacement
FROM classes c JOIN  outcomes o
ON o.ship = c.class
WHERE c.numguns= (SELECT MAX(c2.numguns) FROM classes c2 WHERE c.displacement=c2.displacement)
UNION
SELECT c.class as name, c.numguns, c.displacement
FROM classes c 
----
WHERE c.numguns= (SELECT MAX(c2.numguns) FROM classes c2 WHERE c.displacement=c2.displacement) 
) A

I tried to put in ---- place

AND c.class NOT IN (SELECT s.name 
FROM classes c JOIN ships s
ON s.class = c.class 
UNION
SELECT o.ship
FROM classes c JOIN  outcomes o
ON o.ship = c.class
UNION
SELECT s.name
FROM classes c JOIN ships s ON s.class = c.class JOIN outcomes o
ON  o.ship=s.name )

Result:
Incorrect.

Your query produced correct result set on main database, but it failed test on second, checking database.
* Wrong number of records (more by 1)

[Rahul47]

Wrong number of records (more by 1)

Your sub-queries are producing more than one result to be taken by WHERE clause.

[ruhestorer]

Your sub-queries are producing more than one result to be taken by WHERE clause.

No, that is not the case. There is no error and i get the correct result in the main database, but there is second database that returns more results. In sql-ex that is common issue, as it teaches to write correct code, not code which gives you the result you need at the moment, it predicts that there could be duplicate records or records with no additional info and so on.
In most cases i manage to resolve it with addind DISTINCT, but not this time.

[ruhestorer]

I modified a code a little, but it was no help

SELECT DISTINCT A.name FROM 
(SELECT s.name as name, c.numguns, c.displacement
FROM classes c JOIN ships s ON c.class = s.class JOIN outcomes o
ON s.name=o.ship
UNION
SELECT s.name as name, c.numguns, c.displacement
FROM classes c JOIN ships s
ON s.class = c.class 
UNION
SELECT o.ship as name, c.numguns, c.displacement
FROM classes c JOIN outcomes o 
ON o.ship = c.class
UNION
SELECT c.class as name, c.numguns, c.displacement
FROM classes c 
) A
WHERE A.numguns= (SELECT MAX(c2.numguns) FROM classes c2 WHERE A.displacement=c2.displacement)

[Rahul47]

Will you please be so kind to post table strucure and what outcome you are expecting ?

[ruhestorer]

Database structure

Exercise

[SOURABH_2]

Correct Answer-
Select distinct name from ships join Classes C1 on Ships.class = C1.class and numGuns = (select max(numGuns) from Classes C2 where C1.displacement = C2.displacement and C2.class in (select class from ships union select ship from outcomes))
UNION
Select distinct ship from Outcomes join Classes C1 on Outcomes.ship = C1.class and numGuns = (select max(numGuns) from Classes C2 where C1.displacement = C2.displacement)