成都信息工程学院数据库复习资料数据库期末复习资料操作题原题+程序语句组合原题.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.将“张晓辉”老师教授课程“数据库原