SQL-EX.ru query problemset #32

Question

I'm trying to write an SQL query to solve a question at www.sql-ex.ru (Q. 32), I got the correct result, but the website validates my query as an undesirable solution. (it could be for all sorts of reasons: error in logic or ineffiency etc...)

The schema

http://www.sql-ex.ru/images/ships.gif

SQL Query Question:

One of characteristics of a ship is one-half cube of calibre of its main guns (mw). Within 2 decimal places, define the average value of mw for the ships of each country which has ships in database.

Website note on data

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.

My query

            SELECT c.country, CAST( AVG(0.5*POWER(c.bore,3.0) ) AS DECIMAL(10,2) )
            AS  weight FROM
    (
    SELECT ship FROM Outcomes
    INTERSECT
    SELECT class FROM Classes
    EXCEPT
    SELECT class FROM Ships
    UNION ALL
    SELECT class FROM Ships

    ) AS cte1

    LEFT OUTER JOIN Classes AS c
    ON cte1.ship=c.class
    GROUP BY country

Correct Result

country weight

Germany 1687.50

Gt.Britain 1687.50

USA 1897.78

My question: How is my query incorrect given it yields the correct result? Many thanks.

Answer 1

select country, convert(numeric(10, 2), avg(power(bore, 3)/2)) weight 
from
    (select country, bore, name from classes c, ships s
     where s.class=c.class
     union
     select country, bore, ship from classes c, outcomes o
     where o.ship=c.class 
     and o.ship not in(select distinct name from ships))x
group by country

Answer 2

The exercise itself is faulty (IMO).

What you suppose to do is JOIN the two tables, Classes and Ships, based on their Class, and calculate the average weight based on the actual amount of ships there are.

Yet there's a few problems:

1. There is a ship class (Bismark) that appears in the classes table but not in the ships table. Apparently you have to take this also into account even though it doesn't exist. One might argue how can you include an average of ships actually existing together with an hypothetical average of ships that don't actually exist?

If you do take this into account, you will pass the 1st DB but not the 2nd. Why? Because of the 2nd problem:

2. In order to pass completely, you also have to take into account the ships names that appear in the Outcomes table, and join them on the classes with outcomes.names = classes.class. Why? No idea. Bismark is the only "name" of a ship in outcomes that is also a "name" of a class in classes. Why should they be joined? Who knows. How are you supposed to guess that? Well... I have no clue. It makes NO SENSE.

So, the 2 problems are quite illogical (IMO), and while the 1st one is somewhat ok, the 2nd one is completely absurd.

This, for example, solved the 1st db but failed the 2nd:

--t1, t1_2, t1_3 are creating a class table with count of no. of ships. Any ship appearing only in the classes table would be assigned a count of 1.
with t1 as 
(Select class, count(name) 'c1' from ships group by class),
t1_2 as
(select ship 'class', 1 'c1' from outcomes where ship not in (select class from t1)),
t1_3 as
(select * from t1 union select * from t1_2),

t2 as
(select distinct classes.class, country, 0.5*bore*bore*bore 'mw', c1 from classes left join t1_3 on t1_3.class = classes.class)
select country, cast(sum(mw*c1)/sum(c1) as decimal(10,2)) from t2 group by country

And this solves it completely:

with t1 as
(select country, bore, name from classes, ships
where classes.class=ships.class
union
select country, bore, ship 'name' from classes, outcomes
where classes.class = outcomes.ship)
select country, cast(avg(0.5*bore*bore*bore) as decimal(10,2)) 'mw' 
from t1 group by country