数据库综合练习.doc

数据库期末练习练习题一、创建数据库(本题满分10分)：创建名称为“学生课程管理”的数据库，其中包括两个数据文件，一个日志文件。主数据文件属性为：逻辑名student_data1，存储路径为E:student,初始大小为2MB，最大为50MB，文件增长为2MB；次要数据文件属性为：逻辑名student_data2，存储路径为E: student,初始大小1MB，最大20MB，文件增长为30%；日志文件的属性为：逻辑名student_log，存储路径为E: student,初始大小2MB,最大10MB，文件增长为10%。Use masterIf exists (select * from sysdatabases where name=学生课程管理 )Drop database 学生课程管理create database 学生课程管理 On ( name =student_data1, filename =E:studentstudent_data1.mdf, size = 2Mb, maxsize = 50Mb, filegrowth = 2Mb ),( name=student_data2, filename=E:studentstudent_data2.ndf , size = 1Mb, maxsize =20Mb,filegrowth = 30%)Log on( name=student_log, filename =E:studentstudent_log.ldf , size = 2Mb, maxsize = 10Mb, filegrowth = 10% ) 二、创建数据表（本题满分20分）根据如下数据表创建三个数据表，表名分别为：学生表，成绩表，课程表。根据表格中的数据设计个属性列的属性。学生表：学号姓名性别年龄出生日期身份证号专业1刘月女211985/3/3110228198202064926艺术2李小葱女221984/3/10110228198501202620艺术3刘大伟男201986/1/1110101197701090547艺术4李小白女221984/3/10110227198407200921艺术5李导男241982/3/3230828198107053524艺术6李聪聪男201986/1/3110226198404282822艺术Use 学生课程管理If exists(select * from sysobjects where name=学生表)Drop table 学生表Create table 学生表( 学号 int NOT NULL , 姓名 char(6) NOT NULL, 性别 char(2) NOT NULL, 年龄 int, 出生日期 datetime, 身份证号 numeric(18,0), 专业 char(8)成绩表学号课程号课程成绩学期334514489222862115015271232681Use 学生课程管理If exists(select * from sysobjects where name=成绩表)Drop table 成绩表Create table 成绩表( 学号 int NOT NULL , 课程号 int NOT NULL, 课程成绩 float, 学期 char(2) )课程表课程号课程名称课程类别课程内容1个人单曲艺术类LOVING2喜爱的专辑艺术类0.5公里3SQL Server计算机类SQL查询4经典金曲艺术类童年5个人单曲娱乐类冬天快乐6数据结构计算机类二叉树Use 学生课程管理If exists(select * from sysobjects where name=课程表)Drop table 课程表Create table 课程表( 课程号 int NOT NULL , 课程名称 varchar(20) NOT NULL, 课程类别 varchar(20) NOT NULL, 课程内容 varchar(20) )三、添加约束（本题满分20分，共有5道小题，每道小题4分）1. 在学生表中对“学号”列添加主键约束 Alter table 学生表 Add constraint PK_学号 primary key (学号)2. 在学生表中对“专业”列添加默认值约束默认值为“艺术” Alter table 学生表 Add constraint DF_专业 default(艺术) for 专业3. 在学生表中对“年龄”添加检查约束：年龄在15到30之间 Alter table 学生表 Add constraint CK_年龄 check(年龄 between 15 and 30)4. 在学生表中的“身份证号”添加唯一约束 Alter table 学生表 Add constraint UQ_身份证号 unique(身份证号)5. 在成绩表中“学号”列添加外键约束，主表为学生表 Alter table 学生表 Add constraint FK_学号 Foreign key(学号) references 学生表（学号）四、增删改（本题满分18分，共有6道小题，每道小题3分）1. 课程表中插入一个完整的新纪录内容为：8喜爱的演唱会娱乐类李聪聪个人演唱会Insert into 课程表（课程号，课程名称，课程类别，课程内容）Values（8，喜爱的演唱会，娱乐类，李聪聪个人演唱会）2. 修改成绩表学号为3的学生“课程成绩”和“学期”都加1Update 成绩表 set 课程成绩=课程成绩+1，学期=学期+1 where 学号=33. 修改成绩表使表中“学期”为1的“课程成绩”加上学生表中“学号”为1的“年龄”Update 成绩表 Set 课程成绩=课程成绩（select 年龄 from 学生表 where 学号=1）4. 修改成绩表使表中“学号”为3的学生“学期”改为1“课程成绩”改为98Update 成绩表Set 学期=1，课程成绩=98 where 学号=35. 删除成绩表中“学期”为1且“课程成绩”大于95的记录Delete from 成绩表 where 学期=1 and 课程成绩 95 6. 删除课程表中李小白的记录Delete from 成绩表 where 学号=（select 学号 from 学生表 where 姓名=李小白）五、查询（本题满分24分，共有8道小题，每道小题3分）1. 在学生表中，查询“年龄”不在20-22之间的所有学生。Select * from 学生表 where 年龄 not between 20 and 222. 在课程表中，查询“课程号”为1，3和4的课程信息。Select * from 课程表 where 课程号 in（1,3,4）3. 在学生表中，查询姓“李”的学生信息Select * from 学生表 where 姓名 like李%4. 在成绩表中，按照学生的“课程成绩”升序显示，然后按照“学期”降序排序。Select * from 成绩表 Order by 课程成绩 asc ,学期 desc 5. 显示学生表中的年龄最大的一名学生的信息。Select top1 * from 学生表Order by 年龄 desc 6. 在学生表中，分别求男女生的平均年龄。Select * from 学生表 Compute avg(年龄)7. 在学生表中按“性别”分组求平均年龄，并且查询平均年龄大于21的学生信息。Select * from 学生表Order by 性别Compute avg(年龄) by 性别8. 在课程表中，查询“课程类别”是“艺术类”的结果保存到newtable表中，并查看newtable表的信息。 Create view newtable as Select * from 课程表 where 课程类别=艺术类Select * from newtable六、程序设计（本题满分8分）则根据如下规则对成绩表中的“成绩”进行反复加分，直到平均分超过85分为止。请编写T-SQL语句实现并显示修改之前和修改之后的成绩表情况。90分以上： 不加分8089分： 加1分7079分： 加2分6069分： 加3分60分以下： 加5分select * from 成绩表declare 平均成绩 intwhile(1=1) begin update 成绩表 set 平均成绩= case when 平均成绩=85 break endselect * from 成绩表 一、创建数据库(本题满分10分)：创建名称为“图书馆”的数据库，其中包括两个数据文件，一个日志文件。主数据文件属性为：逻辑名library_data1，存储路径为D:library,初始大小为3MB，最大为10MB，文件增长为1MB；次要数据文件属性为：逻辑名library_data2，存储路径为D:library,初始大小2MB，最大5MB，文件增长为10%；日志文件的属性为：逻辑名library_log，存储路径为D:library,初始大小1MB,最大3MB，文件增长为5%。Use masterIf exists (select * from sysdatabases where name=图书馆)Drop database 图书馆create database 图书馆 On ( name =library_data1, filename =D:librarylibrary_data1.mdf, size = 3Mb, maxsize = 10Mb, filegrowth = 1Mb ),( name=library_data2, filename=D:librarylibrary_data2.ndf , size = 2Mb, maxsize = 5Mb,filegrowth = 10%)Log on( name=library_log, filename =D:librarylibrary_log.ldf , size = 1Mb, maxsize = 3Mb, filegrowth = 5% )二、创建数据表（本题满分20分）根据如下数据表创建三个数据表，表名分别为：图书表，馆藏状态表，阅览室表。根据表格中的数据设计个属性列的属性。图书表：图书编号图书名出版社作者出版时间图书数量P001计算机基础教程高等教育出版社王移芝等2007-8500P002数据库实训教程清华大学出版社李丹丹等2011-5300P003数据库系统工程师考试全程指导清华大学出版社丁宝康等2006-4166P004数据库系统工程师应试辅导人民邮电出版社李强等2006-150P005C程序设计清华大学出版社谭浩强等2007-6800Use 图书馆If exists(select * from sysobjects where name=图书表)Drop table 图书表Create table 图书表( 图书编号 char(4) NOT NULL , 图书名 varchar(30) NOT NULL, 出版社 varchar(30) NOT NULL, 作者 char(10), 出版时间 datetime,图书数量 int)馆藏状态表图书编号阅览室编号存书量P001Y001200P002Y001300P001Y002300P003Y00296P003Y00370Use 图书馆If exists(select * from sysobjects where name=馆藏状态表)Drop table 馆藏状态表Create table 馆藏状态表( 图书编号 varchar(20) NOT NULL , 阅览室编号 varchar(20) NOT NULL, 存书量 int)阅览室表阅览室编号阅览室位置阅览室容量现存图书数量Y001图书馆楼A102100007852Y002图书馆楼A103100008100Y003图书馆楼A2012000015000Y003图书馆楼A2023000016980Y004图书馆楼A203300001900Use 图书馆If exists(select * from sysobjects where name=阅览室表)Drop table 阅览室表Create table 阅览室表( 阅览室编号 varchar(20) NOT NULL, 阅览室位置 varchar(30), 阅览室容量 int, 现存图书数量 int)三、添加约束（本题满分20分，共有5道小题，每道小题4分）1.在图书表中添加主键约束，“图书编号”列设置为主键Alter table 图书表 Add constraint PK_图书编号 primary key (图书编号)2.在图书表中添加默认约束，“出版社”列默认值为 清华大学出版社 Alter table 图书表 Add constraint DF_出版社 default(清华大学出版社) for 出版社3.阅览室表中，“阅览室编号”列添加唯一约束 Alter table 阅览室表 Add constraint UQ_阅览室编号 unique(阅览室编号)4.在阅览室表中添加检查约束，“阅览室容量”必须大于5000 Alter table 阅览室表 Add constraint CK_阅览室容量 check(阅览室容量5000)5.在馆藏状态表添加外键约束，“图书编号”列做外键进行连接 Alter table 图书表 Add constraint FK_图书编号 Foreign key(图书编号) references 馆藏状态表（图书编号）四、增删改（本题满分18分，共有6道小题，每道小题3分）7. 在图书表中添加数据：“图书编号”，“图书名”，“出版社”列内容分别为：P005，C程序设计，清华大学出版社。Insert into 图书表（图书编号，图书名，出版社）Values（P005，C程序设计，清华大学出版社）8. 在馆藏状态表中添加完整数据：P001，Y002，300。9. 修改阅览室表信息，将所有“阅览室容量”扩大为原来的2倍 10. 修改馆藏状态表，将P003号图书的存书量增加100本11. 删除阅览室表中Y003的阅