成都信息工程学院数据库复习资料数据库期末复习资料操作题原题+程序语句组合原题.docx

成都信息工程学院数据库复习资料数据库期末复习资料操作题原题+程序语句组合原题.docx

《成都信息工程学院数据库复习资料数据库期末复习资料操作题原题+程序语句组合原题.docx》由会员分享，可在线阅读，更多相关《成都信息工程学院数据库复习资料数据库期末复习资料操作题原题+程序语句组合原题.docx（19页珍藏版）》请在冰豆网上搜索。

成都信息工程学院数据库复习资料数据库期末复习资料操作题原题+程序语句组合原题

一、上机操作题（40分）：

请下载“学生信息.bak”文件。

在企业管理器中建立一个数据库“学生信息”，并用刚才的文件恢复数据库“学生信息”。

在D盘建立一个以自己学号+姓名的文件夹。

还原时特别注意：

（1）若是先创建数据库再还原的，一定要选中图中所示的“在现有数据库上强制还原”；

（2）注意修改图中红线框中的路径，例如图中所示的“F:

\数据库原理及应用\”目录即为下载后“学生信息.bak”文件所在目录，这应根据你将其放在什么路径而定本。

《数据库原理及应用》上机操作

说明：

【上机操作题是2012级在2014年1月4日星期六考试的原题】

1、从网上下载指定的数据库备份文件，还原到考生所用的系统中，数据库名为“学生信息”。

2、在数据库“学生信息”中，新建一名为“个人信息”表，表结构如下：

个人信息（编码char（10）NOTNULL

姓名char（8）NOTNULL

性别char

（2）NOTNULL

生日datetime

身份证号char（18）

家庭住址varchar（50））

--建表过程（通过查询分析器建立表）

createtable个人信息（

编码char（10）NOTNULLPrimarykey,

姓名char（8）NOTNULL,

性别char

（2）NOTNULL,

生日datetime,

身份证号char（18）,

家庭住址varchar（50）

）

3、将“个人信息”表“编码”列设置为主键。

上面的红色部分：

Primarykey即为将该列设为主键。

4、为“个人信息”表“身份证号”列建立唯一索引，索引名为“IX_个人信息_身份证号”。

5、将“个人信息”表“性别”列的默认值设为“男”，并增加约束，该列只能输入“男”或“女”，约束名为“CK_个人信息_性别”。

6、创建一名为“学生总评成绩”的视图，显示学号、姓名、课程名称、总评成绩。

（此题已经修改了，现在只需学生，成绩，课程三个表了）

--创建视图（通过查询分析器建立视图）

createview学生总评成绩

as

select学生.学号,姓名,课程名称,总评成绩

from学生join成绩on学生.学号=成绩.学号

join课程on成绩.课程代码=课程.课程代码

以上5道题可参考上机实验或教材或课件，完全可通过SQL企业管理器图形界面完成操作。

7、将课程表中至今没有学生选学的课程记录行删除。

deletefrom课程where课程代码notin（select课程代码from成绩）

8、将“学生”表所有名字只有两个字的学生信息复制到表“两字学生”中，再将表“两字学生”的姓名中间加入一全角的空格，例如姓名“张三”则更改为“张　三”。

select*into两字学生from学生wherelen（姓名）=2

update两字学生set姓名=substring（姓名,1,1）+'　'+substring（姓名,2,1）

9、将网上下载的Excel文件“电子注册信息.xls”与学生信息数据库中的“学生”表信息进行对比，以学号为基准，找出姓名不一致的学生，并将这些信息存到表“学生注册信息对比”中，该表包括：

学号、学生表姓名、电子注册姓名。

可以通过两种方法来完成：

建议掌握方法一

方法一：

A、先将电子表格文件内容导入为SQLserver数据表，表名取为注册信息，在导入过程中的下面位置中输入表名即可，

导入时在此选择表名

B、再将“学生”表与“注册信息”表内容按照要求比较，并将结果存入“学生注册信息对比”表中。

这易步只需在查询分析器中输入并执行下面的参考语即可：

select学生.学号,学生.姓名学生表姓名,a.姓名电子注册姓名

into学生注册信息对比

from学生,注册信息a

where学生.学号=a.学号and学生.姓名<>a.姓名

方法二：

通过链接服务器来完成。

链接服务器设置如下图：

设置完成后，再将“学生”表与链接服务器表内容按照要求比较，并将结果存入“学生注册信息对比”表中。

参考语句如下：

select学生.学号,学生.姓名学生表姓名,a.姓名注册表姓名

into学生注册信息对比

from学生,excel...注册信息$a

where学生.学号=a.学号and学生.姓名<>a.姓名

提示：

查询连接服务器数据表内容时，一定要将电子表格文件关闭。

10、编写存储过程

编写存储过程“学生成绩统计分析”，带一输入参数“@学号”，其开始代码严格如下：

        CREATE PROCEDURE 学生成绩统计分析

                @学号 Char（10）

        AS

要求此存储过程实现的功能是：

根据输入参数“@学号”指定的学生，先计算该生的平均总评成绩（保留2位小数），即在程序中要考虑好变量@平均总评成绩的数据类型，然后：

（1）将该生的学习状况添加到表“学生学习状况”中（其xID列为自动赋值），“评价”列的取值为：

优秀、一般或较差，优秀的标准是平均总评成绩>=90，较差的标准是平均总评成绩<60，其余情况则为一般。

往该表中添加记录的程序示例：

假定@学号中指定学生的@平均总评成绩为95.73

        INSERT INTO 学生学习状况 （学号, 姓名, 平均总评成绩, 评价）

                SELECT @学号, X.姓名, @平均总评成绩, '优秀'

                        FROM 学生 X

                        WHERE X.学号 = @学号

（2）若平均总评成绩在90分以上，则查询出该学生总评成绩>=90的课程，并添加到表“优秀课程”表中（其kID列为自动赋值）。

往该表中添加记录的程序示例：

假定@学号中指定学生的@平均总评成绩为95.73，现需将该生总评成绩在70～80间的课程添加到该表中

        INSERT INTO 优秀课程 （学号, 课程名称, 总评成绩）

                SELECT C.学号, K.课程名称, C.总评成绩

                        FROM 成绩 C, 课程 K

                        WHERE C.学号=@学号 and  C.总评成绩 between 70 and 80  and K.课程代码=C.课程代码

　　（3）若平均总评成绩在60分以下，则查询出该学生总评成绩<60的课程，并添加到表“不及格课程”表中（其kID列为自动赋值）。

【下面蓝色字体部分是存储过程，不保证为标准答案】

CREATEPROCEDURE学生成绩统计分析

@学号Char（10）

AS

insertinto学生学习状况（学号,姓名,平均总评成绩,评价）

select

学生.学号,

姓名,

convert（decimal（5,2）,avg（convert（decimal（5,2）,总评成绩）））as平均总评成绩,

评价=

（

case

whenconvert（decimal（5,2）,avg（convert（decimal（5,2）,总评成绩）））>=90then'优秀'

whenconvert（decimal（5,2）,avg（convert（decimal（5,2）,总评成绩）））<60then'较差'

else'一般'

end

）

from学生innerjoin成绩on学生.学号=成绩.学号

where学生.学号=@学号

groupby学生.学号,姓名

if（（selectconvert（decimal（5,2）,avg（convert（decimal（5,2）,总评成绩）））from成绩where学号=@学号）>=90）

begin

insertinto优秀课程（学号,课程名称,总评成绩）

select成绩.学号,课程名称,总评成绩

from成绩join课程on成绩.课程代码=课程.课程代码

where成绩.学号=@学号and总评成绩>=90

end

if（（selectconvert（decimal（5,2）,avg（convert（decimal（5,2）,总评成绩）））from成绩where学号=@学号）<60）

begin

insertinto不及格课程（学号,课程名称,总评成绩）

select成绩.学号,课程名称,总评成绩

from成绩join课程on成绩.课程代码=课程.课程代码

where成绩.学号=@学号and总评成绩<60

end

exec

11、完全备份数据库“学生信息”，备份文件命名为“学生信息.bak”，将文件“学生信息.bak”上传到指定的服务器上

下面是2014年1月4日星期六具体考试的页面：

二、程序填空题（3个SQL填空+3个程序设计，每题两空，两空全对才能得分，共30分）：

1、查询2008级学生中的留级（或复读）学生信息

select*from学生whereleft（学号,4）<>年级and年级=2008

2、查询龙泉和航空港校区的班级个数。

select所在地点,count（*）AS班数from班Groupby所在地点

3、查询2008级学生课程代码为1304的平均总评成绩、总评成绩的最高分和最低分。

selectavg（总评成绩）as平均总评成绩,max（总评成绩）as最高分,min（总评成绩）as最低分from成绩where年级=2008and课程代码=1304

或

selectavg（总评成绩）,max（总评成绩）,min（总评成绩）from成绩where年级=2008and课程代码=1304

4、查找2008级课程1304总评成绩为100分的学生学号、姓名、性别、专业。

select学号,姓名,性别,专业from学生where学号in（select学号from成绩where年级=2008and总评成绩=100and课程代码=1304）

或

select学生.学号,姓名,性别,学生.专业from学生innerjoin成绩on学生.学号=成绩.学号where成绩.年级=2008and总评成绩=100and课程代码=1304

5、查找大气科学专业的学生学号、姓名、性别、年级、进校时间。

select学号,姓名,性别,年级,进校时间from学生where专业=（select专业代码from专业where专业名称='大气科学'）

或

select学号,姓名,性别,年级,进校时间from学生innerjoin专业on学生.专业=专业.专业代码where专业名称='大气科学

6、查询学生学号、姓名、性别及所属班全名。

select学号,姓名,性别,班全名from学生innerjoin班On学生.专业=班.所属专业and

学生.年级=班.年级and学生.班序号=班.班序号

7、查询总评成绩在50~60分之间（不包含50和60）的学生学号、姓名、性别、课程代码、总评成绩，并按学号的升序显示。

select学生.学号,姓名,性别,课程代码,总评成绩from学生innerjoin成绩On学生.学号=成绩.学号where总评成绩>50and总评成绩<60Orderby学生.学号ASC

8、查询学号为''的学生姓名、课程名称、总评成绩。

select姓名,课程名称,总评成绩from学生innerjoin成绩on学生.学号=成绩.学号

innerjoin课程on成绩.课程代码=课程.课程代码where学生.学号=''

9、查询2008级平均总评成绩为80分以上（含80分）的专业代码。

select专业as专业代码from成绩groupby专业havingavg（总评成绩）>=80

10、将2008级同学按照总评成绩的平均分降序输出。

select学号,avg（总评成绩）as平均分from成绩where年级=2008Groupby学号Orderbyavg（总评成绩）Desc

11、查询2008级课程代码为1304且平均成绩在80分以上（含80分）的专业代码及平均成绩。

select专业as专业代码,avg（总评成绩）as平均成绩from成绩where年级=2008and课程代码=1304groupby专业havingavg（总评成绩）>=80

12、查询学号为‘’的学生的总评成绩比‘’号学生的最高分数还要高的课程代码和总评成绩。

select学号,课程代码,总评成绩from成绩where学号=''and总评成绩>All（select总评成绩from成绩where学号=''）

或

select学号,课程代码,总评成绩from成绩where学号=''and总评成绩>（selectmax（总评成绩）from成绩where学号=''）

13、查询2008级学生中各门课程的平均总评成绩情况（课程代码、课程名称、平均成绩、选课人数），并按照平均总评成绩降序排列输出。

select课程.课程代码,课程名称,平均成绩,选课人数from课程innerjoin（select课程代码,avg（总评成绩）as平均成绩,count（*）as选课人数from成绩where年级=2008Groupby课程代码）ASAon课程.课程代码=A.课程代码Orderby平均成绩Desc

14、查找并显示电子信息工程专业2008级同学计算机文化基础课程的最高分、最低分、平均分。

selectmax（总评成绩）as最高分,min（总评成绩）as最低分,avg（总评成绩）as平均分from成绩where年级=2008and课程代码=（select课程代码from课程where课程名称='计算机文化基础'）and专业=（select专业代码from专业where专业名称='电子信息工程'）

或

selectmax（总评成绩）as最高分,min（总评成绩）as最低分,avg（总评成绩）as平均分from成绩innerjoin课程on成绩.课程代码=课程.课程代码where年级=2008and课程名称='计算机文化基础'and专业=（select专业代码from专业where专业名称='电子信息工程'）

或

selectmax（总评成绩）as最高分,min（总评成绩）as最低分,avg（总评成绩）as平均分from成绩innerjoin专业on成绩.专业=专业.专业代码where年级=2008and课程代码=（select课程代码from课程where课程名称='计算机文化基础'）and专业名称='电子信息工程'

15、将成绩表中2008级平均在85分以上的学生信息（学号、姓名、专业代码、平均总评成绩）复制到表goodbaby。

select学生.学号,姓名,学生.专业,A.平均成绩intogoodbabyfrom学生innerjoin（select学号,avg（总评成绩）as平均成绩from成绩where年级=2008Groupby学号havingavg（总评成绩）>85）asAon学生.学号=A.学号

16、将成绩表中2008级平均总评成绩在70分以下且有3门（含3门）以上不及格的学生信息（学号、平均成绩、不及格门数）添加到badbaby中（学号,平均成绩,不及格门数）。

select成绩.学号,count（*）as不及格门数,avg（总评成绩）AS平均成绩intobadbabyfrom成绩innerjoin（select学号from成绩where成绩.年级=2008Groupby学号havingavg（总评成绩）<=70）ASAon成绩.学号=A.学号where总评成绩<60and年级=2008Groupby成绩.学号havingcount（*）>=3

或

select成绩.学号,count（*）as不及格门数,avg（总评成绩）AS平均成绩from成绩innerjoin（select学号from成绩where总评成绩<60and年级=2008Groupby成绩.学号havingcount（*）>=3）ASAon成绩.学号=A.学号where成绩.年级=2008Groupby成绩.学号havingavg（总评成绩）<=70

17编写程序实现[1，40]的奇数平方和赋x，偶数平方和赋y，并输出x，y的值。

DECLARE@nint,@xint,@yint

SET@x=0

SET@y=0

SET@n=1

WHILE@n<=40

BEGIN

if@n%2=1

set@x=@x+@n*@n

else

set@y=@y+@n*@n

set@n=@n+1

END

SELECT@x,@y

18、编写程序计算1-5000之间（含1和5000）所有能被9整除的数的个数及总和。

DECLARE@nint,@xint,@yint

SET@x=0

SET@y=0

SET@n=1

WHILE@n<=5000

BEGIN

if@n%9=0

BEGIN

set@x=@x+1

set@y=@y+@n

end

set@n=@n+1

END

SELECT@x,@y

19、计算1-9999以内的所有完全平方数（如36=6*6，则称36为完全平方数）的总和及个数。

declare@nint,@sumint,@countint

set@n=1

set@sum=0

set@count=0

while@n*@n<=9999

begin

set@sum=@sum+@n*@n

set@count=@count+1

set@n=@n+1

End

select@sum,@count

20、通过while循环计算10！

。

DECLARE@sint,@nint

SET@S=1

SET@n=1

WHILE@n<=10

BEGIN

SET@s=@s*@n

SET@n=@n+1

END

SELECT@s,@n

21、完成程序依次显示字母A~Z。

declare@countint,@abcvarchar（100）

set@count=0

while@count<26

begin

set@abc=char（ascii（'A'）+@count）

print@abc

set@count=@count+1

end

22、完成程序依次显示字母Z~A。

declare@countint,@abcvarchar（100）

set@count=0

while@count<26

begin

set@abc=char（ascii（'Z'）-@count）

print@abc

set@count=@count+1

end

23、查询2008级所有同学的学号、总评成绩的平均分、等级（90分以上A，80-90为B，70-80为C，60-70为D，60分以下为E），并按照等级降序输出（即先输出A级学生，再输出B级学生，最后输出E级学生）。

select学号,avg（总评成绩）,

（casewhenavg（总评成绩）>=90then'A'

whenavg（总评成绩）>=80andavg（总评成绩）<90then'B'

whenavg（总评成绩）>=70andavg（总评成绩）<80then'C'

whenavg（总评成绩）>=60andavg（总评成绩）<70then'D'

whenavg（总评成绩）<60then'E'end）

as等级

from成绩WHERE年级=2008groupby学号

24、显示今天是第几季度，输出格式为“第一季度”、“第二季度”、“第三季度”、“第四季度”。

declare@季度tinyint

set@季度=datename（q,getdate（））

select

case@季度

when1then'第一季度'

when2then'第二季度'

when3then'第三季度'

when4then'第四季度'

End

或

select

casedatename（q,getdate（））

when1then'第一季度'

when2then'第二季度'

when3then'第三季度'

when4then'第四季度'

End

三、SQL语句组合题目参考（2题共10分）：

说明：

【此题也是2012级在2014年1月4日星期六考试原题出自这里面，是程序组合（选ABCDEFG型的题）的原题，可以理解记忆】（下面答案是学长参考答案手打）

（8选2（前4题选1、后4题选1），为了能使考试知识点更加全面，前4题基本围绕在多表（基本是3表）查询中使用表达式、函数、多条件、排序等设计；后4题的知识点分布在分组统计、动作查询中使用子查询等）

参考数据表结构：

教师（编号,姓名,出生日期,学历）

课程（编号,名称,学分）

工资（教师编号,日期,应发工资,扣款）

授课安排（授课ID,教师编号,授课课程,教室）,【授课ID】是自动增长字段，【教师编号】是参照【教师.编号】的外键，【授课课程】是参照【课程.编号】的外键。

带有下划线字段为表的主关键字

1.查询年龄在35岁以下教授“数据库原理”的教师有几位。

Selectcount（*）as人数from课程innerjoin授课安排

on课程.编号=授课安排.授课课程

innerjoin教师

on教师.编号=授课安排.教师编号

wheredateadd（yy,35,出生日期）

2.将“张晓辉”老师教授课程“数据库原