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

1、一、上机操作题（40分）： 请下载“学生信息.bak”文件。在企业管理器中建立一个数据库“学生信息”，并用刚才的文件恢复数据库“学生信息”。在D盘建立一个以自己学号+姓名的文件夹。还原时特别注意：(1)若是先创建数据库再还原的，一定要选中图中所示的“在现有数据库上强制还原”；（2）注意修改图中红线框中的路径，例如图中所示的“F:数据库原理及应用”目录即为下载后“学生信息.bak”文件所在目录，这应根据你将其放在什么路径而定本。数据库原理及应用上机操作说明：【上机操作题是2012级在2014年1月4日星期六考试的原题】1、从网上下载指定的数据库备份文件，还原到考生所用的系统中，数据库名为“学生信

2、息”。2、在数据库“学生信息”中，新建一名为“个人信息”表，表结构如下：个人信息（编码char(10)NOT NULL姓名char(8)NOT NULL性别char(2)NOT NULL生日datetime身份证号char(18)家庭住址varchar(50)-建表过程（通过查询分析器建立表）create table 个人信息(编码 char(10) NOT NULL Primary key,姓名 char(8) NOT NULL,性别 char(2) NOT NULL,生日 datetime,身份证号 char(18),家庭住址 varchar(50)3、将“个人信息”表“编码”列设置为主键

3、。上面的红色部分：Primary key即为将该列设为主键。4、为“个人信息”表“身份证号”列建立唯一索引，索引名为“IX_个人信息_身份证号”。5、将“个人信息”表“性别”列的默认值设为“男”，并增加约束，该列只能输入“男”或“女”，约束名为“CK_个人信息_性别”。6、创建一名为“学生总评成绩”的视图，显示学号、姓名、课程名称、总评成绩。（此题已经修改了，现在只需学生，成绩，课程三个表了）-创建视图（通过查询分析器建立视图）create view 学生总评成绩asselect 学生.学号, 姓名, 课程名称, 总评成绩from 学生 join 成绩 on 学生.学号=成绩.学号join 课

4、程 on 成绩.课程代码=课程.课程代码以上5道题可参考上机实验或教材或课件，完全可通过SQL企业管理器图形界面完成操作。7、将课程表中至今没有学生选学的课程记录行删除。delete from 课程 where 课程代码 not in (select 课程代码 from 成绩)8、将“学生”表所有名字只有两个字的学生信息复制到表“两字学生”中，再将表“两字学生”的姓名中间加入一全角的空格，例如姓名“张三”则更改为“张三”。select * into 两字学生 from 学生 where len(姓名)=2update 两字学生 set 姓名=substring(姓名,1,1)+'

5、9; +substring(姓名,2,1)9、将网上下载的Excel文件“电子注册信息.xls”与学生信息数据库中的“学生”表信息进行对比，以学号为基准，找出姓名不一致的学生，并将这些信息存到表“学生注册信息对比”中，该表包括：学号、学生表姓名、电子注册姓名。可以通过两种方法来完成：建议掌握方法一方法一：A、先将电子表格文件内容导入为SQL server数据表，表名取为注册信息，在导入过程中的下面位置中输入表名即可，导入时在此选择表名B、再将“学生”表与“注册信息”表内容按照要求比较，并将结果存入“学生注册信息对比”表中。这易步只需在查询分析器中输入并执行下面的参考语即可：select 学生.

6、学号,学生.姓名 学生表姓名,a.姓名 电子注册姓名into 学生注册信息对比from 学生,注册信息 awhere 学生.学号=a.学号 and 学生.姓名<>a.姓名方法二：通过链接服务器来完成。链接服务器设置如下图：设置完成后，再将“学生”表与链接服务器表内容按照要求比较，并将结果存入“学生注册信息对比”表中。参考语句如下：select 学生.学号,学生.姓名 学生表姓名,a.姓名 注册表姓名into 学生注册信息对比from 学生,excel.注册信息$ awhere 学生.学号=a.学号 and 学生.姓名<>a.姓名提示：查询连接服务器数据表内容时，一定要将

7、电子表格文件关闭。10、编写存储过程编写存储过程“学生成绩统计分析”，带一输入参数“学号”，其开始代码严格如下：        CREATE PROCEDURE 学生成绩统计分析                学号 Char(10)        A

8、S要求此存储过程实现的功能是：根据输入参数“学号”指定的学生，先计算该生的平均总评成绩(保留2位小数)，即在程序中要考虑好变量平均总评成绩 的数据类型，然后：(1)将该生的学习状况添加到表“学生学习状况”中(其xID列为自动赋值)，“评价”列的取值为：优秀、一般或较差，优秀的标准是平均总评成绩>=90，较差的标准是平均总评成绩<60，其余情况则为一般。往该表中添加记录的程序示例：假定学号中指定学生的平均总评成绩为95.73        INSERT INTO 学生学习状况 

9、;(学号, 姓名, 平均总评成绩, 评价)                SELECT 学号, X.姓名, 平均总评成绩, '优秀'                  

10、60;     FROM 学生 X                        WHERE X.学号 = 学号(2)若平均总评成绩在90分以上，则查询出该学生总评成绩>=90的课程，并添加到表“优秀课程”表中(其kID列为自动赋值)。往该表中添加记录的

11、程序示例：假定学号中指定学生的平均总评成绩为95.73，现需将该生总评成绩在7080间的课程添加到该表中        INSERT INTO 优秀课程 (学号, 课程名称, 总评成绩)                SELECT C.学号, K.课程名称, C.总评成绩

12、0;                       FROM 成绩 C, 课程 K                     

13、;   WHERE C.学号=学号 and  C.总评成绩 between 70 and 80  and K.课程代码=C.课程代码(3)若平均总评成绩在60分以下，则查询出该学生总评成绩<60的课程，并添加到表“不及格课程”表中(其kID列为自动赋值)。【下面蓝色字体部分是存储过程，不保证为标准答案】CREATE PROCEDURE 学生成绩统计分析 学号 Char(10)ASinsert into 学生学习状况(学号,姓名,平均总评成绩,评价)

14、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 成

15、绩 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 总评成绩>=90end if (select convert(decim

16、al(5,2), avg(convert(decimal(5,2), 总评成绩) from 成绩 where 学号=学号) < 60)begin insert into 不及格课程(学号,课程名称,总评成绩)select 成绩.学号, 课程名称,总评成绩from 成绩 join 课程 on 成绩.课程代码=课程.课程代码where 成绩.学号 = 学号 and 总评成绩<60end exec11、完全备份数据库“学生信息”，备份文件命名为“学生信息.bak”，将文件 “学生信息.bak”上传到指定的服务器上下面是2014年1月4日星期六具体考试的页面：二、程序填空题（3个SQL填空

17、+3个程序设计，每题两空，两空全对才能得分，共30分）：1、查询2008级学生中的留级（或复读）学生信息select * from 学生 where left(学号,4) <> 年级 and 年级=20082、查询龙泉和航空港校区的班级个数。select 所在地点,count(*) AS 班数 from 班 Group by 所在地点3、查询2008级学生课程代码为1304的平均总评成绩、总评成绩的最高分和最低分。select avg(总评成绩) as 平均总评成绩, max(总评成绩) as 最高分, min(总评成绩) as 最低分 from 成绩 where 年级=2008

18、and 课程代码=1304或select avg(总评成绩), max(总评成绩), min(总评成绩) from 成绩 where 年级=2008 and 课程代码=13044、查找2008级课程1304总评成绩为100分的学生学号、姓名、性别、专业。select 学号,姓名,性别,专业 from 学生 where 学号 in (select 学号 from 成绩 where 年级=2008 and 总评成绩=100 and 课程代码=1304)或select 学生.学号,姓名,性别,学生.专业 from 学生 inner join 成绩 on 学生.学号=成绩.学号 where 成绩.年级

19、=2008 and 总评成绩=100 and 课程代码=13045、查找大气科学专业的学生学号、姓名、性别、年级、进校时间。select 学号,姓名,性别,年级,进校时间 from学生 where 专业=(select 专业代码 from 专业 where 专业名称='大气科学')或select 学号,姓名,性别,年级,进校时间 from 学生 inner join 专业 on 学生.专业=专业.专业代码 where 专业名称='大气科学6、查询学生学号、姓名、性别及所属班全名。select 学号,姓名,性别,班全名 from 学生 inner join 班 On 学生

20、.专业=班.所属专业 and学生.年级=班.年级 and 学生.班序号=班.班序号7、查询总评成绩在5060分之间（不包含50和60）的学生学号、姓名、性别、课程代码、总评成绩，并按学号的升序显示。select 学生.学号,姓名,性别,课程代码,总评成绩 from 学生 inner join 成绩 On 学生.学号=成绩.学号where 总评成绩>50 and 总评成绩 <60 Order by 学生.学号 ASC8、查询学号为'2008011785'的学生姓名、课程名称、总评成绩。select 姓名,课程名称,总评成绩 from 学生 inner join 成绩

21、on 学生.学号=成绩.学号 inner join 课程 on 成绩.课程代码=课程.课程代码 where 学生.学号='2008011785'9、查询2008级平均总评成绩为80分以上（含80分）的专业代码。select 专业 as 专业代码 from 成绩 group by 专业 having avg(总评成绩)>=8010、将2008级同学按照总评成绩的平均分降序输出。select 学号,avg(总评成绩) as 平均分 from 成绩 where 年级=2008 Group by 学号 Order by avg(总评成绩) Desc11、查询2008级课程代码为1

22、304且平均成绩在80分以上（含80分）的专业代码及平均成绩。select 专业 as 专业代码,avg(总评成绩) as 平均成绩 from 成绩 where 年级=2008 and 课程代码=1304 group by 专业 having avg(总评成绩)>=8012、查询学号为2008092929的学生的总评成绩比2008024763号学生的最高分数还要高的课程代码和总评成绩。select 学号,课程代码,总评成绩 from 成绩 where 学号='2008092929' and 总评成绩>All (select 总评成绩 from 成绩 where 学号

23、='2008024763')或select 学号,课程代码,总评成绩 from 成绩 where 学号='2008092929' and 总评成绩> (select max(总评成绩) from 成绩 where 学号='2008024763')13、查询2008级学生中各门课程的平均总评成绩情况（课程代码、课程名称、平均成绩、选课人数），并按照平均总评成绩降序排列输出。select 课程.课程代码,课程名称,平均成绩, 选课人数from 课程 inner join (select 课程代码,avg(总评成绩) as 平均成绩,count(

24、*) as 选课人数from 成绩 where 年级=2008 Group by 课程代码) AS A on 课程.课程代码=A.课程代码Order by 平均成绩 Desc14、查找并显示电子信息工程专业2008级同学计算机文化基础课程的最高分、最低分、平均分。select max(总评成绩) as 最高分,min(总评成绩) as 最低分,avg(总评成绩) as 平均分 from 成绩 where 年级=2008 and 课程代码=(select 课程代码 from 课程 where 课程名称='计算机文化基础') and 专业=(select 专业代码 from 专业

25、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 jo

26、in 专业 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 b

27、y 学号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 成绩.学号

28、=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

29、17编写程序实现1，40的奇数平方和赋x，偶数平方和赋y，并输出x，y的值。DECLARE n int,x int,y intSET x=0SET y=0SET n=1WHILE n<=40BEGIN if n % 2=1 set x=x+n*n else set y=y+n*n set n=n+1ENDSELECT x,y18、编写程序计算1-5000之间(含1和5000)所有能被9整除的数的个数及总和。DECLARE n int,x int,y intSET x=0SET y=0SET n=1WHILE n<=5000BEGIN if n % 9=0 BEGIN set x=x

30、+1 set y=y+n end set n=n+1ENDSELECT x,y19、计算1-9999以内的所有完全平方数（如36=6*6，则称36为完全平方数）的总和及个数。declare n int,sum int,count intset n=1set sum=0set count=0while n*n<=9999 begin set sum=sum+n*n set count=count+1 set n=n+1 Endselect sum,count20、通过while循环计算10！。DECLARE s int,n intSET S=1SET n=1WHILE n<=10 B

31、EGIN SET s=s*n SET n=n+1 ENDSELECT s,n21、完成程序依次显示字母AZ。declare count int,abc varchar(100)set count=0while count<26begin set abc=char(ascii('A')+count) print abc set count=count+1end 22、完成程序依次显示字母ZA。declare count int,abc varchar(100)set count=0while count<26begin set abc= char(ascii('

32、;Z')-count) print abcset count=count+1end 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 an

33、d 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 季度 tinyintset 季度= datename(q,getdate()select case 季度 when 1 then '第一

34、季度' 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日星期六考试原题出自这里面，是程序组合（选

35、ABCDEFG型的题）的原题，可以理解记忆】（下面答案是学长参考答案手打）（8选2（前4题选1、后4题选1），为了能使考试知识点更加全面，前4题基本围绕在多表（基本是3表）查询中使用表达式、函数、多条件、排序等设计；后4题的知识点分布在分组统计、动作查询中使用子查询等）参考数据表结构：教师(编号,姓名,出生日期,学历)课程(编号,名称,学分)工资(教师编号,日期,应发工资,扣款)授课安排(授课ID,教师编号,授课课程,教室),【授课ID】是自动增长字段，【教师编号】是参照【教师. 编号】的外键，【授课课程】是参照【课程.编号】的外键。带有下划线字段为表的主关键字1. 查询年龄在35岁以下教授“

36、数据库原理”的教师有几位。Select count(*) as 人数 from 课程 inner join 授课安排 on 课程.编号=授课安排.授课课程inner join 教师on 教师.编号=授课安排.教师编号where dateadd(yy,35,出生日期)<getdate() and 名称=数据库原理2. 将“张晓辉”老师教授课程“数据库原理”的教室由原来的“H1210”调整到“H3210”。 Update 授课安排 set 教室=H3210From 授课安排 inner join 课程On 课程.编号=授课安排.授课课程Inner join 教师 on 教师.编号=授课安排.教师编号Where 姓名=张晓辉 a