answer_3.htm

软件设计师模拟试卷,点击里面的Exe文件可以随机出题

<p class=MsoNormal style='mso-layout-grid-align:none;text-autospace:none'><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>注：（</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>4</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>）、（</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>5</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>）也可为<span
lang=EN-US><o:p></o:p></span></span></p>

<p class=MsoNormal style='mso-layout-grid-align:none;text-autospace:none'><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>（</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>4</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>）</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>ANOIN<o:p></o:p></span></p>

<p class=MsoNormal style='mso-layout-grid-align:none;text-autospace:none'><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>（</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>5</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>）</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>ANO<o:p></o:p></span></p>

<p class=MsoNormal style='mso-layout-grid-align:none;text-autospace:none'><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>【解析】本题是关于系数据库标准语言<span
lang=EN-US>--</span></span><span lang=EN-US style='mso-bidi-font-size:10.5pt'>SQL(Structured
Query Language)</span><span style='mso-bidi-font-size:10.5pt;font-family:宋体;
mso-hansi-font-family:"Times New Roman"'>语言的题目，由题目中给出的</span><span lang=EN-US
style='mso-bidi-font-size:10.5pt'>ER</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>图可知</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>3</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>个表中，</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>ATHLETE</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>和</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>ITEM</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>是基本表，表</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>ATHETE</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>的主键是运动员编号</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>ANO</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>，表</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>ITEM</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>的主键是项目编号</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>INO</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>，表</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>GAMES</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>是一个视图，以</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>ANO</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>、</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>INO</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>为外键。<span
lang=EN-US><o:p></o:p></span></span></p>

<p class=MsoNormal style='mso-layout-grid-align:none;text-autospace:none'><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>程序</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>1</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>统计参加比赛的男运动员人数，也就是表</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>ATHLETE</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>中，</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>AEX=</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>′</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>M</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>′的记录的个数，所以要用到库函数</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>COUNT(*)</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>。这里要注意的是</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>COUNT</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>与</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>COUNT(*)</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>区别，</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>COUNT</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>的功能是对一列中的值计算个数，而</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>COUNT(*)</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>才是计算数据库中记录的个数。所以填空①的答案为<span
lang=EN-US>&quot;</span></span><span lang=EN-US style='mso-bidi-font-size:10.5pt'>COUNT(*)</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:
"Times New Roman"'>&quot;。<o:p></o:p></span></p>

<p class=MsoNormal style='mso-layout-grid-align:none;text-autospace:none'><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>程序的</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>2</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>统计</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>100872</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>号运动员参加的所有项目及比赛时间和地点，所以</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>SELECT</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>后面的内容是项目编号</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>ITEM.INO</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>、项目名称</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>INAME</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>时间</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>ITIME</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>及地点</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>IPLACE</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>。统计涉及比赛表</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>GAMES</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>和项目表</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>ITEM</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>，所以</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>FROM</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>后面的内容为</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>GAMES</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>、</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>ITEM</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>。本题考的是连接查询，所谓连接查询指的是涉及两个以上的表的查询。由于是统计</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>100872</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>号运动员参加的所有项目及比赛时间和地点，所以查询条件中必然有</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>GAMES.INO=</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>′</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>100872</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>′</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>(</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>程序中引用到字段时，若字段名在各个表中是惟一的，则可以把字段名前的表名去掉，否则，应当加上表名作为前缀，以免引起混淆</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>)</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>。由于</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>GAMES</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>表中只有比赛的成绩，那些关于项目的数据必须从项目表</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>ITEM</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>中取得，所以还应该有两个表之间的关联，即</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>GAMES.INO=TTEM.INO</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>。所以填空②和③可交换，不影响查询结果。<span
lang=EN-US><o:p></o:p></span></span></p>

<p class=MsoNormal style='mso-layout-grid-align:none;text-autospace:none'><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>程序</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>3</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>要求查参加</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>100035</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>项目的所有运动员名单。分析查询表达式，必首先查询</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>GAMES</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>表，找出参加</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>100035</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>项目的那些运动员的编号</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>ANO</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>，即</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>GAMES.ANO=ATHLETE.ANO AND INO=</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>′</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>100035</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>′，然后再根据查询到的运动员号</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>ANO</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>从</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>ATHLETE</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>表中抽取运动员的数据。所以填空④的答案为<span
lang=EN-US>&quot;</span></span><span lang=EN-US style='mso-bidi-font-size:10.5pt'>EXISTS</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:
"Times New Roman"'>&quot;或&quot;</span><span lang=EN-US style='mso-bidi-font-size:
10.5pt'>ANOIN</span><span lang=EN-US style='mso-bidi-font-size:10.5pt;
font-family:宋体;mso-hansi-font-family:"Times New Roman"'>&quot;，填空⑤的答案为&quot;</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>ANO</span><span lang=EN-US
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>&quot;。<o:p></o:p></span></p>

<p class=MsoNormal align=left style='text-align:left;mso-layout-grid-align:
none;text-autospace:none'><span style='mso-bidi-font-size:10.5pt;font-family:
宋体;mso-hansi-font-family:"Times New Roman"'>程序</span><span lang=EN-US
style='mso-bidi-font-size:10.5pt'>4</span><span style='mso-bidi-font-size:10.5pt;
font-family:宋体;mso-hansi-font-family:"Times New Roman"'>要求建立运动员成绩视图。建立视图的命令为</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>CREATEVIEW</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>，所以填空⑥的答案一定是<span
lang=EN-US>&quot;</span></span><span lang=EN-US style='mso-bidi-font-size:10.5pt'>CREATEVIEW</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:
"Times New Roman"'>&quot;。建立的是运动员成绩视图，那么一定涉及运动员情况、运动员参加的项情况和该项目的成绩，所以要用到</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>ATHLETE</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>、</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>ITEM</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>和</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>GAMES</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>这</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>3</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>个表，因此</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>FROM</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>子句后为</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>ATHLETE</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>、</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>GAMES</span><span
style='mso-bidi-font-size:10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>、</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'><span style="mso-spacerun:
yes">&nbsp; </span>ITEM</span><span style='mso-bidi-font-size:10.5pt;
font-family:宋体;mso-hansi-font-family:"Times New Roman"'>，</span><span
lang=EN-US style='mso-bidi-font-size:10.5pt'>3</span><span style='mso-bidi-font-size:
10.5pt;font-family:宋体;mso-hansi-font-family:"Times New Roman"'>个表可以是任意次序，不影响结果。</span><span
style='font-size:8.5pt;font-family:"MS Sans Serif";mso-font-kerning:0pt;
mso-ansi-language:ZH-CN'><o:p></o:p></span></p>

<p class=MsoNormal><span lang=EN-US><![if !supportEmptyParas]>&nbsp;<![endif]><o:p></o:p></span></p>

</div>

</body>

</html>