SQL query query set -EX.ru # 32

Question

I am trying to write an SQL query to resolve the issue on www.sql-ex.ru (Q. 32), I got the correct result, but the website checks my query as an undesirable solution. (this may be for various reasons: a mistake in logic or inefficiency, etc.)

Scheme

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

SQL query request:

One of the characteristics of the ship is half a cube of calibers of its main guns (mw). Within two decimal places, determine the average mw value for ships of each country that have ships in the database.

Website note by data

A database of naval vessels participating in World War II is currently being reviewed. The database has the following relationships: Classes (class, type, country, numGuns, hole, move) Ships (name, class, launch) Battles (name, date) Results (ship, battle, result) Ships in classes are organized in one project. Typically, the class is given the name of the first ship in the class in question (lead ship); otherwise, the class name does not match any vessel name in the database. The class ratio includes the class name, type (bb for the warship or bc for the battle cruiser), the country in which the ship was built, the number of main guns, the caliber of the gun (gun barrel diameter, inches) and displacement (mass in tons). The ship ratio includes the name of the ship, its class name and year of launch.The battle ratio covers the name and date of the battle in which the ships participated; while the result of their participation in the battle (sunken, damaged or unharmed - OK) is in relation to the results. Notes: 1) The ratio of results may include vessels not included in the relation of vessels. 2) After this, the ship cannot participate in battles.

            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

1687.50

Gt.Britain 1687.50

1897,78

: , ? .

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

().

, , , , , .

:

• (Bismark), , . , , . , , , ?

, 1- , . ? - :

2. , , Outcomes, .names = classes.class. ? . - "" , "" . ? . ? ... . .

, (IMO), 1- , - .

, , 1- db, 2-:

--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

:

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

Answer 3

SELECT COUNTRY, CONVERT(NUMERIC(10,2),AVG(BORE*BORE*BORE/2)) WEIGHT 
FROM (SELECT COUNTRY,BORE,NAME
    FROM CLASSES 
    RIGHT JOIN SHIPS ON SHIPS.CLASS = CLASSES.CLASS
    UNION
    SELECT DISTINCT COUNTRY, BORE, SHIP
    FROM CLASSES C 
    LEFT JOIN OUTCOMES O ON O.SHIP = C.CLASS 
    WHERE  NOT EXISTS(SELECT NAME 
    FROM SHIPS 
    WHERE NAME = O.SHIP) AND 
    NOT (SHIP IS NULL)
 )T
GROUP BY COUNTRY

Answer 4

, , , .

select c.country, convert(numeric(10, 2), avg(power(bore, 3)/2)) as weight from classes as c left join ships as s on (s.class = c.class) group by c.country

, , ( ), - ( , , for), . , bismarck, , ..

select country, convert(numeric(10, 2), avg(power(bore, 3)/2)) as weight from classes inner join (SELECT Ships.name, Ships.class FROM Ships inner join Classes ON Ships.class = Classes.class union SELECT Outcomes.ship, Classes.class FROM Outcomes inner join Classes ON Outcomes.ship = Classes.class ) as ships on (ships.class = classes.class) group by country

, , , , , . .