January 26, 2005 at 9:32 am
I found the following SQL problem on a practice site. I have been stumbling over this query and could really use a suggestion or two.
The question:
"For each country, find the year, in which the maximal number of ships had been launched.
In the case of more than one year in question, take a minimal year. Result set: country, number of ships, year"
The Tables:
CREATE TABLE Classes(class VARCHAR(20), type VARCHAR(20), country VARCHAR(20), numGuns TINYINT, bore REAL, displacement INT)
GO
CREATE TABLE Ships(name VARCHAR(20), class VARCHAR(20), launched SMALLINT)
GO
INSERT INTO Classes values('Bismarck' ,'bb' ,'Germany' ,8 ,15.0 ,42000)
INSERT INTO Classes values('Iowa' ,'bb' ,'USA' ,9 ,16.0 ,46000)
INSERT INTO Classes values('Kongo' ,'bc' ,'Japan' ,8 ,14.0 ,32000)
INSERT INTO Classes values('North Carolina' ,'bb' ,'USA' ,12 ,16.0 ,37000)
INSERT INTO Classes values('Renown' ,'bc' ,'Gt.Britain' ,6 ,15.0 ,32000)
INSERT INTO Classes values('Revenge' ,'bb' ,'Gt.Britain' ,8 ,15.0 ,29000)
INSERT INTO Classes values('Tennessee' ,'bb' ,'USA' ,12 ,14.0 ,32000)
INSERT INTO Classes values('Yamato' ,'bb' ,'Japan' ,9 ,18.0 ,65000)
GO
INSERT INTO Ships values('California' ,'Tennessee' ,1921)
INSERT INTO Ships values('Haruna' ,'Kongo' ,1916)
INSERT INTO Ships values('Hiei' ,'Kongo' ,1914)
INSERT INTO Ships values('Iowa' ,'Iowa' ,1943)
INSERT INTO Ships values('Kirishima' ,'Kongo' ,1915)
INSERT INTO Ships values('Kongo' ,'Kongo' ,1913)
INSERT INTO Ships values('Missouri' ,'Iowa' ,1944)
INSERT INTO Ships values('Musashi' ,'Yamato' ,1942)
INSERT INTO Ships values('New Jersey' ,'Iowa' ,1943)
INSERT INTO Ships values('North Carolina' ,'North Carolina' ,1941)
INSERT INTO Ships values('Ramillies' ,'Revenge' ,1917)
INSERT INTO Ships values('Renown' ,'Renown' ,1916)
INSERT INTO Ships values('Repulse' ,'Renown' ,1916)
INSERT INTO Ships values('Resolution' ,'Renown' ,1916)
INSERT INTO Ships values('Revenge' ,'Revenge' ,1916)
INSERT INTO Ships values('Royal Oak' ,'Revenge' ,1916)
INSERT INTO Ships values('Royal Sovereign' ,'Revenge' ,1916)
INSERT INTO Ships values('South Dakota' ,'North Carolina' ,1941)
INSERT INTO Ships values('Tennessee' ,'Tennessee' ,1920)
INSERT INTO Ships values('Washington' ,'North Carolina' ,1941)
INSERT INTO Ships values('Wisconsin' ,'Iowa' ,1944)
INSERT INTO Ships values('Yamato' ,'Yamato' ,1941)
It's no problem to come up with to see the answer to the problem:
select c.country, s.launched, count(*) as LCount from ships s
inner join classes c on s.class=c.class
group by c.country, s.launched
order by LCount desc
However, I can't seem to see the maximum sum or minimal sum condition. Any ideas?
TIA
Ryan
January 26, 2005 at 9:40 am
For the curious, this is the practice site mentioned in the post: http://www.sql-ex.ru/?Lang=1
Regarding the problem mentioned, consider selecting the max from a derived table. Details are left to the original poster, as a certification is one of the outcomes and depends on the correct solution of the problems.
Wayne
January 26, 2005 at 10:58 am
Wayne, thanks for the response. I am not interested in the site's certification. I am only interested in improving my SQL skills and it is the only place I found practice problems.
Moving on...
I have solved the problem using derived tables. It is a rather involved SQL statement in which I had to hard code certain parameters into my where clause and use a construct of 5 in-line views. There has to be a much better way - which is what I am trying to learn.
If I send you the query, would you be interested in providing re-tuning suggestions?
Ryan
January 26, 2005 at 11:00 am
More than happy to help with tuning. Please post it here as the collective braintrust has a lot of fun with these things.
Wayne
January 26, 2005 at 11:15 am
Being that the website is based out of Russia, I had not given the certfication much thought. However, your comments have reminded me that there may be members of this community that are working on that cert or have already recieved that certification. Therefore, posting solutions may devalue peoples' hard work.
In retrospect I probably shouldn't have started the thread. I sent you my solution via a private message.
Thanks
Ryan
January 27, 2005 at 2:58 am
Hi Ryan,
It was an interesting query and I have the answer, please let me know if you cant follow:
Select country,min(launched),cnt
From
(
Select c.country,min(s.launched) as launched,count(s.name) as cnt
from classes c inner join ships s on c.class = s.class
group by c.country,s.launched
Having count(s.name) >= ALL
( Select count(s1.name) as cnt
from classes c1 inner join ships s1 on c1.class = s1.class
Where c1.country = c.country
group by c1.country,s1.launched)
) as x
Group by country,cnt
Order by country
The above query will give you the desired result
January 27, 2005 at 2:58 am
Hi Ryan,
It was an interesting query and I have the answer, please let me know if you cant follow:
Select country,min(launched),cnt
From
(
Select c.country,min(s.launched) as launched,count(s.name) as cnt
from classes c inner join ships s on c.class = s.class
group by c.country,s.launched
Having count(s.name) >= ALL
( Select count(s1.name) as cnt
from classes c1 inner join ships s1 on c1.class = s1.class
Where c1.country = c.country
group by c1.country,s1.launched)
) as x
Group by country,cnt
Order by country
The above query will give you the desired result
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply