Problem with DML exercise - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with DML exercise

Author

Topic

dpmaster
Starting Member

5 Posts

Posted - 2011-12-05 : 05:16:20

Hello everybody, actually i'm working in the dml's exercises of sql-ex.ru, and I can't resolve the number 17 that says:
"For each ship with unknown launched year, write in the launched column
within a year an average launched year for the ships of the class of a
given ship. If this average year is unknown, write within a year an average
launched year for the ships of the country of a given ship."
I have 2 tables to work in:
SHIPS (name,class,launched)
CLASSES (class, type, country, numGuns, bore, displacement)
I tried with this query:

update s
set launched=(select avg(launched) from ships where ships.class=s.class)
from ships s where s.launched=''

update s
set launched=(select avg(launched) from ships,classes where classes.country=c.country)
from ships s,classes c where (select avg(launched) from ships s group by class) is null

But it give me that result: "Incorrect.

Your query produced correct result set on main database, but it failed test on second, checking database.
* Data mismatch (11)"

Can you help me? Thank you very much!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 05:27:07

[code]
update s
set launched=coalesce(c.avgyr,cn.avgyr)
from ships s
inner join class cl
on cl.class=s.class
outer apply (select avg(launched) as avgyr from ships where class = s.class )c
outer apply (select avg(launched) as avgyr from ships s1,classes cl1 where cl1.class=s1.class and cl1.country = cl.country)cn
where launched=''
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dpmaster
Starting Member

5 Posts

Posted - 2011-12-05 : 06:01:52

Thank you, but it gives me again the same error

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 06:09:54

what error? please post exact error message

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dpmaster
Starting Member

5 Posts

Posted - 2011-12-05 : 06:11:43

That's the error:

Incorrect.

Your query produced correct result set on main database, but it failed test on second, checking database.
* Data mismatch (11)
This exercise has FAQ

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 06:15:42

this is not a standard sql server error but its specific one raised from the site. As such I dont understand whats the exact problem they're referring to.
ANyways query i've provided works fine on standard sql server editor which is sql management studio

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-05 : 06:25:20

It sounds like your excercise has two checks with different data. It's passed the first but failed the second, so the syntax is ok but the results are incorrect (or the check is wrong).
Are you sure it sholdn't be where s.launched is null?
If it was '' then you should probably doing some casts to numeric values and back again to get the average.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dpmaster
Starting Member

5 Posts

Posted - 2011-12-05 : 07:26:04

quote:
Originally posted by nigelrivett

It sounds like your excercise has two checks with different data. It's passed the first but failed the second, so the syntax is ok but the results are incorrect (or the check is wrong).
Are you sure it sholdn't be where s.launched is null?
If it was '' then you should probably doing some casts to numeric values and back again to get the average.

Yes, it works in that way. First the web does a check, and later a second check in another database.
I change the "last where" to s.launched and appear the same 'mistake'. Syntax is ok, but the fu.... databases found a mistake in others checks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-05 : 07:38:02

Could be a datatype issue.
The launched needs to be rounded to a year - maybe it's giving a decimal.

Are you allowed two statements?
Maybe it doesn't like including nulls in the avg?
There is also an issue with the table structure - only class has a country not ship - is there a constraing to make sure that country is unique for a class?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-05 : 07:49:39

Maybe something like

update s
set launched= case when exists (select * from ships s2 where s2.class=s.class and s2.launched is not null)
then convert(int,(select avg(1.0*launched) from ships s2 where s2.class=s.class and s2.launched is not null))
else convert(int,(select avg(1.0*s2.launched) from ships s2 join classes c2 on c2.class = s2.class and c2.country = c.country and s2.launched is not null))
end
from ships s
join classes c
on s.class = c.class
where s.launched is null

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dpmaster
Starting Member

5 Posts

Posted - 2011-12-07 : 07:11:56

quote:
Originally posted by nigelrivett

Maybe something like

update s
set launched= case when exists (select * from ships s2 where s2.class=s.class and s2.launched is not null)
then convert(int,(select avg(1.0*launched) from ships s2 where s2.class=s.class and s2.launched is not null))
else convert(int,(select avg(1.0*s2.launched) from ships s2 join classes c2 on c2.class = s2.class and c2.country = c.country and s2.launched is not null))
end
from ships s
join classes c
on s.class = c.class
where s.launched is null

Buff, I have already try it, and its give again the same "error". That's crazy!!! Thank you very much for all help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources