SQL-EX.ru Exercise #36

I am trying to solve exercise #36 which is List the names of head ships in the database (taking into account Outcomes table).

Head ships are the names of ships that were first in line for that class (i.e. 2 ships but 1 build before another so the first ship is the "head ship"). My result says I did says the following:

Your query returned the correct dataset on the first (available) database, but it returned incorrect dataset on the second checking database.
* Wrong number of records (less by 1)

My query is as follows:

SELECT A.name FROM Ships A
WHERE A.name = A.class
AND A.launched = (SELECT MIN(B.launched) FROM Ships B
WHERE A.name = B.name
AND A.class = B.class)
UNION ALL
SELECT A.ship
FROM Outcomes A
WHERE A.ship IN (SELECT C.ship FROM Outcomes C, Classes D
WHERE C.ship IN (SELECT ship FROM Outcomes)
AND C.ship NOT IN (SELECT name FROM Ships)
AND C.ship = D.class
)


Database is setup as follows:

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:

Now my query returns the correct answer but on the union it is not returning the correct value? My understanding is that one of the ship names does not exist in the Ships table so I picked it up from Outcomes to resolve the issue. But even that is not correct. Is there anything I am missing?

3 Answers
3

Head ships are the ships that have the same name as the class they belong too, I worked wit that and it worked:

WITH union1 (name) AS
(SELECT name FROM ships WHERE name = class
UNION SELECT ship AS name FROM outcomes
INNER JOIN classes ON classes.class = outcomes.ship)
SELECT DISTINCT name FROM union1


A simpler solution:

select name from ships where name IN (select class from classes)
UNION
select ship from outcomes where ship IN (select class from classes)


Select name from ships where name = class
union
select ship from outcomes join classes on outcomes.ship = classes.class


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.