IBM XML Challenge

I found this great website, XML Challenge. Basically it allows peopleto use their database scouring skills using XQUERY statements. While I have basically no knowledge of using XQuery statements with DB2 Express-C. When you get Express-C downloaded, I admit there is a bit of set-up difficulty. But, after those troubles pass you're ready to go.

There are some basic similarities between Oracle and DB2....but unfortunately, the XML challenge is demonstrating how differently these programs work. So, after you say "start the challenge" you are given 24 hours to answer 5 queries. And...the queries have to be 100% perfect. It is interesting how the logic works and how there is no additional feedback on the queries. Needless to say, I guess I had an error.

The following are my questions and my answers:

Question 1:Question #1: List the five countries with the smallest population in ascending order.

Answer 1:select name, p.population
from countries c, c_info ci,
xmltable('db2-fn:xmlcolumn("C_INFO.INFO")/country' COLUMNS "POPULATION" INTEGER PATH 'population',
"CTRY_ID" INTEGER PATH '@cid') as p
where c.id=P.ctry_id
and c.id=ci.cid
ORDER BY p.population;

Question 2:List the five countries with the largest total area in descending order (largest to smallest).

Answer 2:select name, a.total_area
from countries c, c_info ci,
xmltable('db2-fn:xmlcolumn("C_INFO.INFO")/country' COLUMNS "TOTAL_AREA" DOUBLE PATH 'area/total',
"CTRY_ID" INTEGER PATH '@cid') as a
where c.id = a.ctry_id
and c.id = ci.cid
ORDER BY a.total_area desc;

Question 3:Which countries have more water area than half its land area? As your answer, enter the name, population, land area, water area and coastline of the first country in the list.

Answer 3: select name, a.population, a.land_area, a.water_area, a.coastline
from countries c, c_info ci,
xmltable('db2-fn:xmlcolumn("C_INFO.INFO")/country' COLUMNS "CTRY_ID" INTEGER PATH '@cid',
"POPULATION" INTEGER PATH 'population',
"LAND_AREA" DOUBLE PATH 'area/land',
"WATER_AREA" DOUBLE PATH 'area/water',
"COASTLINE" DOUBLE PATH 'coastline') AS a
where c.id = a.ctry_id
and c.id = ci.cid
and a.water_area > (a.LAND_AREA/2);

Question 4:Which continents have the shortest coastline? For the answer to this question, enter the names of the continents in ascending order of coastline (least to most).

Answer 4:select con.continent, sum(a.coastline) "total_coastline"
from countries ctry, c_info ci, continents con,
xmltable('db2-fn:xmlcolumn("C_INFO.INFO")/country' COLUMNS "CTRY_ID" INTEGER PATH '@cid',
"COASTLINE" DOUBLE PATH 'coastline') AS a
where ctry.id = a.ctry_id
and ctry.id = ci.cid
and ctry.continent = con.cid
GROUP BY con.continent
ORDER BY sum(a.coastline);

Question 5:Which countries have the shortest coastline per square kilometer of (total) area? Exclude all countries that do not have a coastline. For the result set, enter a table of those countries with the name, the coastline per square kilometer as "COASTLINERATIO", the coastline, and the area. As the answer to this question, enter the first five countries in ascending order of coastline ratio.

Answer 5:select name, DECIMAL(a.coastline/a.total_area,6,5) "COASTLINERATIO", a.coastline, a.total_area
from countries ctry, c_info ci,
xmltable('db2-fn:xmlcolumn("C_INFO.INFO")/country' COLUMNS "CTRY_ID" INTEGER PATH '@cid',
"COASTLINE" DOUBLE PATH 'coastline',
"TOTAL_AREA" DOUBLE PATH 'area/total') AS a
where ctry.id = a.ctry_id
and ctry.id = ci.cid
and a.coastline > 0
order by (a.coastline/a.total_area);


The only real way that you'll understand what's going on is by actually going to the XML Challenge website and at least poking around a little. It does seem like a lot of fun...and if you get the answers correct you win stuff. So, you actually get some value back out of your 2-4 hours of thinking.