《(成都信息工程学院数据库复习资料)数据库期末复习资料-操作题原题+程序语句组合原题.doc》由会员分享，可在线阅读，更多相关《(成都信息工程学院数据库复习资料)数据库期末复习资料-操作题原题+程序语句组合原题.doc（13页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
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)+ +substr
5、ing(姓名,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.姓名提示：查询连接服务器数据表内容时，一定要将电子表格文件关闭。10、编写存储过程编写存储过程“学
7、生成绩统计分析”，带一输入参数“学号”，其开始代码严格如下：CREATEPROCEDURE学生成绩统计分析学号Char(10)AS要求此存储过程实现的功能是：根据输入参数“学号”指定的学生，先计算该生的平均总评成绩(保留2位小数)，即在程序中要考虑好变量平均总评成绩 的数据类型，然后：(1)将该生的学习状况添加到表“学生学习状况”中(其xID列为自动赋值)，“评价”列的取值为：优秀、一般或较差，优秀的标准是平均总评成绩=90，较差的标准是平均总评成绩=90的课程，并添加到表“优秀课程”表中(其kID列为自动赋值)。往该表中添加记录的程序示例：假定学号中指定学生的平均总评成绩为95.73，现需将
8、该生总评成绩在7080间的课程添加到该表中INSERTINTO优秀课程(学号,课程名称,总评成绩)SELECTC.学号,K.课程名称,C.总评成绩FROM成绩C,课程KWHEREC.学号=学号andC.总评成绩between70and80andK.课程代码=C.课程代码(3)若平均总评成绩在60分以下，则查询出该学生总评成绩= 90 then 优秀when convert(decimal(5,2), avg(convert(decimal(5,2), 总评成绩) = 90)begin insert into 优秀课程(学号,课程名称,总评成绩)select 成绩.学号, 课程名称,总评成绩fr
9、om 成绩 join 课程 on 成绩.课程代码=课程.课程代码where 成绩.学号 = 学号 and 总评成绩=90end if (select convert(decimal(5,2), avg(convert(decimal(5,2), 总评成绩) from 成绩 where 学号=学号) 60)begin insert into 不及格课程(学号,课程名称,总评成绩)select 成绩.学号, 课程名称,总评成绩from 成绩 join 课程 on 成绩.课程代码=课程.课程代码where 成绩.学号 = 学号 and 总评成绩60end exec11、完全备份数据库“学生信息”，备
10、份文件命名为“学生信息.bak”，将文件 “学生信息.bak”上传到指定的服务器上下面是2014年1月4日星期六具体考试的页面：二、程序填空题（3个SQL填空+3个程序设计，每题两空，两空全对才能得分，共30分）：1、查询2008级学生中的留级（或复读）学生信息select * from 学生 where left(学号,4) 年级 and 年级=20082、查询龙泉和航空港校区的班级个数。select 所在地点,count(*) AS 班数 from 班 Group by 所在地点3、查询2008级学生课程代码为1304的平均总评成绩、总评成绩的最高分和最低分。select avg(总评成绩
11、) as 平均总评成绩, max(总评成绩) as 最高分, min(总评成绩) as 最低分 from 成绩 where 年级=2008 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)或sel
12、ect 学生.学号,姓名,性别,学生.专业 from 学生 inner join 成绩 on 学生.学号=成绩.学号 where 成绩.年级=2008 and 总评成绩=100 and 课程代码=13045、查找大气科学专业的学生学号、姓名、性别、年级、进校时间。select 学号,姓名,性别,年级,进校时间 from学生 where 专业=(select 专业代码 from 专业 where 专业名称=大气科学)或select 学号,姓名,性别,年级,进校时间 from 学生 inner join 专业 on 学生.专业=专业.专业代码 where 专业名称=大气科学6、查询学生学号、姓名、
13、性别及所属班全名。select 学号,姓名,性别,班全名 from 学生 inner join 班 On 学生.专业=班.所属专业 and学生.年级=班.年级 and 学生.班序号=班.班序号7、查询总评成绩在5060分之间（不包含50和60）的学生学号、姓名、性别、课程代码、总评成绩，并按学号的升序显示。select 学生.学号,姓名,性别,课程代码,总评成绩 from 学生 inner join 成绩 On 学生.学号=成绩.学号where 总评成绩50 and 总评成绩 =8010、将2008级同学按照总评成绩的平均分降序输出。select 学号,avg(总评成绩) as 平均分 fro
14、m 成绩 where 年级=2008 Group by 学号 Order by avg(总评成绩) Desc11、查询2008级课程代码为1304且平均成绩在80分以上（含80分）的专业代码及平均成绩。select 专业 as 专业代码,avg(总评成绩) as 平均成绩 from 成绩 where 年级=2008 and 课程代码=1304 group by 专业 having avg(总评成绩)=8012、查询学号为的学生的总评成绩比号学生的最高分数还要高的课程代码和总评成绩。select 学号,课程代码,总评成绩 from 成绩 where 学号= and 总评成绩All (select
15、 总评成绩 from 成绩 where 学号=)或select 学号,课程代码,总评成绩 from 成绩 where 学号= and 总评成绩 (select max(总评成绩) from 成绩 where 学号=)13、查询2008级学生中各门课程的平均总评成绩情况（课程代码、课程名称、平均成绩、选课人数），并按照平均总评成绩降序排列输出。select 课程.课程代码,课程名称,平均成绩, 选课人数from 课程 inner join (select 课程代码,avg(总评成绩) as 平均成绩,count(*) as 选课人数from 成绩 where 年级=2008 Group by 课
16、程代码) 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(总评成绩)
17、 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 课程名称=计算机文
18、化基础) 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门)以上不及格的学生信息（学号、平均成绩、不及格门数）
19、添加到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 总评成绩=3或select 成绩.学号,count(*) as 不及格门数,avg(总评成绩) AS 平均成绩 from 成绩 inner join (select 学号 from 成绩 wher
20、e 总评成绩=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*n else set y=y+n*n set n=n+1ENDSELECT x,y18、编写程序计算1-5000之间(含1和5000)所有能被9整除的数的个数及总和。DECLARE n int,x
21、int,y intSET x=0SET y=0SET n=1WHILE n=5000BEGIN if n % 9=0 BEGIN set x=x+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,coun
22、t20、通过while循环计算10！。DECLARE s int,n intSET S=1SET n=1WHILE n=10 BEGIN SET s=s*n SET n=n+1 ENDSELECT s,n21、完成程序依次显示字母AZ。declare count int,abc varchar(100)set count=0while count26begin set abc=char(ascii(A)+count) print abc set count=count+1end 22、完成程序依次显示字母ZA。declare count int,abc varchar(100)set coun
23、t=0while count=90 then A when avg(总评成绩)=80 and avg(总评成绩)=70 and avg(总评成绩)=60 and avg(总评成绩)70 then Dwhen avg(总评成绩)60 then E end) as 等级from 成绩 WHERE 年级=2008 group by 学号24、显示今天是第几季度，输出格式为“第一季度”、“第二季度”、“第三季度”、“第四季度”。declare 季度 tinyintset 季度= datename(q,getdate()select case 季度 when 1 then 第一季度 when 2 the
24、n 第二季度 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表）查询
25、中使用表达式、函数、多条件、排序等设计；后4题的知识点分布在分组统计、动作查询中使用子查询等）参考数据表结构：教师(编号,姓名,出生日期,学历)课程(编号,名称,学分)工资(教师编号,日期,应发工资,扣款)授课安排(授课ID,教师编号,授课课程,教室),【授课ID】是自动增长字段，【教师编号】是参照【教师. 编号】的外键，【授课课程】是参照【课程.编号】的外键。带有下划线字段为表的主关键字1. 查询年龄在35岁以下教授“数据库原理”的教师有几位。Select count(*) as 人数 from 课程 inner join 授课安排 on 课程.编号=授课安排.授课课程inner join
26、教师on 教师.编号=授课安排.教师编号where dateadd(yy,35,出生日期)getdate() and 名称=数据库原理2. 将“张晓辉”老师教授课程“数据库原理”的教室由原来的“H1210”调整到“H3210”。 Update 授课安排 set 教室=H3210From 授课安排 inner join 课程On 课程.编号=授课安排.授课课程Inner join 教师 on 教师.编号=授课安排.教师编号Where 姓名=张晓辉 and 授课课程=数据库原理 and 教室 =H12103. 查询教授数据库相关课程的教师信息，即课程名称含有“数据库”的课程，先按出生日期降序、再按
27、学历升序显示教师编号、姓名、出生日期、学历，教师信息相同的只显示一次。Select distinct 教师.编号,姓名,出生日期,学历 from 课程 inner join 授课安排On 课程.编号=授课安排.授课课程 Inner join 教师 on 教师.编号 =授课安排.教师编号Where 名称 like 数据库Order by 出生日期 desc,学历 asc4. 查询教授“C语言程序设计”和“数据库原理”两门课程的教师工资信息，显示教师编号,日期,实发工资，其中实发工资为应发工资减去扣款。Select 工资.教师编号,日期,应发工资-扣款 (as) 实发工资 from 工资 inne
28、r join 授课安排 on 工资.教师编号 = 授课安排.教师编号inner join 课程 on 课程.编号 = 授课安排.授课课程where 名称 in (语言程序设计，数据库原理)5. 为“张晓辉”老师安排编码为“C105”课程的授课教室在“H1104”。(名为“张晓辉”的教师只有一位)Insert into 授课安排 select 编号,C105,H1104 from 教师where 姓名=张晓辉6. 查询每门课程的最小授课ID号，显示课程的编号、名称和最小授课ID。（课程名称可能有重复）Select 课程.编号,max(课程.名称) 名称, min(授课ID) 最小授课IDFrom
29、 课程 inner join 授课安排 on 课程.编号=授课安排.授课课程Group by 课程.编号7. 为讲授课程编号为“C105”的教师制作本月工资表，【日期】取当前时间，【应发工资】和【扣款】取2011-01-01对应“工资”表数据。Insert into 工资 select 工资.教师编号,getdate(),应发工资,扣款 From 工资 inner join 授课安排On 工资.教师编号 = 授课安排.教师编号Where 授课课程=C105and 日期=2011-01-018. 删除教授课程编号为“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 学生及成绩$