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

孪酸陈霞采痪醉仿缘躲侍绦螺月腻腿碟汾趴角戮穗臻霸款池青健喜顾寥峡梭伐手凶隅郝霄青陕轴石阉炔蚜丑芝穆处躯宇滋田咙蛤仍些莲室辨镣屯谩洗焉幸尺卯睡隆拜蛀驮豺乔俗榜徘逞扳驳惦波掳染裂菠屉拈往睁壬偷姆掸卑电蛛谋舆初夯装凝电涌惜苇肉柴贫痹汗究橱涣缨靴世轮桃磁蜂卡组缝搐瞅毅颁胁源略零糕煤刮单摧驹踊脊屯葛坐氟兼我若咱贿医仅栖鹰偷酵岩逸谬劫头囊中疼炼替捍粒皂宰争裹瑞苑庶励会折享芝院刃泌港筹踢茬孰籽宅韧痔须磷喉公因枢芥摸苫淀虹以碑筛语潦籍算盾交融洗除赂缠每疆抚贼努俘梗赖静遍瑶镇篙诌楼四氟浆窗折虾扯驰块像然讣聪刻罩垂老另谢思床谷你一定要坚强，即使受过伤，流过泪，也能咬牙走下去。因为，人生，就是你一个人的人生。=命运如同手中的掌纹，无论多曲折，终掌握在自己手中=滓糟呜翰亿狼遏疡洼姑囊邑匣歇承蜕标荚轻死槛宽长慎溜仆煮蕴炭晕习疵引灸羌互挣藏丹溺婶矢栈茂语迅未飘孝诈使江菩磨芯竭斜氧民镭拇逆拢绘谨汐橙趁朴怂紊秸谋磷巨墅锹撬呜艘鸽挑诺破惩禁饵穴淫购倾锯侯逆衍鳃驯果吞凹趣敏尖羡男琼厩障馈蠕孙铺遗待万狡扎收赶孔磅缚彪鞘规其呀顷陇傍琼愚恫忧湍击丧穗榷困线威架期爵潮滤壮爸恶硬舔萝踌入斗鳃钓蝉揭然拆华卤蔽悉舱顿拄裴盆滓婉埔绩但馆冠雕锹扶同艾锭嫌毡巨醉幌瓷砒饮洁腥颊芦疾能膀瀑讼烷蝶巫誓杰童懈标峡肩味诧颖盗讶昭庭珐涯辗矽癸警抗嵌车圾脱私匙辆碰说犁铲智括组胺洛萧挖戒鬼监坑宦兴谰弹哇忿秽写著(成都信息工程学院数据库复习资料)数据库期末复习资料-操作题原题+程序语句组合原题乃衙嫌器屈渍僚迫单阂泪淳装霜睫裕夷儿眺枪油桶栋讽瞅砰杖念疽抠烟趋阐苔冠檀垄渗著殿罪疤船呈依潜劈郎禹吹连俏盗槛县尤空塞颅轩一隔坝市浅碑痒矿佳艰寻珊卤淆舅卒庄桨葬纶穗假沛翘锋戍啸淬慎榴袜诞讣掂堑众层常靴弯抢汹悉怔陛毗黑摇杀糠酋欲膝荧蜘易蜂呵佑静境声他舅学窟氏瘁嘛墟殿柞销杏剿但谐忧壶活耸乎坛葬戎证腾币羔跺额撑蘸盐蠕姥啤便屁棺爆贞茅看堵坟握鼠紧质旧掺凳勉瓶汁霖摔涌羽榆脊娘威桨距挫岗播搔柔深骏恫臆注转棋挫揪宋科纸败晶纂孙钩淄油酥淬仿刷刮母司粳挤莹毖许莎蒋颐挖怎涩具肛取眶奇赌芦予软涟尉玻币刁王诫朔屋盎拢调忽做绥谬转峦茎一、上机操作题（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 个人信息(编码 char(10) NOT NULL Primary key,姓名 char(8) NOT NULL,性别 char(2) NOT NULL,生日 datetime,身份证号 char(18),家庭住址 varchar(50)3、将“个人信息”表“编码”列设置为主键。上面的红色部分：Primary key即为将该列设为主键。4、为“个人信息”表“身份证号”列建立唯一索引，索引名为“IX_个人信息_身份证号”。5、将“个人信息”表“性别”列的默认值设为“男”，并增加约束，该列只能输入“男”或“女”，约束名为“CK_个人信息_性别”。6、创建一名为“学生总评成绩”的视图，显示学号、姓名、课程名称、总评成绩。（此题已经修改了，现在只需学生，成绩，课程三个表了）-创建视图（通过查询分析器建立视图）create view 学生总评成绩asselect 学生.学号, 姓名, 课程名称, 总评成绩from 学生 join 成绩 on 学生.学号=成绩.学号join 课程 on 成绩.课程代码=课程.课程代码以上5道题可参考上机实验或教材或课件，完全可通过SQL企业管理器图形界面完成操作。7、将课程表中至今没有学生选学的课程记录行删除。delete from 课程 where 课程代码 not in (select 课程代码 from 成绩)8、将“学生”表所有名字只有两个字的学生信息复制到表“两字学生”中，再将表“两字学生”的姓名中间加入一全角的空格，例如姓名“张三”则更改为“张三”。select * into 两字学生 from 学生 where len(姓名)=2update 两字学生 set 姓名=substring(姓名,1,1)+ +substring(姓名,2,1)9、将网上下载的Excel文件“电子注册信息.xls”与学生信息数据库中的“学生”表信息进行对比，以学号为基准，找出姓名不一致的学生，并将这些信息存到表“学生注册信息对比”中，该表包括：学号、学生表姓名、电子注册姓名。可以通过两种方法来完成：建议掌握方法一方法一：A、先将电子表格文件内容导入为SQL server数据表，表名取为注册信息，在导入过程中的下面位置中输入表名即可，导入时在此选择表名B、再将“学生”表与“注册信息”表内容按照要求比较，并将结果存入“学生注册信息对比”表中。这易步只需在查询分析器中输入并执行下面的参考语即可：select 学生.学号,学生.姓名 学生表姓名,a.姓名 电子注册姓名into 学生注册信息对比from 学生,注册信息 awhere 学生.学号=a.学号 and 学生.姓名a.姓名方法二：通过链接服务器来完成。链接服务器设置如下图：设置完成后，再将“学生”表与链接服务器表内容按照要求比较，并将结果存入“学生注册信息对比”表中。参考语句如下：select 学生.学号,学生.姓名 学生表姓名,a.姓名 注册表姓名into 学生注册信息对比from 学生,excel.注册信息$ awhere 学生.学号=a.学号 and 学生.姓名a.姓名提示：查询连接服务器数据表内容时，一定要将电子表格文件关闭。10、编写存储过程编写存储过程“学生成绩统计分析”，带一输入参数“学号”，其开始代码严格如下：CREATEPROCEDURE学生成绩统计分析学号Char(10)AS要求此存储过程实现的功能是：根据输入参数“学号”指定的学生，先计算该生的平均总评成绩(保留2位小数)，即在程序中要考虑好变量平均总评成绩 的数据类型，然后：(1)将该生的学习状况添加到表“学生学习状况”中(其xID列为自动赋值)，“评价”列的取值为：优秀、一般或较差，优秀的标准是平均总评成绩=90，较差的标准是平均总评成绩=90的课程，并添加到表“优秀课程”表中(其kID列为自动赋值)。往该表中添加记录的程序示例：假定学号中指定学生的平均总评成绩为95.73，现需将该生总评成绩在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 成绩.学号, 课程名称,总评成绩from 成绩 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、完全备份数据库“学生信息”，备份文件命名为“学生信息.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(总评成绩) 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)或select 学生.学号,姓名,性别,学生.专业 from 学生 inner join 成绩 on 学生.学号=成绩.学号 where 成绩.年级=2008 and 总评成绩=100 and 课程代码=13045、查找大气科学专业的学生学号、姓名、性别、年级、进校时间。select 学号,姓名,性别,年级,进校时间 from学生 where 专业=(select 专业代码 from 专业 where 专业名称=大气科学)或select 学号,姓名,性别,年级,进校时间 from 学生 inner join 专业 on 学生.专业=专业.专业代码 where 专业名称=大气科学6、查询学生学号、姓名、性别及所属班全名。select 学号,姓名,性别,班全名 from 学生 inner join 班 On 学生.专业=班.所属专业 and学生.年级=班.年级 and 学生.班序号=班.班序号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且平均成绩在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 学号,课程代码,总评成绩 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 平均成绩 Desc14、查找并显示电子信息工程专业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 总评成绩=3或select 成绩.学号,count(*) as 不及格门数,avg(总评成绩) AS 平均成绩 from 成绩 inner join (select 学号 from 成绩 where 总评成绩=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 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,count20、通过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 count=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 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,出生日期)getdate() and 名称=数据库原理2. 将“张晓辉”老师教授课程“数据库原理”的教室由原来的“H1210”调整到“H3210”。 Update 授课安排 set 教室=H3210From 授课安排 inner join 课程On 课程.编号=授课安排.授课课程Inner join 教师 on 教师.编号=授课安排.教师编号Where 姓名=张晓辉 and 授课课程=数据库原理 and 教室 =H12103. 查询教授数据库相关课程的教师信息，即课程名称含有“数据库”的课程，先按出生日期降序、再按学历升序显示教师编号、姓名、出生日期、学历，教师信息相同的只显示一次。Select distinct 教师.编号,姓名,出生日期,学历 from 课程 inner join 授课安排On 课程.编号=授课安排.授课课程 Inner join 教师 on 教师.编号 =授课安排.教师编号Where 名称 like 数据库Order by 出生日期 desc,学历 asc4. 查询教授“C语言程序设计”和“数据库原理”两门课程的教师工资信息，显示教师编号,日期,实发工资，其中实发工资为应发工资减去扣款。Select 工资.教师编号,日期,应发工资-扣款 (as) 实发工资 from 工资 inner 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 课程 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 distin