数据库原理及应用期末考试 (2).doc

综合题一、1使用TransactSQL语言创建教学管理数据库，创建两个表学生信息（学号，姓名，性别，籍贯，班级编号）和成绩表（学号，课程编号，成绩，是否重修）。其中，学生信息表中学号为主键，其他信息都不允许为空。通过学号与成绩表形成一个一对多的关系，成绩表中的成绩的默认值为0，但必须在0100之间。注：将学号设置为主键，主键名为pk_xh；为成绩添加默认值约束，约束名为df_xb,其值为“0”；为成绩添加check约束，约束名为诚恳ck_xb, 在0100之间。CREATEDATABASE教学管理use教学管理go createtable学生信息（ 学号varchar(20)NOTNULLPRIMARYKEY,姓名varchar(20)NOTNULL,性别varchar(10)NOTNULL,籍贯varchar(20)NOTNULL,班级编号varchar(20)NOTNULL)go createtable成绩表( 学号varchar(20)NOTNULLFOREIGNKEYREFERENCES学生信息（学号）, 课程编号varchar(20)NOTNULL, 成绩numeric(5,2)DEFAULT(0)CHECK(成绩)=0and成绩=85THEN优秀WHEN成绩=75THEN良好WHEN成绩=60THEN及格WHEN成绩(SELECTAVG(成绩)FROM学期成绩JOIN课程注册ON学期成绩.课程代码=课程注册.课程代码WHERE课程名称=软件工程)(3)查询男同学选修的课程号。(4)查询至少选修课程号为“C01”和“C02”的学生学号。将所有学生的“01”课程的成绩增加10分。3在“学生信息”数据库中创建一个带参数的存储过程st_xsxx。该存储过程的作用是：当输入某门课程的课程名称时，检索该门课程成绩，并将查询结果以“学号” ，“姓名” ，“课程名称” ，“成绩等级”输出（成绩划分成四个等级：高于或等于85分者为优秀，高于或等于75分者为良好，高于或等于60分者为及格，低于60分者为不及格）。CREATEPROCEDUREst_xsxx（1分）KCMCCHAR(20)（1分）ASSELECT学生.学号,姓名,课程名称,成绩等级=CASEWHEN成绩=85THEN优秀WHEN成绩=75THEN良好WHEN成绩=60THEN及格WHEN成绩=90)7修改教师表结构：教师表的职称列上，规定取值为（教授，副教授，讲师， 助教）之一。createrulezhicheng_ruleaszhichengin(教授,副教授,讲师,助教)gosp_bindrulezhicheng_rule,教师表.职称8计算某教师代课总课时，并将值返回的存储过程以及执行代码。执行：计算“郭老师”的总课时。createprocedure统计课时教师名nchar(16),asbegindeclare总课时intselect总课时=sum(课时)from任课表视图where姓名=教师名endgoexecute统计课时郭老师五、现在要为学校建立一个数据库，设学校的环境如下：一个系有若干个专业，每个专业一年只招一个班，每个班有若干个学生，现要建立关于系、学生、班级的数据库，关系模式为：学生表student（学号，姓名，性别，班级，生源地，高考成绩，出生日期）班级表class（班级名，专业名，系号，入学年份）系表 department（系号，系名）使用SQL语句完成一下功能：1. 建立数据库名为school并激活当前新建的数据库，要求主数据文件的逻辑文件名为school，实际文件名为school.mdf，事务日志文件的逻辑文件名为school,，实际文件名分别为school.ldf，上述文件的初始容量均为5MB，最大容量均为20MB，递增量均为1MB。createdatabaseschoolonprimary(name=school,filename=SCHOOL.mdf,SIZE=5MB,MAXSIZE=20MB,filegrowth=1MB)logon(name=school_log,filename= SCHOOL_log.ldf,SIZE=2MB,maxsize=20MB,filegrowth=10%)go2建立表student（学号，姓名，性别，班级，生源地，高考成绩，出生日期）要求：（1）表要有主键（2）学生的性别只能取男或者女 （3）学生姓名不能为空useSCHOOLcreatetablestudent-学生表定义(学号char(10)primarykey,-主键为学号姓名varchar(20)notnull,-姓名不能为空值性别char(4)default男,-性别默认为男性生源地varchar(12),-生源地出生日期datetime,check(性别=男)or(性别=女) -性别必须为男性或女性)3.在student表中插入一条记录：（学号：8101，姓名:张三，性别：男，生源地：山东）insertintostudentvalues(8101,张三,男,山东)4.根据student表完成以下的查询功能（1）通过对学生信息表的查询，查询出所有学生的学号，姓名和性别。select学号,姓名,性别fromstudent（2）通过对学生信息表的查询，查询来自福建的学生的记录。select*fromstudentwhere生源地=福建（3）在查询语句中使用聚合函数计算出所有学生总数（起别名为学生人数）和平均高考成绩（起别名为平均成绩）。selectcount(*)as学生人数fromstudent（4）更新学号为8103的这位学生的姓名为张玲。updatestudentset姓名=张玲where学号=8103（5）查询高考成绩最高的前5条学生记录。Selecttop5*fromstudentorderby高考成绩desc（6）在学生表中统计班级列表及各班级的人数。Select班级,count(*)fromstudentgroupby班级（7）查询高考成绩在90到100之间的学生的学号、姓名、专业名。Select学号,姓名,专业名from学生表,班级表where学生表.班级=班级表.班级名and高考成绩between90and100（8）删除姓名为李飞的学生记录；删除数据表student。deletefromstudentwhere姓名=李飞六、已知逻辑结构见下表，写出各题的T-SQL语句。表readers（读者信息表）结构属性名数据类型宽度小数位数编号（主码）char8姓名char8读者类型int2已借数量int2books表（图书信息表）结构属性名数据类型宽度编号（主键）char15书名char42作者char8出版社char28出版日期datetime8定价（0）decimal8borrowinf表（借阅信息表）结构属性名数据类型宽度读者编号（外键）char8图书编号（外键）char15借期datetime8还期datetime8主键（读者编号，图书编号，借期）视图overdue（超期信息）列名对应基本表的列名读者编号Reaers.编号读者姓名Readers.姓名图书编号Books.书名书名Books.应还日期Borrwinf.借期+readrtype期限1. 创建基本表books。CREATETABLEbooks(图书编号CHAR(15)PRIMARYKEY,书名CHAR(42),作者CHAR(8),出版社CHAR(28),出版日期DATETIME,价格float(8);2. 创建基本表borrowinf。CREATETABLEborrowinf(读者编号CHAR(10),图书编号CHAR(15),借期DATETIME,还期DATETIME,PRIMARYKEY(读者编号,图书编号),FOREIGNKEY(读者编号)REFERENCESreaders(读者编号),FOREIGNKEY(图书编号)REFERENCESbooks(图书编号);3. 创建视图overdue（超期信息）。Create view overdue as SELECT 图书编号 FROM borrowinf WHERE 还期04. 查询姓名为王立群的读者的借阅信息。SELECT图书编号,借期,还期FROMborrowinfWHERE读者编号IN(SELECT读者编号FROMreadersWHERE姓名=王立5. 查询书名中包含文字程序设计的图书信息。SELECT * FROM books WHERE 书名 LIKE%程序设计6. 查询各出版社的馆藏书数量。SELECT COUNT(DISTINCT 图书编号) FROM books7. 查询2004-1-1和2004-12-31之间作者为梁晓峰的图书借阅情况。SELECT * FROM borrowinf WHERE 借期2007-1-1AND 还期 2007-12-31 AND 图书编号 IN (SELECT 图书编号 FROM books WHERE 作者=梁晓峰)8.创建存储过程计算用户指定图书的价格