-2

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 somethings missing.

This is the exercise they propose.

You should add from the Outcomes table to the Ships table all the head ships (first ship in a class), which are absent in the Ships table.
Launching year is the average index with accuracy to within 1 year for country ships of a given ship.
If this average year is unknown, the record must not be inserted into the table.

"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 12)" and this is the hint they gave me. "Check up whether you add the same ship more than once if she took part in several battles."

This is the query I wrote:

insert into ships
select 
  ship, classes.class, 
  (select round(avg(1.0 * launched), 0, 0)  
   from ships 
join classes on ships.name = classes.class
where 
  launched is not null
group by country) as l_avgo  
from 
  outcomes 
join 
  classes on outcomes.ship = classes.class 
where 
  not exists (select name from ships )
group by 
  outcomes.ship, classes.class, classes.country
having 
  count(ship) = 1
union
select 
  name, ships.class, round(avg(1.0 * launched), 0, 0) as l_avgs 
from 
  ships
join 
  outcomes on ships.name = outcomes.ship 
where 
  launched is not null
group by 
  ships.name, ships.class

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: 1) Outcomes relation may include the ships not included in the Ships relation. 2) Sunk ship can not participate in battles after that.

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

DeadZone
  • 1,633
  • 1
  • 17
  • 32
Miguel Santos
  • 3
  • 3
  • Without knowing more about the tables, it's difficult to tell you what you're doing wrong. If you could post the table structure and maybe some sample data, you will be more likely to get some assistance. With that said, one thing that looks odd to me is joining `ships.name` and `classes.class`. I would expect `ships.name` to hold the name of the ship and `classes.class` to hold the name of the ship class ('Destroyer', 'cruiser', etc.) Also, why the `union all`? And why aren't you filtering the Union All with a `WHERE` clause? – DeadZone May 14 '15 at 15:19
  • Ok so I updated the information and the code because i made a new query but still with the same problem. So the join the ships.name = classes.class is the name of the head ship as you can see in the description so those are the ones they want. About the union, yes no need to do the union all but a simple union but why a WHERE clause i dont see the need for it what validation or filtering should i do? – Miguel Santos May 15 '15 at 09:05
  • Okay, I think I see 3 requirements. 1) The ship must be in the Outcomes table, but not in the Ships table. 2) The ship must be the Head Ship. 3) You have to be able to calculate the "average year". I'm a bit confused by this 3rd requirement. I think you're supposed to calculate the average year from `Ships.launched` for every ship in the class' Country. Is that correct? (Seems really strange if it is.) – DeadZone May 15 '15 at 14:54
  • Yes it something like that, the average year of launched is exactly that. Except if its empty they are not added to the ships table. – Miguel Santos May 21 '15 at 09:27

1 Answers1

0
insert into ships 
select ship name,class,launched from
(select distinct ship,c.class,country 
from outcomes o join classes c on c.class=o.ship 
where ship not in(select distinct name from ships)) f join 
(select country,round(avg(launched*1.0),0) launched 
from ships s join classes c on c.class=s.class 
group by country) n on f.country = n.country
where n.launched is not null
Ken Wu
  • 18
  • 1