综合练习题v1.doc

一、基本知识1. 了解数据库、数据库管理系统、数据库系统、关系数据库概念2. 理解关系规范化概念？关系规范过程三个范式理论？3. 数据库对象的命名格式：服务器名.数据库名.架构名.对象名4. 批处理：用户传递给服务器的一个完整的语句组，以GO为结束标志5. SQL SERVER提供了许多函数：。理解Substring(),getdate(), datepart(), datediff(), dateadd(),day(),month(),year()select substring(sql server2008,1,3)聚合函数avg(),sum(),min(),max(),count(*) count(列名)注意: count(*) 和count(列名) 是否有区别6. 流程控制语句：数据库中，将多个t-sql语句组合成一个语句块，应包含在begin。end中；理解If else 语句，Case语句等,break,continute语句用法 （1）在读者信息表中，显示读者的姓名和性别，性别如果为男，显示M；如果为女，则显示F（2）分析下面脚本，思考多少条记录插入到表tblTemp表中？CREATE TABLE tblTemp( TempID integer not null,TempDate datetime not null)GODECLARE count intSET count = 1WHILE count 3 BEGIN CONTINUE /* continue语句可以使程序跳过continute语句后面的语句，回到while循环的第一行命令*/END ELSE INSERT tblTemp VALUES(count,GETDATE() END7. print语句作用8. 数据库文件9. 系统数据库：master,model,msdb, tempdb10. 数据库快照概念：是一个只读、静态的数据库视图11. 掌握用sql语句创建表（注意数据类型的使用，注意约束的使用）12. 数据类型：（系统数据类型和用户定义数据类型） Bigint,int(integer),smallint,tinyint,decimal(p,s), datetime 新增的：date(定义日期)，time Datetime2(n) (n为秒的小数部分指定数字的位数，默认为7) Datetimeoffset(n) 采用24小时制并可识别时区的一日内时间相组合的日期13.理解DML触发器概念，理解deleted表和inserted表？ 14.用户定义数据类型：CREATE TYPE USER_TYPE FROM VARCHAR 注意：只能删除已经创建但未被使用的用户定义数据类型；15.修改表结构：例如，要为表tblreaders中增加家庭地址一列，如何修改？16.熟练掌握select语句的主要子句: Select pall|distinct top (n) percent 列 From table Where Group by Having Order by掌握：通配符的含义：%，_, , between.and Order by 子句可以按一列或多列对查询结果进行排序显示；例如：显示得到以下结果，按照出版社进行升序排列，对同一个出版社按照价格降序排列，sql 语句如何实现？ 17.熟练掌握子查询，联接查询（inner join | left outer join |right outer join | full join18.集合运算：union, except, intersectselect teacherid,tname,sex from tblteachers union all select studentid,sname,sex from tblstudents19.事务概念，特点？sql server如何管理事务？20.熟练掌握用sql语句创建视图； 掌握创建视图的一些注意事项：（1） 只能在当前数据库中创建视图（2） 定义的视图不能包行compute、compute by子句，（3） 定义的视图查询不能包含order by子句，除非在select语句的列表中有一个top子句（4） 定义视图中with check option选项（5）create view v_test with schemabinding as select name,department from dbo.user （至少必须指出架构名）（6）with schemabinding ：不能按照将影响视图定义的方式修改基表或表，必须首先修改或删除视图定义本身，才能删除将要修改的表的依赖关系21.掌握存储过程的基本概念，学会编写简单的存储过程22.SQL Server2008支持的用户定义函数：标量函数、表值函数和内置函数，弄清三种函数的特点，学会创建标量函数、表值函数和内置函数。23.了解空间数据类型 和xml数据类型24. 在sql server 2008数据库中实现XML数据类型提供了许多支持，其中三个比较基本的方法query(),value(),exist()，请分别说明这三种方法的用途？25.索引概念（聚集索引和非聚集索引）二、学生成绩管理系统表结构简述如下表结构文字说明系信息表：（系号，主键），（系名，不可为空），（系介绍）学生信息表：（学号，主键），（所在系号，外键），（姓名，不可为空），（性别，只存0或者1，分别代表男或女），（出生日期），（个人简介）科目信息表：（科目ID，主键），（科目名称）成绩信息表： (成绩ID，主键)，（学号，外键），（科目ID，外键），（整型，存第几学期），（成绩）1. 用SQL语句数据查询：查找出成绩优秀（成绩高于90分）的学生信息，查询结果为学号、姓名、科目、成绩。Select xh,xm,km,cjFrom cjxx,xsxx,kmxxwhere cjxx.xh=xsxx.xh and kmxx.id=cjxx.id and cj902. 参加奖学金评比，请统计出平均分要求在90分以上而且各科没有不及格（成绩=60分）的同学名单Select *From 3.因考试科目office 2000考题难度很大，学生不及格人数很多，考虑到学生平时上机作业完成情况良好，教师对学生统一加分，加分规则是仅限office 2000科目，每人都加2分，但加完的分数不得超过100分。用SQL语句完成加分规则提醒：分情况更新数据即使用两次UPDATE的操作Begin tranDeclare n int set n=0Update T_Score set score=score+2 where subjected in(select subjected from T_subject where subjectname= office 2000 Set n=n+errorUpdate T_Score set score=score-2 where score100Set n=n+errorIf n0Rollback tranCommit tran4.用SQL语句创建视图Vtest：在学生管理系统的使用中学生成绩经常需要被查询，为了简化查询时关联多张表，决定创建学生成绩的视图，该视图要求返回的结果集有学号、姓名、科目名称、成绩。Create view Vtest asSelect xh , name , subjectName , scoreFrom T_score t1 join T_Student t2 on t1.xh=t2.xhjoin T_Subject t3 on t1.subjectid=t3.Subjectid5.用SQL语句创建存储过程sp_test：在学生管理系统的使用中教师希望输入自己任教的科目名称查询出成绩优秀（高于90分）学生姓名和成绩，以便于对照学生的平时成绩。Creat proc sp_test Declare n varchar(20) AsSelect name , score From T_Score t1Join T_Student t2 on t1.xh=t2.xhWhere subjectid in(select subjectidFrom T_subjectWhere subjectName=n) and score90提醒：可以使用4创建的视图Vtest，输入参数为科目名称，结果为一个集合包含了学生姓名和成绩两列。Creat proc sp_test Declare n varchar(20) AsSelect name, scoreFrom VtestWhere subjectname=n6.用SQL语句创建触发器Tr_Student_Sex：学生管理系统设计时原定为性别字段char(1)当存储0时代表男，存储1代表女，存储2代表保密，但在使用中发现了性别字段被意外的修改为a，K，3等不是系统预定的数据；现需要在学生信息表上创建一个更新的触发器，当性别字段受到修改时，如果修改后数据不是（0，1，2）范围内则返回一个错误（性别输入不规范）并取消此次修改。Create trigger Tr_Student_Sex on T_StudentFor update as declare sex char(1)Select sex=sex from insertedIf sex!=1 and sex!=2 and sex!=3beginRollback tran Raiserror(性别输入不规范16,-1)end提示：仅对修改后数据不是（0，1，2）范围内回滚事务7.用SQL语句创建用户自定义函数fn_test：在学生管理系统的使用中教师希望输入自己任教的科目名称查询出高于指定分数的学生人数或者低于指定分类的学生人数，以便于控制和管理考试体制。提醒：可以使用4创建的视图Vtest，输入参数为科目名称，指定的分数，查询类型（高于分数还是低于分类），结果为满足条件的学生人数。参考示例：（示例为做题参考，答题并不要受到示例限制，只要能满足要求即可）fn_Score_Count(office 2000,60,-1)表示查找sql server 2000分数低于60的学生人数；fn_Score_Count(office 2000,90,1) 表示查找sql server 2000分数高于90的学生人数create function fn_test(subjectname varchar(20), score numeric(18,2)returns int asbegin declare n int select n= count(xh)from Vtestwhere subjectname=subjectname and score scoreend 2.根据TSGL数据库以下三个表图书信息表(tblbooks)字段名称字段类型说明BookID字符串，长度20图书的唯一编号，主键Bname字符串，长度50图书的唯一名称，唯一性Author可变字符串，长度为30图书编著者的名称Press可变字符串，长度为30图书的出版社Pricemoney图书价格读者信息表(tblReaders)字段名字段类型说明ReaderID字符串，长度10读者唯一编号，主键Rname字符串，长度8读者姓名，非空置Sex字符串，长度2读者性别，取值只能为男或女Department可变字符串，长度30读者所在部门Phone字符串，长度8读者宿舍电话借阅信息表tblLending字段名称字段类型说明ReaderID字符串，长度10读者编号，外键参照读者信息表BookID字符串，长度20图书编号，外键参图书信息表Borrowdatedate借出日期，非空值，默认为当前日期Returndatedate归还日期，归还日期晚于借出日期 主键（ReaderID, BookID）A. 根据上述数据库的设计，写出创建读者信息表、图书信息表、借阅信息表的SQL语句Create table tblbooks ( BookID char(20) primary key, Bname char(50), Author varchar(30), Press varchar(30), Price money)Create table tblReaders( ReaderID char(10) primary key, Rname char(8) not null, Sex char(2) check (Sex=男 or Sex=女), Department varchar(30), Phone char(8)Create table tb1Lending( ReaderID char(10) , BookID char(20) , Borrowdate date not null default(getdate(), Returndate date, primary key (ReaderID,BookID), foreign key(ReaderID)references tblReaders(ReaderID), foreign key(BookID)references tblbooks(BookID)create trigger a on tb1Lendingfor insert as declare n date,m dateselect n=Borrowdate,m=Returndate from insertedif nmbegin rollback tranRaiserror(归还日期晚于借出日期,16,-1)endB. 创建视图vtest，显示书名、作者、出版社、价格、读者姓名、借书日期和还书日期。 Create view vtest as( select Bname , Author , Press , Price, Rname, Borrowdate, Returndate From tb1Lending t1 Join tb1Book t2 on t1.BookID=t2.BookIDJoin tb1Reader t3 on t1.ReaderID = t3.ReaderID)创建一个存储过程，执行该存储过程能实现一个超期借阅情况列表，其中包括读者编号、姓名、所在部门、超期天数、应罚款额。执行该存储过程。(限制读者借100天，如果超过天数，每天按0.5元计算) create proc chaoqi asselect t1.ReaderID,Rname,Department,DATEDIFF(day,Borrowdate,Returndate) as 超期天数,DATEDIFF(day,Borrowdate,Returndate)*0.5 as 应罚金额from tb1Lending t1join tblReaders t2 on t1.ReaderID=t2.ReaderID C. 查询借阅了出版社为人民邮电大学出版社，并且书名中包含win7字符串的图书，显示姓名、书名、出版社、借出日期及归还日期。Select ReaderID,Bookname,Press,Brorrowday,ReturndayFrom tb1Lending t1Join tb1books t2 on t1.BookID=t2.BookIDJoin tb1reader t3 on t1.ReaderID =t2.ReaderIDWhere bookname=%win7% and Press=人民邮电大学出版社D. 查询所有图书的借阅情况，要求包括被借阅了图书和没被借阅的图书，显示它们的图书号、书名、读者姓名和借出日期,并按借阅日期最近的排在前面的方式排列 Select BookID,Bookname,Readername,BrorrwdayFrom tb1Lending t1Join tb1books t2 on t1.BookID=t2.BookIDJoin tb1reader t3 on t1.ReaderID =t2.ReaderIDOrder by Brorrowday descE. 查询借阅图书网络数据库教程读者的姓名。Select ReadernameFrom tb1Lending t1Join tb1books t2 on t1.BookID=t2.BookIDJoin tb1reader t3 on t1.ReaderID =t2.ReaderIDWhere BookName= 网络数据库教程F. 显示各个出版社平均价格在99元及以下的图书信息 Select * From tb1books Where Price99 Group by PressG. 编写一个借书的存储过程。要求：每个读者限借3本图书。如果超出已经借3本了，提示“不允许借阅！，否则允许借阅，每借一本书，应相应的将图书数量减一，如果