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),家庭住址 varch
3、ar(50)3、将“个人信息”表“编码”列设置为主键。上面的红色部分：Primary key 即为将该列设为主键。4、为“个人信息”表“身份证号”列建立唯一索引，索引名为“IX_个人信息_身份证号” 。5、将“个人信息”表“性别”列的默认值设为“男” ，并增加约束，该列只能输入“男”或“女” ，约束名为“CK_个人信息_性别” 。6、创建一名为“学生总评成绩”的视图，显示学号、姓名、课程名称、总评成绩。（此题已经修改了，现在只需学生，成绩，课程三个表了）-创建视图（通过查询分析器建立视图）create view 学生总评成绩asselect 学生.学号, 姓名, 课程名称 , 总评成绩from
4、 学生 join 成绩 on 学生.学号=成绩.学号join 课程 on 成绩.课程代码=课程.课程代码以上 5 道题可参考上机实验或教材或课件，完全可通过 SQL 企业管理器图形界面完成操作。7、将课程表中至今没有学生选学的课程记录行删除。delete from 课程 where 课程代码 not in (select 课程代码 from 成绩)8、将“学生”表所有名字只有两个字的学生信息复制到表“两字学生”中，再将表“两字学生”的姓名中间加入一全角的空格，例如姓名“张三”则更改为“张 三” 。select * into 两字学生 from 学生 where len(姓名)=2update
5、两字学生 set 姓名=substring(姓名,1,1)+ +substring(姓名,2,1)9、将网上下载的 Excel 文件 “电子注册信息.xls”与学生信息数据库中的“学生”表信息进行对比，以学号为基准，找出姓名不一致的学生，并将这些信息存到表“学生注册信息对比”中，该表包括：学号、学生表姓名、电子注册姓名。可以通过两种方法来完成：建议掌握方法一方法一：A、先将电子表格文件内容导入为 SQL server 数据表，表名取为注册信息，在导入过程中的下面位置中输入表名即可，B、再将“学生”表与“注册信息 ”表内容按照要求比较，并将结果存入“学生注册信息对比”表中。这易步只需在查询分析器
6、中输入并执行下面的参考语即可：select 学生.学号,学生.姓名 学生表姓名 ,a.姓名 电子注册姓名into 学生注册信息对比导入时在此选择表名from 学生,注册信息 awhere 学生.学号=a.学号 and 学生.姓名a.姓名提示：查询连接服务器数据表内容时，一定要将电子表格文件关闭。10、编写存储过程编写存储过程“学生成绩统计分析” ，带一输入参数“学号” ，其开始代码严格如下：CREATE PROCEDURE 学生成绩统计分析学号 Char(10)AS要求此存储过程实现的功能是：根据输入参数“学号”指定的学生，先计算该生的平均总评成绩(保留 2 位小数)，即在程序中要考虑好变量平
7、均总评成绩 的数据类型，然后：(1)将该生的学习状况添加到表“学生学习状况”中(其 xID 列为自动赋值)， “评价”列的取值为：优秀、一般或较差，优秀的标准是平均总评成绩=90，较差的标准是平均总评成绩=90 的课程，并添加到表“优秀课程”表中(其 kID 列为自动赋值 )。往该表中添加记录的程序示例：假定学号中指定学生的平均总评成绩为 95.73，现需将该生总评成绩在 7080 间的课程添加到该表中INSERT INTO 优秀课程 (学号, 课程名称, 总评成绩)SELECT C.学号, K.课程名称, C.总评成绩FROM 成绩 C, 课程 KWHERE C.学号= 学号 and C.总
8、评成绩 between 70 and 80 and K.课程代码=C.课程代码(3)若平均总评成绩在 60 分以下，则查询出该学生总评成绩 = 90 then 优秀when convert(decimal(5,2), avg(convert(decimal(5,2), 总评成绩) = 90)begin insert into 优秀课程( 学号,课程名称,总评成绩)select 成绩.学号, 课程名称,总评成绩from 成绩 join 课程 on 成绩.课程代码=课程.课程代码where 成绩.学号 = 学号 and 总评成绩=90end if (select convert(decimal(5
9、,2), avg(convert(decimal(5,2), 总评成绩) from 成绩 where 学号=学号) 年级 and 年级=20082、查询龙泉和航空港校区的班级个数。select 所在地点,count(*) AS 班数 from 班 Group by 所在地点3、查询 2008 级学生课程代码为 1304 的平均总评成绩、总评成绩的最高分和最低分。select avg(总评成绩) as 平均总评成绩 , max(总评成绩) as 最高分, min(总评成绩) as 最低分 from 成绩 where 年级=2008 and 课程代码=1304或select avg(总评成绩),
10、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 成绩.年级=2008 and 总评成绩=100 and 课程
11、代码=13045、查找大气科学专业的学生学号、姓名、性别、年级、进校时间。select 学号 ,姓名,性别,年级,进校时间 from 学生 where 专业=(select 专业代码 from 专业 where 专业名称= 大气科学)或select 学号 ,姓名,性别,年级,进校时间 from 学生 inner join 专业 on 学生.专业= 专业.专业代码 where 专业名称= 大气科学6、查询学生学号、姓名、性别及所属班全名。select 学号,姓名,性别,班全名 from 学生 inner join 班 On 学生.专业=班.所属专业 and学生.年级=班.年级 and 学生.班序
12、号=班.班序号7、查询总评成绩在 5060 分之间（不包含 50 和 60）的学生学号、姓名、性别、课程代码、总评成绩，并按学号的升序显示。select 学生 .学号,姓名,性别,课程代码,总评成绩 from 学生 inner join 成绩 On 学生.学号=成绩.学号 where 总评成绩50 and 总评成绩 =8010、将 2008 级同学按照总评成绩的平均分降序输出。select 学号,avg(总评成绩) as 平均分 from 成绩 where 年级=2008 Group by 学号 Order by avg(总评成绩) Desc11、查询 2008 级课程代码为 1304 且平均
13、成绩在 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 学号=2008024763)或select
14、 学号,课程代码,总评成绩 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 课程代码)
15、 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 专业 where 专业名称=电子信息工程)或select max(总评成绩) as 最高分,min( 总评
16、成绩) 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 课
17、程名称=计算机文化基础) 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 门) 以
18、上不及格的学生信息（学号、平均成绩、不及格门数）添加到 badbaby 中(学号,平均成绩,不及格门数)。select 成绩.学号,count(*) as 不及格门数,avg(总评成绩) AS 平均成绩 into badbaby from 成绩 inner join (select 学号 from 成绩 where 成绩.年级=2008 Group by 学号 having avg(总评成绩)=3或select 成绩.学号 ,count(*) as 不及格门数,avg(总评成绩) AS 平均成绩 from 成绩 inner join (select 学号 from 成绩 where 总评成绩=
19、3) AS A on 成绩 .学号=A.学号 where 成绩.年级=2008 Group by 成绩.学号 having avg(总评成绩)=7017 编写程序实现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*nelseset 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=0BEGINset x=x+1set y=y+n end set n=n+1ENDSELECT x,y19、计算 1-9999 以内的所有完全平方数（如 36=6*6，则称 36 为完全平方数）的总和及个数。declare n int,sum int,count intset n=1
