sql dml exercise 19

Question

I have been working on this query for a couple of time I managed to find a solution but on the exercise solution itself it says I got the correct number of results but mismatch.

This is the exercise they propose:

Assuming that no any two battles were on the same day, wreck in next battle the ships, which in the first their battle were damaged and nowhere else took part. If the following battle is absent from the database with respect to this ship, do not insert the ship into the Outcomes table.

Your query returned the correct dataset on the first (available) database, but it returned incorrect dataset on the second checking database. * Data mismatch (162)

This is the query I wrote:

insert into outcomes
select 
  ship, b.name, 'sunk' from 
  (select ship, name, date from outcomes o
   inner join battles bv
   on o.battle = bv.name
   where result = 'damaged'
   and date = (select max(date) 
               from outcomes os 
               inner join battles ba
               on os.battle = ba.name
               where os.ship = o.ship)      
    ) aq cross join battles b
    where b.date = ( select min(date) from battles where date > aq.date)

This is the database description:

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:

Outcomes relation may include the ships not included in the Ships relation.
Sunk ship can not participate in battles after that.

And here is the tables relations http://sql-ex.ru/help/select13.php#db_3

Among other things, you should change the name of the question so that is more clear to other what you are asking. — tumultous_rooster, Jun 03 '15 at 18:31
I don't know if it's just me, but I can't understand the important bolded part. Any chance you can fix up the language a bit? — sstan, Jun 03 '15 at 18:32
- there are no two battles are in the same day -insert ship into outcomes table with result 'sunk' in the next battle if ship is damaged and is not in any later battles. -do not insert if the next battle is not in the database — Ken Wu, Jun 04 '15 at 20:19

score 1 · Answer 1 · answered Jul 14 '15 at 17:22

1

nowhere else took part

That ship took part in only one battle, the one where it was damaged.

answered Jul 14 '15 at 17:22

Marius Bobin

11
2

score 0 · Answer 2 · answered Aug 17 '16 at 19:23

Such a confusing question. Hope this helps:

INSERT INTO Outcomes
SELECT ship, next, 'sunk'
FROM Outcomes
LEFT JOIN
(SELECT a.first, b.next FROM
(SELECT ROW_NUMBER() OVER(ORDER BY date ASC)+1 AS nxt, name AS first, date
FROM Battles) a
LEFT JOIN
(SELECT ROW_NUMBER() OVER(ORDER BY date ASC) AS rnk, name AS next, date
FROM Battles) b
ON a.nxt = b.rnk) sub
ON Outcomes.battle = sub.first
WHERE Outcomes.result = 'damaged'
AND Outcomes.ship NOT IN
(SELECT Outcomes.ship
FROM Outcomes 
WHERE Outcomes.result = 'sunk')
AND Outcomes.ship NOT IN
(SELECT Outcomes.ship
FROM Outcomes
GROUP BY Outcomes.ship
HAVING COUNT(Outcomes.ship)>1)

sql dml exercise 19

2 Answers2