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

《成都信息工程学院数据库复习资料数据库期末复习资料操作题原题程序语句组合原题》由会员分享，可在线阅读，更多相关《成都信息工程学院数据库复习资料数据库期末复习资料操作题原题程序语句组合原题（13页珍藏版）》请在装配图网上搜索。

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

2、生信息”。2、在数据库“学生信息”中，新建一名为“个人信息”表，表结构如下：个人信息（编码char（10） NOT NULL姓名char(8) NOT NULL性另 ijchar(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）+ +substring（姓名，2

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

6、姓名into学生注册信息对比from学生,注册信息awhere 学生.学号=a.学号 and学生.姓名a.姓名方法二：通过链接服务器来完成。链接服务器设置如下图：设置完成后，再将“学生”表与链接服务器表内容按照要求比较，并将结果存入“学生注册信息对比”表中。参考语句如下：select学生.学号，学生姓名 学生表姓名a姓名 注册表姓名into学生注册信息对比from 学生，excel.注册信息$ awhere 学生.学号=a.学号 and学生.姓名a.姓名提示：查询连接服务器数据表内容时，一定要将电子表格文件关闭。10、编写存储过程编写存储过程“学生成绩统计分析”，带一输入参数“ 学号”，其开始

7、代码严格如下：CREATE PROCEDURE 学生成绩统计分析 学号 Char（10）AS要求此存储过程实现的功能是：根据输入参数“学号”指定的学生，先计算该生的平均总评成绩（保留2位小数），即在程序中要考虑好变量 平均总评成绩的数据类型，然后：（1）将该生的学习状况添加到表“学生学习状况”中（其xID列为自动赋值），“评价”列的取值为：优秀、一般或较差，优秀的标准是平均总评成绩=90，较差的标准是平均总评成绩=90 的课程，并添加到表“优 秀课程”表中 (其 kID 列为自动赋值 )。往该表中添加记录的程序示例：假定学号中指定学生的平均总评成绩为95.73,现需将该生总评成绩在 7080间

8、的课程添加到该表中INSERT INTO 优秀课程 ( 学号 , 课程名称 , 总评成绩 )SELECT C. 学号 , K. 课程名称 , C. 总评成绩FROM 成绩 C, 课程 KWHERE C. 学号 = 学号 and C. 总评成绩 between 70 and 80 and K. 课程代码 =C. 课 程代码(3) 若平均总评成绩在 60 分以下, 则查询出该学生总评成绩 = 90 then 优秀 when convert(decimal(5,2), avg(convert(decimal(5,2), 总评成绩 ) = 90)begininsert into 优秀课程 (学号 ,课

9、程名称 ,总评成绩 )select 成绩.学号 , 课程名称 ,总评成绩from 成绩 join 课程 on 成绩.课程代码 =课程 .课程代码where成绩学号 =学号 and总评成绩=90endif (select convert(decimal(5,2), avg(convert(decimal(5,2),总评成绩)from 成绩 where 学号=学号) 60)begininsert into不及格课程(学号，课程名称，总评成绩)select成绩.学号，课程名称，总评成绩from成绩join 课程on成绩.课程代码=课程課程代码where成绩学号 =学号 and总评成绩60endexe

10、c11、完全备份数据库“学生信息”，备份文件命名为“学生信息.bak”，将文件“学生信息.bak”上传到指定的服务器上下面是2014年1月4日星期六具体考试的页面：当nt悅恥Mia* 世虫垃1锂皐工g逖理段用用-辄胡末考试RfFi IPLH!目中旳岀曲|扫茅朮，死战相三旺鷲作*桔丸骑对拿生在总1! 耳巾上停阳作li立:件ifti亍目砂评卄一一曲一卜H怔18立徉卜忏5U疇钟才溥左件的耳卄拈卑，若吹启n廿 幹卜y 劭丄悴下日名丸件！*匸：时的兗件新+境评好畀目良超甕至J：号百，井轻.i W *. i5iS 创知扌吕H目55速1单击晝处TimSflt备龄立件注TU.严还压刮宅生牙用的和丘电期匿莘華力

11、-与2冇弊牖圧“宇士梢堰”中，辆理一霑舟个血信息”第抽怖0下1 平人僧恿1事戸电逐如皿NQTROL宅bar#)KPTHULLtf划chUDlTKILL空日dateTlie鼻爭证咅略baL；】l ll 址 TKrrhCM)：!3-柠竹W“炳“*吗”対绘IS力主at4.力 t-XM.衰辑AiiW 肌3T.E-It制祇引岂力4%卒人13盘*lit吕” 耳朽 FJt歓#5 “性別秤幽迪认Ml妙严斟11 .斗旧谕丸 注刃展妙-PT克P確包向吒SJdBJ*hW M .si理-宕刃*欤生总评阳*的砸，尺宦至生的学号.空生的陀吕，単号.st禹7馬厦毘炭屮至令浜肖卒主也詢.1*駁记录厅.3-H “鼻生” SFt

12、ffBJfMMSJK 14円罕工主-q:.西佰去“Pf科生的！3呂申航人一全肃麻播r嗚如管名亠賬三 SHFM*0wifctti.冲islt娶求至厅i軻匕舉xcL文件电王主册直鼻辺严与?蛙朮肿墟宦中的“学生畫陆鼠曲询出L：悖：舶星庇 拗證占不一边 的賈L昇将空兰信M7J怡九学生迪划也鼻对片缭中，谨农倒！i三列.酬舍田沏：宇十生事姓耳14即存伸il揑严欤wctj轴计吩押,阳吓：讣E惟FPKreWF宇左晋fl!花计勺话砰号 CkMTl：Ltl)哪卿的删L IIW擋扎罚斗禅暑“拒宝即憚注”先计贏轴(肝期鲜)m我林儿剋Ui用文生时事川细止制列斷亠学主学唤丹f車=凹西旨朝rtr“唧tmi商：亠帕r ,-A

13、,i 咸“技护忱护的U准3：平1堪坏朋:呦“技4“掃道圧円處送戍杯阴H钟1恋曲TT苗酋弼姻.评加左酬艮上.！上右|11出i=r科且评删 应的坨唯.血索忧兇侧”衰童車灼曲由自理 临事若平世評趨在昭臥眄血昨E忌押瞬価的iffi.，昇通站朋殛皆ST聃中诅口蚀口动 KA4II舄主酗怡舟叵“平生13象r肴m立井击呂対化?注国事网护,爵立件”手生E!駁呼用巧n血於3商“社怡 丁,上气到駐梱员岂上.Cd.HcS bft郭.tBr1二、程序填空题（3个SQL填空+3个程序设计，每题两空，两空全对才能得分，共 30分）：1、查询 2008 级学生中的留级（或复读）学生信息select * from 学生 whe

14、re left（ 学号,4） 年级 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（总评成绩）,max（总评成绩）,min（总评成绩）from 成绩 where年级=2008 and课程 代码 =

15、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 课程代码 =13045、查找大气科学专业的学生学号、姓名、性别、年级、进校时间。select

16、学号，姓名，性别，年级，进校时间 from 学生 where专业=（select专业代码 from 专业 where 专业名称 =大气科学 ）或select 学号 ,姓名 ,性别 ,年级,进校时间 from 学生 inner join 专业 on 学生.专业=专业.专业 代码 where 专业名称 =大气科学6、查询学生学号、姓名、性别及所属班全名。select 学号 ,姓名,性别,班全名 from 学生 inner join 班 On 学生.专业=班.所属专业 and 学生 .年级 =班.年级 and 学生 .班序号 =班.班序号7、查询总评成绩在 5060 分之间（不包含 50和 60）的

17、学生学号、姓名、性别、课程代码、总评成绩，并 按学号的升序显示。select 学生 .学号,姓名 ,性别 ,课程代码 ,总评成绩 from 学生 inner join 成绩 On 学生.学号= 成绩 .学号 where 总评成绩 50 and 总评成绩 60 Order by 学生 .学号 ASC8、查询学号为 的学生姓名、课程名称、总评成绩。select 姓名 ,课程名称 ,总评成绩 from 学生 inner join 成绩 on 学生 .学号=成绩.学号inner join 课程 on 成绩.课程代码 =课程.课程代码 where 学生.学号=9、查询 2008 级平均总评成绩为 80

18、分以上（含 80 分）的专业代码。select 专业 as 专业代码 from 成绩 group by 专业 having avg（ 总评成绩 ）=8010、将 2008 级同学按照总评成绩的平均分降序输出。select学号，avg（总评成绩）as平均分 from 成绩 where年级=2008 Group by学号 Order by avg（总评成绩）Desc11、查询 2008 级课程代码为 1304 且平均成绩在 80 分以上（含 80 分）的专业代码及平均成绩。select专业 as专业代码,avg（总评成绩）as平均成绩 from 成绩 where年级=2008 and课 程代码 =

19、1304 group by 专业 having avg（ 总评成绩 ）=8012、查询学号为的学生的总评成绩比号学生的最高分数还要高的课程代码和总评成绩。select 学号 ,课程代码 ,总评成绩 from 成绩 where 学号= and 总评成绩 All （select 总评成 绩 from 成绩 where 学号 =）或select学号，课程代码，总评成绩from 成绩 where学号=and总评成绩 （select max（总评成 绩） from 成绩 where 学号=）13、 查询 2008 级学生中各门课程的平均总评成绩情况（课程代码、课程名称、平均成绩、选课人数），并 按照平均

20、总评成绩降序排列输出。select 课程 .课程代码 ，课程名称 ，平均成绩 ， 选课人数 from 课程 inner join （select 课程代 码,avg（总评成绩）as平均成绩,count（*） as选课人数from 成绩 where年级=2008 Group by 课程代码）AS A on课程.课程代码=A.课程代码 Order by平均成绩 Desc14、查找并显示电子信息工程专业 2008 级同学计算机文化基础课程的最高分、最低分、平均分。select max（总评成绩）as最高分，min（总评成绩）as最低分，avg（总评成绩）as平均分from 成 绩 where 年级

21、=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（总

22、评成绩）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（

23、总评成绩）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 Grou

24、p by 学号 having avg（总 评成绩 ）=70） AS A on 成绩 . 学号 =A. 学号 where 总评成绩 60 and 年级 =2008 Group by 成 绩.学号 having count（*）=3或select成绩学号,coun t（*） as不及格门数,avg （总评成绩）AS平均成绩 from 成绩inn er join （select 学号 from 成绩 where 总评成绩 60 and 年级 =2008 Group by 成绩 . 学号 having count（*）=3） AS A on 成绩学号=A.学号 where 成绩.年级=2008 Gro

25、up by 成绩.学号 having avg（总评成绩）=7017编写程序实现1 , 40的奇数平方和赋x,偶数平方和赋y，并输出x, y的值。DECLARE n int,x int,y intSET x=0SET y=0SET n=1WHILE n=40BEGINif n % 2=1set x=x+n*nelseset y=y+n*nset n=n+1ENDSELECT x,y18、编写程序计算 1-5000之间（含1和5000）所有能被 9整除的数的个数及总和。DECLARE n int,x int,y intSET x=0SET y=0SET n=1WHILE n=5000BEGINif

26、 n % 9=0BEGINset x=x+1set y=y+nendset 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=9999beginset sum=sum+n*nset count=count+1set n=n+1Endselect sum,count20、通过 while 循环计算 10！。DECLARE s int,n intSET S=1SET n

27、=1WHILE n=10BEGINSET s=s*nSET n=n+1ENDSELECT s,n21、完成程序依次显示字母 AZ 。declare count int,abc varchar（100）set count=0while count26beginset abc=char（ascii（A）+count）print abcset count=count+1end22、完成程序依次显示字母 ZA 。declare count int,abc varchar（100）set count=0while count26beginset abc= char（ascii（Z）-count）prin

28、t abcset count=count+1end23、查询2008级所有同学的学号、总评成绩的平均分、 等级（90分以上A , 80-90为B, 70-80为C, 60-70 为D，60分以下为E）,并按照等级降序输出（即先输出A级学生，再输出B级学生，最后输出E级学生）。select学号，avg（总评成绩）,（case when avg（总评成绩）=90 then Awhen avg（总评成绩）=80 and avg（总评成绩）90 then Bwhen avg（总评成绩）=70 and avg（总评成绩）80 then Cwhen avg（总评成绩）=60 and avg（总评成绩）70

29、 then Dwhen avg（总评成绩）60 then E end）as 等级from 成绩 WHERE 年级=2008 group by 学号24、 显示今天是第几季度，输出格式为“第一季度”、 “第二季度” 、 “第三季度” 、 “第四季度” 。declare 季度 tinyintset 季度 = datename（q，getdate（）selectcase 季度when 1 then 第一季度when 2 then 第二季度when 3 then 第三季度when 4 then 第四季度End或selectcase date name（q,getdate（）when 1 then 第一

30、季度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题的知识点分布在分组统计、动作查询中使用子查询等）参考数据表结构：教师（编号，姓名，出生日期，学历）课程（编号，名称，学分）工资（教师编

31、号，日期,应发工资,扣款）授课安排（授课ID,教师编号，授课课程，教室）,【授课ID】是自动增长字段，【教师编号】 是参照【教师编号】的外键，【授课课程】是参照【课程 编号】的外键。带有下划线字段为表的主关键字1. 查询年龄在35岁以下教授 数据库原理”的教师有几位。Select count（*） as 人数 from 课程 inner join 授课安排on课程编号=授课安排授课课程innerjoin 教师on教师编号=授课安排.教师编号where dateadd（yy,35,出生日期）getdate（） and 名称=/ 数据库原理/2. 将张晓辉”老师教授课程 数据库原理”的教室由原来的

32、 “ H1210调整到“ H3210。Update授课安排 set教室=H3210From授课安排inner join课程On课程.编号=授课安排.授课课程Inner join 教师 on 教师 .编号 =授课安排 .教师编号Where姓名=张晓辉and授课课程-数据库原理/and 教室 = H1210 3. 查询教授数据库相关课程的教师信息，即课程名称含有 “数据库 ”的课程，先按出生日 期降序、再按学历升序显示教师编号、姓名、出生日期、学历，教师信息相同的只显 示一次。Select distinct 教师.编号,姓名,出生日期 ,学历 from 课程 inner join 授课安排On 课

33、程.编号=授课安排 .授课课程Inner join 教师 on 教师 .编号 =授课安排 .教师编号Where 名称 like 数据库Order by出生日期 desc学历asc4. 查询教授“C语言程序设计”和 数据库原理”两门课程的教师工资信息，显示教师编号 日期 ,实发工资，其中实发工资为应发工资减去扣款。Select 工资 .教师编号 ,日期 ,应发工资 -扣款 （as） 实发工资 from 工资inner join 授课安排 on 工资.教师编号 = 授课安排 .教师编号inner join 课程 on 课程.编号 = 授课安排 .授课课程where名称in （，C语言程序设计/ ,

34、数据库原理/ ）5. 为张晓辉”老师安排编码为“ C105课程的授课教室在“ H1104” （名为张晓辉”的教师只有一位 ）Insert into 授课安排 select 编号 , C105 , H1104from 教师 where 姓名 =张晓辉6. 查询每门课程的最小授课ID号，显示课程的编号、名称和最小授课ID。（课程名称可能有重复）Select课程.编号,max（课程名称）名称,min（授课ID）最小授课IDFrom 课程 inner join 授课安排 on 课程.编号=授课安排 .授课课程Group by 课程 .编号7. 为讲授课程编号为“ C105的教师制作本月工资表，【日期】

35、取当前时间，【应发工资】和【扣款】取 2011-01-01对应“工资”表数据。Insert into工资 select工资教师编号，getdate（）,应发工资，扣款From 工资 inner join 授课安排On 工资 教师编号 = 授课安排 教师编号Where 授课课程 =C105and 日期 = 2011-01-01 8. 删除教授课程编号为“ 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 学生及成绩 $