说明：
1、复赛全部为单项选择题共有两次答题机会，取最高成绩。
2、由于试题采用word编辑，请选手注意标点符号的问题。
基本题(第1题4分 第2、3题5分 共14分)
1.为每个国家创建一个包含以下结构和信息的新XML文件。以表格形式显示查询结果，包含国家名称、所在洲名称和XML文件。
<c cid=”the ID”>
<localLong>the local long name of the countries table</localLong>
<population>the population of the c_info table</population>
<area>the area of the c_info table including the subtree</area>
</c>
A.
select co.continent，c.name，xmlquery(‘let $i:=$INFO/country
return <c cid=”{$i/@cid}”>
<localLong>
</localLong>
<population></population>
{$i/area}
</c>’)
from continents co，countries c， c_info ci
where ci.cid=c.id
B.
select co.continent，c.name，xmlquery(‘let $i:=$INFO/country
return <c cid=”{$i/cid}”>
<localLong>{$EXTNAME//local_long}
</localLong>
<population></population>
{$i/area}
</c>’)
from continents co，countries c，c_info ci
where co.cid=c.continent and ci.cid=c.id
C.
select co.continent，c.name，xmlquery(‘let $i:=$INFO/country
return <c cid=”{$i/@cid}”>
<localLong>
</localLong>
<population></population>
{$i/area}
</c>’)
from continents co，countries c，c_info ci
where co.cid=c.continent and ci.cid=c.id
D.
select co.continent，c.name，xmlquery(‘let $i:=$INFO/country
return <c cid=”{$i/@cid}”>
<localLong>
</localLong>
<population></population>
{$i/area}
</c>’)
from continents co，countries c， c_info ci
where ci.cid=c.id and co.cid=ci.cid
2.查询所有以C开头的亚洲国家名称。以表格形式显示查询结果，包含国家名称、人口数量和本地全称
A.
select c.name，xmlcast(xmlquery(‘$INFO/country/population’) as varchar(30)) as population，
xmlcast(xmlquery(‘$EXTNAME/country/country_name/local_long’) as varchar(160)) as longname
from continents co，countries c，c_info ci
where co.cid=c.continent and ci.cid=co.cid
and co.continent=’Asia’ and c.name like ‘C%’
B.
select c.name，xmlcast(xmlquery(‘$INFO/country/population’) as varchar(30)) as population，
xmlcast(xmlquery(‘$EXTNAME/country/country_name/local_long’) as varchar(160)) as longname
from continents co，countries c，c_info ci
where co.continent=’Asia’ and c.name like ‘C%’
C.
select c.name，xmlcast(xmlquery(‘$INFO/country/population’) as varchar(30)) as population，
xmlcast(xmlquery(‘$EXTNAME/country/country_name/local_long’) as varchar(160)) as longname
from continents co，countries c，c_info ci
where co.cid=c.continent and ci.cid=c.id
and co.continent=’Asia’ and c.name like ‘C%’
D.
select c.name，xmlcast(xmlquery(‘$INFO/country/population’) as varchar(30)) as population，
xmlcast(xmlquery(‘$EXTNAME/country/country_name/local_long’) as varchar(160)) as longname
from continents co，countries c，c_info ci
where co.cid=c.continent and ci.cid=c.id and c.name like ‘C%’
3.查询结果作为一个单独的XML文档返回。包括所有的国家的信息，以”countries”作为根元素。对每个国家在元素country下有英文短名称(“name”)和首都名称(“capital”)作为下级的元素。
A.
select xmlquery(‘<countries>{for $i in $doc/country
return <country><name>{$i/country_name/short}</name>{$i/capital}
</country>}
</countries>’ passing extname as “doc”)
from COUNTRIES
B.
xquery <countries>{for $i in db2-fn:xmlcolumn(“COUNTRIES.EXTNAME”)/country
return <country><name>{$i/country_name/short}</name>{$i/capital}
</country>}
</countries>
C.
xquery <countries>{for $i in db2-fn:xmlcolumn(“COUNTRIES.EXTNAME”)/country
return
<country>
<name></name>
{$i/capital}
</country>}</countries>
D.
xquery <countries>{for $i in db2-fn:xmlcolumn(“COUNTRIES.EXTNAME”)/country
return
<country>
<name>{$i/capital}</name>
</country>}</countries>
中级题(每题8分 共56分)
4.以表格形式显示查询结果，只包含所在洲名称、国家名称和海拔。显示全部国家名称并按照国家名称排序。提示: 用字符串处理功能把海拔抽取为字符串或者数字
A.
select co.continent，c.name，xmlcast(xmlquery(
‘let $i:=tokenize(substring-before($INFO/country/elevation_extremes/highest_point，” “)，” “)
return replace($i[count($i)]，”，”，””)’) as varchar(20))
from continents co，countries c， c_info ci
where co.cid=c.continent and c.id=ci.cid
order by c.name
B.
select co.continent，c.name，xmlcast(xmlquery(
‘let $i:=tokenize(substring-before($INFO/country/elevation_extremes/highest_point，” m”)，” “)
return replace($i[count($i)]，”，”，””)’) as varchar(20))
from continents co，countries c， c_info ci
where co.cid=c.continent and c.id=ci.cid
C.
select co.continent，c.name，xmlcast(xmlquery(
‘let $i:=tokenize(substring-before($INFO/country/elevation_extremes/highest_point，” m”)，” “)
return replace($i[count($i)]，”，”，””)’) as varchar(20))
from continents co，countries c， c_info ci
where co.cid=c.continent and co.cid=ci.cid
order by c.name
D.
select co.continent，c.name，xmlcast(xmlquery(
‘let $i:=tokenize(substring-before($INFO//elevation_extremes/highest_point，” m”)，” “)
return replace($i[count($i)]，”，”，””)’) as varchar(20))
from continents co，countries c， c_info ci
where co.cid=c.continent and c.id=ci.cid
order by c.name
5.查询有最短的本地短名称的国家列表。返回国家包括所在洲，国家名称，本地短名称，和其长度。除去没有本地短名称的国家(长度为0或者没有值的).
A.
select continent，name，localshort，locallength
from (select co.continent，c.name，xmlcast(xmlquery(
‘$EXTNAME/country/country_name/short’) as varchar(80)) as localshort，
xmlcast(xmlquery(
‘fn:string-length($EXTNAME/country/country_name/short)’) as double) as locallength
from continents co，countries c
where co.cid=c.continent)
where locallength>0 and localshort!=’none’
order by locallength
B.
select continent，name，localshort，locallength
from (select co.continent，c.name，xmlcast(xmlquery(
‘$EXTNAME/country/country_name/local_short’) as varchar(80)) as localshort，
xmlcast(xmlquery(
‘fn:string-length($EXTNAME/country/country_name/local_short)’) as double) as locallength
from continents co，countries c
where co.cid=c.continent)
where locallength>0 and localshort!=’none’
order by locallength
C.
select continent，name，localshort，locallength
from (select co.continent，c.name，xmlcast(xmlquery(
‘$EXTNAME/country/country_name/local_short’) as varchar(80)) as localshort，
xmlcast(xmlquery(
‘fn:string-length($EXTNAME/country/country_name/local_short)’) as double) as locallength
from continents co，countries c
where co.cid=c.continent)
where localshort !=’none’
order by locallength
D.
select continent，name，localshort，locallength
from (select co.continent，c.name，xmlcast(xmlquery(
‘$EXTNAME/country/country_name/local_short’) as varchar(80)) as localshort，
xmlcast(xmlquery(
‘fn:string-length($EXTNAME/country/country_name/local_short)’) as double) as locallength
from continents co，countries c
where co.cid=c.continent)
where localshort !=” and locallength >0
order by locallength
6.哪些国家的水域面积大于30000陆地面积大于800000？返回它们的名字，水域面积，陆地面积，都是以Varchar(30)的数据类型。
A.
select cast(c.name as varchar(30))，xmlcast(xmlquery(‘$info//water’ passing ci.info as “info”) as varchar(30)) as water_area，
xmlcast(xmlquery(‘$info//land’ passing ci.info as “info”) as varchar(30)) as land_area
from c_info ci， countries c
where
xmlexists(‘$INFO/country/area[xs:double(land/text())>3000 and xs:double(land/text())>80000]’)
and ci.cid=c.id
B.
select c.name，xmlcast(xmlquery(‘$info//water’ passing ci.info as “info”) as varchar(30))，
xmlcast(xmlquery(‘$info//land’ passing ci.info as “info”) as varchar(30))
from c_info ci， countries c
where
xmlexists(‘$INFO/country/area[xs:double(water/text())>30000 and xs:double(land/text())>800000]’)
and ci.cid=c.id
C.
select cast(c.name as varchar(30))，xmlcast(xmlquery(‘$info//water’ passing ci.info as “info”) as varchar(30))，
xmlcast(xmlquery(‘$info//land’ passing ci.info as “info”) as varchar(30))
from c_info ci， countries c
where
xmlexists(‘$INFO/country/area[xs:double(water/text())>30000 and xs:double(land/text())>800000]’)
and ci.cid=c.id
D.
select cast(c.name as varchar(30))，xmlquery(‘$info//water’ passing ci.info as “info”)，
xmlquery(‘$info//land’ passing ci.info as “info”)
from c_info ci， countries c
where
xmlexists(‘$INFO/country/area[xs:double(water/text())>30000 and xs:double(land/text())>800000]’)
and ci.cid=c.id
7.基于数据库中所包含的国家，哪些洲的海岸线最长/最短？
A.
select continent，sum(coastline) from
(select co.continent， c.name， xmlcast( xmlquery(‘$INFO/country/coastline’) as double) as coastline
from continents co，countries c，c_info ci
where co.cid=c.continent and ci.cid=c.id)
group by continent
order by 2 desc
B.
select continent，sum(integer(coastline)) as coastline from
(select co.continent， c.name， xmlcast( xmlquery(‘$INFO/country/coastline’) as double) as coastline
from continents co，countries c，c_info ci
where co.cid=c.continent and ci.cid=c.id)
group by continent
order by 2 desc
C.
select continent，sum(coastline) from
(select co.continent， c.name， xmlcast( xmlquery(‘$INFO/country/coastline’) as integer) as coastline
from continents co，countries c，c_info ci
where co.cid=c.continent and ci.cid=c.id)
group by continent
order by 2 desc
D.
select continent，sum(integer(coastline)) as coastline from
(select co.continent， c.name， xmlcast( xmlquery(‘$INFO/country/coastline’) as double) as coastline
from continents co，countries c，c_info ci
where co.cid=c.continent and ci.cid=c.id)
group by continent
order by 2
8.根据各国英语全称，有多少国家认为自己是“共和制”？请列出这些国家的数量，并按“所在洲”分组，共和制最多的洲最先列出。
A.
select count(name) as count，continent from
(select c.name，ct.continent
from countries c， continents ct
where xmlexists(‘$EXTNAME/country/country_name[contains(long，”Republic”)]’) )
group by continent
order by count desc
B.
select count(name)，continent from
(select c.name，ct.continent
from countries c， continents ct
where ct.cid=c.continent and xmlexists(‘$EXTNAME/country/country_name[contains(long，”Republic”)]’) )
group by continent
order by 1 desc
C.
select count(name)，continent from
(select c.name，ct.continent
from countries c， continents ct
where ct.cid=c.continent and c.name like ‘%Republic%’ )
group by continent
order by 1
D.
select name，continent from
(select c.name，ct.continent
from countries c， continents ct
where xmlexists(‘$EXTNAME/country/country_name[contains(long，”Republic”)]’) )
order by name
9.哪个国家与其他洲的国家相邻？以表格形式显示查询结果，包含该国名称和所在洲名称，以及邻国名称和所在洲名称。
A.
select ct.continent，c.name
from c_info ci， countries c， continents ct
where xmlexists(‘$INFO/country[contains(border_countries，$c)]’ passing c.name as “c”)
and ci.cid=c.id and ct.cid=c.continent
B.
select ct.continent，c.name，ct2.continent，c2.name
from c_info ci， countries c， continents ct， continents ct2， countries c2
where xmlexists(‘$INFO/country[contains(border_countries，$c)]’ passing c2.name as “c”)
and ci.cid=c.id and ct.cid=c.continent and ct2.cid=c2.continent
C.
select ct.continent，c.name，ct2.continent，c2.name
from c_info ci， countries c， continents ct， continents ct2， countries c2
where xmlexists(‘$INFO/country[contains(border_countries，$c)]’ passing c2.name as “c”)
and ci.cid=c.id and ct.cid=c.continent and ct2.cid=c2.continent and ct.cid!=ct2.cid
D.
select ct.continent，c.name，ct2.continent，c2.name
from c_info ci， countries c， continents ct， continents ct2， countries c2
where xmlexists(‘$INFO/country[contains(border_countries，$c)]’ passing c2.name as “c”)
and ci.cid=c.id and ct.cid=c.continent and c.continent!=c2.continent
10.打印洲的列表和其包括的国家的平均的total area数值，按照平均面积排序，最大的在最前。
A.
select cont，avg(totalarea) as avgarea from
(select xmlcast(xmlquery(‘$i/country/area/total’ passing ci.info as “i”) as double) as totalarea，ct.continent as cont
from c_info ci，countries c， continents ct
where c.id=ci.cid and ct.cid=c.continent)
group by cont
order by avgarea desc
B.
select cont，totalarea from
(select xmlcast(xmlquery(‘$i/country/area/total’ passing ci.info as “i”) as double) as totalarea，ct.continent as cont
from c_info ci，countries c， continents ct
where c.id=ci.cid and ct.cid=c.continent)
order by avgarea desc
C.
select cont，avg(totalarea) as avgarea from
(select xmlquery(‘$i/country/area/total’ passing ci.info as “i”) as totalarea，ct.continent as cont
from c_info ci，countries c， continents ct
where c.id=ci.cid and ct.cid=c.continent)
group by cont
order by avgarea desc
D.
select cont，avg(totalarea) as avgarea from
(select xmlcast(xmlquery(‘xs:double($i/country/area/land/text())+xs:double($i/country/area/water/text())’ passing ci.info as “i”) as double) as totalarea，ct.continent as cont
from c_info ci，countries c， continents ct
where c.id=ci.cid and ct.cid=c.continent)
group by cont
order by avgarea desc
高级题(每题10分 共30分)
下面的题目较难，可能需要一些准备工作。
11.请列出面积第13大的国家名称，以及与该国人口数量相当（差额不超过10%）的国家名称及其（总）面积。以表格形式显示查询结果，包含国家名称、人口数量和（总）面积。
A.
select c.name， xmlcast(xmlquery(‘$INFO/country/population’) as double) as population，
xmlcast(xmlquery(‘$INFO/country/area/total’) as double) as area
from countries c，c_info ci
where xmlexists(‘
for $k in ( let $co:=(for $i in db2-fn:xmlcolumn(“C_INFO.INFO”)/country
order by $i/area/total descending
return $i[13])，
$cs:=(for $j in db2-fn:xmlcolumn(“C_INFO.INFO”)/country
where ($j/population > $co/population*0.9 and $j/population<$co/population*1.1)and $co/@cid!=$j/@cid
return $j)
return $cs)
where $ID=$k/@cid
return <cid>{$k/@cid}</cid>’)and ci.cid=c.id
B.
select c.name， xmlcast(xmlquery(‘$INFO/country/population’) as double) as population，
xmlcast(xmlquery(‘$INFO/country/area/total’) as double) as area
from countries c，c_info ci
where xmlexists(‘
for $k in ( let $co:=(for $i in db2-fn:xmlcolumn(“C_INFO.INFO”)/country
order by $i/area/total descending
return $i[13])，
$cs:=(for $j in db2-fn:xmlcolumn(“C_INFO.INFO”)/country
where ($j/population > $co/population*0.9 and $j/population<$co/population*1.1)and $co/@cid!=$j/@cid
return $j)
return $cs)
where $ID=$k/@cid
return <cid>{$k/@cid}</cid>’)and ci.cid=c.id
C.
select c.name， xmlcast(xmlquery(‘$INFO/country/population’) as double) as population，
xmlcast(xmlquery(‘$INFO/country/area/total’) as double) as area
from countries c，c_info ci
where xmlexists(‘let $co:=(for $i in db2-fn:xmlcolumn(“C_INFO.INFO”)/country
order by $i/area/total descending
return $i)[13]，
$cs:=(for $j in db2-fn:xmlcolumn(“C_INFO.INFO”)/country
where ($j/population > $co/population*0.9 and $j/population<$co/population*1.1)and $co/@cid!=$j/@cid
return $j)
where $ID=$cs/@cid
return <cid>{$cs/@cid}</cid>’)and ci.cid=c.id
D.
select c.name， xmlcast(xmlquery(‘$INFO/country/population’) as double) as population，
xmlcast(xmlquery(‘$INFO/country/area/total’) as double) as area
from countries c，c_info ci
where xmlexists(‘
for $k in (
let $co:=(for $i in db2-fn:xmlcolumn(“C_INFO.INFO”)/country
order by $i/area/total descending
return $i)[13]，
$cs:=(for $j in db2-fn:xmlcolumn(“C_INFO.INFO”)/country
where ($j/population > $co/population*0.9 and $j/population<$co/population*1.1)
and $co/@cid!=$j/@cid
return $j)
return $cs)
where $ID=$k/@cid
return <cid>{$k/@cid}</cid>’)
and ci.cid=c.id
12.哪些国家每平方千米（总面积）的海岸线最短？剔除所有没有海岸线的国家名称，返回这些国家的列表，包括名称，每平方公里海岸线作为” COASTLINERATIO”， 海岸线，和面积。
A.
select c.name，xmlcast(xmlquery(‘let $base:=$INFO/country
return $base/coastline / $base/area/total’) as double) as coastlineratio，
xmlcast(xmlquery(‘$INFO/country/coastline’) as varchar(20)) as coastline，
xmlcast(xmlquery(‘$INFO/country/area/total’) as varchar(20)) as area
from countries c，c_info ci
where xmlexists(‘$INFO/country/coastline[./text()>0]’) and c.id=ci.cid
order by coastlineratio asc
B.
select c.name，xmlcast(xmlquery(‘let $base:=$INFO/country
return $base/coastline div $base/area/total’) as double) as coastlineratio
from countries c，c_info ci
where xmlexists(‘$INFO/country/coastline[./text()>0]’) and c.id=ci.cid
order by coastlineratio asc
C.
select c.name，xmlcast(xmlquery(‘let $base:=$INFO/country
return $base/coastline div $base/area/total’) as double) as coastlineratio，
xmlcast(xmlquery(‘$INFO/country/coastline’) as varchar(20)) as coastline，
xmlcast(xmlquery(‘$INFO/country/area/total’) as varchar(20)) as area
from countries c，c_info ci
where xmlexists(‘$INFO/country/coastline[./text()>0]’) and c.id=ci.cid
order by coastlineratio asc
D.
select c.name，xmlcast(xmlquery(‘let $base:=$INFO/country
return $base/coastline / $base/area/total’) as double) as coastlineratio，
xmlcast(xmlquery(‘$INFO/country/coastline’) as varchar(20)) as coastline，
xmlcast(xmlquery(‘$INFO/country/area/total’) as varchar(20)) as area
from countries c，c_info ci
where xmlexists(‘$INFO/country[coastline>0]’) and c.id=ci.cid
order by coastlineratio asc
13.列出这些国土面积和另一个国家相差10%以内的，但是人口差异大于10%的国家。返回这些国家的列表，包括国家名，匹配国家名，人口数，和总面积。去掉重复项。
A.
select c1.name，c2.name，xmlcast(xmlquery(‘$i/country/population’ passing ci1.info as “i”) as varchar(20)) as pop1，
xmlcast(xmlquery(‘$i/country/population’ passing ci2.info as “i”) as varchar(20)) as pop2，
xmlcast(xmlquery(‘$i/country/area/total’ passing ci1.info as “i”) as varchar(20)) as area1，
xmlcast(xmlquery(‘$i/country/area/total’ passing ci2.info as “i”) as varchar(20)) as area2
from countries c1，countries c2，c_info ci1， c_info ci2
where c1.id=ci1.cid and c2.id=ci2.cid and c1.id>c2.id
and xmlexists(‘$i/country[area/total > $j/country/area/total*0.9
and area/total<=$j/country/area/total*1.1
and (population < $j/country/population*0.9 or population>$j/country/population*1.1)]’ passing ci1.info as “i”， ci2.info as “j”)
B.
select c1.name，c2.name，xmlcast(xmlquery(‘$i/country/population’ passing ci1.info as “i”) as varchar(20)) as pop1，
xmlcast(xmlquery(‘$i/country/population’ passing ci2.info as “i”) as varchar(20)) as pop2，
xmlcast(xmlquery(‘$i/country/area/total’ passing ci1.info as “i”) as varchar(20)) as area1，
xmlcast(xmlquery(‘$i/country/area/total’ passing ci2.info as “i”) as varchar(20)) as area2
from countries c1，countries c2，c_info ci1， c_info ci2
where c1.id=ci1.cid and c2.id=ci2.cid and c1.id>c2.id
and xmlexists(‘$i/country[area/total > $j/country/area/total*0.9
and area/total<$j/country/area/total*1.1
and ($i/country/population < $j/country/population*0.9 or $i/country/population>$j/country/population*1.1)]’ passing ci1.info as “i”， ci2.info as “j”)
C.
select c1.name，c2.name，xmlcast(xmlquery(‘$i/country/population’ passing ci1.info as “i”) as varchar(20)) as pop1，
xmlcast(xmlquery(‘$i/country/population’ passing ci2.info as “i”) as varchar(20)) as pop2，
xmlcast(xmlquery(‘$i/country/area/total’ passing ci1.info as “i”) as varchar(20)) as area1，
xmlcast(xmlquery(‘$i/country/area/total’ passing ci2.info as “i”) as varchar(20)) as area2
from countries c1，countries c2，c_info ci1， c_info ci2
where c1.id=ci1.cid and c2.id=ci2.cid and c1.id>c2.id
and xmlexists(‘$i/country[area/total > $j/country/area/total*0.9
and area/total<$j/country/area/total*1.1
and (populat < $j/country/population*0.9 or population>$j/country/population*1.1)]’ passing ci1.info as “i”， ci2.info as “j”)
D.
select c1.name，c2.name，xmlcast(xmlquery(‘$i/country/population’ passing ci1.info as “i”) as varchar(20)) as pop1，
xmlcast(xmlquery(‘$i/country/population’ passing ci2.info as “i”) as varchar(20)) as pop2，
xmlcast(xmlquery(‘$i/country/area/total’ passing ci1.info as “i”) as varchar(20)) as area1，
xmlcast(xmlquery(‘$i/country/area/total’ passing ci2.info as “i”) as varchar(20)) as area2
from countries c1，countries c2，c_info ci1， c_info ci2
where c1.id=ci1.cid and c2.id=ci2.cid
and xmlexists(‘$i/country[area/total > $j/country/area/total*0.9
and area/total<$j/country/area/total*1.1
and (population < $j/country/population*0.9 or population>$j/country/population*1.1)]’ passing ci1.info as “i”， ci2.info as “j”)
我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。
我原创，你原创，我们的内容世界才会更加精彩！
【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】
微信公众号
TechTarget
官方微博
TechTarget中国
相关推荐
-
IBM加入Spark社区 计划培养百万数据科学家
IBM近日宣布，将大力推进Apache Spark项目，并计划培养超过100万名Spark数据科学家和数据工程师。
-
IBM成立物联网部门旨在整合未用数据
IBM准备在未来四年投资30亿美元成立一个专门的物联网（IoT）部门，并由此建立一个基于云的开放平台来帮助客户进行更好的数据整合。
-
ODP项目能否成为Hadoop助推器？
开放数据平台联盟的成立旨在为了推动Hadoop的标准化，但项目能否最终成功，或能否项向着承诺的方向发展，还有很多不确定因素。
-
“开放数据平台”联盟成立 将推动Hadoop技术发展
近日，包括IBM、Pivotal以及Hortonworks在内的一系列IT厂商成立了“开放数据平台”协会，旨在推动大数据技术发展。