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

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

请下载“学生信息.bak”文件。在企业管理器中建立一个数据库“学生信息”，并用刚才的文件恢复数据库“学生信息”。在D盘建立一个以自己学号+姓名的文件夹。 还原时特别注意：(1)若是先创建数据库再还原的，一定要选中图中所示的“在现有数据库上强制还原”；（2）注意修改图中红线框中的路径，例如图中所示的“F:\\数据库原理及应用\\”目录即为下载后“学生信息.bak”文件所在目录，这应根据你将其放在什么路径而定本。

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

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

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

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

个人信息（编码 char(10) NOT NULL

姓名 性别

char(8) NOT NULL char(2) NOT NULL

生日 datetime

char(18) varchar(50))

身份证号 家庭住址

--建表过程（通过查询分析器建立表） create table 个人信息(

)

3、将“个人信息”表“编码”列设置为主键。 上面的红色部分：Primary key即为将该列设为主键。

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

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

6、创建一名为“学生总评成绩”的视图，显示学号、姓名、课程名称、总评成绩。（此题已经修改了，现在只需学生，成绩，课程三个表了）

--创建视图（通过查询分析器建立视图） create view 学生总评成绩 as

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

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

编码 char(10) NOT NULL Primary key, 姓名 char(8) NOT NULL, 性别 char(2) NOT NULL, 生日 datetime, 身份证号 char(18), 家庭住址 varchar(50)

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

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

delete from 课程 where 课程代码 not in (select 课程代码 from 成绩)

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

select * into 两字学生 from 学生 where len(姓名)=2

update 两字学生 set 姓名=substring(姓名,1,1)+' ' +substring(姓名,2,1)

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

可以通过两种方法来完成：建议掌握方法一

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

导入时在此选择表名

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列为自动赋值)。

【下面蓝色字体部分是存储过程，不保证为标准答案】 CREATE PROCEDURE 学生成绩统计分析 @学号 Char(10) AS

insert into 学生学习状况(学号,姓名,平均总评成绩,评价) select

学生.学号, 姓名,

convert(decimal(5,2),avg(convert(decimal(5,2), 总评成绩))) as 平均总评成绩, 评价= ( case

when convert(decimal(5,2), avg(convert(decimal(5,2), 总评成绩))) >= 90 then '优秀' when convert(decimal(5,2), avg(convert(decimal(5,2), 总评成绩))) < 60 then '较差' else '一般'

end )

from 学生 inner join 成绩 on 学生.学号 = 成绩.学号 where 学生.学号 = @学号 group by 学生.学号, 姓名

if ((select convert(decimal(5,2), avg(convert(decimal(5,2), 总评成绩))) from 成绩 where 学号=@学号) >= 90) begin

insert into 优秀课程(学号,课程名称,总评成绩) select 成绩.学号, 课程名称,总评成绩

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

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

end

if ((select convert(decimal(5,2), avg(convert(decimal(5,2), 总评成绩))) from 成绩 where 学号=@学号) < 60) begin

insert into 不及格课程(学号,课程名称,总评成绩) select 成绩.学号, 课程名称,总评成绩

from 成绩 join 课程 on 成绩.课程代码=课程.课程代码 where 成绩.学号 = @学号 and 总评成绩<60

end exec

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

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

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

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

select * from 学生 where left(学号,4) <> 年级 and 年级=2008

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

select 所在地点,count(*) AS 班数 from 班 Group by 所在地点

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

select avg(总评成绩) as 平均总评成绩, max(总评成绩) as 最高分, min(总评成绩) as 最低分 from 成绩 where 年级=2008 and 课程代码=1304

或

select avg(总评成绩), max(总评成绩), min(总评成绩) from 成绩 where 年级=2008 and 课程代码=1304

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

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

或

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

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

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

或

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

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

select 学号,姓名,性别,班全名 from 学生 inner join 班 On 学生.专业=班.所属专业 and 学生.年级=班.年级 and 学生.班序号=班.班序号

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

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

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

select 姓名,课程名称,总评成绩 from 学生 inner join 成绩 on 学生.学号=成绩.学号 inner join 课程 on 成绩.课程代码=课程.课程代码 where 学生.学号='2008011785'

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

select 专业 as 专业代码 from 成绩 group by 专业 having avg(总评成绩)>=80

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

select 学号,avg(总评成绩) as 平均分 from 成绩 where 年级=2008 Group by 学号 Order by avg(总评成绩) Desc

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

select 专业 as 专业代码,avg(总评成绩) as 平均成绩 from 成绩 where 年级=2008 and 课程代码=1304 group by 专业 having avg(总评成绩)>=80

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

select 学号,课程代码,总评成绩 from 成绩 where 学号='2008092929' and 总评成绩>All (select 总评成绩 from 成绩 where 学号='2008024763')

或

select 学号,课程代码,总评成绩 from 成绩 where 学号='2008092929' and 总评成绩> (select max(总评成绩) from 成绩 where 学号='2008024763')

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

select 课程.课程代码,课程名称,平均成绩, 选课人数from 课程 inner join (select 课程代码,avg(总评成绩) as 平均成绩,count(*) as 选课人数from 成绩 where 年级=2008 Group by 课程代码) AS A on 课程.课程代码=A.课程代码Order by 平均成绩 Desc

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

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

或

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

或

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

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

select 学生.学号,姓名,学生.专业,A.平均成绩 into goodbaby from 学生 inner join (select 学号,avg(总评成绩) as 平均成绩 from 成绩 where 年级=2008 Group by 学号having avg(总评成绩)>85) as A on 学生.学号=A.学号

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

select 成绩.学号,count(*) as 不及格门数,avg(总评成绩) AS 平均成绩 into badbaby from 成绩 inner join (select 学号 from 成绩 where 成绩.年级=2008 Group by 学号 having avg(总评成绩)<=70) AS A on 成绩.学号=A.学号 where 总评成绩<60 and 年级=2008 Group by 成绩.学号 having count(*)>=3 或

select 成绩.学号,count(*) as 不及格门数,avg(总评成绩) AS 平均成绩 from 成绩 inner join (select 学号 from 成绩 where 总评成绩<60 and 年级=2008 Group by 成绩.学号 having count(*)>=3) AS A on 成绩.学号=A.学号 where 成绩.年级=2008 Group by 成绩.学号 having avg(总评成绩)<=70

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

DECLARE @n int,@x int,@y int 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 @n int,@x int,@y int 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 @n int,@sum int,@count int 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 @s int,@n int 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 @count int,@abc varchar(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 @count int,@abc varchar(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(总评成绩),

(case when avg(总评成绩)>=90 then 'A'

when avg(总评成绩)>=80 and avg(总评成绩)<90 then 'B' when avg(总评成绩)>=70 and avg(总评成绩)<80 then 'C' when avg(总评成绩)>=60 and avg(总评成绩)<70 then 'D' when avg(总评成绩)<60 then 'E' end)

as 等级

from 成绩 WHERE 年级=2008 group by 学号

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

declare @季度 tinyint

set @季度= datename(q,getdate()) select case @季度

when 1 then '第一季度' when 2 then '第二季度' when 3 then '第三季度' when 4 then '第四季度' End 或 select

case datename(q,getdate()) when 1 then '第一季度' when 2 then '第二季度' when 3 then '第三季度' when 4 then '第四季度' End

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

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

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

参考数据表结构： 教师(编号,姓名,出生日期,学历) 课程(编号,名称,学分) 工资(教师编号,日期,应发工资,扣款) 授课安排(授课ID,教师编号,授课课程,教室),【授课ID】是自动增长字段，【教师编号】是参照【教师. 编号】的外键，【授课课程】是参照【课程.编号】的外键。 带有下划线字段为表的主关键字

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

Select count(*) as 人数 from 课程 inner join 授课安排

on 课程.编号=授课安排.授课课程 inner join 教师 on 教师.编号=授课安排.教师编号 where dateadd(yy,35,出生日期)

2. 将“张晓辉”老师教授课程“数据库原理”的教室由原来的“H1210”调整到“H3210”。

Update 授课安排 set 教室=＇H3210＇

3.

4.

5.

6.

7.

8.

From 授课安排 inner join 课程 On 课程.编号=授课安排.授课课程 Inner join 教师 on 教师.编号=授课安排.教师编号 Where 姓名=’张晓辉’ and 授课课程=＇数据库原理＇

and 教室 =＇H1210＇

查询教授数据库相关课程的教师信息，即课程名称含有“数据库”的课程，先按出生日期降序、再按学历升序显示教师编号、姓名、出生日期、学历，教师信息相同的只显示一次。

Select distinct 教师.编号,姓名,出生日期,学历 from 课程 inner join 授课安排 On 课程.编号=授课安排.授课课程 Inner join 教师 on 教师.编号 =授课安排.教师编号 Where 名称 like ＇％数据库％＇ Order by 出生日期 desc,学历 asc

查询教授“C语言程序设计”和“数据库原理”两门课程的教师工资信息，显示教师编号,日期,实发工资，其中实发工资为应发工资减去扣款。

Select 工资.教师编号,日期,应发工资-扣款 (as) 实发工资 from 工资

inner join 授课安排 on 工资.教师编号 = 授课安排.教师编号 inner join 课程 on 课程.编号 = 授课安排.授课课程 where 名称 in (＇Ｃ语言程序设计＇，＇数据库原理＇)

为“张晓辉”老师安排编码为“C105”课程的授课教室在“H1104”。(名为“张晓辉”的教师只有一位)

Insert into 授课安排 select 编号,＇C105＇,＇H1104＇

from 教师where 姓名=＇张晓辉＇

查询每门课程的最小授课ID号，显示课程的编号、名称和最小授课ID。（课程名称可能有重复）

Select 课程.编号,max(课程.名称) 名称, min(授课ID) 最小授课ID From 课程 inner join 授课安排 on 课程.编号=授课安排.授课课程 Group by 课程.编号

为讲授课程编号为“C105”的教师制作本月工资表，【日期】取当前时间，【应发工资】和【扣款】取'2011-01-01'对应“工资”表数据。

Insert into 工资 select 工资.教师编号,getdate(),应发工资,扣款 From 工资 inner join 授课安排 On 工资.教师编号 = 授课安排.教师编号 Where 授课课程=＇C105＇and 日期=＇2011-01-01＇

删除教授课程编号为“C101”的教师对应于2011年1月1日的工资数据。 Delete from 工资where 日期=＇2011-01-01＇

and 教师编号 in (select 教师编号 from 授课安排 where 授课课程 = ＇C101＇)

四、数据库设计要用到的命令：

select distinct 学号,姓名,性别,生源省 into 学生 from 学生及成绩$ select distinct 课程号,课程名称 into 课程 from 学生及成绩$ select 学号,课程号,总评成绩 into 成绩 from 学生及成绩$

本文来源：https://www.bwwdw.com/article/j485.html